Void and gift report by script

V5 Question
1 
Select GroupCode,MenuItemName,isnull(Sold,0) as Sold,isnull(Gift,0) as Gift,isnull(Void,0) as Void from
(select 
    M.GroupCode as GroupCode,
    O.MenuItemName as MenuItemName,
    O.Quantity as Quantity,
	CASE
     WHEN CalculatePrice = 0 and DecreaseInventory = 1 THEN 'Gift'
	 WHEN DecreaseInventory = 0 and CalculatePrice = 0 THEN 'Void'
	 Else 'Sold'
	End as OrderType
  from Orders as O
  inner join MenuItems as M on M.Id = O.MenuItemId 
  where 
  MenuItemName = 'Ankara Döneri') as DataTable
  

  PIVOT(
    Sum(Quantity)
	For OrderType 
	IN ([Sold],[Gift],[Void])
  ) as PivotTable

I have something like this, and it works well. But the problem is, this report gives the total amounts, mean from the first day of the DB to the current day. I need it to give current day report or week or month as we can select on reports screen.

Here is an image of the result of this report

qwe3
qwe3.png950×405 9.69 KB

EDIT: Sorry about wrong ss. This one is what i try to get here.

2

void_gift

Like this?

1 Like
3

You will need to use {REPORT SQL DETAILS:yourquery} and feed {Start} and {End} into the SQL as conditions in the WHERE clause.

2 Likes
4

Sure somethin like urs, probably fix the issue actually. But i also need to restrict report by group code, cuz the customer doesnt want all the staff. They just need two or three group codes to be reported.

If it possible, is there a tutorial or any other topic on forum ?
Or can u share the report code ?

5

I have built a report for a pizza shop before and at the report there was a line somethin like this :

Where Date > '{Start}' and Date < '{End}'

Sorry about i dont know any Sql coding, i tried to place this line to somewhere in my code but no success here.