• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Rehash of previous Join Question

Thanks to help on here and part myself I have the following query which gives me the  correct results,

SELECT     CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END AS contract, jobcat.shortcode, COUNT(job.jobid) AS jobs, CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd)
                      >= 0 THEN 'Yes' ELSE 'No' END AS Possible
FROM         complete INNER JOIN
                      job ON complete.jobid = job.jobid INNER JOIN
                      vw_job_abd ON job.jobid = vw_job_abd.jobid RIGHT OUTER JOIN
                      jobcat LEFT OUTER JOIN
                          (SELECT DISTINCT contractid
                            FROM          job) contract ON 1 = 1 ON job.jobcatid = jobcat.jobcatid AND job.contractid = contract.contractid
WHERE     contract.contractid IN (7, 14)
GROUP BY CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END, jobcat.shortcode, CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No' END
ORDER BY contract, jobcat.shortcode

The results are in the format

contract      Jobcat    Orders     Value               Possible
TV AAP         C            2           3378.14              Yes
TVAAP          C            0           0                        No  
TV AAP         D            4           7841.28               No
TV AAP         D            8           14919.79             Yes
TV AAP         E            2            1203.28              Yes
TV AAP         E            0            0                        No

The problem occurs when I add more clauses to the Where clause

i.e. WHERE     contract.contractid IN (7, 14)

becomes

WHERE     contract.contractid IN (7, 14) AND job.electronic = 1 AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      >= CONVERT(DATETIME, '2005-04-01 00:00:00', 102)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      <= CONVERT(DATETIME, '2005-04-05 00:00:00', 102))

I lose all the results where the count is 0 and the results are

contract      Jobcat    Orders     Value               Possible
TV AAP         C            2           3378.14              Yes
TV AAP         D            4           7841.28               No
TV AAP         D            8           14919.79             Yes
TV AAP         E            2            1203.28              Yes

How can I get the 0 counts back

Thanks for everybodys help on this so far

Mark

0
polynominal
Asked:
polynominal
  • 2
1 Solution
 
Brian CroweCommented:
That's because you have a left outer join on the table you are adding the WHERE statement for.  The zero values occur when there is no matching record (NULL) in that table so restricting the output to particular contractid's eliminates those records.

Try adding it to the join clause instead...

...LEFT OUTER JOIN (SELECT DISTINCT contractid FROM job) contract
     ON 1 = 1 ON job.jobcatid = jobcat.jobcatid
     AND job.contractid = contract.contractid
     AND contract.contractid IN (7, 14)
...

0
 
Brian CroweCommented:
otherwise you have to allow for nulls in contractid...

not sure about the syntax but you can try

WHERE ... contract.contractid in (7,14,NULL) ...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now