Cross reference data sql 1

[snowneil]

var sqlComplaint = "SELECT lc.complaintType, COUNT(*) AS complaintsReceived ";
sqlComplaint += "FROM complaints c LEFT OUTER JOIN ";
sqlComplaint += "lookup_complaint lc ON c.complaintType = lc.id ";
sqlComplaint += "WHERE (c.dateReceived BETWEEN CONVERT(DATETIME, '" + dateReceivedFrom + " 00:00:00', 102) AND CONVERT(DATETIME, '" + dateReceivedTo + " 23:59:59', 102)) ";
sqlComplaint += "GROUP BY lc.complaintType ";
sqlComplaint += "ORDER BY lc.complaintType";

AND

var sqlFinance = "SELECT lf.financeCompany, COUNT(*) AS complaintsReceived ";
sqlFinance += "FROM dbo.complaints c INNER JOIN ";
sqlFinance += "dbo.complaints_a ca ON c.aId = ca.id LEFT OUTER JOIN ";
sqlFinance += "dbo.lookup_finance_company_a lf ON ca.financeCompany = lf.id ";
sqlFinance += "WHERE (c.dateReceived BETWEEN CONVERT(DATETIME, '" + dateReceivedFrom + " 00:00:00', 102) AND CONVERT(DATETIME, '" + dateReceivedTo + " 23:59:59', 102)) ";
sqlFinance += "GROUP BY lf.financeCompany ";
sqlFinance += "ORDER BY lf.financeCompany";


These are my 2 statements, separately they return 2 tables like below:

Complaint Type , Count
"Complaint Name1", 2
"Complaint Name2", 6
etc...


I want to know how to write a statement to produce a table like the below:

finance type
Complaint Type "finance type1" "finance type2"
"complaint name1" 3 5
"complaint name2" 7 6

Complaint type crossed with finance type and a count of them showing.. if that makes sense??

 

[r937]

let's start slowly here

in your first query, you have

FROM complaints c LEFT OUTER JOIN
lookup_complaint lc ON c.complaintType = lc.id

why the LEFT OUTER JOIN? that says that you are expecting to find at least one complaint where complaintType doesn't exist in the lookup_complaint table -- what's up with that???

r937.com | rudy.ca

 

[snowneil]

Some of the complaint types from old records are blank.. so the record wouldn't show if it didnt exist in the lookup table.

 

[r937]

good answer, bad data

if the complaint type is blank, where would you like to report the data in your results?

finance type
Complaint Type "finance type1" "finance type2"
"complaint name1" 3 5
"complaint name2" 7 6


r937.com | rudy.ca

 

[snowneil]

The data is old and has been moved into an updated system so some of it is blank at the moment, soon enough it wont be because it is a required field and the old fields are being updated.

If you could explain how it can be done when not using a left join i would be very grateful.

 

[r937]

i thought you said the left outer join was needed because some of the lookup types can be blank

change LEFT OUTER to INNER to see the difference

r937.com | rudy.ca

 

[snowneil]

Any info on how to go about doing a cross reference of the two?

 

[r937]

this works only if you know which finance types you're looking for --

SELECT lc.complaintType
     , sum(case when lf.financeCompany 
                   = 'finance type1'
                then 1 else o end)
                   as finance_type1      
     , sum(case when lf.financeCompany 
                   = 'finance type2'
                then 1 else o end)
                   as finance_type2
     , sum(case when lf.financeCompany 
                   = 'finance type3'
                then 1 else o end)
                   as finance_type3      
     , ...
  FROM dbo.complaints c 
INNER 
  JOIN dbo.complaints_a ca 
    ON c.aId = ca.id 
LEFT OUTER 
  JOIN dbo.lookup_finance_company_a lf 
    ON ca.financeCompany = lf.id
LEFT OUTER 
  JOIN lookup_complaint lc 
    ON c.complaintType = lc.id    
 WHERE ...
GROUP
    by lc.complaintType

r937.com | rudy.ca

 

[snowneil]

Thanks for that it was very useful.

 

[r937]

thanks

of course the typo "then 1 else o" should be "then 1 else 0"

r937.com | rudy.ca