have problem with some query issues

[omid020]

Hi , this problem is related to SQL not SQLServer but I hope somebody guide me ;
This is part of my sample query :
Select projects.projectName,
Sum(sent_receive_tbl.ReceivedNum) - Sum(sent_receive_tbl.SentNum) As 'FirstWarehouse',
...
From
....
Group By projects.projectName,
....
Having sent_receive_tbl.StationID = '7'

This query shows me (FirstWarehose) for those StationID that are equal (7) . Now I want to have additional column in above SQL code like this :
Sum(sent_receive_tbl.ReceivedNum) - Sum(sent_receive_tbl.SentNum) As 'SecondWarehouse',

But now I want to see (SecondWarehouse) for those StationID that are equal (8) .
How can I do it ?
Another issue is calculating a value with using other (Select statements) ;
let`s assume that we have this sample query :
Select projects.projectName,
Sum(sent_receive_tbl.ReceivedNum) - Sum(sent_receive_tbl.SentNum) As 'FirstWarehouse',
Sum(Distinct receipt_details.rAmount) - Sum(Distinct
assignment_details.Amount) As 'FactoryInventory',
projects_details.OrderAmount
From
....
Group By projects.projectName,
....
Having sent_receive_tbl.StationID = '7'

How can I write this formula with SQL :
(projects_details.OrderAmount) - (FirstWarehouse) + (FactoryInventory) = Remained

In fact I want to have a column in my report that shows me (Remained Value) .
How can I do it ?

 

[r937]

SELECT projects.projectName
     , SUM(CASE WHEN sent_receive_tbl.StationID = '7'
                THEN sent_receive_tbl.ReceivedNum
                ELSE NULL END) - 
       SUM(CASE WHEN sent_receive_tbl.StationID = '7'
                THEN sent_receive_tbl.SentNum
                ELSE NULL END) As FirstWarehouse
     , SUM(CASE WHEN sent_receive_tbl.StationID = '6'
                THEN sent_receive_tbl.ReceivedNum
                ELSE NULL END) - 
       SUM(CASE WHEN sent_receive_tbl.StationID = '6'
                THEN sent_receive_tbl.SentNum
                ELSE NULL END) As SecondWarehouse
  FROM ...
GROUP 
    BY projects.projectName

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[omid020]

Thanks r937 , now I found when should I use Case statement .
But could you please tell me how could I obtain this formula result with SQL :

(projects_details.OrderAmount) - (FirstWarehouse) + (FactoryInventory) = Remained

Should I unwrap (FirstWarehouse) and (FactoryInventory) to their original formula or SQL has an easy solution for it ?

P.S.
= Remained is something like As Remained in SQL

 

[r937]

yes, you could substitute the original formulas, but the easier solution is to do the calculation in an outer query

SELECT t.projectName
     , t.OrderAmount  
     , t.FirstWarehouse
     , t.SecondWarehouse  
     , t.OrderAmount - 
       t.FirstWarehouse + 
       t.FactoryInventory AS Remained
  FROM ( SELECT projects.projectName
              , projects_details.OrderAmount
              , SUM(CASE WHEN sent_receive_tbl.StationID = '7'
                         THEN sent_receive_tbl.ReceivedNum
                         ELSE NULL END) -
                SUM(CASE WHEN sent_receive_tbl.StationID = '7'
                         THEN sent_receive_tbl.SentNum
                         ELSE NULL END) As FirstWarehouse
              , SUM(CASE WHEN sent_receive_tbl.StationID = '6'
                         THEN sent_receive_tbl.ReceivedNum
                         ELSE NULL END) -
                SUM(CASE WHEN sent_receive_tbl.StationID = '6'
                         THEN sent_receive_tbl.SentNum
                         ELSE NULL END) As SecondWarehouse  
           FROM ...
         GROUP
              BY projects.projectName ) AS t

note that you have to put the projects_details.OrderAmount into the SELECT clause of the subquery, so that it is available in the outer query too

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[omid020]

Thanks rudy , you are really a good teacher for teaching SQL power ; I used your hints and solved my many problems but still I`m confused with something else ! this is my final code :

Select t.projectName,
 t.LeafCode, t.ColorsCode,
 t.FirstWarehouse,
 t.SecondWarehouse,
 t.Sent,
 t.OrderAmount,
 t.Quota,
 t.FirstWarehouse + t.SecondWarehouse - t.Quota As InHand,
 t.OrderAmount - t.Amount - t.FirstWarehouse - t.SecondWarehouse As Remained,
 Round(((t.Amount + t.FirstWarehouse + t.SecondWarehouse - t.Quota) * 100 / t.OrderAmount), 2) As Progress 
From (Select projects.projectName,
 rs_leaves.LeafCode,
 rs_colors.ColorsCode,
 assignment_details.Amount,
 Sum(Case When sent_receive_tbl.StationID = '7' Then sent_receive_tbl.ReceivedNum Else Null End)
 - Sum(Case When sent_receive_tbl.StationID = '7' Then sent_receive_tbl.SentNum Else Null End) As FirstWarehouse,
 Sum(Case When sent_receive_tbl.StationID = '8' Then sent_receive_tbl.ReceivedNum Else Null End)
 - Sum(Case When sent_receive_tbl.StationID = '8' Then sent_receive_tbl.SentNum Else Null End) As SecondWarehouse,
 assignment_details.Amount As Sent,
 projects_details.OrderAmount,
 projects_details.Quota 
 From assignment_details Right Join projects_details 
 On assignment_details.leafCode = projects_details.LeafCode 
 And assignment_details.leafColor = projects_details.LeafColor 
 And assignment_details.projectID = projects_details.projectID Inner Join rs_leaves 
 On projects_details.LeafCode = rs_leaves.LeafID Inner Join rs_colors 
 On projects_details.LeafColor = rs_colors.ColorsID Inner Join sent_receive_tbl 
 On projects_details.LeafColor = sent_receive_tbl.ColorsID 
 And sent_receive_tbl.LeafID = projects_details.LeafCode Inner Join projects 
 On projects_details.projectID = projects.projectID 
 Group By projects.projectName, 
 rs_leaves.LeafCode, 
 rs_colors.ColorsCode, 
 assignment_details.Amount, 
 projects_details.OrderAmount, 
 projects_details.Quota) As t

Now I can run my query for grabbing some of my most important requirements but I`m faced with these problems after runing query :

1 - I like to view projects_details table data completely , now when projects_details.LeafColor and projects_details.LeafCode got a NULL value for (FirstWarehouse) value , related LeafCode and LeafColor doesn`t appear in list after running query .

2 - For (Progress) column when one of parameters of its own formula like (t.FirstWarehouse) or (t.Amount) got Null value , Progress value will not be calculated and SQL returns me a Null value for Progress column .

Could you please guide me on these issues ?

 

[r937]

1, don't mix RIGHT and LEFT joins

2. use COALESCE function

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon