just for learning sake i want to create item summary report via sql in v4.
SELECT [MenuItemName],CONVERT(INT,SUM([Quantity])) as Qty,[Price],CONVERT(INT,[Price]*(SUM([Quantity]))) as TAmt
FROM [Orders]
LEFT OUTER JOIN [MenuItems] on [Orders].[MenuItemId] = [MenuItems].[Id]
WHERE [CreatedDateTime] > '{Start}' AND [CreatedDateTime] < '{End}' AND DecreaseInventory = 1 AND CalculatePrice <> 0
GROUP BY [MenuItems].[GroupCode],[MenuItems].[Tag],[MenuItemName],[Price]
ORDER BY [MenuItems].[GroupCode],[MenuItems].[Tag]
select SUM(GT) FROM
(SELECT CONVERT(INT,[Price]*(SUM([Quantity]))) as GT
FROM [Orders]
LEFT OUTER JOIN [MenuItems] on [Orders].[MenuItemId] = [MenuItems].[Id]
WHERE [CreatedDateTime] > ‘{Start}’ AND [CreatedDateTime] < ‘{End}’ AND DecreaseInventory = 1 AND CalculatePrice <> 0
GROUP BY [MenuItems].[GroupCode],[MenuItemName],[Price]) t
Sorry, do you have a question here? What are your expected results? What is the results of the Pivot Report? Is it correct? Are you trying to match the results in SQL?
@madiha It’s not SQL but this does the same thing using Report Tags and no Pivot Table required:
[Item Sale per Group:1,1,1,1]
>Group|MenuItemName|Qty|Total
{REPORT ORDER DETAILS:O.ItemGroup,O.MenuItemName,O.Quantity,O.Total}
>||Grand Total: {REPORT ORDER DETAILS:O.Quantity.Sum}|Grand Total: ${REPORT ORDER DETAILS:O.Total.Sum}
declare @dat_WorkPeriod_Beg Datetime = '2015-07-01T06:00:00.000'
declare @dat_WorkPeriod_End Datetime = '2015-07-01T18:00:00.000'
set @dat_WorkPeriod_Beg = '{Start}'
set @dat_WorkPeriod_Beg = '{End}'
declare @tbl_Orders table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Group] varchar(255)
,[Item] varchar(255)
,[Qty] int
,[TAmt] money
)
INSERT INTO @tbl_Orders
SELECT
[GroupCode] as[Group]
--, m.[Tag]
, [MenuItemName] as [Item]
, CONVERT(INT,SUM([Quantity])) as [Qty]
--, [Price]
, [Price]*SUM([Quantity]) as [TAmt]
FROM [Orders] o
LEFT JOIN [MenuItems] m on m.[Id] = o.[MenuItemId]
WHERE 1=1
and [CreatedDateTime] >= @dat_WorkPeriod_Beg
and [CreatedDateTime] <= @dat_WorkPeriod_End
and DecreaseInventory = 1
and CalculatePrice <> 0
GROUP BY m.[GroupCode], [MenuItemName], [Price]
ORDER BY m.[GroupCode], [MenuItemName]
INSERT INTO @tbl_Orders
SELECT
'',''
,SUM([Qty]) as [Qty]
,SUM([Tamt]) as [Tamt]
FROM @tbl_Orders
SELECT
[Group]
,[Item]
,[Qty]
,[TAmt]
FROM @tbl_Orders
I always do that. It has no functional purpose. It saves me having to comment out WHERE when I want to remove all conditions, or remove some but leave others in place. It lets me do this easily:
WHERE 1=1
and [CreatedDateTime] >= @dat_WorkPeriod_Beg
and [CreatedDateTime] <= @dat_WorkPeriod_End
-- and DecreaseInventory = 1 -- condition removed
and CalculatePrice <> 0
Your report does not exclude voids (I guess) but QMcKay’s script does. Better create a new work period, make some sales, gifts, voids and after each transaction review how both reports changes. I think you’ll easily spot the difference.
i have checked with new work period and spotted the difference:
with SQL:
if item voided ----- quantity decreases
if item given discount — original price stated
if item gifted ---- quantity decreases
with Pivot:
if item voided ----- quanity doesnt decrease
if item given discount — price after discount stated
The [Orders] table is unaware of Discounts, unless the discount is given via an Order Tag. But in that case, you would need to parse the JSON data in the [OrderTags] field to determine the discount given by the specific tag(s), and subtract the value from the [Price] field. That is unless you are in the habit of adding the Order Tag Price to the Product Price, which may or may not be the case for you. Even if you add the Tag Price to the Order, the [Price] field will still contain the original Product Price.
You could also join the [Tickets] table along with the [AccountTransactions] table and the [AccountTransactionValues] table to determine effective price, but this is Ticket-based, not Order-based.
Either way, all of the above is rather complex, which is a good reason to use Report Tags instead.
It is up to you how you want to track Voids and Gifts via SQL. If you want to track Gifts, remove this:
and [CalculatePrice] <> 0
Just know that your [Qty] will be correct, but your [TAmt] will be out, since you received no money for Gifted items.
A similar thing could be said for [DecreaseInventory]=1 … remove that condition to track Void [Qty].
