Exclude columns query based on criteria

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

Transact-SQL (2000)

Exclude columns query based on criteria

Author

Topic

andersonca
Starting Member

2 Posts

Posted - 2011-01-31 : 12:49:49

I have a query that summarizes a calls taken during a week. I obtain a name then sum the totals for each week. a portion of the query is below. What I want to do is only include a certain amount of weeks back based on the current date. i.e. I want to exclude week 32 this week, week 32 and 33 next week. without having to modify my query each week.


select  d.lname + ', ' + d.fname  'agent',
sum(Case when d.week = 32 then 1 else 0 end) '32',sum(Case when d.week = 33 then 1 else 0 end) '33',sum(Case when d.week = 34 then 1 else 0 end) '34',
sum(Case when d.week = 35 then 1 else 0 end) '35',sum(Case when d.week = 36 then 1 else 0 end) '36',sum(Case when d.week = 37 then 1 else 0 end) '37',
sum(Case when d.week = 38 then 1 else 0 end) '38',sum(Case when d.week = 39 then 1 else 0 end) '39',
sum(Case when d.week = 40 then 1 else 0 end) '40',sum(Case when d.week = 41 then 1 else 0 end) '41',sum(Case when d.week = 42 then 1 else 0 end) '42',
....
from Table d 
]

edit: fixed line breaks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 13:30:40

How about some DDL for the table structure, and sample data, and sample desired results.

Poor planning on your part does not constitute an emergency on my part.

andersonca
Starting Member

2 Posts

Posted - 2011-02-01 : 10:23:45

The table looks like
agent week Ans
Name 1 1 26
Name 2 1 19
Name 3 1 19
Name 4 1 13
Name 5 1 13
Name 6 2 89
Name 7 2 6
Name 8 2 101
Name 9 2 71
Name 10 2 109
Name 11 2 4
Name 12 2 14
Name 13 2 3

The results look like:
Week
agent 41 42 43 44 45 46 47 48 49
Name 1 4 0 5 11 6 23 11 5 13
Name 2 121 41 100 103 46 95 0 87 102
Name 3 64 58 87 63 57 42 102 78 106
Name 4 52 70 78 63 41 61 51 38 48
Name 5 60 72 68 77 60 57 68 68 78
Name 6 79 71 71 23 53 73 94 79 50

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-01 : 17:18:34

I am still not sure what you are after here. Those results can't come from your sample data.

As far as a query you don't have to modify, perhaps a procedure that limits the period using variables for the weeks to summarize. Part of this will need to be fixed..lets say you always want a 4 week period to summarize, you would pass your starting week as a paramater, and use that as the base..


CREATE PROCEDURE pSummaryOfCalls 
	(@startweek int)
AS

select  
AGENT = d.lname + ', ' + d.fname
,Period = 'Weeks:  ' + Convert(varchar(2),@startweek) + ' to ' + convert(varchar(2),@startweek+3) 
,Week1 = sum(case when d.week = @startweek then 1 else 0 end)
,Week2 = sum(case when d.week = @startweek+1 then 1 else 0 end)
,Week3 = sum(Case when d.week = @startweek+2 then 1 else 0 end)
,Week4 = sum(Case when d.week = @startweek+3 then 1 else 0 end)
--repeat as necessary
from Table d 
Where d.week >= @startweek 
	AND d.week <= (@startweek + 3)

GO


--call the procedure like this, passing the starting weeknumber
Exec pSummaryOfCalls 32

This way, your period is always fixed, and the report will indicate which period it is for.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources