Sfrgttk

Recommendation letter by significant other if you worked with them professionally?

How strictly should I take "Candidates must be local"?

What was the Kree's motivation in Captain Marvel?

Why would one plane in this picture not have gear down yet?

Why is computing ridge regression with a Cholesky decomposition much quicker than using SVD?

Good for you! in Russian

How to secure an aircraft at a transient parking space?

Is it "Vierergruppe" or "Viergruppe", or is there a distinction?

Does this video of collapsing warehouse shelves show a real incident?

Can Mathematica be used to create an Artistic 3D extrusion from a 2D image and wrap a line pattern around it?

How to draw cubes in a 3 dimensional plane

Latex does not go to next line

How does one describe somebody who is bi-racial?

Signed and unsigned numbers

How does NOW work?

NASA's RS-25 Engines shut down time

Difference on montgomery curve equation between EFD and RFC7748

Does "Until when" sound natural for native speakers?

Word for a person who has no opinion about whether god exists

How to detect if C code (which needs 'extern C') is compiled in C++

How did Alan Turing break the enigma code using the hint given by the lady in the bar?

When a wind turbine does not produce enough electricity how does the power company compensate for the loss?

Bash script should only kill those instances of another script's that it has launched

They call me Inspector Morse

Using group by in CT with Cross Apply in SQL Server 2008

Calculate Average between two dates of different rowsWhat is the optimal way to design an approval queue and audit trail for entities in SQL Server 2008 R2?SQL Server 2008, cpu 100% until recreate an indexSQL Server 2008 R2 - VLDB Backup StrategiesAttempting to tune a Sharepoint site using SQL Server Waits and QueuesGetting File not found error doing point in time database restore in SQL Server 2008How do you translate this into SQL Server 2008 statement?Group daily schedule into [Start date; End date] intervals with the list of week daysHow to convert datetime string of different formats in a valid datetime in SQL 2008Calculate Average between columns by comparing two rows in SQL ServerCalculate Average between two dates of different rows

0

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, 18, '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.

DBFiddle

Thanks

sql-server-2008

share

asked 1 min ago

SP1SP1

1554

add a comment | 

0

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, 18, '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.

DBFiddle

Thanks

sql-server-2008

share

asked 1 min ago

SP1SP1

1554

add a comment | 

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, 18, '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.

DBFiddle

Thanks

sql-server-2008

share

asked 1 min ago

SP1SP1

1554

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, 18, 'Approved',  '20171114')

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

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

share

asked 1 min ago

SP1SP1

1554

share

asked 1 min ago

SP1SP1

1554

asked 1 min ago

SP1SP1

1554

asked 1 min ago

SP1SP1

1554