CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2008-r2
CodeNinja
CodeNinja

Reputation: 3278

Sql Query : Group by Month & Year for this case

I have a table Product with the following columns

ProductId Name RegistrationDate UnregistrationDate
1          AB    2013-01-01      2013-03-01
2          CD    2013-01-10      2013-03-13

etc

I would like to get a list of Registered Products per every month of a year.

Example : Year , Month and the number of dealers which are registered and not unregistered.

Year Month RegisteredProucts
2013  2         35
2013  3         45(includes products even registered before March 2013)

I wrote the follwing stored procedure to find the Registered products for one month: & it works : 

@Begin Time = First Day of the Month
@End Time = Last Day of the Month


select COUNT(DISTINCT P.ProductId) as RegisteredProducts from Product P    
where ((P.RegisteredDate < @EndTime)
AND (P.UnregisteredDate > @EndTime))

I then wrote the query below but it seems to group the results by the RegisteredDate. I would like to know how I can group registered products (which are not unregistered) by the end of each month for a duration of one year ?

select YEAR(P.RegisteredDate) AS [YEAR],MONTH(P.RegisteredDate) AS [MONTH],       COUNT(DISTINCT P.ProductId) as RegisteredProducts from Product P    
where ((P.RegisteredDate < @EndTime)
AND (P.UnregisteredDate > @EndTime))  
group by YEAR(D.RegisteredDate), MONTH(D.RegisteredDate)

Upvotes: 1

Views: 5157

Answers (2)

Quassnoi
Quassnoi

Reputation: 425823

WITH    months (mon) AS
        (
        SELECT  CAST('2013-01-01' AS DATE) AS mon
        UNION ALL
        SELECT  DATEADD(month, 1, mon)
        FROM    months
        WHERE   mon < DATEADD(month, -1, GETDATE())
        )
SELECT  mon, COUNT(productId)
FROM    months
LEFT JOIN
        registeredProducts
ON      registrationDate < DATEADD(month, 1, mon)
        AND (unregistrationDate >= mon OR unregistrationDate IS NULL)
GROUP BY
        mon

Upvotes: 1

Andomar
Andomar

Reputation: 238296

; with  months as
        (
        select  cast('2013-01-01' as date) as dt
        union all
        select  dateadd(month, 1, dt)
        from    months
        where   dt < '2014-01-01'
        )
select  *
from    months m
cross apply
        (
        select  count(*) as ProductCount
        from    Product p
        where   p.RegistrationDate < dateadd(month, 1, m.dt) and 
                (
                    UnregistrationDate is null
                    or UnregistrationDate >= m.dt
                )
        ) p

Example at SQL Fiddle.

Upvotes: 1

Related Questions