Stored Procedure / Year Problem

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 2000 Forums

SQL Server Development (2000)

Stored Procedure / Year Problem

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-10 : 09:00:04

Hi All,

I have the following stored procedure that nearly works.


CREATE PROCEDURE cdsc_6Weeks 
AS
DECLARE @Week as int
DECLARE @Week1 as int
DECLARE @Week2 as int
DECLARE @Week3 as int
DECLARE @Week4 as int
DECLARE @Week5 as int
DECLARE @Week6 as int

Set @Week = (SELECT DATEPART(wk,getdate()) - 1)
Set @Week1 = @Week - 1
Set @Week2 = @Week - 2
Set @Week3 = @Week - 3
Set @Week4 = @Week - 4
Set @Week5 = @Week - 5
Set @Week6 = @Week - 6

If @Week1 < 0 
	Set @Week1 = 52 + @Week1
If @Week2 < 0 
	Set @Week2 = 52 + @Week2
If @Week3 < 0 
	Set @Week3 = 52 + @Week3
If @Week4 < 0 
	Set @Week4 = 52 + @Week4
If @Week5 < 0 
	Set @Week5 = 52 + @Week5
If @Week6 < 0 
	Set @Week6 = 52 + @Week6

select  LondonLabs.LondonLabName,

SUM(CASE LabReps.Week WHEN @WEEK1 THEN 1 ELSE 0 END) AS "THISWEEK1",
SUM(CASE LabReps.Week WHEN @WEEK2 THEN 1 ELSE 0 END) AS "THISWEEK2",
SUM(CASE LabReps.Week WHEN @WEEK3 THEN 1 ELSE 0 END) AS "THISWEEK3",
SUM(CASE LabReps.Week WHEN @WEEK4 THEN 1 ELSE 0 END) AS "THISWEEK4",
SUM(CASE LabReps.Week WHEN @WEEK5 THEN 1 ELSE 0 END) AS "THISWEEK5",
SUM(CASE LabReps.Week WHEN @WEEK6 THEN 1 ELSE 0 END) AS "THISWEEK6"

from LabReps INNER JOIN  LondonLabs ON   Labreps.LABSRCCODE = LondonLabs.LondonLabCode
		where year(LabReps.LABREPDATE) = year(getdate())
		group by Labreps.LABSRCCODE, LondonLabs.LondonLabName
		order by LondonLabs.LondonLabName

Now what I am trying to do is make sure I get the right data from this procedure during the time when 6 weeks back goes into the previous year.

Has anyone got any ideas on this one?

Thanks,
Gavin

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-10 : 09:37:13

I think you will be better off if you use date functions to operate on getdate() from this:

select LondonLabs.LondonLabName,
SUM(CASE LabReps.Week WHEN DATEPART(wk,DATEADD(wk,-1,getdate()))THEN 1 ELSE 0 END) AS "THISWEEK1"...
from LabReps INNER JOIN LondonLabs ON Labreps.LABSRCCODE = LondonLabs.LondonLabCode where year(LabReps.LABREPDATE) = year(getdate()) group by Labreps.LABSRCCODE, LondonLabs.LondonLabName order by LondonLabs.LondonLabName

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-10 : 10:03:29

Two small problems with that method, my database is stupid

depending on what the year was I have some weeks on 53 and some on 0, this solution does not seem to cater for them.

Is there a better way do you think?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-10 : 10:12:43

Todd's solution with a little modification (DATEDIFF) should do it:

select LondonLabs.LondonLabName,
SUM(CASE DateDiff(ww,LabReps.LABREPDATE,getdate()) WHEN 1 THEN 1 ELSE 0 END) AS "THISWEEK1",
SUM(CASE DateDiff(ww,LabReps.LABREPDATE,getdate()) WHEN 2 THEN 1 ELSE 0 END) AS "THISWEEK2",
SUM(CASE DateDiff(ww,LabReps.LABREPDATE,getdate()) WHEN 3 THEN 1 ELSE 0 END) AS "THISWEEK3",
SUM(CASE DateDiff(ww,LabReps.LABREPDATE,getdate()) WHEN 4 THEN 1 ELSE 0 END) AS "THISWEEK4",
SUM(CASE DateDiff(ww,LabReps.LABREPDATE,getdate()) WHEN 5 THEN 1 ELSE 0 END) AS "THISWEEK5",
SUM(CASE DateDiff(ww,LabReps.LABREPDATE,getdate()) WHEN 6 THEN 1 ELSE 0 END) AS "THISWEEK6"
from LabReps INNER JOIN LondonLabs ON Labreps.LABSRCCODE = LondonLabs.LondonLabCode
where DateDiff(ww,LabReps.LABREPDATE,getdate()) BETWEEN 1 AND 6
group by Labreps.LABSRCCODE, LondonLabs.LondonLabName
order by LondonLabs.LondonLabName

nrafiq
Starting Member

9 Posts

Posted - 2002-01-11 : 00:20:11

Hi Mannga,

Yes. rob's query will be solve your problem. Try it.

Rafi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources