none
Bir alt satır ile üst satır hesaplama. RRS feed

  • Soru

  • Merhabalar,

    Arkadaşlar kolay gelsin, aşağıdaki bir sonraki satırdan bir önceki satırı çıkartıp sonucu LastTotal e yazmak istiyorum nasıl yapa
    bilirim örn (Time:01:00) 600 - (Time:01:00) 300 = 300 (Last Total) 
     bu şekilde alt satırlara doğru sırayla gidecek.

    declare @date1 datetime='20201104'; SELECT Time,DateData,[GenSet-1],[GenSet-2],[GenSet-3],[GenSet-4],[GenSet-5], [GenSet-6], 0 LastTotal,

    SUM(ISNULL([GenSet-1],0)+ISNULL([GenSet-2],0)+ISNULL([GenSet-3],0)+ISNULL([GenSet-4],0)+ISNULL([GenSet-5],0)+ISNULL([GenSet-6],0))

    [Hourly Total] FROM ( select MIN(CONVERT(VARCHAR(5), cd.TimeCounter, 108)) as [Time], pc.PlaceOfCounterName,

    ISNULL(cd.CounterData,0) CounterData, cd.DateData from PlaceOfCounterData as cd with(nolock) inner join PlaceOfCounter as pc on cd.PlaceOfCounterKey=pc.PlaceOfCounterKey where pc.PlaceOfCounterKey in ( '2b370085-9da0-419d-954f-d2c5f076cb77', '9897d7db-74ac-4238-82b0-3a0046914093', 'd173e4dd-475b-4345-b9f0-ffcd1fd4b14b', '0a16436c-1731-4299-8517-19c5efd2e478', 'cf1249c3-b264-45d3-87d8-4c8931340747', 'a4ba1fa1-38ef-4b0b-8f64-24fdd95f77f6') and cd.DateData=@date1 GROUP BY CONVERT(VARCHAR(5), cd.TimeCounter, 108),pc.PlaceOfCounterName,cd.CounterData,cd.DateData ) AS tablom PIVOT ( SUM(CounterData) FOR PlaceOfCounterName IN ([GenSet-1],[GenSet-2],[GenSet-3],[GenSet-4],[GenSet-5],[GenSet-6]) ) AS pivotTablom group by Time,DateData,[GenSet-1],[GenSet-2],[GenSet-3],[GenSet-4],[GenSet-5], [GenSet-6]



    5 Kasım 2020 Perşembe 11:41

Yanıtlar

Tüm Yanıtlar