CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SP1
SP1

Reputation: 1202

Using group by in Common Table Expression in SQL Server 2008

I am trying to do an average between different rows data as shown below.

Here's my original Questions which McNets answered.

So basically here is the table

create table t (id int identity, BIDID int,  AppID int, AppStatus varchar(20), [Time] date);
insert into t values
(23390, 16, 'In Review', '20170307'), 
(23390, 16, 'Approved',  '20170309'), 
(23390, 16, 'In Review', '20171110'), 
(23390, 16, 'Approved',  '20171112'), 
(23390, 17, 'In Review',  '20171114'),
(23390, 18, 'Approved',  '20171112'), 
(23390, 16, 'Approved',  '20171114'),
(23391, 17, 'In Review',  '20171112'), 
(23391, 17, 'Approved',  '20171114')

I am trying to calculate the average between In Review and Approved values only first between an individual AppID and then doing the Average in different AppId's in a individual bid and then finally doing the Average between all the bids to come to a single number

Here is the code for it 

SELECT
    CASE WHEN t1.AppStatus = 'In Review' AND t2.AppStatus = 'Approved'
         THEN DATEDIFF(day, t1.[Time], t2.[Time])
         ELSE 0
    END as Days
FROM
    t t1
CROSS APPLY(SELECT TOP 1 * 
            FROM t
            WHERE id > t1.id
            ORDER BY id) t2

and this gets me the Average

WITH ct AS
(
SELECT
    CASE WHEN t1.AppStatus = 'In Review' AND t2.AppStatus = 'Approved'
         THEN DATEDIFF(day, t1.[Time], t2.[Time])
         ELSE 0
    END as Days
FROM
    t t1
CROSS APPLY(SELECT TOP 1 * 
            FROM t
            WHERE id > t1.id
            ORDER BY id) t2
)
SELECT
    SUM(days) / COUNT(*) Average
FROM
    ct
WHERE
    days <> 0

This currently does not do any grouping of any sort so it just takes into account first In Review and then finds the next Approved and so on. I need to group it first by AppID to calculate individual AppId average and then do the average of different AppId's inside a bid and then do an average of the bid's

Can someone please tell me how should I add the grouping.

Expected Result 

    (1 23390, 16, 'In Review', '20170308'), 
    (2 23390, 16, 'Approved',  '20170309'), 
    (3 23390, 16, 'In Review', '20171110'), 
    (4 23390, 16, 'Approved',  '20171112'), 
    (5 23390, 17, 'In Review',  '20171114'),
    (6 23390, 18, 'In Review',  '20171112'), 
    (7 23390, 16, 'Approved',  '20171114'),
    (8 23391, 17, 'In Review',  '20171112'), 
    (9 23391, 17, 'Approved',  '20171114')

23390 AppID16 FirstData Id1 to Id2  1Day       (In Review -> Approved)
23390 AppID16 SecondData Id3 to Id4 2Day
23390 AppID16 ThirdData Id7 Ignored Since no In Review Before..

Avg AppID 16 = 1.5 days

23390 AppID 17 ignored since no Approved after in review
23390 AppID 18 Ignored 

Avg for 23390 = 1.5

..similarly calculate average for other bids

23391 AppID 17 Days 2.

So Total Avg between bids is 2 + 1.5 / 2

DBFiddle

Thanks

Upvotes: 1

Views: 51

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Here is one way to do it. Very straight-forward. Each step is wrapped in a CTE. Examine intermediate results of each CTE to understand how it works and see the comments in the code.

Sample data

create table t (id int identity, BIDID int,  AppID int, AppStatus varchar(20), dt date);
insert into t values
(23390, 16, 'In Review', '20170308'), 
(23390, 16, 'Approved',  '20170309'), 
(23390, 16, 'In Review', '20171110'), 
(23390, 16, 'Approved',  '20171112'), 
(23390, 17, 'In Review',  '20171114'),
(23390, 18, 'Approved',  '20171112'), 
(23390, 16, 'Approved',  '20171114'),
(23391, 17, 'In Review',  '20171112'), 
(23391, 17, 'Approved',  '20171114');

Query

I adjusted your CROSS APPLY to match the logic you described in the question.

WITH
-- Find matching pairs and calculate the difference of individual dates
CTE_Diffs
AS
(
    SELECT
        MainT.id
        ,MainT.BIDID
        ,MainT.AppID
        ,CAST(DATEDIFF(day, MainT.dt, NextT.dt) AS float) AS Diff
    FROM
        T AS MainT
        CROSS APPLY
        (
            SELECT TOP 1 
                T.AppStatus
                ,T.dt
            FROM T
            WHERE
                -- the matching row must be from the same Bid and App
                -- and have a higher ID
                T.BIDID = MainT.BIDID
                AND T.AppID = MainT.AppID
                AND T.id > MainT.id
            ORDER BY
                T.id
        ) AS NextT
    WHERE
        -- the pair is counted only when it starts with 'In Review' and finishes with 'Approved' 
        MainT.AppStatus = 'In Review'
        AND NextT.AppStatus = 'Approved'
)
-- Average by Bid and App
,CTE_AvgBidApp
AS
(
    SELECT
        BIDID
        ,AppID
        ,AVG(Diff) AS AvgBidApp
    FROM CTE_Diffs
    GROUP BY
        BIDID
        ,AppID
)
-- Average by Bid
,CTE_AvgBid
AS
(
    SELECT
        BIDID
        ,AVG(AvgBidApp) AS AvgBid
    FROM CTE_AvgBidApp
    GROUP BY
        BIDID
)
-- Final average
SELECT
    AVG(AvgBid) AS TotalAvg
FROM CTE_AvgBid
;

Result

TotalAvg
1.75

Clean up

DROP TABLE t;

Upvotes: 1

Related Questions