CASE or another way? - 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)

CASE or another way?

Author

Topic

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-12-19 : 10:46:30

I didn't really know what to name the subject, sorry. The problem that I am having is that I am trying to split number of leads into four time categories, yesterday, month to date, last month, and previous 45 days. CASE would work if each situation was unique but they overlap with the last 45 days and that is the problem. Is CASE the correct approach to do this or should I group it another way? Thanks,
Anthony

--**************************************
declare @date datetime
select @date='12/18/2002 23:59:59.999'

select Timeframe=case
when datediff(dd,convert(varchar,l.creation_datetime,101),@date)=1
then 'Yesterday'
when datediff(mm,convert(varchar,l.creation_datetime,101),@date)=0
then 'Month To Date'
when datediff(mm,convert(varchar,l.creation_datetime,101),@date)=1
then 'Last Month'
when datediff(dd,convert(varchar,l.creation_datetime,101),@date)<=45
then 'Previous 45 Days'
else 'Other'
end,
[#Leads]=count(distinct l.lead_id)
from vw_validleaddetail l
join organizationinfo oi on l.org_id=oi.org_id
and oi.org_id='2000008'
where l.creation_datetime between @date-45 and @date
group by case
when datediff(dd,convert(varchar,l.creation_datetime,101),@date)=1
then 'Yesterday'
when datediff(mm,convert(varchar,l.creation_datetime,101),@date)=0
then 'Month To Date'
when datediff(mm,convert(varchar,l.creation_datetime,101),@date)=1
then 'Last Month'
when datediff(dd,convert(varchar,l.creation_datetime,101),@date)<=45
then 'Previous 45 Days'
else 'Other'
end

"DBAs are overpaid and their jobs are easy...heck, I could do it."

-Quote from a former boss who calls often for help and speaks to my voice mail :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-19 : 11:23:58

There are two approaches you can take:

1) Cross tab the different categories:

SELECT
SUM(CASE WHEN DayDiff = 1 THEN 1 ELSE 0 END) as yesterday,
SUM(CASE WHEN MonthDiff = 0 THEN 1 ELSE 0 END) as MTD,
SUM(CASE WHEN MonthDiff =1 THEN 1 ELSE 0 END) as LastMonth,
SUM(CASE WHEN DayDiff < 45 THEN 1 ELSE 0 END) as Last45Days,
COUNT(*) as Total
FROM
(SELECT datediff(dd,creationdatetime,@date) as DayDiff,
datediff(mm,creationdatetime,@date) as MonthDiff
FROM
vw_validleaddetail) A

or something like that (just a rough example).

2) you create a table, call it "Categories", like this:


Cat        MonthDiff   DayDiffMin   DayDiffMax
Yesterday  0           1            1
MTD        0           0            100
LastMonth  1           0            100
Last45Days 0           0            45
Last45Days 1           0            45

and join to it like this:

SELECT ISNULL(Categories.Cat,'Other') as Category, COUNT(*) as [#Leads]
FROM
YourSQL A
LEFT OUTER JOIN
Categories A
ON
YourSQL.MonthDiff = A.MonthDiff AND
YourSQL.DayDiff BETWEEN A.DayDiffMin and A.DayDiffMax

In "YourSQL", you'd have to return DayDiff and MonthDiff for each record.

This will cause duplicate records, but that's what you want.

Sorry for the rough (and probably confusing) SQL, just showing some techniques.

But if you want to "create" more records or have overlaps, a "helper" table is the way to go.

- Jeff

Edited by - jsmith8858 on 12/19/2002 11:24:59

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-12-19 : 14:04:07

Thanks for the help. I usually go the temp table route when I can't get the data the way I want it. Its basically four inserts to get the info the way I wanted. Code is below and am sure it could be done much more efficient but it had to get done this morning ASAP...you all know how that goes.

--*********************
declare @date datetime
select @date='12/18/2002 23:59:59.999'

select Timeframe=case
when datediff(dd,convert(varchar,l.creation_datetime,101),@date)=1
then 'Previous 45 Days'
else 'Other'
end ,
[#Leads]=count(distinct l.lead_id)
into ##Test999
from vw_validleaddetail l
join organizationinfo oi on l.org_id=oi.org_id
and oi.org_id='2000008'
where l.creation_datetime between @date-45 and @date
group by case
when datediff(dd,convert(varchar,l.creation_datetime,101),@date)=1
then 'Previous 45 Days'
else 'Other'
end
go
delete from ##test999 where timeframe='Other'
go

declare @date datetime
select @date='12/18/2002 23:59:59.999'

insert into ##test999
select Timeframe=case
when datediff(mm,convert(varchar,l.creation_datetime,101),@date)=0
then 'Month To Date'
else 'Other'
end,
[#Leads]=count(distinct l.lead_id)
from vw_validleaddetail l
join organizationinfo oi on l.org_id=oi.org_id
and oi.org_id='2000008'
where l.creation_datetime between @date-45 and @date
group by case
when datediff(mm,convert(varchar,l.creation_datetime,101),@date)=0
then 'Month To Date'
else 'Other'
end
go
delete from ##test999 where timeframe='Other'
go

declare @date datetime
select @date='12/18/2002 23:59:59.999'

insert into ##test999
select Timeframe=case
when datediff(mm,convert(varchar,l.creation_datetime,101),@date)=1
then 'Last Month'
else 'Other'
end,
[#Leads]=count(distinct l.lead_id)
from vw_validleaddetail l
join organizationinfo oi on l.org_id=oi.org_id
and oi.org_id='2000008'
where l.creation_datetime between @date-45 and @date
group by case
when datediff(mm,convert(varchar,l.creation_datetime,101),@date)=1
then 'Last Month'
else 'Other'
end
delete from ##test999 where timeframe='Other'
go

declare @date datetime
select @date='12/18/2002 23:59:59.999'

insert into ##test999
select Timeframe=case
when datediff(dd,convert(varchar,l.creation_datetime,101),@date)<=45
then 'Yesterday'
else 'Other'
end,
[#Leads]=count(distinct l.lead_id)
from vw_validleaddetail l
join organizationinfo oi on l.org_id=oi.org_id
and oi.org_id='2000008'
where l.creation_datetime between @date-45 and @date
group by case
when datediff(dd,convert(varchar,l.creation_datetime,101),@date)<=45
then 'Yesterday'
else 'Other'
end
delete from ##test999 where timeframe='Other'
go

select * from ##test999
drop table ##test999

"DBAs are overpaid and their jobs are easy...heck, I could do it."

-Quote from a former boss who calls often for help and speaks to my voice mail :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-19 : 14:18:13

That's a lot of work and a lot of SQL ... if you get a chance, try to understand what I wrote, it takes the place of #temp tables and multiple INSERTS and is easier to maintain in the future.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources