I've been struggling with how best to run my SQL query and display the results in SSRS. I have a table with Time Values, and I want to display all of the time values in my final result. The problem I am running into, is if there is no total count or records in that time segment value...the record does not show up at all. This should be a simple query, but I'm having difficulty in filling in the null and 0 value records. So what I would like to see is 2 rows for every time value (1 for Urban and 1 for Rural) even if there is no value....just display a 0.
When I try to bring the results over to SSRS, of course it only displays the values with data. But what I want is Every time time value for Urban and Rural even if it is 0.
SELECT isnull([MIN],0) as [MIN]
, ISNULL(HOTFractile.CALLTYPE, @CallType) AS CALLTYPE
, ISNULL(HOTFractile.Urban_Rural,@Urban_Rural) As Urban_rural
, ISNULL(HOTFractile.Jurisdiction,@Jurisdiction) as Jurisdiction
LEFT JOIN HOTFractile
ON HOTFractileRanges.ID = HOTFractile.FractileID
ORDER BY HOTFractileRanges.FractileRange
Left Join #temp2
on HOTFractileRanges.FractileRange = #temp2.Fractilerange
group by HOTFractileRanges.FractileRange