-2

Here is my sample data:

 ID |      Load Dt     |     Start Dt     |    Complete Dt   
----+------------------+------------------+------------------
  1 |    3/2/2017 9:17 |    6/8/2017 9:59 |    6/8/2017 9:59 
  2 |    2/2/2017 9:13 |  4/21/2017 13:48 |   5/1/2017 10:12 
  3 |    3/2/2017 9:17 |  4/27/2017 17:58 |  4/27/2017 17:58 
  4 |    4/3/2017 8:46 |  4/27/2017 15:52 |  4/27/2017 15:52 
  5 |    4/3/2017 8:46 |  5/15/2017 11:33 |  5/15/2017 11:33 

Below are my expected results. I want to use the year and month from Load Dt, Start Dt, and Complete Dt to generate a month-by-month count of how many rows fell into that month for each column.

Is there an alternative to using a UNION as the execution time is an issue. Can a case statement be used?

  Yr-Mth  | ID Load Cnt | ID Start Cnt | ID Complete Cnt 
----------+-------------+--------------+-----------------
  2017-02 |       1     |        0     |           0     
  2017-03 |       2     |        0     |           0     
  2017-04 |       2     |        3     |           2     
  2017-05 |       0     |        1     |           2     
  2017-06 |       0     |        1     |           1     
  • 3
    Yes, I have a thought. Why don't you post DDL (CREATE TABLE mytab (...);), DML (INSERT INTO mytab VALUES (...);), tell us the result you want and the logic used to get it? Your question is not clear! – Vérace Nov 12 '17 at 1:40
  • 1
    Please read meta.stackoverflow.com/questions/285551/… and the accepted answer – a_horse_with_no_name Nov 13 '17 at 15:37
  • It's generally preferable to include sample data and results as formatted text rather than images; that way, people looking to help you can copy the data without worrying as much about introducing typos, etc. That said, this is clearer. Still one point of confusion - Is there a typo in the results? I would expect to 2 for ID Complete Count for 2017-04, not 0. – RDFozz Nov 13 '17 at 17:01
  • You are correct, ID Complete Cnt 2017-04 should be 2. – user138388 Nov 13 '17 at 19:24
  • I've taken the liberty to replace your images with formatted text, and to correct the typo in the sample results. Feel free to revert the changes if they seem incorrect, or to modify them to make them more correct. – RDFozz Nov 13 '17 at 22:29
0

I used a combination of UNPIVOT and GROUP BY to come up with a solution.

--Data setup
set nocount on
Declare @Table1 table
    (ID int, LoadDt datetime, StartDt datetime, CompleteDt datetime)
;

INSERT INTO @Table1
    (ID, LoadDt, StartDt, CompleteDt)
VALUES
    (1, '2017-03-02 04:17:00', '2017-06-08 05:59:00', '2017-06-08 05:59:00'),
    (2, '2017-02-02 04:13:00', '2017-04-21 09:48:00', '2017-05-01 06:12:00'),
    (3, '2017-03-02 04:17:00', '2017-04-27 13:58:00', '2017-04-27 13:58:00'),
    (4, '2017-04-03 04:46:00', '2017-04-27 11:52:00', '2017-04-27 11:52:00'),
    (5, '2017-04-03 04:46:00', '2017-05-15 07:33:00', '2017-05-15 07:33:00')
;

--Here is the solution
--Use UNPIVOT to get a result set of distinct month/years for all date columns
WITH DistinctYearAndMonths
AS (
    SELECT DISTINCT Month(MonthAndYear) AS MonthDate
        ,Year(MonthAndYear) AS YearDate
    FROM (
        SELECT LoadDt
            ,StartDt
            ,CompleteDt
        FROM @Table1
        ) AS cp
    UNPIVOT(MonthAndYear FOR MonthAndYears IN (
                LoadDt
                ,StartDt
                ,CompleteDt
                )) AS upvt
    )
--Cross join to source table, group by month/year and sum counts
    ,GroupedData
AS (
    SELECT ym.MonthDate
        ,ym.YearDate
        ,sum(CASE 
                WHEN ym.MonthDate = month(t1.LoadDt)
                    AND ym.YearDate = year(t1.LoadDt)
                    THEN 1
                ELSE 0
                END) AS LoadCnt
        ,sum(CASE 
                WHEN ym.MonthDate = month(t1.StartDt)
                    AND ym.YearDate = year(t1.StartDt)
                    THEN 1
                ELSE 0
                END) AS StartCnt
        ,sum(CASE 
                WHEN ym.MonthDate = month(t1.CompleteDt)
                    AND ym.YearDate = year(t1.CompleteDt)
                    THEN 1
                ELSE 0
                END) AS CompleteCnt
    FROM DistinctYearAndMonths ym
    CROSS JOIN @Table1 t1
    GROUP BY ym.MonthDate
        ,ym.YearDate
    )
--Final select to create the [Yr-Mth] column and grouped counts
SELECT convert(VARCHAR(4), YearDate) + '-' + right('0' + convert(VARCHAR(2), MonthDate), 2) AS [Yr-Mth]
    ,LoadCnt
    ,StartCnt
    ,CompleteCnt
FROM GroupedData

| Yr-Mth  | LoadCnt | StartCnt | CompleteCnt |
|---------|---------|----------|-------------|
| 2017-02 | 1       | 0        | 0           |
| 2017-03 | 2       | 0        | 0           |
| 2017-04 | 2       | 3        | 2           |
| 2017-05 | 0       | 1        | 2           |
| 2017-06 | 0       | 1        | 1           |
0

Test my Script,

    set nocount on
Declare @Table1 table
    (ID int, LoadDt datetime, StartDt datetime, CompleteDt datetime)
;

INSERT INTO @Table1
    (ID, LoadDt, StartDt, CompleteDt)
VALUES
    (1, '2017-03-02 04:17:00', '2017-06-08 05:59:00', '2017-06-08 05:59:00'),
    (2, '2017-02-02 04:13:00', '2017-04-21 09:48:00', '2017-05-01 06:12:00'),
    (3, '2017-03-02 04:17:00', '2017-04-27 13:58:00', '2017-04-27 13:58:00'),
    (4, '2017-04-03 04:46:00', '2017-04-27 11:52:00', '2017-04-27 11:52:00'),
    (5, '2017-04-03 04:46:00', '2017-05-15 07:33:00', '2017-05-15 07:33:00'),
    (6, '2017-08-03 04:46:00', '2017-07-15 07:33:00', '2017-05-15 07:33:00'),
    (7, '2017-08-03 04:46:00', '2017-07-15 07:33:00', '2017-05-15 07:33:00'),
    (8, '2017-08-03 04:46:00', '2017-07-15 07:33:00', '2017-05-15 07:33:00')
;
DECLARE @FromDate datetime='2017-01-01'
declare @ToDate datetime='2017-06-30'

;with CTE as
(
select @FromDate FirstDoM
union ALL
select DATEADD(month,1,FirstDoM) from CTE
where FirstDoM<@ToDate
)

SELECT cast(year(c.FirstDoM) AS VARCHAR) + '-' 
            + right('00' + cast(month(c.FirstDoM) AS VARCHAR), 2)
    ,sum(CASE 
            WHEN month(c.FirstDoM) = month(oa.LoadDt)
                AND year(c.FirstDoM) = year(oa.LoadDt)
                THEN 1
            ELSE 0
            END) AS LoadCnt
    ,sum(CASE 
            WHEN month(c.FirstDoM) = month(oa.StartDt)
                AND year(c.FirstDoM) = year(oa.StartDt)
                THEN 1
            ELSE 0
            END) AS StartCnt
    ,sum(CASE 
            WHEN month(c.FirstDoM) = month(oa.CompleteDt)
                AND year(c.FirstDoM) = year(oa.CompleteDt)
                THEN 1
            ELSE 0
            END) AS CompleteCnt
FROM CTE C
OUTER APPLY (
    SELECT *
    FROM @Table1 T
    WHERE t.LoadDt BETWEEN @FromDate
            AND @ToDate
    ) oa
GROUP BY year(c.FirstDoM)
    ,month(c.FirstDoM)

Explanation :

i) If you have Datecalender table then there is no need of recursive CTE.

ii) Some kind of filtering is important inside outer apply like in example above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.