From: jj297 on
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
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
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
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
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