CODEWITHSUNDEEP
sqlsql-server-2008
Obfuscated
Obfuscated

Reputation: 321

Running total but different

My current query... 

SELECT datepart(hour,s.prodtime) ProdHr
    , Min(distinct R.FinalProd) Prod
    , MIN(r.chargeseq) StartSeq
    , MAX(r.chargeseq) EndSeq
    , MIN(S.RollRecID) RID
    , (MIN(r.locseq) + COUNT(DISTINCT S.RollRecID) - 1) endd
    FROM NYS1Reheat R INNER JOIN NYS1SawPieces S ON R.locseq = S.RollRecID
    where s.ShiftIdent = '06/03/15154B' and r.Location = 'HISTORY'
    GROUP BY datepart(hour,s.prodtime)
    order by RID

It returns ...

17  S18X70          14  17  542999660   542999663
18  S18X70          18  41  542999700   542999720
19  S18X54.7        40  61  542999920   542999940
20  S18X54.7        62  79  543000140   543000157
21  S18X54.7        80  105 543000320   543000345
22  S18X54.7        106 133 543000580   543000606
23  S18X54.7        134 171 543000860   543000897
1   W16X100         1   5   543001250   543001254
2   W16X100         6   17  543001300   543001311
3   W16X100         18  32  543001420   543001434
4   W16X77          33  42  543001570   543001578
5   W16X77          41  51  543001650   543001659

With the last two columns (9 digit numbers), I need to subtract the first from the second and create another column/field with the difference for each line. Any help please?

Upvotes: 0

Views: 25

Answers (1)

Tom
Tom

Reputation: 7740

You can either do it inline:

SELECT datepart(hour,s.prodtime) ProdHr
, Min(distinct R.FinalProd) Prod
, MIN(r.chargeseq) StartSeq
, MAX(r.chargeseq) EndSeq
, MIN(S.RollRecID) RID
, (MIN(r.locseq) + COUNT(DISTINCT S.RollRecID) - 1) endd
, (MIN(r.locseq) + COUNT(DISTINCT S.RollRecID) - 1) - MIN(S.RollRecID) ColumnName
FROM NYS1Reheat R INNER JOIN NYS1SawPieces S ON R.locseq = S.RollRecID
where s.ShiftIdent = '06/03/15154B' and r.Location = 'HISTORY'
GROUP BY datepart(hour,s.prodtime)
order by RID

Or put this all into a subquery and call it by name:

SELECT tbl.*, tbl.endd - tbl.RID as ColumnName
FROM
(
    SELECT datepart(hour,s.prodtime) ProdHr
    , Min(distinct R.FinalProd) Prod
    , MIN(r.chargeseq) StartSeq
    , MAX(r.chargeseq) EndSeq
    , MIN(S.RollRecID) RID
    , (MIN(r.locseq) + COUNT(DISTINCT S.RollRecID) - 1) endd
    FROM NYS1Reheat R INNER JOIN NYS1SawPieces S ON R.locseq = S.RollRecID
    where s.ShiftIdent = '06/03/15154B' and r.Location = 'HISTORY'
    GROUP BY datepart(hour,s.prodtime)
    order by RID
) as tbl

Upvotes: 1

Related Questions