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'

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

On May 18, 2:33*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
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)

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

On May 18, 4:54*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
Thank you I will try this in the morning. I do need to change the
query

On May 18, 8:48*pm, jj297 <nchildress... (AT) gmail (DOT) com> wrote:
Okay I'm back and tried it but getting some errors on the big stored
procedure.

I had to get rid of this the columns after the insert into
pendingdiary as I got an error message:

The select list for the INSERT statement contains more items than the
insert list. The number of SELECT values must match the number of
INSERT columns.

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;

So when I got rid of that it was okay but now I'm getting:
Invalid column name 'totpnd'.
Insert Error: Column name or number of supplied values does not match
table definition.

This is what I have:

INSERT INTO PendingDiary
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;

It doesn't like the sum column

The column does not exist in the source tables, this is why you get an
error. In the SQL you posted that was an alias from a prior column and
then used in the SUM expression. This is something that is not allowed
in SQL Server...

I suggest to post you CREATE TABLE statements, INSERTS for sample
data, and desired results.

--
Plamen Ratchev
http://www.SQLStudio.com

On May 19, 12:46*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
Okay thanks getting everything together will send to you.

On May 19, 1:56*pm, jj297 <nchildress... (AT) gmail (DOT) com> wrote:
Here's the info:

CREATE TABLE CurrentDiary
(
doc Varchar(3) NULL ,
pan VARCHAR(5) NULL ,
hun VARCHAR(5) NULL ,
recno VARCHAR(2) NULL ,
week_start_date DATETIME NULL ,
drycde VARCHAR(2) NULL,
drydat DATETIME NULL,
dryfu VARCHAR(1) NULL ,

)
GO


insert into CurrentDiary ("doc", "pan", "hun", "recno",
"week_start_date", "drycde", "drydat", "dryfu")
values('009','12568','12568' ,'01','5/14/2010','7W','4/7/2010','1')

(here are extra rows)

values('009','18254', '18254', '01', '5/14/2010', '9W', '4/8/2010',
'1')
values('009','18848', '18848', '01', '5/14/2010', '5W', '5/13/2010',
'1')
values('009','19244', '19244', '01', '5/14/2010', '9W', '6/2/2010',
'0')
values('009','19458', '19458', '01', '5/14/2010', '9W', '6/13/2010',
'0')
values('009','20346', '20346', '05', '5/14/2010',
'5W', '5/29/2010','0')
values('200','13654', '13654', '02', '5/14/2010',
'9W', '6/11/2010','0')
values('200','15562', '15562', '03', '5/14/2010',
'5W', '5/30/2010','0')
values('200','16044', '16044', '01', '5/14/2010',
'9W', '5/15/2010','0')
values('200''16244', '16244', '01', '5/14/2010',
'9W', '6/13/2010','0')
values('200''16366', '16366', '02', '5/14/2010',
'5W', '5/19/2010','0')
values('200','14860', '16638', '04', '5/14/2010',
'7W', '4/17/2010','1')


Here's the Pending Diary Table since it has to be truncated:

CREATE TABLE PendingDiary
(
doc (varchar(3) not NULL) ,
totovrs (int, null),
totpnds (int, null),
totovr (int, null),
totpnd (int, null),
weekdat (datetime, null),
dowrdat (datetime, null)

)
GO


Here's the entire stored procedure.

declare @dowrdat datetime

select doc, week_start_date as weekdat, @Dowrdat as dowrdat,
"Clear"=Case
when week_start_date - 60 >= drydat then 'O' else 'M' end,
"SDW"='Y'
into #temppend
from CurrentDiary
where exists (select stp from sebct21.specnew.dbo.vwSDWPending where
stp = pan)

insert #temppend
select doc, week_start_date as weekdat, @Dowrdat as dowrdat,
"Clear"=Case
when week_start_date - 60 >= drydat then 'O' else 'M' end,
"SDW"='N'
from CurrentDiary
where not exists (select stp from sebct21.specnew.dbo.vwSDWPending
where stp = pan)

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, 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

My desired results:

Doc totovrs totpnds totovr totpnd
weekdat dowrdat
009 0 0 1 18 5/14/2010 12:00:00 AM 5/28/2010 12:00:00 AM
200 0 0 0 16 5/14/2010 12:00:00 AM 5/28/2010 12:00:00 AM


Thanks!

jj297 (nchildress297 (AT) gmail (DOT) com) writes:
Do you think it should? Where do you think the column totpnd is defined?
I can't see it anywhere in your query, but in the SELECT clause.

I saw that in a latter post you had posted some sort of SQL script, but
I had to quite some editing before I was able to run it without syntax
errors. Only to find it failed because it referred to a linked server
I don't have.

Could you care to explain what you are actually trying to achieve in
words?

By the way, an INSERT statement without a column list as in the example
below is definitely bad practice.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx