SQL CASE Question: Only display certain columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

SQL CASE Question: Only display certain columns?

Author

Topic

asdoye111
Starting Member

16 Posts

Posted - 2010-07-21 : 14:30:55

i am trying to track "requests". I am building a report that spits out a table that is easily readable, but i can't make it do what i would like it to. On the website, users can select a date range to see how many requests there were during that time period. My query does this... but i would like to display only the columns that have information selected. For example, if I selected June and July, i want the columns to be: Description, June, July, Total.

as it stands right now, it spits out Description, Jan, Feb, Mar, Apr... all the way thru Dec, & Total. how can i use CASES to only show the months that are appropriate.

Here is my code:
Select Description as officeDescrip,
SUM(CASE WHEN requestedDate >= '2010-01-01 00:00:00' AND requestedDate <= '2010-01-31 23:59:59' then 1 else 0 end) as 'Jan',
SUM(CASE WHEN requestedDate >= '2010-02-01 00:00:00' AND requestedDate <= '2010-02-28 23:59:59' then 1 else 0 end) as 'Feb',
SUM(CASE WHEN requestedDate >= '2010-03-01 00:00:00' AND requestedDate <= '2010-03-31 23:59:59' then 1 else 0 end) as 'Mar',
SUM(CASE WHEN requestedDate >= '2010-04-01 00:00:00' AND requestedDate <= '2010-04-30 23:59:59' then 1 else 0 end) as 'Apr',
SUM(CASE WHEN requestedDate >= '2010-05-01 00:00:00' AND requestedDate <= '2010-05-31 23:59:59' then 1 else 0 end) as 'May',
SUM(CASE WHEN requestedDate >= '2010-06-01 00:00:00' AND requestedDate <= '2010-06-30 23:59:59' then 1 else 0 end) as 'Jun',
SUM(CASE WHEN requestedDate >= '2010-07-01 00:00:00' AND requestedDate <= '2010-07-31 23:59:59' then 1 else 0 end) as 'Jul',
SUM(CASE WHEN requestedDate >= '2010-08-01 00:00:00' AND requestedDate <= '2010-08-31 23:59:59' then 1 else 0 end) as 'Aug',
SUM(CASE WHEN requestedDate >= '2010-09-01 00:00:00' AND requestedDate <= '2010-09-30 23:59:59' then 1 else 0 end) as 'Sep',
SUM(CASE WHEN requestedDate >= '2010-10-01 00:00:00' AND requestedDate <= '2010-10-31 23:59:59' then 1 else 0 end) as 'Oct',
SUM(CASE WHEN requestedDate >= '2010-11-01 00:00:00' AND requestedDate <= '2010-11-30 23:59:59' then 1 else 0 end) as 'Nov',
SUM(CASE WHEN requestedDate >= '2010-12-01 00:00:00' AND requestedDate <= '2010-12-31 23:59:59' then 1 else 0 end) as 'Dec',
count(*) as 'Total'
from tbl_Requests
Inner Join tbl_Areas on areaID = reqArea
Where ((requestedDate >= @dtFrom) AND (requestedDate <= @dtTo))
Group By Description
Order By Description
END

my 2nd questions would be, how can i check using WILDCARDS to see if the MONTH dates are chosen versus just '2010-01-01'?

THANKS FOR ALL YOUR HELP!

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-21 : 15:25:25

try this:


create table test
(date smalldatetime
,num int)

insert into test
select '2010/07/01', 120 union all
select '2010/07/02', 100 union all
select '2010/07/03', 210 union all
select '2010/01/21', 142 union all
select '2010/02/09', 97


select

x.*
into test2
from
(
select 
SUM(CASE WHEN date >= '2010-01-01 00:00:00' AND date <= '2010-01-31 23:59:59' then 1 else 0 end) as 'Jan',
SUM(CASE WHEN date >= '2010-02-01 00:00:00' AND date <= '2010-02-28 23:59:59' then 1 else 0 end) as 'Feb',
SUM(CASE WHEN date >= '2010-03-01 00:00:00' AND date <= '2010-03-31 23:59:59' then 1 else 0 end) as 'Mar',
SUM(CASE WHEN date >= '2010-04-01 00:00:00' AND date <= '2010-04-30 23:59:59' then 1 else 0 end) as 'Apr',
SUM(CASE WHEN date >= '2010-05-01 00:00:00' AND date <= '2010-05-31 23:59:59' then 1 else 0 end) as 'May',
SUM(CASE WHEN date >= '2010-06-01 00:00:00' AND date <= '2010-06-30 23:59:59' then 1 else 0 end) as 'Jun',
SUM(CASE WHEN date >= '2010-07-01 00:00:00' AND date <= '2010-07-31 23:59:59' then 1 else 0 end) as 'Jul',
SUM(CASE WHEN date >= '2010-08-01 00:00:00' AND date <= '2010-08-31 23:59:59' then 1 else 0 end) as 'Aug',
SUM(CASE WHEN date >= '2010-09-01 00:00:00' AND date <= '2010-09-30 23:59:59' then 1 else 0 end) as 'Sep',
SUM(CASE WHEN date >= '2010-10-01 00:00:00' AND date <= '2010-10-31 23:59:59' then 1 else 0 end) as 'Oct',
SUM(CASE WHEN date >= '2010-11-01 00:00:00' AND date <= '2010-11-30 23:59:59' then 1 else 0 end) as 'Nov',
SUM(CASE WHEN date >= '2010-12-01 00:00:00' AND date <= '2010-12-31 23:59:59' then 1 else 0 end) as 'Dec',
count(*) as nof_occasions 
from test
where date between '2010/06/01' and '2010/07/03'
) as x

select
x.nof_occ
,x.months
from
(
select
nof_occ, months
from
(
select 
	jan
	,feb
	,mar
	,apr
	,may
	,jun
	,jul
	,aug
	,sep
	,oct
	,nov
	,dec
	,nof_occasions 
from test2
) as p
unpivot 
( nof_occ for months in 
	(jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec)
) as unpivoting
) as x
	where x.nof_occ <> 0


drop table test
drop table test2

asdoye111
Starting Member

16 Posts

Posted - 2010-07-21 : 15:59:26

thanks, i will try that and let you know. appreciate your help...

asdoye111
Starting Member

16 Posts

Posted - 2010-07-21 : 16:43:18

could you explain a little bit of what you accomplished with that statement?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-21 : 17:28:34

Slimt: I found a minor bug with your example. Using smalldatetime causes the July 1 2010 value to count for both June and July. If the seconds portion of your CASE expression is 29 seconds or less, it works correctly. Seems like it rounds 30 June 23:59:30 up to the next minute, which makes it July 1. I tested this for 2010 and 2009 dates.

Changing to datetime or removing the seconds portion fixes the problem. Seconds aren't stored with smalldatetime anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources