Plamen Ratchev ed 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.recno
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?

The point was to completely eliminate the need for multiple tables, not
only to do this during the processing period.

For example, here is what you have now (simplified version, as you never
provided complete table structure, you can add/correct columns):

CREATE TABLE Diary9_22 (
pan VARCHAR(10) PRIMARY KEY);

CREATE TABLE Diary9_29 (
pan VARCHAR(10) PRIMARY KEY);

-- Sample data
INSERT INTO Diary9_22 VALUES('pan1');
INSERT INTO Diary9_22 VALUES('pan2');
INSERT INTO Diary9_22 VALUES('pan3');

INSERT INTO Diary9_29 VALUES('pan2');
INSERT INTO Diary9_29 VALUES('pan4');

-- Old query to get duplicates
SELECT pan
FROM (SELECT pan FROM Diary9_22
UNION ALL
SELECT pan FROM Diary9_29) AS T
GROUP BY pan
HAVING COUNT(*) > 1;


What I was referring to is completely to remove those weekly tables and
to store all data into a single table with column identifying the week.
Taking the sample data from above, that will look like this:

-- Transform to use a single table
CREATE TABLE Diaries (
week_start_date DATETIME,
pan VARCHAR(10),
PRIMARY KEY (week_start_date, pan));

INSERT INTO Diaries (week_start_date, pan)
SELECT '20080922', pan
FROM Diary9_22
UNION ALL
SELECT '20080929', pan
FROM Diary9_29;

-- New query to get duplicates
SELECT pan
FROM Diaries
WHERE week_start_date IN ('20080922', '20080929')
GROUP BY pan
HAVING COUNT(*) > 1;

Now tables Diary9_22 and Diary9_29 are no longer needed, and in the
future instead of creating new table for Diary10_X (I am still confused
on what you call weekly data as your dates seem to skip from Mondays to
Fridays), you can simply insert the new data into table Diaries with the
correct week start date and run your queries. Then if your goal is to
always compare the data for the last two weeks, you can completely
eliminate the need to hard code dates in the query:

SELECT pan
FROM Diaries
WHERE week_start_date IN
(SELECT DISTINCT TOP 2 week_start_date
FROM Diaries
ORDER BY week_start_date DESC)
GROUP BY pan
HAVING COUNT(*) > 1;

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

On Oct 9, 4:50pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> The point was to completely eliminate the need for multiple tables, not
> only to do this during the processing period.
>
> For example, here is what you have now (simplified version, as you never
> provided complete table structure, you can add/correct columns):
>
> CREATE TABLE Diary9_22 (
> pan VARCHAR(10) PRIMARY KEY);
>
> CREATE TABLE Diary9_29 (
> pan VARCHAR(10) PRIMARY KEY);
>
> -- Sample data
> INSERT INTO Diary9_22 VALUES('pan1');
> INSERT INTO Diary9_22 VALUES('pan2');
> INSERT INTO Diary9_22 VALUES('pan3');
>
> INSERT INTO Diary9_29 VALUES('pan2');
> INSERT INTO Diary9_29 VALUES('pan4');
>
> -- Old query to get duplicates
> SELECT pan
> FROM (SELECT pan FROM Diary9_22
> UNION ALL
> SELECT pan FROM Diary9_29) AS T
> GROUP BY pan
> HAVING COUNT(*) > 1;
>
> What I was referring to is completely to remove those weekly tables and
> to store all data into a single table with column identifying the week.
> Taking the sample data from above, that will look like this:
>
> -- Transform to use a single table
> CREATE TABLE Diaries (
> week_start_date DATETIME,
> pan VARCHAR(10),
> PRIMARY KEY (week_start_date, pan));
>
> INSERT INTO Diaries (week_start_date, pan)
> SELECT '20080922', pan
> FROM Diary9_22
> UNION ALL
> SELECT '20080929', pan
> FROM Diary9_29;
>
> -- New query to get duplicates
> SELECT pan
> FROM Diaries
> WHERE week_start_date IN ('20080922', '20080929')
> GROUP BY pan
> HAVING COUNT(*) > 1;
>
> Now tables Diary9_22 and Diary9_29 are no longer needed, and in the
> future instead of creating new table for Diary10_X (I am still confused
> on what you call weekly data as your dates seem to skip from Mondays to
> Fridays), you can simply insert the new data into table Diaries with the
> correct week start date and run your queries. Then if your goal is to
> always compare the data for the last two weeks, you can completely
> eliminate the need to hard code dates in the query:
>
> SELECT pan
> FROM Diaries
> WHERE week_start_date IN
> (SELECT DISTINCT TOP 2 week_start_date
> FROM Diaries
> ORDER BY week_start_date DESC)
> GROUP BY pan
> HAVING COUNT(*) > 1;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

okay thanks I look over tonight and work with this in the morning.

On Oct 9, 7:55pm, JJ297 <nc...@yahoo.com> wrote:
> On Oct 9, 4:50pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
>
>
>
>
>
> > The point was to completely eliminate the need for multiple tables, not
> > only to do this during the processing period.
>
> > For example, here is what you have now (simplified version, as you never
> > provided complete table structure, you can add/correct columns):
>
> > CREATE TABLE Diary9_22 (
> > pan VARCHAR(10) PRIMARY KEY);
>
> > CREATE TABLE Diary9_29 (
> > pan VARCHAR(10) PRIMARY KEY);
>
> > -- Sample data
> > INSERT INTO Diary9_22 VALUES('pan1');
> > INSERT INTO Diary9_22 VALUES('pan2');
> > INSERT INTO Diary9_22 VALUES('pan3');
>
> > INSERT INTO Diary9_29 VALUES('pan2');
> > INSERT INTO Diary9_29 VALUES('pan4');
>
> > -- Old query to get duplicates
> > SELECT pan
> > FROM (SELECT pan FROM Diary9_22
> > UNION ALL
> > SELECT pan FROM Diary9_29) AS T
> > GROUP BY pan
> > HAVING COUNT(*) > 1;
>
> > What I was referring to is completely to remove those weekly tables and
> > to store all data into a single table with column identifying the week.
> > Taking the sample data from above, that will look like this:
>
> > -- Transform to use a single table
> > CREATE TABLE Diaries (
> > week_start_date DATETIME,
> > pan VARCHAR(10),
> > PRIMARY KEY (week_start_date, pan));
>
> > INSERT INTO Diaries (week_start_date, pan)
> > SELECT '20080922', pan
> > FROM Diary9_22
> > UNION ALL
> > SELECT '20080929', pan
> > FROM Diary9_29;
>
> > -- New query to get duplicates
> > SELECT pan
> > FROM Diaries
> > WHERE week_start_date IN ('20080922', '20080929')
> > GROUP BY pan
> > HAVING COUNT(*) > 1;
>
> > Now tables Diary9_22 and Diary9_29 are no longer needed, and in the
> > future instead of creating new table for Diary10_X (I am still confused
> > on what you call weekly data as your dates seem to skip from Mondays to
> > Fridays), you can simply insert the new data into table Diaries with the
> > correct week start date and run your queries. Then if your goal is to
> > always compare the data for the last two weeks, you can completely
> > eliminate the need to hard code dates in the query:
>
> > SELECT pan
> > FROM Diaries
> > WHERE week_start_date IN
> > (SELECT DISTINCT TOP 2 week_start_date
> > FROM Diaries
> > ORDER BY week_start_date DESC)
> > GROUP BY pan
> > HAVING COUNT(*) > 1;
>
> > --
> > Plamen Ratchevhttp://www.SQLStudio.com
>
> okay thanks I look over tonight and work with this in the morning.- Hide quoted text -
>
> - Show quoted text -

This is perfect thanks so much!!!

I made a few changes as I want to keep the single occurrences so I set
up a staging table and want to then move the info back to Diaries and
delete the stuff I don't need but It's moving everything. What am I
missing?

Select pan into HoldingTable
From Diaries
select pan from diaries
where week_start_date IN
(select distinct top 2 week_start_date
from Diaries
order by week_start_date DESC)
Group by pan
having count (*) =1

Truncate Table Diaries

insert into Diaries
select * from HoldingTable

Drop Table HoldingTable

Your queries do not do anything to remove the duplicate values. In the
first statement you select all 'pan' values to HoldingTable, the second
is just a select statement that does not affect any tables, then the you
truncate and insert back all values.

If your goal is to remove the duplicates based on 'pan' value, then the
following will do it (note that it keeps the earliest occurrence, you
can change 'ORDER BY week_start_date' to 'ORDER BY week_start_date DESC'
to keep the latest). This requires SQL Server 2005/2008.

WITH Dups
AS
(SELECT week_start_date, pan,
ROW_NUMBER() OVER(PARTITION BY pan
ORDER BY week_start_date) AS rk
FROM Diaries)
DELETE Dups
WHERE rk > 1;

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

On Oct 10, 10:51am, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> Your queries do not do anything to remove the duplicate values. In the
> first statement you select all 'pan' values to HoldingTable, the second
> is just a select statement that does not affect any tables, then the you
> truncate and insert back all values.
>
> If your goal is to remove the duplicates based on 'pan' value, then the
> following will do it (note that it keeps the earliest occurrence, you
> can change 'ORDER BY week_start_date' to 'ORDER BY week_start_date DESC'
> to keep the latest). This requires SQL Server 2005/2008.
>
> WITH Dups
> AS
> (SELECT week_start_date, pan,
> ROW_NUMBER() OVER(PARTITION BY pan
> ORDER BY week_start_date) AS rk
> FROM Diaries)
> DELETE Dups
> WHERE rk > 1;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Don't understand how do I put this all together like this?

Select pan into HoldingTable
From Diaries
select pan from diaries
where week_start_date IN
(select distinct top 2 week_start_date
from Diaries
order by week_start_date DESC)
Group by pan
having count (*) =1

Truncate Table Diaries

insert into Diaries
(SELECT week_start_date, pan,
ROW_NUMBER() OVER(PARTITION BY pan
ORDER BY week_start_date) AS rk
FROM Diaries)
DELETE HoldingTable
WHERE rk > 1;

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

On Oct 10, 11:28am, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> 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.com

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.

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

On Oct 10, 11:42am, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> 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.com

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.

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

On Oct 10, 12:40pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> 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.com

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

On Oct 10, 1:58pm, JJ297 <nc...@yahoo.com> wrote:
> On Oct 10, 12:40pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
>
>
>
>
>
> > 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.com
>
> Thanks so much Plamen for this explaination. Like I said you've
> taught me a lot. I'm sure I'll be back again!- Hide quoted text -
>
> - Show quoted text -

I'm now trying to incorporate this into my data but can't get it to
work.

When I try to do a select * I get invalid column name for all of them
so I put in all of the columns and sill getting invalid name. What am
I doing wrong?

select RGN, AREA, DIST, DOC, TSC, HUN, RECNO, PAN, FNAME, LNAME,
[PERSON-TYPE], [REDET-IND], [OVERPAY-IND], [OVERPAY-AMT], CURSTAT,
APPEAL, GKC, REDLOWPRO, PROFILE, [GK-STATUS], ARC, OPST, OPSC, OPSND,
OPSNT, FILLER, [IN-CT], DryCd1, DryDte1, DryFu1, DryCd2, DryDte2,
DryFu2, DryCd3, DryDte3, DryCd4, DryDte4, DryFu4,
DryCd5, DryDte5, DryFu5, DryCd6, DryDte6, DryFu6, DryCd7, DryDte7,
DryFu7, DryCd8, DryDte8, DryFu8, DryCd9, DryDte9, DryFu9, DryCd10,
DryDte10, DryFu10, DryCd11, DryDte11, DryFu11, DryCd12, DryDte12,
DryFu12, DryCd13, DryDte13, DryFu13, DryCd14, DryDte14, DryFu14,
DryCd15, DryDte15, DryFu15, DryCd16, DryDte16, DryFu16, DryFu3, [OP-
JURIS], REDTYPE, Out_DryDte1, Out_DryDte2,
Out_DryDte3, Out_DryDte4, Out_DryDte5, Out_DryDte6, Out_DryDte7,
Out_DryDte8, [Derived Column]
from diaries
where week_start_date IN
(select distinct top 2 week_start_date
from Diaries
order by week_start_date DESC)
Group by RGN, AREA, DIST, DOC, TSC, HUN, RECNO, PAN, FNAME, LNAME,
[PERSON-TYPE], [REDET-IND], [OVERPAY-IND], [OVERPAY-AMT], CURSTAT,
APPEAL, GKC, REDLOWPRO, PROFILE, [GK-STATUS], ARC, OPST, OPSC, OPSND,
OPSNT, FILLER, [IN-CT], DryCd1, DryDte1, DryFu1, DryCd2, DryDte2,
DryFu2, DryCd3, DryDte3, DryCd4, DryDte4,
DryFu4, DryCd5, DryDte5, DryFu5, DryCd6, DryDte6, DryFu6, DryCd7,
DryDte7, DryFu7, DryCd8, DryDte8, DryFu8, DryCd9, DryDte9, DryFu9,
DryCd10, DryDte10, DryFu10, DryCd11, DryDte11, DryFu11, DryCd12,
DryDte12, DryFu12, DryCd13, DryDte13, DryFu13, DryCd14, DryDte14,
DryFu14, DryCd15, DryDte15, DryFu15, DryCd16, DryDte16, DryFu16,
DryFu3, [OP-JURIS], REDTYPE, Out_DryDte1, Out_DryDte2,
Out_DryDte3, Out_DryDte4, Out_DryDte5, Out_DryDte6, Out_DryDte7,
Out_DryDte8, [Derived Column]
having count (*) =1

WITH Dups
AS
(SELECT RGN, AREA, DIST, DOC, TSC, HUN, RECNO, PAN, FNAME, LNAME,
[PERSON-TYPE], [REDET-IND], [OVERPAY-IND], [OVERPAY-AMT], CURSTAT,
APPEAL, GKC, REDLOWPRO, PROFILE, [GK-STATUS], ARC, OPST, OPSC, OPSND,
OPSNT, FILLER, [IN-CT], DryCd1, DryDte1, DryFu1, DryCd2, DryDte2,
DryFu2, DryCd3, DryDte3, DryCd4, DryDte4, DryFu4,
DryCd5, DryDte5, DryFu5, DryCd6, DryDte6, DryFu6, DryCd7, DryDte7,
DryFu7, DryCd8, DryDte8, DryFu8, DryCd9, DryDte9, DryFu9, DryCd10,
DryDte10, DryFu10, DryCd11, DryDte11, DryFu11, DryCd12, DryDte12,
DryFu12, DryCd13, DryDte13, DryFu13, DryCd14, DryDte14, DryFu14,
DryCd15, DryDte15, DryFu15, DryCd16, DryDte16, DryFu16, DryFu3, [OP-
JURIS], REDTYPE, Out_DryDte1, Out_DryDte2,
Out_DryDte3, Out_DryDte4, Out_DryDte5, Out_DryDte6, Out_DryDte7,
Out_DryDte8, [Derived Column],
COUNT(*) OVER(PARTITION BY pan) AS cnt
FROM ClearedDiaries)
DELETE Dups
WHERE cnt > 1;

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

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

On Oct 10, 2:55pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> 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.com

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 ed 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_start_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

Then you are probably after this query (just replace the marker
<other_columns> with all other columns in your table, excluding
week_start_date and pan):

WITH Last2Weeks
AS
(SELECT <other_columns>, week_start_date, pan,
DENSE_RANK() OVER(ORDER BY week_start_date) AS rk,
COUNT(*) OVER(PARTITION BY pan) AS cnt
FROM Diaries
WHERE week_start_date IN
(SELECT DISTINCT TOP (2) week_start_date
FROM Diaries
ORDER BY week_start_date DESC))
INSERT INTO ClearedDiaries (<other_columns>, week_start_date, pan)
SELECT <other_columns>, week_start_date, pan
FROM Last2Weeks
WHERE rk = 1
AND cnt = 1;

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

On Oct 10, 4:24pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> Then you are probably after this query (just replace the marker
> <other_columns> with all other columns in your table, excluding
> week_start_date and pan):
>
> WITH Last2Weeks
> AS
> (SELECT <other_columns>, week_start_date, pan,
> DENSE_RANK() OVER(ORDER BY week_start_date) AS rk,
> COUNT(*) OVER(PARTITION BY pan) AS cnt
> FROM Diaries
> WHERE week_start_date IN
> (SELECT DISTINCT TOP (2) week_start_date
> FROM Diaries
> ORDER BY week_start_date DESC))
> INSERT INTO ClearedDiaries (<other_columns>, week_start_date, pan)
> SELECT <other_columns>, week_start_date, pan
> FROM Last2Weeks
> WHERE rk = 1
> AND cnt = 1;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Okay thanks will try it Tuesday when I get back to work! Talk to you
then.

On Oct 11, 8:17am, JJ297 <nc...@yahoo.com> wrote:
> On Oct 10, 4:24pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
>
>
>
>
>
> > Then you are probably after this query (just replace the marker
> > <other_columns> with all other columns in your table, excluding
> > week_start_date and pan):
>
> > WITH Last2Weeks
> > AS
> > (SELECT <other_columns>, week_start_date, pan,
> > DENSE_RANK() OVER(ORDER BY week_start_date) AS rk,
> > COUNT(*) OVER(PARTITION BY pan) AS cnt
> > FROM Diaries
> > WHERE week_start_date IN
> > (SELECT DISTINCT TOP (2) week_start_date
> > FROM Diaries
> > ORDER BY week_start_date DESC))
> > INSERT INTO ClearedDiaries (<other_columns>, week_start_date, pan)
> > SELECT <other_columns>, week_start_date, pan
> > FROM Last2Weeks
> > WHERE rk = 1
> > AND cnt = 1;
>
> > --
> > Plamen Ratchevhttp://www.SQLStudio.com
>
> Okay thanks will try it Tuesday when I get back to work! Talk to you
> then.- Hide quoted text -
>
> - Show quoted text -

Thanks this works fine which you knew anyway. I am now trying to put
this all in SSIS and trying to bring in the new date. I need to throw
in another sql query but will get this one working first then figure
what I need. So I will be back.

Thanks again!

On Oct 10, 4:24pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> Then you are probably after this query (just replace the marker
> <other_columns> with all other columns in your table, excluding
> week_start_date and pan):
>
> WITH Last2Weeks
> AS
> (SELECT <other_columns>, week_start_date, pan,
> DENSE_RANK() OVER(ORDER BY week_start_date) AS rk,
> COUNT(*) OVER(PARTITION BY pan) AS cnt
> FROM Diaries
> WHERE week_start_date IN
> (SELECT DISTINCT TOP (2) week_start_date
> FROM Diaries
> ORDER BY week_start_date DESC))
> INSERT INTO ClearedDiaries (<other_columns>, week_start_date, pan)
> SELECT <other_columns>, week_start_date, pan
> FROM Last2Weeks
> WHERE rk = 1
> AND cnt = 1;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

I'm back!

I need to make sure that when I am pulling out the one's that are
cleared pan and recno numbers match the record. Does that make sense?

This is what I was using before:

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

I want to incorporate the pan and recno numbers into what you created.


WITH Last2Weeks
AS
(SELECT <other_columns>, week_start_date, pan,
DENSE_RANK() OVER(ORDER BY week_start_date) AS rk,
COUNT(*) OVER(PARTITION BY pan) AS cnt
FROM Diaries
WHERE week_start_date IN
(SELECT DISTINCT TOP (2) week_start_date
FROM Diaries
ORDER BY week_start_date DESC))
INSERT INTO ClearedDiaries (<other_columns>, week_start_date, pan)
SELECT <other_columns>, week_start_date, pan
FROM Last2Weeks
WHERE rk = 1
AND cnt = 1;

Try adding the recno column to the PARTITION BY section of the OVER
clause for COUNT:

WITH Last2Weeks
AS
(SELECT <other_columns>, week_start_date, pan,
DENSE_RANK() OVER(ORDER BY week_start_date) AS rk,
COUNT(*) OVER(PARTITION BY pan, recno) AS cnt
FROM Diaries
WHERE week_start_date IN
(SELECT DISTINCT TOP (2) week_start_date
FROM Diaries
ORDER BY week_start_date DESC))
INSERT INTO ClearedDiaries (<other_columns>, week_start_date, pan)
SELECT <other_columns>, week_start_date, pan
FROM Last2Weeks
WHERE rk = 1
AND cnt = 1;

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

On Oct 16, 11:32am, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> Try adding the recno column to the PARTITION BY section of the OVER
> clause for COUNT:
>
> WITH Last2Weeks
> AS
> (SELECT <other_columns>, week_start_date, pan,
> DENSE_RANK() OVER(ORDER BY week_start_date) AS rk,
> COUNT(*) OVER(PARTITION BY pan, recno) AS cnt
> FROM Diaries
> WHERE week_start_date IN
> (SELECT DISTINCT TOP (2) week_start_date
> FROM Diaries
> ORDER BY week_start_date DESC))
> INSERT INTO ClearedDiaries (<other_columns>, week_start_date, pan)
> SELECT <other_columns>, week_start_date, pan
> FROM Last2Weeks
> WHERE rk = 1
> AND cnt = 1;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Thanks much!