How would I write this?
in [Using MS SQL Server]
|
From: jj297 on 18 May 2010 14:12 update PendingDiary SELECT doc, totovrs, totpnds, totovr, SUM(totpnd) AS totpnd FROM Pendingdiary GROUP BY doc, totovrs, totpnds, totovr I'm getting incorrect syntax near the keyword 'Select'
From: Plamen Ratchev on 18 May 2010 14:33 What are you trying to do? Just a SELECT statement or an UPDATE? If UPDATE then which column to update and what expression is the source? It is always best to provide CREATE TABLE statements, INSERT statements for sample data, desired results, and the required logic. -- Plamen Ratchev http://www.SQLStudio.com
From: jj297 on 18 May 2010 15:01 On May 18, 2:33 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > What are you trying to do? Just a SELECT statement or an UPDATE? If > UPDATE then which column to update and what expression is the source? > > It is always best to provide CREATE TABLE statements, INSERT > statements for sample data, desired results, and the required logic. > > -- > Plamen Ratchevhttp://www.SQLStudio.com Thanks! Here's some data: Doc Totovrs Totpnds Totovr Totpnd weekdat dowrdat 020 0 0 5 2 5/14/2010 5/28/2010 020 0 0 5 6 5/14/2010 5/28/2010 207 0 0 5 1 5/14/2010 5/28/2010 207 0 0 5 1 5/14/2010 5/28/2010 207 0 0 5 4 5/14/2010 5/28/2010 207 0 0 5 2 5/14/2010 5/28/2010 007 0 0 5 2 5/14/2010 5/28/2010 007 0 0 5 4 5/14/2010 5/28/2010 007 0 0 5 1 5/14/2010 5/28/2010 008 0 0 5 1 5/14/2010 5/28/2010 008 0 0 5 3 5/14/2010 5/28/2010 008 0 0 5 6 5/14/2010 5/28/2010 I would like to have this outcome instead where only one doc is shown and the grand total is in the Totpnd column Doc Totovrs Totpnds Totovr Totpnd weekdat dowrdat 020 0 0 5 8 5/14/2010 5/28/2010 207 0 0 5 8 5/14/2010 5/28/2010 007 0 0 5 7 5/14/2010 5/28/2010 008 0 0 5 10 5/14/2010 5/28/2010 This stored procedure works but it doens't add up the Totpnd column: truncate table PendingDiary insert into PendingDiary Select b.doc, isnull(totos,0) as totovrs, isnull(totps,0) as totpnds, isnull(toto,0) as totovr, isnull(totp,0) as totpnd, sum(totpnd) b.weekdat, b.dowrdat From (select doc, count(doc) as totos, toto = '0' from #temppend where clear = 'O' and SDW = 'Y' group by doc )a right join (select doc, count(doc) as totps, totp = '0', weekdat, dowrdat from #temppend where SDW = 'Y' group by doc,weekdat, dowrdat )b on b.doc = a.doc insert into PendingDiary Select d.doc, isnull(totos,0) as totovrs, isnull(totps,0) as totpnds, isnull(toto,0) as totovr, isnull(totp,0) as totpnd, d.weekdat, d.dowrdat from (select doc, totos = '0', count(doc) as toto from #temppend where clear = 'O' and SDW = 'N' group by doc )c right join (select doc, totps = '0', count(doc) as totp, weekdat, dowrdat from #temppend where SDW = 'N' group by doc, weekdat, dowrdat )d on d.doc = c.doc Update PendingDiary set dowrdat=offices.dbo.endofmonth(weekdat) where dowrdat is null drop table #temppend I've inherited this from a retired programmer. Is there anyway I can add to this stored procedure to Sum(totpnd)
From: Plamen Ratchev on 18 May 2010 16:54 I am not sure if you just need to insert the total. Then this will do: INSERT INTO PendingDiary (doc, totovrs, totpnds, totovr, totpnd, weekdat, dowrdat) SELECT doc, totovrs, totpnds, totovr, SUM(totpnd), weekdat, dowrdat FROM PendingDiary GROUP BY doc, totovrs, totpnds, totovr, weekdat, dowrdat; If you need to change your query, then try this: INSERT INTO PendingDiary (doc, totovrs, totpnds, totovr, totpnd, weekdat, dowrdat) SELECT B.doc, COALESCE(totos, 0), COALESCE(totps, 0), COALESCE(toto, 0), COALESCE(totp, 0), SUM(totpnd), B.weekdat, B.dowrdat FROM ( SELECT doc, COUNT(doc) AS totos, '0' AS toto FROM #temppend WHERE clear = 'O' AND SDW = 'Y' GROUP BY doc ) AS A RIGHT JOIN ( SELECT doc, COUNT(doc) AS totps, '0' AS totp, weekdat, dowrdat FROM #temppend WHERE SDW = 'Y' GROUP BY doc, weekdat, dowrdat ) AS B ON B.doc = A.doc GROUP BY B.doc, COALESCE(totos, 0), COALESCE(totps, 0), COALESCE(toto, 0), COALESCE(totp, 0), B.weekdat, B.dowrdat; -- Plamen Ratchev http://www.SQLStudio.com
From: jj297 on 18 May 2010 20:48
On May 18, 4:54 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > I am not sure if you just need to insert the total. Then this will do: > > INSERT INTO PendingDiary > (doc, totovrs, totpnds, totovr, totpnd, weekdat, dowrdat) > SELECT doc, totovrs, totpnds, totovr, SUM(totpnd), weekdat, dowrdat > FROM PendingDiary > GROUP BY doc, totovrs, totpnds, totovr, weekdat, dowrdat; > > If you need to change your query, then try this: > > INSERT INTO PendingDiary > (doc, totovrs, totpnds, totovr, totpnd, weekdat, dowrdat) > SELECT B.doc, > COALESCE(totos, 0), > COALESCE(totps, 0), > COALESCE(toto, 0), > COALESCE(totp, 0), > SUM(totpnd), > B.weekdat, > B.dowrdat > FROM ( > SELECT doc, COUNT(doc) AS totos, '0' AS toto > FROM #temppend > WHERE clear = 'O' > AND SDW = 'Y' > GROUP BY doc > ) AS A > RIGHT JOIN ( > SELECT doc, COUNT(doc) AS totps, '0' AS totp, weekdat, dowrdat > FROM #temppend > WHERE SDW = 'Y' > GROUP BY doc, weekdat, dowrdat > ) AS B > ON B.doc = A.doc > GROUP BY B.doc, > COALESCE(totos, 0), > COALESCE(totps, 0), > COALESCE(toto, 0), > COALESCE(totp, 0), > B.weekdat, > B.dowrdat; > > -- > Plamen Ratchevhttp://www.SQLStudio.com Thank you I will try this in the morning. I do need to change the query |