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

SQL Server Development (2000)

Complicated Stored Procedure

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-14 : 05:34:31

Hi All,

I am trying to create a stored procedure which will show me the amount of reports filed per client for the last 6 weeks, week by week.

I have this so far


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

Now the problem I noticed with this after I had finished it was that it only checks how many reports where filed exactly 1, 2, 3 etc.. weeks ago. Not the amount for the whole week.

Has anyone got any good ideas on how to do this?

Thanks
Gavin

Nazim
A custom title

1408 Posts

Posted - 2002-01-14 : 08:31:30

Modify your code this way

SUM(CASE left(LabReps.LABREPDATE,11) WHEN left(DATEADD(wk,-1,getdate()),11) THEN Amountfield ELSE 0 END) AS "THISWEEK1"

HTH

----------------------------------
"True love stories don't have endings."

Edited by - Nazim on 01/14/2002 08:31:59

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-14 : 09:06:06

you seem to have confused me here

How do you mean Amountfield.

I don't have a amount field that is why I am so stuck

Thanks

Nazim
A custom title

1408 Posts

Posted - 2002-01-14 : 10:18:38

Huh?? then where is the amount of every week stored??. Post your table schemas with DDL and DML statments and explain furthur someone will help you here.

quote:

Now the problem I noticed with this after I had finished it was that it only checks how many reports where filed exactly 1, 2, 3 etc.. weeks ago. Not the amount for the whole week.

----------------------------------
"True love stories don't have endings."

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-14 : 10:28:44

quote:

Huh?? then where is the amount of every week stored??. Post your table schemas with DDL and DML statments and explain furthur someone will help you here.

----------------------------------
"True love stories don't have endings."

Umm what are DDL and DML?
I am trying to calculate the amount of every week stored.
The way it works is that it is just a flat table and I need to count the records per lab per week which are in the table.

So it can look something like this.

[LABCASENUMBER] [LABREPDATE] [LABSRCCODE]
(random number) (date of report) (Lab Code)
34734843874387 01/01/2002 4536
23423423423433 01/01/2002 4555
23423523453245 02/01/2002 4536
12432436523455 20/12/2001 4555

Then my stored procedure needs to return
LabCode Week1 Week2
4536 2 0
4555 1 1

I hope you see what I am trying to do now.

Nazim
A custom title

1408 Posts

Posted - 2002-01-14 : 11:02:50

DDL stands for Data Definition Language . it is your create table or alter table or statements to anything with the structure of the table.

DML stands for Data Manipulation language. typical statements are insert, update,delete.

coming to your query . try on these lines. i havent tested this.

select LondonLabs.LondonLabName,datepart(yy,labrepdate),datepart(mm,labrepdate),SUM(CASE datepart(wk,LABREPDATE) WHEN 1 THEN 1 ELSE 0 END) AS "WEEK1",
SUM(CASE datepart(wk,LABREPDATE) WHEN 2 THEN 1 ELSE 0 END) AS "WEEK2"
from LabReps INNER JOIN LondonLabs ON Labreps.LABSRCCODE = LondonLabs.LondonLabCode
group by Labreps.LABSRCCODE, LondonLabs.LondonLabName,datepart(yy,labrepdate),datepart(mm,labrepdate)
order by LondonLabs.LondonLabName

----------------------------------
"True love stories don't have endings."

Edited by - Nazim on 01/14/2002 11:06:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources