not getting desired results - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

not getting desired results

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-21 : 09:50:40

This query gives me what I want:

SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd
FROM Pendingdiary
GROUP BY doc, totovrs, totpnds, totovr

When I add the two fields, weekdat and dowrdat as such the totpnd column isn't totalled. What am I doing wrong?

SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd, weekdat, dowrdat
FROM Pendingdiary
GROUP BY doc, totovrs, totpnds, totovr, weekdat, dowrdat

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 11:50:29

Can you post some sample data and your expected result.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-21 : 14:12:33

This query

SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd
FROM Pendingdiary
GROUP BY doc, totovrs, totpnds, totovr

produces this:


doc  totovrs  totpnds  totovr  totpnd
007	0	0	0	5
008	0	0	0	11
009	0	0	0	14
016	0	0	0	11
019	0	0	2	26

When I add the columns weekdat and dowrdat to the select statement I get this:


doc  totovrs  totpnds  totovr  totpnd   weekdat                  dowrdat
007	0	0	0	4	5/14/2010 11:52:09 AM	5/28/2010 11:52:09 AM
007	0	0	0	1	5/14/2010 11:52:09 AM	5/28/2010 11:52:09 AM
008	0	0	0	2	5/14/2010 11:52:34 AM	5/28/2010 11:52:34 AM
008	0	0	0	3	5/14/2010 11:52:34 AM	5/28/2010 11:52:34 AM
008	0	0	0	3	5/14/2010 11:52:34 AM	5/28/2010 11:52:34 AM
008	0	0	0	1	5/14/2010 11:52:34 AM	5/28/2010 11:52:34 AM
008	0	0	0	1	5/14/2010 11:52:34 AM	5/28/2010 11:52:34 AM
008	0	0	0	1	5/14/2010 11:52:35 AM	5/28/2010 11:52:35 AM
009	0	0	0	3	5/14/2010 11:50:26 AM	5/28/2010 11:50:26 AM
009	0	0	0	2	5/14/2010 11:50:26 AM	5/28/2010 11:50:26 AM
009	0	0	0	2	5/14/2010 11:50:26 AM	5/28/2010 11:50:26 AM
009	0	0	0	3	5/14/2010 11:50:26 AM	5/28/2010 11:50:26 AM
009	0	0	0	1	5/14/2010 11:50:26 AM	5/28/2010 11:50:26 AM
009	0	0	0	3	5/14/2010 11:50:26 AM	5/28/2010 11:50:26 AM
016	0	0	0	2	5/14/2010 11:51:40 AM	5/28/2010 11:51:40 AM
016	0	0	0	5	5/14/2010 11:51:40 AM	5/28/2010 11:51:40 AM
016	0	0	0	2	5/14/2010 11:51:40 AM	5/28/2010 11:51:40 AM
016	0	0	0	2	5/14/2010 11:51:40 AM	5/28/2010 11:51:40 AM
019	0	0	2	4	5/14/2010 11:51:16 AM	5/28/2010 11:51:16 AM
019	0	0	2	5	5/14/2010 11:51:16 AM	5/28/2010 11:51:16 AM
019	0	0	2	3	5/14/2010 11:51:16 AM	5/28/2010 11:51:16 AM
019	0	0	2	5	5/14/2010 11:51:16 AM	5/28/2010 11:51:16 AM

the totpnd column isn't totaling.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-21 : 14:27:58

Expected output?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-21 : 14:46:05

Oops sorry. I want the column totpnd totaled with weekdat and dowrdat listed too. I plan to put the results into a new table.


doc  totovrs  totpnds  totovr  totpnd   weekdat                 dowrdat
007	0	0	0	 5     5/14/2010 12:00:00 AM	5/28/2010 12:00:00 AM
008	0	0	0	11     5/14/2010 12:00:00 AM	5/28/2010 12:00:00 AM
009	0	0	0	14     5/14/2010 12:00:00 AM	5/28/2010 12:00:00 AM
016	0	0	0	11     5/14/2010 12:00:00 AM	5/28/2010 12:00:00 AM
019	0	0	2	26     5/14/2010 12:00:00 AM	5/28/2010 12:00:00 AM

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-21 : 16:30:13

try this..

select doc
, totovrs
, totpnds
, totovr
, sum(totpnd)
, dateadd(d, datediff(d, 0, weekdat), 0) 
, dateadd(d, datediff(d, 0, dowrdat), 0)  
from Pendingdiary
group by 
doc
, totovrs
, totpnds
, totovr
, dateadd(d, datediff(d, 0, weekdat), 0) 
, dateadd(d, datediff(d, 0, dowrdat), 0)

I have to say...if you had provided some sample data and expected output with your OP, this problem would have been solved probably hours earlier. Your problem is clear only after you provide sample data and expected ouput. Kindly provide it with your OP going forward.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-21 : 18:00:33

Okay thanks going forward I will post stored procedures and sample data and outcome. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources