Another stored procedure question...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Another stored procedure question...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-03 : 08:24:18

I want to get records having only single occurance each in Tables Diary9_22 & Diary9_29?

I'm comparing last weeks file to this weeks so those records would be considered cleared.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-03 : 08:28:54

Can you post the query you used?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 08:29:57

[code]SELECT t1.*,t2.*
FROM Diary9_22 t1
INNER JOIN Diary9_29 t2
ON t2.PK=t1.PK
INNER JOIN
(
SELECT PK,Cat
FROM
(SELECT Pk,'A' AS Cat
FROm Diary9_22
UNION ALL
SELECT Pk,'B' AS Cat
FROm Diary9_29)t
GROUP BY PK,Cat
HAVING COUNT(*)=1
)r
ON r.PK=t1.PK[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 08:32:34

to clear others

DELETE t1
FROM Diary9_22 t1
INNER JOIN
(
SELECT PK,Cat
FROM
(SELECT Pk,'A' AS Cat
FROm Diary9_22 
UNION ALL
SELECT Pk,'B' AS Cat
FROm Diary9_29)t
GROUP BY PK,Cat
HAVING COUNT(*)>1
)r
ON r.PK=t1.PK

DELETE t2
FROM Diary9_29 t2
INNER JOIN
(
SELECT PK,Cat
FROM
(SELECT Pk,'A' AS Cat
FROm Diary9_22 
UNION ALL
SELECT Pk,'B' AS Cat
FROm Diary9_29)t
GROUP BY PK,Cat
HAVING COUNT(*)=1
)r
ON r.PK=t2.PK

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-03 : 09:21:01

visakh16 this works but it's giving me duplicates.

I think I've worded it wrong as to what I want.

Looking at Diary9_22 I want to see if that PAN number is in Diary9_29 if it isn't I want that record. Here's some sample data:

Diary9_22

DOC PAN FNAME LNAME
200 1256 Brenda Smith
300 4562 Karen Jackson
700 4589 Lucy Jones
500 5589 Gary Hall

Diary9_29

DOC PAN FNAME LNAME
200 1256 Brenda Smith
700 5589 Gary Hall
400 2289 James Jones

I want to write a stored procedure that will look at both tables and pull out Karen Jackson and Lucy Jones records because they are not in Diary9_29 that means they have been cleared. I want all cleared records. I hope that makes sense.

I don't want to delete the other ones as I'm going to put this in SSIS to keep the other ones.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 09:44:11

[code]SELECT t1.*
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL[/code]

and for deleteing

[code]DELETE t1
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL[/code]

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-03 : 10:00:56

Perfect thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 10:19:19

quote:
Originally posted by JJ297

Perfect thanks!

welcome

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-03 : 14:50:19

How do I get the below results to go into it's own table?

Do I create the table first?

SELECT t1.*
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-03 : 14:53:16

quote:
Originally posted by JJ297

How do I get the below results to go into it's own table?

Do I create the table first?

SELECT t1.*
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL

SELECT t1.* INTO NewTable
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-03 : 14:54:34

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 14:55:14

quote:
Originally posted by JJ297

How do I get the below results to go into it's own table?

Do I create the table first?

SELECT t1.*
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL

you mean get these records in Diary9_29 table?

INSERT INTO Diary9_29 
SELECT t1.columns...
FROM Diary9_22 t1
LEFT JOIN Diary9_29 t2
ON t1.DOC=t2.DOC
WHERE t2.DOC IS NULL

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-03 : 15:21:12

No I want to put those results into a new table called Cleared which worked out fine.

Now I want to query the Cleared Table for results of how many records were cleared within 60 days when the report was run on 9/19. I want to use the field Out_DryDte1 to get that info.

How do I get this to work?

DATEDIFF("dd",Out_DryDte1,GETDATE()) > 60 - 14

Using today's date and going back 14 days as to when it was run on Sept. 19th.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-03 : 15:27:30

SELECT * FROM Cleared WHERE DATEDIFF("dd",Out_DryDte1,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-14,0))<=60

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-10-03 : 15:35:42

Thanks again. Have a great weekend I'm sure I'll visit again next week!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources