vendredi 5 mai 2017

SQL - Group many categories into few categories

I have a query. It returns rows, with one column being a status column, and one column being an elapsed time column. There are ~8 status categories. I want to sum certain statuses and their elapsed times into ~3 categories. Anyone have an idea how I can do this?

Here is the current query, which splits entries into bins by day.

declare @STARDTZ as datetime2
declare @LASTDTZ as datetime2

declare @STARTDT as datetime2
declare @LASTDT as datetime2

set @LASTDT = getdate()
set @STARTDT = DATEADD(dd,-7,getdate())

SET @STARDTZ = DATEADD(dd, DATEDIFF(dd, 0, @STARTDT), 0) --set the start time to 12am (zero) given input datetime
SET @LASTDTZ = DATEADD(dd, DATEDIFF(dd, 0, @LASTDT), 0) --set the stop time to 12am (zero) given input stoptime
;
with dates(dt) as (select @STARDTZ union all select dateadd(day,1,dt) from dates where dt < @LASTDTZ)
select t.Name
,case when t.StartDT>=d.dt then t.StartDT else d.dt end as StartDT
,case when datediff(day,LastDT,dt)=0 then t.LastDT else dateadd(day,1,d.dt) end as LastDT 
,datediff(millisecond,case when t.StartDT>=d.dt then t.StartDT else d.dt end
,case when datediff(day,LastDT,dt)=0 then t.LastDT else dateadd(day,1,d.dt) end)/60000.0 as IntervalMin
,t.FullName
,DATEADD(dd, DATEDIFF(dd, 0, dt), 0) as DateBin

from 
(
    SELECT
    CoatingChamberStates.Name as Name
    ,c.StartDT as StartDT
    ,c.LastDT as LastDT
    ,c.IntervalMin
    ,CoatingChambers.FullName
    ,DATEADD(dd, DATEDIFF(dd, 0, StartDT), 0) as DateBin

    FROM [REO].[dbo].CoatingChamberStateLogs as c
    INNER JOIN CoatingChamberStates on c.CoatingChamberStatesID = CoatingChamberStates.CoatingChamberStatesID
    INNER JOIN CoatingChambers on c.CoatingChambersID = CoatingChambers.CoatingChambersID

    where c.StartDT >= @STARDTZ
    and c.LastDT <= @LASTDTZ


) as t

join dates d on d.dt >= cast(t.StartDT as date) and d.dt<=cast(t.LastDT as date)

group by DateBin, StartDT, Name, dt, LastDT, FullName

Here is what it returns:

Name    StartDT LastDT  IntervalMin FullName    DateBin
UNKNOWN 2017-04-28 00:05:31.1335380 2017-04-28 11:36:40.1200328 691.1497833 SPECTOR7                2017-04-28 00:00:00.000
UNKNOWN 2017-04-28 00:06:31.6555661 2017-04-28 00:07:31.5121862 0.9976166   LOTUS3                  2017-04-28 00:00:00.000
IDLE    2017-04-28 00:07:31.5121862 2017-04-28 11:53:39.7066464 706.1365666 LOTUS3                  2017-04-28 00:00:00.000
UNKNOWN 2017-04-28 00:50:32.1654631 2017-04-28 00:51:32.0670071 0.9983666   LOTUS1                  2017-04-28 00:00:00.000
RUN_PARTS   2017-04-28 00:51:32.0670071 2017-04-28 00:53:32.0966491 2.0004833   LOTUS1                  2017-04-28 00:00:00.000
IDLE    2017-04-28 00:53:32.0966491 2017-04-28 05:50:36.3819295 297.0714166 LOTUS1                  2017-04-28 00:00:00.000
RUN_OTHER   2017-04-28 00:53:32.1747858 2017-04-28 12:03:40.0639753 670.1314833 OXFORD2                 2017-04-28 00:00:00.000
IDLE    2017-04-28 01:00:32.3043454 2017-04-28 02:26:33.3361640 86.0172000  SPECTOR1                2017-04-28 00:00:00.000
IDLE    2017-04-28 01:50:32.8690160 2017-04-28 07:07:37.3384170 317.0744833 IONTECH                 2017-04-28 00:00:00.000
IDLE    2017-04-28 01:57:32.9759673 2017-04-28 02:36:33.6234811 39.0108000  LOTUS8                  2017-04-28 00:00:00.000
IDLE    2017-04-28 02:04:33.0037095 2017-04-28 03:10:33.8922959 66.0148166  LOTUS6                  2017-04-28 00:00:00.000
UNKNOWN 2017-04-28 02:12:33.2932821 2017-04-28 02:13:33.1640310 0.9978500   SPECTOR6                2017-04-28 00:00:00.000
IDLE    2017-04-28 02:12:33.3089156 2017-04-28 05:27:36.3023807 195.0499000 SPECTOR8                2017-04-28 00:00:00.000
RUN_PARTS   2017-04-28 02:13:33.1640310 2017-04-28 02:31:33.4447516 18.0046666  SPECTOR6                2017-04-28 00:00:00.000
RUN_TEST    2017-04-28 02:26:33.3361640 2017-04-28 03:43:34.4053900 77.0178166  SPECTOR1                2017-04-28 00:00:00.000
IDLE    2017-04-28 02:31:33.4447516 2017-04-28 04:27:35.0346386 116.0265000 SPECTOR6                2017-04-28 00:00:00.000

I want to combine the elapsed times for certain categories into 3 categories: "idle", "running", and "waste", so for example, "run_parts" and "run_test" would be summed up into "running".

Does that make sense? How can I approach this? Thanks!

Aucun commentaire:

Enregistrer un commentaire