January 28, 2009 at 2:36 pm
I have query which worked like charm :), 'till they asked for more.
Basically, it looks like:
_____________________
SELECT Item.ItemLookupCode, Item.Description, Supplier.SupplierName, SUM(TransactionEntry.Quantity) AS Sold,
Item.Quantity AS OnHand,
SUM(PurchaseOrderEntry.QuantityOrdered - PurchaseOrderEntry.QuantityReceivedToDate) AS OnOrder
FROM TransactionEntry
LEFT JOIN Item ON TransactionEntry.ItemID = Item.ID
LEFT JOIN PurchaseOrderEntry ON Item.ID = PurchaseOrderEntry.ItemID
LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID
LEFT JOIN SupplierList WITH(NOLOCK) ON Supplier.ID = SupplierList.SupplierID AND Item.ID = SupplierList.ItemID
LEFT JOIN "Transaction" ON TransactionEntry.TransactionNumber = "Transaction".TransactionNumber
WHERE Item.ID = 932
AND (CONVERT(VARCHAR(10),"Transaction".Time,121) BETWEEN '2009-01-17' AND '2009-01-23')
GROUP BY Item.ItemLookupCode, Item.Quantity, Item.Description, Supplier.SupplierName
_____________________
Now, I need to show SUM(TransactionEntry.Quantity) for last week, for week before last, and two weeks ago week.
So, instead of one column Sold in report, I need Sold1, Sold2, Sold3 based on time interval.
Any idea,
Brano
January 28, 2009 at 2:45 pm
It sounds like you could do that in an inline query.
Basically, write the query for the sum, put that in parentheses, and put it in the Select list just as if it were a column.
Does that make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2009 at 3:04 pm
branovuk (1/28/2009)
I have query which worked like charm :), 'till they asked for more.Basically, it looks like:
_____________________
SELECT Item.ItemLookupCode, Item.Description, Supplier.SupplierName, SUM(TransactionEntry.Quantity) AS Sold,
Item.Quantity AS OnHand,
SUM(PurchaseOrderEntry.QuantityOrdered - PurchaseOrderEntry.QuantityReceivedToDate) AS OnOrder
FROM TransactionEntry
LEFT JOIN Item ON TransactionEntry.ItemID = Item.ID
LEFT JOIN PurchaseOrderEntry ON Item.ID = PurchaseOrderEntry.ItemID
LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID
LEFT JOIN SupplierList WITH(NOLOCK) ON Supplier.ID = SupplierList.SupplierID AND Item.ID = SupplierList.ItemID
LEFT JOIN "Transaction" ON TransactionEntry.TransactionNumber = "Transaction".TransactionNumber
WHERE Item.ID = 932
AND (CONVERT(VARCHAR(10),"Transaction".Time,121) BETWEEN '2009-01-17' AND '2009-01-23')
GROUP BY Item.ItemLookupCode, Item.Quantity, Item.Description, Supplier.SupplierName
_____________________
Now, I need to show SUM(TransactionEntry.Quantity) for last week, for week before last, and two weeks ago week.
So, instead of one column Sold in report, I need Sold1, Sold2, Sold3 based on time interval.
Any idea,
Brano
Something like:
SELECT Item.ItemLookupCode, Item.Description, Supplier.SupplierName,
SUM(TransactionEntry.Quantity) AS Sold,
SUM(CASE WHEN "Transaction".Time BETWEEN @Begindate and @BegindateWeekBefore THEN TransactionEntry.Quantity ELSE 0 END) AS Sold1,
SUM(CASE WHEN "Transaction".Time BETWEEN @BegindateWeekBefore and @Begindate2WeeksBefore THEN TransactionEntry.Quantity ELSE 0 END) AS Sold2,
SUM(CASE WHEN "Transaction".Time BETWEEN @Begindate2WeeksBefore and @Begindate3WeeksBefore THEN TransactionEntry.Quantity ELSE 0 END) AS Sold3,
....
You should pre-calculate
BegindateWeekBefore, @Begindate2WeeksBefore,@Begindate3WeeksBefore
From @BeginDate 😉
Good Luck!
e
* Noel
January 28, 2009 at 3:26 pm
Thank you, thank you, one step less :)!
I tried with actual dates:
SUM(CASE WHEN "Transaction".Time BETWEEN '2009-01-10' and '2009-01-28' THEN TransactionEntry.Quantity ELSE 0 END) AS Sold1,
SUM(CASE WHEN "Transaction".Time BETWEEN '2008-12-17' and '2009-01-09' THEN TransactionEntry.Quantity ELSE 0 END) AS Sold2,
SUM(CASE WHEN "Transaction".Time BETWEEN '2008-10-17' and '2008-12-16' THEN TransactionEntry.Quantity ELSE 0 END) AS Sold3
and it works!
Now, calculation, like you said. I will try tonight, this query is going into report services, I hope I can manage to calculate weeks there (or here?).
Thank you, you saved my day :)!
Brano
January 28, 2009 at 3:45 pm
Glad it worked for you 😀
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply