T-SQL aggregate quetion

  • 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

  • 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.

  • 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

  • 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

  • Very nice PW! Great example of recursive CTEs too!

    <jealous>I wish I'd thought of that</jealous>

    SQL guy and Houston Magician

  • 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.

     

  • 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

  • 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

  • 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