Custom Report SOLD ITEMS

Version 4 V4 Question
1

if I want to know the sold items during a work period… could this be the correct SQL?

SELECT [MenuItemName],CONVERT(INT,SUM([Quantity])) as Cantidad

FROM [Orders]

WHERE [CreatedDateTime] > '{Start}' AND [CreatedDateTime] < '{End}'

GROUP BY [MenuItemName]

ORDER BY Cantidad DESC

THANKS!!!

G.

2

It looks good to me. But I am not very good with SQL Yet :stuck_out_tongue: So I am probably wrong haha

I plan to become a SQL guru soon! Then I can perform some ninja moves on my database.

3

The SQL fotr sure is good, I have tested it with SQL STUDIO… I am sorry I was not clear enough, I ment if the table where I am extracting the data is ok…

I am asking this because in the SQL studio I get all the sold items, but in SAMBA with the very same SQL script the report is empty… and I have sold 2 items by now…

THANKS!!!

G.

4

yes, using Orders table is a great idea.
maybe you can add one more constraint to exclude voided items.

For example

WHERE [CreatedDateTime] > '{Start}' AND [CreatedDateTime] > '{End}' 
      AND DecreaseInventory = 1

or you can use this to display void items.

WHERE [CreatedDateTime] > '{Start}' AND [CreatedDateTime] > '{End}'  
      AND DecreaseInventory = 0

or this one to display “free of charge” items (eg. gifts)

WHERE [CreatedDateTime] > '{Start}' AND [CreatedDateTime] > '{End}' 
      AND CalculatePrice = 0
1 Like
5

THANKS!!!

I was just going to do that!!! you read my mind!!! Dr Charles Xavier…

LOL

G.

6

I am convinced he really can. He has read my mind several times :stuck_out_tongue:

7

@Emre, I noticed that CALCULATEPRICE is allways 0

Is that correct?

G.

8

I don’t even need cerebro :slight_smile:

It becomes 1 when you gift items.

9

calculateprice does not become 1 when you gift… i pretty sure that…

G…

10

Sorry :slight_smile: It should be always 1 and it becomes 0 when you gift items. This is a boolean (true/false) value.

1 Like