sum qty and amount is wrong , why ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

sum qty and amount is wrong , why ?

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 01:00:29

This query result is below , who can help check which error on sum?

result 1
shopcode createdatetime sku qty actualsalesamt
mc001 2014-09-02 12:03 0229 1 $10 ok
mc001 2014-09-02 12:04 0239 2 $30 ok
Total :
-------------------------------------------------------------
result 2
i am edit this query in change to sum(qty) and sum(actualsalesamt)
but the actualsaleamt result is very big and wrong. I don't know why ?
shopcode createdatetime sku qty actualsalesamt
mc001 2014-09-02 12:03 0229 1 $3000 wrong
mc001 2014-09-02 12:04 0239 2 $3500 wrong
Total : 3 $6500
----------------------------------------------------------
result 1 this query
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,
a.salesqty,
a.actualsalesamt
FROM xsodetail a , singlepiecemask b
where Convert(varchar, GETDATE(),112)= a.txdate and LEFT(CONVERT(TIME(0),GETDATE()),2) = DATEPART(hour, a.createdatetime) and a.sku = b.sku
GROUP BY GROUPING SETS((a.shopcode),(a.shopcode,a.createdatetime, a.sku,a.salesqty,a.actualsalesamt),())

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-02 : 03:28:25

Learn how to indent your code for easier reading.
Is your objective to filtering by the hour but aggregating by the millisecond?

-- Original code, indented
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,
		a.salesqty,
		a.actualsalesamt 
FROM		dbo.xsodetail AS a
INNER 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 refactoring
GROUP BY	GROUPING SETS
		(
			(a.shopcode),
			(a.shopcode, a.createdatetime, a.sku,a.salesqty, a.actualsalesamt),
			()
		);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 04:06:09

Dear Sir,

I just want only to filter the hour and desire to omitted the millsecond , how i can edit this query ?

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 04:09:51

I try this indented coding and use of sum(a.salesqty) and sum(a.actualsaleamt) is wrong qty and amount , then without use the sum command, then the qty and amount is correct. why ?

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 04:43:15

I need to filter the current hour with all the sales transaction and different shop code to calcuate the qty and amount , how I can achieve this result ? above the query is cannot handle filter the calucation problem.

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 05:39:05

Anyone can give idea how to edit this query can filter today of sales by current hour for example : current time is 16:39 then extract sales data from 16:00 to 16:59 , and sum of total amount for different shop for boss requested. Above of this query have problem when using the sum command it cannot handle filter hour by qty and amount then the qty is amount is very large and wrong figure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources