2

My data is like this from my tsql query.

enter image description here

The duration column is the count of 0 from the recent month backwards till it reach/hit 1. As of now I'm doing it via excel but can it be done via T-SQL itself? Here is my formula in excel.

IF(AND(E2=0,D2=1),1,IF(AND(E2=0,D2=0,C2=1),COUNTIFS(E2:D2,0),IF(AND(E2=0,D2=0,C2=0,B2=1),COUNTIFS(E2:C2,0)

... so on.

Here is my query:

CREATE TABLE #tempcomp (CompanyID nvarchar(max),[WithSalesGreaterThanTarget] int, [Month] DATE)



INSERT INTO #tempcomp (CompanyID,[WithSalesGreaterThanTarget],[Month])
VALUES ('C0002','1','2019-01-01'),
 ('C0002','1','2019-02-01'),
 ('C0002','1','2019-03-01'),
 ('C0002','0','2019-04-01'),
 ('C0002','0','2019-05-01'),
 ('C0002','1','2019-06-01'),
 ('C0002','1','2019-07-01'),
 ('C0002','1','2019-08-01'),
 ('C0001','1','2019-01-01'),
 ('C0001','1','2019-02-01'),
 ('C0001','1','2019-03-01'),
 ('C0001','1','2019-04-01'),
 ('C0001','1','2019-05-01'),
 ('C0001','1','2019-06-01'),
 ('C0001','1','2019-07-01'),
 ('C0003','1','2019-08-01'),
 ('C0003','1','2019-01-01'),
 ('C0003','1','2019-02-01'),
 ('C0003','1','2019-03-01'),
 ('C0003','1','2019-04-01'),
 ('C0003','1','2019-05-01'),
 ('C0003','1','2019-06-01'),
 ('C0003','0','2019-07-01'),
 ('C0003','0','2019-08-01')



 SELECT * FROM 
(
SELECT 
        t.CompanyID,
        t.[Month] as SalesMonth,
        t.WithSalesGreaterThanTarget as Met
 FROM #tempcomp t

) AS Sales
PIVOT ( SUM(Met)
FOR SalesMonth IN ([2019-01-01],[2019-02-01],[2019-03-01],[2019-04-01],[2019-05-01],[2019-06-01],[2019-07-01],[2019-08-01]))
as PVT





--DROP TABLE #tempcomp
1

You can join the pivoted table to an aggregate of the base table, but there's a catch here: if you're missing a record for a given month (i.e. C0001 is missing a record for 2019-08-01), it will not count this month because we can't count what doesn't exist in the table we're performing our aggregation against.

SELECT  Pivoted.*, DurationCalc.Duration
FROM    (
    SELECT * FROM 
    (
    SELECT 
            t.CompanyID,
            t.[Month] as SalesMonth,
            t.WithSalesGreaterThanTarget as Met
     FROM #tempcomp t

    ) AS Sales
    PIVOT ( SUM(Met)
        FOR SalesMonth IN ([2019-01-01],[2019-02-01],[2019-03-01],[2019-04-01],[2019-05-01],[2019-06-01],[2019-07-01],[2019-08-01]))
    as PVT
) Pivoted INNER JOIN
(
    SELECT  CompanyID, SUM(CASE [sumWSGTT] WHEN 0 THEN 1 ELSE 0 END) AS Duration
    FROM    (
        SELECT  CompanyID, SUM(WithSalesGreaterThanTarget) sumWSGTT, [Month]
        FROM    #tempcomp
        GROUP BY CompanyID, [Month]
    ) rlup
    GROUP BY CompanyID
) DurationCalc
    ON Pivoted.CompanyID = DurationCalc.CompanyID

This returns the results:

+-----------+------------+------------+------------+------------+------------+------------+------------+------------+----------+
| CompanyID | 2019-01-01 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 2019-08-01 | Duration |
+-----------+------------+------------+------------+------------+------------+------------+------------+------------+----------+
| C0001     |          1 |          1 |          1 |          1 |          1 |          1 |          1 | NULL       |        0 |
| C0002     |          1 |          1 |          1 |          0 |          0 |          1 |          1 | 1          |        2 |
| C0003     |          1 |          1 |          1 |          1 |          1 |          1 |          0 | 1          |        1 |
+-----------+------------+------------+------------+------------+------------+------------+------------+------------+----------+

A quick note here, because your sample input lists multiple August 2019 records for C0001, I'm performing some nested aggregation to rollup months and then count 0s from that result... yes, it's hideous.

If you want to treat NULLs as 0s in a programmatic fashion, you have to create the missing dates by joining the base table back to itself then aggregate the product, as follows:

SELECT  Pivoted.*, DurationCalc.Duration
FROM    (
        SELECT PVT.CompanyID,
             COALESCE(PVT.[2019-01-01], 0) AS [2019-01-01],
             COALESCE(PVT.[2019-02-01], 0) AS [2019-02-01],
             COALESCE(PVT.[2019-03-01], 0) AS [2019-03-01],
             COALESCE(PVT.[2019-04-01], 0) AS [2019-04-01],
             COALESCE(PVT.[2019-05-01], 0) AS [2019-05-01],
             COALESCE(PVT.[2019-06-01], 0) AS [2019-06-01],
             COALESCE(PVT.[2019-07-01], 0) AS [2019-07-01],
             COALESCE(PVT.[2019-08-01], 0) AS [2019-08-01]
    FROM 
    (
    SELECT 
            t.CompanyID,
            t.[Month] as SalesMonth,
            t.WithSalesGreaterThanTarget as Met
     FROM #tempcomp t

    ) AS Sales
    PIVOT ( SUM(Met)
        FOR SalesMonth IN ([2019-01-01],[2019-02-01],[2019-03-01],[2019-04-01],[2019-05-01],[2019-06-01],[2019-07-01],[2019-08-01]))
    as PVT
) Pivoted INNER JOIN
(
    SELECT xj_c_m.CompanyID, SUM(CASE ISNULL(rlup.sumWSGTT, 0) WHEN 0 THEN 1 ELSE 0 END) AS Duration
    FROM    (
            SELECT  CompanyID, SUM(WithSalesGreaterThanTarget) sumWSGTT, [Month]
            FROM    #tempcomp
            GROUP BY CompanyID, [Month]
            ) rlup RIGHT JOIN (
            SELECT  *
            FROM    (
                SELECT  [Month]
                FROM #tempcomp
                GROUP BY [Month]
            ) t_months CROSS JOIN
                (
                SELECT  CompanyID
                FROM #tempcomp
                GROUP BY CompanyID
            ) t_companies
        ) xj_c_m ON xj_c_m.CompanyID = rlup.CompanyID
            AND xj_c_m.[Month] = rlup.[Month]
    GROUP BY xj_c_m.CompanyID
) DurationCalc
    ON Pivoted.CompanyID = DurationCalc.CompanyID

Which returns the following results:

+-----------+------------+------------+------------+------------+------------+------------+------------+------------+----------+
| CompanyID | 2019-01-01 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 2019-08-01 | Duration |
+-----------+------------+------------+------------+------------+------------+------------+------------+------------+----------+
| C0001     |          1 |          1 |          1 |          1 |          1 |          1 |          1 |          0 |        1 |
| C0002     |          1 |          1 |          1 |          0 |          0 |          1 |          1 |          1 |        2 |
| C0003     |          1 |          1 |          1 |          1 |          1 |          1 |          0 |          1 |        1 |
+-----------+------------+------------+------------+------------+------------+------------+------------+------------+----------+

Final note here, this is just to demonstrate a way you can perform this and by no means am I implying this is the most efficient or optimal way to achieve your desired results, but hopefully gives you some tricks you can use to get what you want with minimal work.

0

You may want T-SQL as follows which you can start with, you can add remaining columns with same CASE expression.


 SELECT 
        t.CompanyID,
        SUM (CASE WHEN (FORMAT(t.[Month], 'MMM')) = 'JAN' Then t.WithSalesGreaterThanTarget else 0 end) as JAN,
        SUM (CASE WHEN (FORMAT(t.[Month], 'MMM')) = 'FEB' Then t.WithSalesGreaterThanTarget else 0 end) as FEB,
        SUM (CASE WHEN (FORMAT(t.[Month], 'MMM')) = 'MAR' Then t.WithSalesGreaterThanTarget else 0 end) as MAT
FROM #tempcomp t
Group by t.CompanyID 

P.S: Adding desired result also in your question would help you to get better answer

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.