Kristian Francischini Kristian Francischini - 5 months ago 30
Vb.net Question

VB + SQL SUM with condition

i'm trying to get this code to work.

SELECT InputDate
,Number
,Name
,Sector
,YukyuDate
,YukyuIn
,YukyuOut
,ISNULL(SUM(YukyuIn), 0)-ISNULL(SUM(YukyuOut), 0)As YukyuLeft
,Reason
FROM TYukyu
WHERE Number = '0011683963'
GROUP BY Number
ORDER BY InputDate


i already tried

SUM(YukyuIn-YukyuOut) As YukyuLeft
SUM(YukyuIn)-SUM(YukyuOut) As YukyuLeft


My sql have this colums

InputDate |Number|Name |Sector |YukyuDate |YukyuIn|YukyuOut|Reason
-------------------------------------------------------------------------
2016-06-26 11:50| 1 |User1|Sector1|2016-06-26| 10 | |Test1
2016-06-26 11:51| 1 |User1|Sector1|2016-06-26| | 1 |Test2
2016-06-26 11:52| 1 |User1|Sector1|2016-06-26| | 1 |Test3
2016-06-26 11:53| 2 |User2|Sector1|2016-06-26| 10 | 0 |Test4
2016-06-26 11:54| 1 |User1|Sector1|2016-06-26| | 1 |Test5
2016-06-26 11:55| 3 |User3|Sector1|2016-06-26| | 1 |Test6




i want this results to show in datagridview

InputDate |Number|Name |Sector |YukyuDate |YukyuIn|YukyuOut|YukyuLeft|Reason
-------------------------------------------------------------------------
2016-06-26 11:50| 1 |User1|Sector1|2016-06-26| 10 | | 10 |Test1
2016-06-26 11:51| 1 |User1|Sector1|2016-06-26| | 1 | 9 |Test2
2016-06-26 11:52| 1 |User1|Sector1|2016-06-26| | 1 | 8 |Test3
2016-06-26 11:54| 1 |User1|Sector1|2016-06-26| | 1 | 7 |Test5



Answer

try this

      SELECT InputDate       
                ,Number       
                ,Name       
                ,Sector       
                ,YukyuDate       
                ,YukyuIn       
                ,YukyuOut       
                ,SUM(isnull(YukyuIn,0)-isnull(YukyuOut,0)) 
over (partition by number order by InputDate) as YukyuLeft
                ,Reason  
            FROM TYukyu 
           WHERE Number = '0011683963'
        ORDER BY InputDate


    '