How would I write this?
in [Using MS SQL Server]
|
From: Plamen Ratchev on 19 May 2010 22:53 Your sample data does not seem to correspond to the desired results. After cleaning your data and code a bit, commenting out the components that you did not provide (like the function and linked server), running the code results in this: doc totovrs totpnds totovr totpnd weekdat dowrdat ---- ----------- ----------- ----------- ----------- ----------------------- ----------------------- 009 0 6 0 0 2010-05-14 00:00:00.000 2010-05-28 00:00:00.000 200 0 6 0 0 2010-05-14 00:00:00.000 2010-05-28 00:00:00.000 009 0 0 0 6 2010-05-14 00:00:00.000 2010-05-28 00:00:00.000 200 0 0 0 6 2010-05-14 00:00:00.000 2010-05-28 00:00:00.000 Not really sure how you need this data summarized. Here is just a guess. eliminate both insert queries into PendingDiary and use the following insert query: INSERT INTO PendingDiary (doc, totovrs, totpnds, totovr, totpnd, weekdat, dowrdat) SELECT doc, COUNT(CASE WHEN clear = 'O' AND SDW = 'Y' THEN doc END) AS totovrs, COUNT(CASE WHEN SDW = 'Y' THEN doc END) AS totpnds, COUNT(CASE WHEN clear = 'O' AND SDW = 'N' THEN doc END) AS totovr, COUNT(CASE WHEN SDW = 'Y' THEN doc END) AS totopnd, weekdat, dowrdat FROM #temppend WHERE SDW IN ('Y', 'N') GROUP BY doc, weekdat, dowrdat; This results in the following summarized data: doc totovrs totpnds totovr totpnd weekdat dowrdat ---- ----------- ----------- ----------- ----------- ----------------------- ----------------------- 009 0 6 0 6 2010-05-14 00:00:00.000 2010-05-28 00:00:00.000 200 0 6 0 6 2010-05-14 00:00:00.000 2010-05-28 00:00:00.000 -- Plamen Ratchev http://www.SQLStudio.com
From: jj297 on 20 May 2010 07:58
On May 19, 10:53 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > Your sample data does not seem to correspond to the desired results. > After cleaning your data and code a bit, commenting out the components > that you did not provide (like the function and linked server), > running the code results in this: > > doc totovrs totpnds totovr totpnd weekdat dowrdat > ---- ----------- ----------- ----------- ----------- > ----------------------- ----------------------- > 009 0 6 0 0 2010-05-14 > 00:00:00.000 2010-05-28 00:00:00.000 > 200 0 6 0 0 2010-05-14 > 00:00:00.000 2010-05-28 00:00:00.000 > 009 0 0 0 6 2010-05-14 > 00:00:00.000 2010-05-28 00:00:00.000 > 200 0 0 0 6 2010-05-14 > 00:00:00.000 2010-05-28 00:00:00.000 > > Not really sure how you need this data summarized. Here is just a > guess. eliminate both insert queries into PendingDiary and use the > following insert query: > > INSERT INTO PendingDiary > (doc, totovrs, totpnds, totovr, totpnd, weekdat, dowrdat) > SELECT doc, > COUNT(CASE WHEN clear = 'O' AND SDW = 'Y' THEN doc END) AS > totovrs, > COUNT(CASE WHEN SDW = 'Y' THEN doc END) AS totpnds, > COUNT(CASE WHEN clear = 'O' AND SDW = 'N' THEN doc END) AS > totovr, > COUNT(CASE WHEN SDW = 'Y' THEN doc END) AS totopnd, > weekdat, > dowrdat > FROM #temppend > WHERE SDW IN ('Y', 'N') > GROUP BY doc, weekdat, dowrdat; > > This results in the following summarized data: > > doc totovrs totpnds totovr totpnd weekdat dowrdat > ---- ----------- ----------- ----------- ----------- > ----------------------- ----------------------- > 009 0 6 0 6 2010-05-14 > 00:00:00.000 2010-05-28 00:00:00.000 > 200 0 6 0 6 2010-05-14 > 00:00:00.000 2010-05-28 00:00:00.000 > > -- > Plamen Ratchevhttp://www.SQLStudio.com Plamen, Thanks so much I'm sorry I left out so much. I inherited this code and as you can see it's so confusing to me. The Programmer retired so I'm left figuring it out. Anyway what you presented worked it wasn't coming out tallied but now it is. Sorry for bothering you with this as it was a complicated procedure. Thanks for you help. |