Missing records - outer join???

[codrutza]

SQL Server Management Studio Express

Maybe you could help me with this:
I have to do a report based on a script I wrote.

SELECT
TA.ReportingDate,
TB.JobNumber,
TB.Sales,
TC.Amount,
TC.Tn

FROM
TC,TA,TB

where
SUBSTRING(TC.Ref,4,7)= TA.Number
AND TB.JobNumber=TA.Number
AND TC.Tn<>''
AND YEAR(TA.ReportingDate)=2012
AND month(TA.ReportingDate)=1

Desired output:
Job Sales Amount
11 50 100
12 20 40
16 70
14 40 200
.....

Ouput I got:
Job Sales Amount
11 50 100
12 20 40
14 40 200
....

 

[r937]

SELECT TA.ReportingDate
     , TB.JobNumber
     , TB.Sales
     , TC.Amount
     , TC.Tn
  FROM TA
LEFT OUTER
  JOIN TB    
    ON TB.JobNumber = TA.Number
LEFT OUTER
  JOIN TC 
    ON SUBSTRING(TC.Ref,4,7)= TA.Number
   AND TC.Tn <> ''
 WHERE TA.ReportingDate >= '2012-01-01'
   AND TA.ReportingDate  < '2012-02-01'

notice the date range test for the month of january 2012

this methid is more efficient than extracting the year and month



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

 

[codrutza]

Thank you very much. I have another question maybe you could help me with. I have to obtain for each job the sales, cost (only once)+ all amounts

Job Sales Costs Amount Sales+Amount=SA
11 50 10 100 50+(100+30+40)
11 50 10 30
11 50 10 40

12 20 5 40 20+(40+50+60)
12 20 5 50
12 20 5 60

16 70 10 70

14 40 100 200 40+(200+210)
14 40 100 210

So the output would be:
Job SA Costs
11 220 10
12 170 5
16 70 10
14 450 100

Can I write that in the script?

 

[markros]

select Job, Sales, Costs, Amount, Sales + SUM(Amount) OVER (partition by Job) AS [SA]
from myTable

PluralSight Learning Library

 

[codrutza]

Thanks, Rudy. It's working.
Macros: I wrote SELECT TA.ReportingDate
, TB.JobNumber, TB.Sales, TB.Costs
, TB.Sales+sum(TC.Amount)
over (partition by TB.JobNumber) as SA
, TC.Tn
FROM TA
LEFT OUTER
JOIN TB
ON TB.JobNumber = TA.Number
LEFT OUTER
JOIN TC
ON SUBSTRING(TC.Ref,4,7)= TA.Number
AND TC.Tn <> ''
WHERE TA.ReportingDate >= '2012-01-01'
AND TA.ReportingDate < '2012-02-01'
It gives the Warning: Null value is eliminated by an aggregate or other SET operation.
SA it has null when the sales is <>0 and the amount is null, when should have the value of the sales. In addition, I have more records, and I get 3 different SA for a job.
What do I do wrong?

 

[markros]

I was thinking you only need to use 1 JOIN. In case of multiple JOINs this solution is not going to work. See this blog post for a different workaround

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/aggregates-with-multiple-tables

PluralSight Learning Library