Help with stored procedure... - SQL Server Forums

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)

Help with stored procedure...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-31 : 14:58:02

Could someone help me with this...

The weekdat column will have a weeks worth of data that I will add to the table weekly so I want to compare the last two dates in the weekdat column to see which diaries are cleared. I've written this but can't get it to work. What am I doing wrong?

WITH Last2Weeks
AS
SELECT RGN, AREA, DIST, DOC, PAN, RECNO, FNAME, LNAME, DRYCde, DryDat, WeekDat, DENSE_RANK() OVER (ORDER BY WeekDat)
AS rk,
COUNT(*) OVER (PARTITION BY pan, recno) AS cnt
FROM OffSetDiaries
WHERE weekdat IN
(SELECT DISTINCT TOP (2) weekdat
FROM OffSetDiaries
ORDER BY weekdat DESC))
SELECT *
INTO ClearedDiaries
SELECT RGN, AREA, DIST, DOC, PAN, RECNO, FNAME, LNAME, DRYCde, DryDat, WeekDat
FROM Last2Weeks
WHERE rk = 1
AND cnt = 1;

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-03-31 : 15:19:33

Can you give us some insight into what the symptom of the problem is? (e.g., Syntax error vs no data vs bad data vs etc.) "I can't get it to work" isn't much to go on.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-31 : 15:31:02

Okay here's some data from the table:


RGN AREA DIST DOC FNAME  LNAME  DRYCde DryDat      WeekDat
01  H21  278  094  Jane  Doe     5W    12/2/2008    12/13/2008
01  H21  278  094  Jane  Doe     5W    12/2/2008    12/20/2008
06  H24  278  094  Bob   Smith   7W    11/2/2008    12/13/2008
07  H28  275  091  Karen Jones   9W    10/2/2008    11/13/2008

I would like to write a stored procedure to use the last two highest WeekDat to compare whether a person has been cleared or not. If the person is in both weeks such as Jane Doe then that person is not cleared and should go into the Pending Table. If there is only one copy of the name listed like Bob and Karen then they should go into a table called Pending.

What I will be doing is updating the table to put a weeks worth of data in each week. So next week I will add the next date (I go back 90 days to see if it's cleared) so I would enter 12/27/2008 and would like to compare that to 12/20/2008. I hope this makes sense

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources