February 20, 2007 at 12:28 pm
I want to count the number of consumers served each month for the last 5 years. A consumer is served if they have a service at any time during a month, even for only one day. A service has a begin and end date and usually runs for 1 year, sometimes less. If Larry, Moe and Curly get services in January and only Larry and Moe get services in February (Curly's ended in Jan.), the result set should look like - Jan. = 3; Feb. = 2. The data looks like this:
Consumer table: consumerId, name, adddress, blah, blah
Service table: serviceId, consumerId, beginDate, endDate, blah, blah
Is there a set based solution that will give me the total consumers served each month for the last 60 months?
Here is the code to do 1 month.
DECLARE
@begDate datetime, @endDate datetime
SET
@begDate = '01/01/2006'
SET
@endDate = '01/31/2006'
SELECT
DISTINCT service.consumerId
FROM
service
WHERE
service.begDate <= @endDate
AND
service.endDate >= @begDate
TIA
John Deupree
February 20, 2007 at 2:36 pm
i did not have any luck putting together one sql statement but i was able to write up a script that you could put into a procedure and then execute the procedure to get you your results. if you are able to go the procedure route, make @i (for number of months) and @check_date (or better yet @start_date) parameters for the procedure:
create
table #tmp(date_value datetime, count_check int)
declare
@check_date datetime, @i int, @tmp_date datetime
select
@check_date = '1/1/2006', @i = 0
while
@i < 15
begin
set @tmp_date = dateadd(mm, @i, @check_date)
insert into #tmp(date_value, count_check)
select @tmp_date, count(*)
from service
where @tmp_date between begDate and endDate
set @i = @i + 1
end
select
right('00' + cast(month(date_value) as varchar), 2) + cast(year(date_value) as varchar ), count_check
from
#tmp
drop
table #tmp
hope this helps.
February 20, 2007 at 4:22 pm
How about something like this?
CREATE TABLE #Services
(
serviceId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
consumerId INT,
beginDate SMALLDATETIME,
endDate SMALLDATETIME
)
INSERT INTO #Services(consumerId, begindate, enddate)
VALUES (1, '2006-08-05', '2007-01-01')
INSERT INTO #Services(consumerId, begindate, enddate)
VALUES (1, '2006-02-16', '2006-12-01')
----
DECLARE @BeginDate DATETIME ; SET @BeginDate = DATEADD(mm,-60,'2007-02-01')
SELECT COUNT(ConsumerId) AS ConsumerCount,
DATEPART(MM, DATEADD(mm, Num, @BeginDate)) AS [MONTH],
DATEPART(YY, DATEADD(mm, Num, @BeginDate)) AS [YEAR]
FROM (
select top 100 percent (i2.number*10 + i1.number) as [num]
from (select 0 as [number] union all
select 1 as [number] union all
select 2 as [number] union all
select 3 as [number] union all
select 4 as [number] union all
select 5 as [number] union all
select 6 as [number] union all
select 7 as [number] union all
select 8 as [number] union all
select 9 as [number]) as i1
cross join
(select 0 as [number] union all
select 1 as [number] union all
select 2 as [number] union all
select 3 as [number] union all
select 4 as [number] union all
select 5 as [number] union all
select 6 as [number] union all
select 7 as [number] union all
select 8 as [number] union all
select 9 as [number]) as i2
WHERE (i2.number*10 + i1.number) 0
order by [num] ) N
INNER JOIN #Services S
ON DATEADD(mm, Num, @BeginDate) BETWEEN S.beginDate AND S.EndDate
WHERE N.num <= 60
GROUP BY DATEPART(MM, DATEADD(mm, Num, @BeginDate)), DATEPART(YY, DATEADD(mm, Num, @BeginDate))
DROP TABLE #Services
Be aware that the derived numbers table only counts up to 99, you can extend the syntax or populate a static numbers table if you wish.
SQL guy and Houston Magician
February 20, 2007 at 4:30 pm
Since this is posted in the SQL2K5 forums, here's an example using SQL2K5's new features.
Use a recursive CTE to generate your 60 months:
With
SixtyMonths (TheDate, TheYear, TheMonth) As
(
Select GetDate(),
Year(GetDate()),
Month(GetDate())
Union All
Select DateAdd(mm, -1, TheDate),
Year( DateAdd(mm, -1, TheDate) ),
Month( DateAdd(mm, -1, TheDate) )
From SixtyMonths
Where TheDate > DateAdd(mm, -59, GetDate())
)
-- Using a Select for demo purposes only. Join to Services table on the
-- year & month
Select *
From SixtyMonths
Order By TheDate
February 20, 2007 at 4:43 pm
Very nice PW! Great example of recursive CTEs too!
<jealous>I wish I'd thought of that</jealous>
SQL guy and Houston Magician
February 21, 2007 at 7:59 am
Hi,
This may be of some use.
Note: i have a table in all my db's that consists of 50 years worth of dates as
DDates:
DDate: 2006/01/31
DMonth: 01
DYear: 2006
DFinancial year etc etc
SELECT TOP 100 PERCENT dbo.DDates.Dyear AS Dyear, dbo.DDates.Dmonth AS Dmonth, COUNT(T1.CountConsumers) AS CountConsumers
FROM (SELECT DISTINCT
CAST(CAST(YEAR(dbo.Service.begDate) AS char(4)) + '/' + CAST(MONTH(dbo.Service.begDate) AS char(2)) + '/' + '01' AS smalldatetime)
AS Bdate, dbo.Consumer.consumerID AS CountConsumers
FROM dbo.Consumer INNER JOIN
dbo.Service ON dbo.Consumer.consumerID = dbo.Service.ConsumerID) T1 RIGHT OUTER JOIN
dbo.DDates ON T1.Bdate = dbo.DDates.Ddate
GROUP BY dbo.DDates.Dyear, dbo.DDates.Dmonth
ORDER BY dbo.DDates.Dyear, dbo.DDates.Dmonth
You could put a criteria on Dyear and Dmonth on the date table to get a rolling 5 years worth of data and as it's obviously using a R.O.J you'll get a 0 count if no service occurs that month which you could remove with a simple <> 0 if necessary.
HTH
K.
February 21, 2007 at 2:40 pm
Thanks for the reply. In your example though, the second insert statement starts on 2/16/2006 which means that month 2, year 2006 should have a count of 1. That's why my example for 1 month used overlapping date ranges.
John Deupree
February 21, 2007 at 2:43 pm
Great idea! I had originally created a function that, given a date range, would create a table variable with the begin and end dates of every month in the range. I'll give this a try though.
Thanks
John Deupree
February 21, 2007 at 2:44 pm
Thanks. I'll give it a try.
John Deupree
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply