Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
usafelix
Posting Yak Master
165 Posts |
 Posted - 2014-09-03 : 14:28:30
|
| I am spend 2 day to find the problem but no finding the answer. Anyone can help me, I am run this query by two time, first time, I don't have use sum command and normal to display the qty and amt by each transaction , all the information is correct. firs time query and within 22:00 - 23:00shop time sku qty amountkc001 22:10 book 1 $10 kc001 22:45 apple 2 $20 total not use sum command.second time query - wrong I am use the sum command then all qty and amount have wrong calcuation in qty and amount , why ? it is grouping set issue or time filter happen wrong ? shop ime sku qty amountkc001 22:10 book 10 $ 100kc001 22:45 apple 20 $2000total 30 $3000-------------------------------SELECT CASE WHEN a.shopcode IS NULL THEN 'Grand :' WHEN a.shopcode IS NOT NULL AND a.sku IS NULL THEN 'Total :' ELSE ' ' + a.shopcode END shopcode, a.createdatetime, a.sku, sum(a.salesqty), --if no use sum that qty is correct sum(a.actualsalesamt) , -- if no use sum that amt is correct FROM dbo.xsodetail AS aINNER JOIN dbo.SinglePieceMask AS b ON b.sku = a.sku WHERE Convert(varchar, GETDATE(),112) = a.txdate -- Need major refactoring and LEFT(CONVERT(TIME(0),GETDATE()),2) = DATEPART(hour, a.createdatetime) -- Need major refactoringGROUP BY GROUPING SETS ( (a.shopcode), (a.shopcode, a.createdatetime, a.sku,a.salesqty, a.actualsalesamt), () );-------------------------------------------------- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-03 : 15:25:08
|
| Please post your question like is shown here for us to help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|
|
Urgent anyone help !!!