Prev: Teradata DBA , Richmond, VA
Next: Business/ Data Analyst
From: jj297 on 19 May 2010 09:42
On May 18, 8:48 pm, jj297 <nchildress...(a)gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

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
From: Plamen Ratchev on 19 May 2010 12:46
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
From: jj297 on 19 May 2010 13:56
On May 19, 12:46 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> 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 Ratchevhttp://www.SQLStudio.com

Okay thanks getting everything together will send to you.
From: jj297 on 19 May 2010 15:10
On May 19, 1:56 pm, jj297 <nchildress...(a)gmail.com> wrote:
> On May 19, 12:46 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
>
> > 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 Ratchevhttp://www.SQLStudio.com
>
> Okay thanks getting everything together will send to you.

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!
From: Erland Sommarskog on 19 May 2010 17:41
jj297 (nchildress297(a)gmail.com) writes:
> 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.
>...
> It doesn't like the sum column

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.

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



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.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/prodinfo/previousversions/books.mspx

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Teradata DBA , Richmond, VA
Next: Business/ Data Analyst