3

I know there are various threads out there about this topic.

I am trying to create a query that returns a predefined groping of records and if there are not any results found I would like to return Zero. Most of the threads suggest we create a temporary table to house this grouping data but i will need to do it in a single statement. However we can make use of sub queries.

This was my original code...

and it returns the correct values but does not contain the groupings that do not have tickets:

SELECT SC.Service_Company_Code , SSPM.Mapping , COUNT(T.Service_Ticket_Id) AS 'Ticket_Count' FROM SV_Service_Ticket T INNER JOIN KS_SedonaSync_Problem SSP ON T.Sub_Problem_Id = SSP.Problem_Id RIGHT JOIN KS_SedonaSync_Problem_Sub_Mapping SSPM ON SSP.SedonaSync_Problem_Sub_Mapping_Id = SSPM.SedonaSync_Problem_Sub_Mapping_Id RIGHT JOIN SV_Service_Company SC ON T.Service_Company_Id = SC.Service_Company_Id WHERE SC.Vendor_Id = 1 AND SC.Inactive = 'N' AND SC.Service_Company_Id <> 1 AND SSPM.Inactive = 'N' AND T.Ticket_Status <> 'CL' GROUP BY SC.Service_Company_Code, SSPM.Mapping ORDER BY SC.Service_Company_Code, SSPM.Mapping

My revised code...

now contains a sub query that does show the correct groupings and ran alone without linking in the Ticket table. But again when linked to the ticket table it does not show the grouping when there are no records present. The use of the left join is supposed to show me null records when they do not exist.

SELECT Q.Service_Company_Code , Q.Mapping , COUNT(T.Service_Ticket_Id) AS 'Ticket_Count' FROM (SELECT SC.Service_Company_Id, SC.Service_Company_Code, SSP.Problem_Id, SSPM.SedonaSync_Problem_Sub_Mapping_Id, SSPM.Mapping FROM SV_Service_Company SC FULL OUTER JOIN KS_SedonaSync_Problem_Sub_Mapping SSPM ON 1=1 LEFT JOIN KS_SedonaSync_Problem SSP ON SSPM.SedonaSync_Problem_Sub_Mapping_Id = SSP.SedonaSync_Problem_Sub_Mapping_Id WHERE SC.Vendor_Id = 1 AND SC.Inactive = 'N' AND SC.Service_Company_Id <> 1 AND SSPM.Inactive = 'N' ) Q LEFT JOIN SV_Service_Ticket T ON T.Sub_Problem_Id = Q.Problem_Id AND T.Service_Company_Id = Q.Service_Company_Id WHERE T.Ticket_Status <> 'CL' GROUP BY Q.Service_Company_Code, Q.Mapping ORDER BY Q.Service_Company_Code, Q.Mapping

Any help is greatly welcomed, Thank you in advance.

Brad Swindell

EDIT: This is the current result set.

enter image description here

This is the desired result set.

enter image description here

EDIT: I have found a working solution thanks to Mike M!

SELECT SC.Service_Company_Code , SSPM.Mapping , COUNT(T.Service_Ticket_Id) AS 'Ticket_Count' FROM SV_Service_Company SC CROSS JOIN KS_SedonaSync_Problem SSP INNER JOIN KS_SedonaSync_Problem_Sub_Mapping SSPM ON SSP.SedonaSync_Problem_Sub_Mapping_Id = SSPM.SedonaSync_Problem_Sub_Mapping_Id AND SC.Vendor_Id = 1 AND SC.Inactive = 'N' AND SC.Service_Company_Id <> 1 LEFT JOIN SV_Service_Ticket T ON T.Sub_Problem_Id = SSP.Problem_Id AND T.Service_Company_Id = SC.Service_Company_Id AND T.Ticket_Status <> 'CL' WHERE SSPM.Inactive = 'N' GROUP BY SC.Service_Company_Code, SSPM.Mapping ORDER BY SC.Service_Company_Code, SSPM.Mapping
Improve this question
3
  • Do you mean there's no zero count where there are no records in SV_Service_Ticket table that correspond to records from Q subquery? Commented Dec 31, 2013 at 0:08
  • See above, added result sets. Commented Jan 2, 2014 at 18:41
  • Correct, SV_Service_Ticket is a list of tickets, I need to see when there is a count of Zero for a defigned category. If there are no tickets that match that category, it should be displayed as zero. currently the category is not displayed altogether. Commented Jan 2, 2014 at 18:46

3 Answers 3

Reset to default
1

Have you tried moving that last bit in the WHERE clause into the join criteria? NULL does not = or <> anything :)

So 

T.Ticket_Status <> 'CL'

is probably failing to pull those rows that have NULL.

Add "and T.Ticket_Status <> 'CL'" as one more item in your Join criteria, to have

 LEFT JOIN SV_Service_Ticket T ON T.Sub_Problem_Id = Q.Problem_Id AND T.Service_Company_Id = Q.Service_Company_Id and T.Ticket_Status <> 'CL'

So you see where we're coming from, here is the basic case we (and you) want to happen. Are you sure that subquery is settled down?

if object_id('tempdb..#tempItems') is not null drop table #tempItems; create table #tempItems ( id int primary key , category varchar(100) , subCategory varchar(100) ) ; insert into #tempItems (id, category, subCategory) values (1, 'First', 'subA'); insert into #tempItems (id, category, subCategory) values (2, 'First', 'subB'); insert into #tempItems (id, category, subCategory) values (3, 'Second', 'subA'); insert into #tempItems (id, category, subCategory) values (4, 'NotFound', 'subNotFound'); --------------------------------------------------------- if object_id('tempdb..#tempCounts') is not null drop table #tempCounts; create table #tempCounts ( id int primary key , itemId int ) ; insert into #tempCounts (id, itemId) values (1, 1); insert into #tempCounts (id, itemId) values (2, 2); insert into #tempCounts (id, itemId) values (3, 3); insert into #tempCounts (id, itemId) values (4, 3); ---------------------------------------------------------- select items.category ,items.subcategory , count(counts.id) as count from #tempItems items left join #tempCounts counts on items.id = counts.itemid group by items.category, items.subcategory order by items.category, items.subcategory
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Intresting, however I tried this and returned the same result set. I also tried to move the other filters as well, but same thing.
Hmmmm. I see. Could you share the result set that you're talking about? (and where it should be different)
Sure I have posted it as a pic in the oringal post.
hmmmm. Ok. That is what I understood from your description. I'm not convinced the left join is the problem, then :) . Is there any chance those aren't in the first part, the subquery, as well? I'll put the demo of the basic left join case in my answer, so you see where I'm coming from.
I see, do to the double table join I was forgeting this concept. And apperently i did not test your orignal answer correctly. Thank you for the help. It does work corretly.
|
1

Im not sure if it will work but give it a go, since no data is available so its a bit difficult to exactly see what is going on . anyway give it a go.

SELECT SC.Service_Company_Code , SSPM.Mapping , COUNT(T.Service_Ticket_Id) AS [Ticket_Count] FROM KS_SedonaSync_Problem SSP RIGHT JOIN SV_Service_Ticket T ON T.Sub_Problem_Id = SSP.Problem_Id RIGHT JOIN KS_SedonaSync_Problem_Sub_Mapping SSPM ON SSP.SedonaSync_Problem_Sub_Mapping_Id = SSPM.SedonaSync_Problem_Sub_Mapping_Id RIGHT JOIN SV_Service_Company SC ON T.Service_Company_Id = SC.Service_Company_Id WHERE SC.Vendor_Id = 1 AND SC.Inactive = 'N' AND SC.Service_Company_Id <> 1 AND SSPM.Inactive = 'N' AND T.Ticket_Status <> 'CL' GROUP BY SC.Service_Company_Code, SSPM.Mapping ORDER BY SC.Service_Company_Code, SSPM.Mapping
Improve this answer

2 Comments

Thanks for the reply, unfortunately this produced the same results. The rows that i am looking for are not present.
I have posted above the current resultset and desired resultset.
1

If I'm reading your SQL correctly, I think you just need to move your where condition to the LEFT JOIN. If this fails to produce the desiredresults, could you please set up a SQL Fiddle?

SELECT Q.Service_Company_Code , Q.Mapping , COUNT(T.Service_Ticket_Id) AS 'Ticket_Count' FROM ( SELECT SC.Service_Company_Id, SC.Service_Company_Code, SSP.Problem_Id, SSPM.SedonaSync_Problem_Sub_Mapping_Id, SSPM.Mapping FROM SV_Service_Company SC CROSS JOIN KS_SedonaSync_Problem_Sub_Mapping SSPM LEFT JOIN KS_SedonaSync_Problem SSP ON SSPM.SedonaSync_Problem_Sub_Mapping_Id = SSP.SedonaSync_Problem_Sub_Mapping_Id WHERE SC.Vendor_Id = 1 AND SC.Inactive = 'N' AND SC.Service_Company_Id <> 1 AND SSPM.Inactive = 'N' ) Q LEFT JOIN SV_Service_Ticket T ON T.Sub_Problem_Id = Q.Problem_Id AND T.Service_Company_Id = Q.Service_Company_Id AND T.Ticket_Status <> 'CL' GROUP BY Q.Service_Company_Code, Q.Mapping ORDER BY Q.Service_Company_Code, Q.Mapping
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.