CODEWITHSUNDEEP
sql-servert-sql
Philip
Philip

Reputation: 2628

Combining two queries together to form month count

I currently have the following query:

select 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear, count(*) as Count
from 
    visit
where 
    StartDate >= DATEADD(year,-1,GETDATE())
    and clientid = 142
group by  
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)

which returns the following results:

+-------------------------+----+
| 2015-12-01 00:00:00.000 |  1 |
| 2016-02-01 00:00:00.000 | 13 |
| 2016-03-01 00:00:00.000 |  1 |
| 2016-04-01 00:00:00.000 | 22 |
| 2016-05-01 00:00:00.000 | 22 |
| 2016-06-01 00:00:00.000 | 25 |
| 2016-07-01 00:00:00.000 | 36 |
+-------------------------+----+

I also have the following query:

SELECT TOP (12) 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - test, 0) as        MonthYear
FROM
    test

which returns the following results:

+-------------------------+
| 2016-10-01 00:00:00.000 |
| 2016-09-01 00:00:00.000 |
| 2016-08-01 00:00:00.000 |
| 2016-07-01 00:00:00.000 |
| 2016-06-01 00:00:00.000 |
| 2016-05-01 00:00:00.000 |
| 2016-04-01 00:00:00.000 |
| 2016-03-01 00:00:00.000 |
| 2016-02-01 00:00:00.000 |
| 2016-01-01 00:00:00.000 |
| 2015-12-01 00:00:00.000 |
| 2015-11-01 00:00:00.000 |
+-------------------------+

What I need to do is combine the two queries into one query and show 0 for those months that are missing from the original query.

Could someone help please?

Upvotes: 9

Views: 530

Answers (10)

Maha Khairy
Maha Khairy

Reputation: 352

you can format the query as follows 

;with CTE AS (select 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear, count(*) as Count
from 
    visit
where 
    StartDate >= DATEADD(year,-1,GETDATE())
    and clientid = 142
group by  
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)

)
, cte2 AS(
SELECT TOP (12) 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - test, 0) as        MonthYear
FROM
    test )

    SELECT ISNULL(cte.MonthYear, cte2.MonthYear) AS MonthYear , ISNULL(cte.Count, 0) as Count
    from cte FULL OUTER join cte2 on cte.MonthYear = cte2.MonthYear

Upvotes: 0

Matt
Matt

Reputation: 14361

so there are a lot of answers and good ones about how to create a MONTH table then left join your visits to get to your answer. But I just wanted to show you how conditional Aggregation could assist you if there will always be at least 1 date from each month in the visit table. Note, I do not mean 1 record for the client you are filtering but rather for any/all clients. In other words will there be at least 1 client in every month? If so you can do conditional aggregation to get to answer like so:

select 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear
    ,count(CASE WHEN clientid = 142 THEN clientid END) as Count
from 
    visit
where 
    StartDate >= DATEADD(year,-1,GETDATE())
group by  
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)

If not then yep you will need a date table @pacreely shows you a nice method of doing that.

Upvotes: 0

pacreely
pacreely

Reputation: 1931

This method uses CROSS APPLY with VALUES to create your Master Month List.

I recommend replacing GETDATE() with a variable if you can.

CREATE TABLE #Visit(StartDate DATE,clientid int)

INSERT INTO #Visit VALUES
('20160304',142)
,('20160305',142)
,('20160508',142)
,('20160612',142)
,('20160617',142)

SELECT 
    T.Mnth
    ,COUNT(V.StartDate) AS [Count]
FROM 
     (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) c (test)
        CROSS APPLY (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - c.test, 0) Mnth) T 
        LEFT JOIN #Visit V
            ON
            DATEDIFF(MM,V.startdate,T.Mnth) = 0
            AND StartDate >= DATEADD(year,-1,GETDATE())
            AND clientid = 142
GROUP BY
    T.Mnth
ORDER BY 
    T.Mnth DESC

Upvotes: 1

tlemaster
tlemaster

Reputation: 859

After assuming that the "test" table is a table listing the first of each month for at least the past year and may include future dates as well, here is my suggestion:

With VisitList as
(SELECT 
DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear, count(*) as VisitCount
FROM visit
WHERE
StartDate >= DATEADD(year,-1,GETDATE())
and clientid = 142
GROUP BY  
DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)

UNION

SELECT testDate, 0 AS VisitCount 
FROM test
WHERE testDate BETWEEN DATEADD(YEAR, -1, GETDATE()) AND GETDATE())

SELECT MonthYear, Sum(VisitCount) AS VisitCount FROM VisitList
GROUP BY MonthYear
ORDER BY MonthYear DESC

Upvotes: -1

Dexter
Dexter

Reputation: 31

declare @Dates table(date datetime, count int)

select 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear, count(*) as Count
    into @Dates
from 
    visit
where 
    StartDate >= DATEADD(year,-1,GETDATE())
    and clientid = 142
group by  
    DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)

    SELECT TOP (12) 
    into @Dates
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - test, 0) as        MonthYear,0
FROM
    test

    select * from @Dates

Upvotes: -1

IVNSTN
IVNSTN

Reputation: 9316

;with cteCalendar as 
(
  SELECT TOP (12) 
      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - test, 0) as        MonthYear
  FROM
      test
),
cteVisits as
(
  select 
      DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear, count(*) as Count
  from 
      visit
  where 
      StartDate >= DATEADD(year,-1,GETDATE())
      and clientid = 142
  group by  
      DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)
)
SELECT
  c.MonthYear,
  ISNULL(v.Count, 0) as VisitCount
FROM cteCalendar c
LEFT JOIN cteVisits v
ON v.MonthYear = c.MonthYear
ORDER BY c.MonthYear

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82010

I often use a TVF to create dynamic date/time ranges. This udf is faster than the recursive cte approach (especially on larger sets). Furthermore, you get to define the date range, datepart, and increment. I should note, a tally table would do the trick as well.

-- Generate Some Sample Data
Declare @Visit table (StartDate date,Cnt int)
Insert Into @Visit values
('2015-12-01 00:00:00.000', 1),
('2016-02-01 00:00:00.000',13),
('2016-03-01 00:00:00.000', 1),
('2016-04-01 00:00:00.000',22),
('2016-05-01 00:00:00.000',22),
('2016-06-01 00:00:00.000',25),
('2016-07-01 00:00:00.000',36)

-- Set Key Dates
Declare @DateR2 date = '2016-10-01'
Declare @DateR1 date = DateAdd(YY,-1,@DateR2)

-- Execute Query
Select DateR1
      ,Cnt    = isnull(sum(Cnt),0)
 From  (Select DateR1=RetVal,DateR2=DateAdd(MM,1,RetVal)-1 From [dbo].[udf-Range-Date](@DateR1,@DateR2,'MM',1)) A
 Left  Join @Visit B on B.StartDate Between A.DateR1 and A.DateR2
 Group By DateR1
 Order by DateR1

Returns

Period      Cnt
2015-10-01  0
2015-11-01  0
2015-12-01  1
2016-01-01  0
2016-02-01  13
2016-03-01  1
2016-04-01  22
2016-05-01  22
2016-06-01  25
2016-07-01  36
2016-08-01  0
2016-09-01  0
2016-10-01  0

The UDF if needed

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15185

If you are going to be doing a lot of gaps and island calculation and or date based queries without gaps then I cannot stress how much simpler a Calendar table will make your life.

Basically, all you need is a table similar to:

Calender

    Day INT,
    Month INT, 
    Year INT, 
    StartOfDay DATETIME,
    EndOfDay DATETIME,
    DayOfWeek INT, 
    WeekInyear INT, 
    IsWeekDay BIT

Pick a date range. I chose this century and last century as it fits my business models needs. Next, simply run a loop function once and populate your table, finally apply proper indexes and use as needed.

DECLARE @BeginDate DATETIME ='01/01/2015'
DECLARE @EndDate DATETIME   ='01/01/2017' 

DECLARE @MONTHS TABLE(Month INT,Year INT)

WHILE @BeginDate<=@EndDate BEGIN
    INSERT @Months SELECT DATEPART(MONTH,@BeginDate),DATEPART(YEAR,@BeginDate)
    SET @BeginDate=DATEADD(MONTH,1,@BeginDate)
END

SELECT 
    Calendar.Month,
    Calendar.Year,
    COUNT(visit.userID) 
FROM 
    @Months Calendar
    LEFT JOIN visit ON visit.year=Calendar.Year AND visit.Month=Calendar.Month
GROUP BY
    Calendar.Month,
    Calendar.Year

Upvotes: 0

Steve Chambers
Steve Chambers

Reputation: 39454

A couple of subqueries with a LEFT JOIN and an ISNULL should do it:

SELECT sub1.MonthYear,
       ISNULL(sub2.Count, 0)
FROM
(SELECT TOP (12) DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - test, 0) AS MonthYear
 FROM test t) sub1
LEFT JOIN
(SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear,
        COUNT(*) AS Count
 FROM visit
 WHERE StartDate >= DATEADD(year,-1,GETDATE())
   AND clientid = 142
 GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)) sub2
ON sub1.MonthYear = sub2.MonthYear

Upvotes: 0

GHWP
GHWP

Reputation: 77

Traditionally this is solved with a calendar table (eg this msdn answer), but in your case a derived table and outer join should work:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0) as MonthYear,count(*) as Count
    from (SELECT TOP (12) DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-test, 0) as MonthYear
  from test) cal LEFT OUTER JOIN visit on (cal.MonthYear = visit.MonthYear)
    where StartDate >= DATEADD(year,-1,GETDATE())
    and clientid = 142
    group by  DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDate), 0)

Upvotes: 3

Related Questions