OK you Kings of Kode..please help me. I am stumped.
I have several tender types..some are INTERNET orders, some are IN-STORE orders. I am trying to create a report that lists every item sold (for a time period), how many sold, how much it actually sold for, and if it was an internet or in-store sale.
I have the Tender Detailed report by CheckPoint Software..that works pretty well for monthly totals (even though I would LOVE to have the shipping $$ included in those numbers!!! hint hint)
So I looked at that structure to come up with the following SQL query. (ignore the time in the last line..I am trying to get December sales data).
However, there are often TWO tenderEntry.ID number for each transaction because one os for the initial tender amount and another is for the change given. So I am getting 2 items with costs which will screw up my totals.
How do I do this? Arg...I hate myself!
I just want a list of every item sold, $ paid, and internet or in-store sale (I call SaleType). Thats it!
SELECT dbo.[Transaction].Time, dbo.[Transaction].TransactionNumber, dbo.Item.ItemLookupCode, dbo.Item.SubDescription1 AS Brand, dbo.Item.Description AS ITEM, dbo.TransactionEntry.Quantity AS QTYSOLD, dbo.TenderEntry.TenderID, CASE WHEN TenderEntry.TenderId < 6 THEN 'IN-STORE' ELSE 'INTERNET' END AS SaleType, dbo.TenderEntry.Description AS TenderType, dbo.TransactionEntry.Price AS SOLDPRICE, dbo.TransactionEntry.Price * dbo.TransactionEntry.Quantity AS TOTALPRICE
FROM dbo.TenderEntry INNER JOIN dbo.TransactionEntry ON dbo.TenderEntry.TransactionNumber dbo.TransactionEntry.TransactionNumber INNER JOIN dbo.Item ON dbo.TransactionEntry.ItemID = dbo.Item.ID LEFT OUTER JOIN dbo.OrderHistory ON dbo.TenderEntry.OrderHistoryID = dbo.OrderHistory.ID LEFT OUTER JOIN dbo.[Order] ON dbo.OrderHistory.OrderID = dbo.[Order].ID LEFT OUTER JOIN dbo.[Transaction] ON dbo.TenderEntry.TransactionNumber dbo.[Transaction].TransactionNumber
WHERE (dbo.[Transaction].Time BETWEEN CONVERT(DATETIME, '2006-12-01 00:00:00',
102) AND CONVERT(DATETIME, '2006-12-31 00:00:00', 102))