Stored procedure Question

[JJ297]

Plamen Ratchev helped me at this post:

http://groups.google.com/group/comp....716c65bc21a2f#

You mentioned this:
You can still keep all weekly data in one table, just need an extra
column to store the week start date. That will make the system a lot
more flexible (and you do not have to create a new table every week).
Right now you have to change your queries every week to reflect the
table name change.

How do I do that with this stored procedure?

SELECT t1.* into HoldingTable
FROM Diary9_19 t1
left JOIN Diary9_29 t2
ON t1.PAN=t2.PAN and t1.recno=t2.rec no
WHERE t2.PAN IS NULL

Insert into ClearDiaries
Select * from HoldingTable

Drop Table HoldingTable

I have to bring in a new table once a week and compare the two to see
if PAN is listed in the new table(Diary9_29 ) and not the old
one(Diary9_19). I ran the above procedure fine but now I'm ready to
bring in Diary10_3 and compare them. I don't want to have to change
my queries each time. How do I set up another column to store the
week start date which you suggest?

[Plamen Ratchev]

You simply insert all values to the Diaries table, then run the DELETE
statement I posted. There is no need for the HoldingTable and all other
queries you posted.

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

[JJ297]

On Oct 10, 11:28*am, Plamen Ratchev <Pla...@SQLStud io.comwrote:

You simply insert all values to the Diaries table, then run the DELETE
statement I posted. There is no need for the HoldingTable and all other
queries you posted.

--
Plamen Ratchevhttp://www.SQLStudio.c om

Okay that did it but I worded it wrong as to what I want.

When I compare the two files I want the dups to be deleted and the
single occurrences to remain in the table. I'm looking for the
cleared items and if there are dups then they are not cleared. Single
occurrences are cleard so I want them to be placed in the Diaries
table. So I should have Pan 1 Pan 3, and Pan 4 only in the Diaries
table as Pan 2 had duplicates. I hope this makes sense.

[Plamen Ratchev]

Then simply use this delete statement instead of the other one:

WITH Dups
AS
(SELECT week_start_date , pan,
COUNT(*) OVER(PARTITION BY pan) AS cnt
FROM Diaries)
DELETE Dups
WHERE cnt 1;

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

[JJ297]

On Oct 10, 11:42*am, Plamen Ratchev <Pla...@SQLStud io.comwrote:

Then simply use this delete statement instead of the other one:

WITH Dups
AS
(SELECT week_start_date , pan,
* * * * *COUNT(*) OVER(PARTITION BY pan) AS cnt
* FROM Diaries)
DELETE Dups
WHERE cnt 1;

--
Plamen Ratchevhttp://www.SQLStudio.c om

PERFECT!!!! Thanks so much. Can you explain the last section so I
can understand what you had me to do. Thanks so much you've taught me
a lot.

[Plamen Ratchev]

Here are some inline comments to the significant parts of the query:

-- define common table expression (CTE) using the WITH keyword,
-- which is pretty much like derived table but more powerful
WITH Dups
AS
(SELECT week_start_date , pan,
-- use aggregate function with the OVER clause
-- this allows to apply count over a partition/group
-- in this case will provide counts for each 'pan' value
-- note the PARTITION BY section that lists which column
-- will define the partition
COUNT(*) OVER(PARTITION BY pan) AS cnt
FROM Diaries)
DELETE Dups
-- delete only those rows that have count 1
-- since count was per 'pan' value it will remove
-- those that have any duplicate rows
WHERE cnt 1;

Note that the DELETE statement has to follow the CTE definition:

WITH
AS
(SELECT ...)
DELETE ...

The query that consumes the CTE has to be immediately after the CTE
definition (and it could be SELECT/INSERT/DELETE/UPDATE query).

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

[JJ297]

On Oct 10, 12:40*pm, Plamen Ratchev <Pla...@SQLStud io.comwrote:

Here are some inline comments to the significant parts of the query:

-- define common table expression (CTE) using the WITH keyword,
-- which is pretty much like derived table but more powerful
WITH Dups
AS
(SELECT week_start_date , pan,
* * * * *-- use aggregate function with the OVER clause
* * * * *-- this allows to apply count over a partition/group
* * * * *-- in this case will provide counts for each 'pan' value
* * * * *-- note the PARTITION BY section that lists which column
* * * * *-- will define the partition
* * * * *COUNT(*) OVER(PARTITION BY pan) AS cnt
* FROM Diaries)
DELETE Dups
-- delete only those rows that have count 1
-- since count was per 'pan' value it will remove
-- those that have any duplicate rows
WHERE cnt 1;

Note that the DELETE statement has to follow the CTE definition:

WITH
AS
(SELECT ...)
DELETE ...

The query that consumes the CTE has to be immediately after the CTE
definition (and it could be SELECT/INSERT/DELETE/UPDATE query).

--
Plamen Ratchevhttp://www.SQLStudio.c om

Thanks so much Plamen for this explaination. Like I said you've
taught me a lot. I'm sure I'll be back again!

[Plamen Ratchev]

I really do not understand what you are trying to do. It will be best to
post your complete table structures, insert statements to populate
sample data, and describe what are the desired results.

The error message should tell you what is the invalid column name. Seems
to me the first query will not accomplish much as you are grouping on
all columns. Plus not sure if you already have added the week_start_date
column which is essential for it to work.

You do not have to list any columns in the delete statement, it can be
as simple as this:

WITH Dups
AS
(SELECT COUNT(*) OVER(PARTITION BY pan) AS cnt
FROM ClearedDiaries)
DELETE Dups
WHERE cnt 1;

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

[Plamen Ratchev]

One more note - perhaps you do not even need a delete statement to clean
data. I am not sure how you get the data loads, but assuming you have
some input staging table, you can insert only rows with 'pan' values
that do not exists in the target table:

INSERT INTO Diaries
SELECT ...
FROM Staging AS S
WHERE NOT EXISTS(SELECT *
FROM Diaries AS D
WHERE D.pan = S.pan);

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

[JJ297]

On Oct 10, 2:55*pm, Plamen Ratchev <Pla...@SQLStud io.comwrote:

I really do not understand what you are trying to do. It will be best to
post your complete table structures, insert statements to populate
sample data, and describe what are the desired results.

The error message should tell you what is the invalid column name. Seems
to me the first query will not accomplish much as you are grouping on
all columns. Plus not sure if you already have added the week_start_date
column which is essential for it to work.

You do not have to list any columns in the delete statement, it can be
as simple as this:

WITH Dups
AS
(SELECT COUNT(*) OVER(PARTITION BY pan) AS cnt
* FROM ClearedDiaries)
DELETE Dups
WHERE cnt 1;

--
Plamen Ratchevhttp://www.SQLStudio.c om

This is what I have to do...I have to compare last week's diary with
this week's diary to get PAN's that are cleared. If PAN is in last
week and not in this week then it's cleared and should go into
ClearedDiaries. I am bringing in the Flat File via SSIS then I will
add this stored procedure you helped me with to the Execute SQL Task
in SSIS. Here's the table structure. I didn't put it out there
because there's so many fields.

I first did a select * but got this:

Column 'diaries.week_s tart_date' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY
clause.

So I thought I had to add all of the fields?

RGN varchar(2)
AREA varchar(3)
DIST varchar(3)
DOC varchar(3)
TSC varchar(3)
HUN varchar(9)
RECNO varchar(2)
PAN varchar(9)
Fname varchar(15)
Lname varchar(20)
Person-Type varchar(1)
Redet-Ind varchar(1)
OverPay-Ind varchar(1)
OverPay-Amt varchar(8)
Curstat varchar(3)
Appeal varchar(1)
GKC varchar(1)
Redlowpro varchar(1)
Profile varchar(1)
GK-Status varchar(1)
ARC varchar(1)
OPST varchar(1)
OPSC varchar(1)
OPSND varchar(8)
OPSNT varchar(1)
Filler varchar(8)
IN-CT varchar(2)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd2 varchar(2)
DryDte2 varchar(8)
DryDte2 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
DryCd1 varchar(2)
DryDte1 varchar(8)
DryDte1 varchar(1)
OP-Juris varchar(1)
Redtype varchar(1)
Out_DryDte1 datetime
Out_DryDte2 datetime
Out_DryDte3 datetime
Out_DryDte4 datetime
Out_DryDte5 datetime
Out_DryDte6 datetime
Out_DryDte6 datetime
Out_DryDte8 datetime