i have following table structure
HUB_DETAILS (Master)
Branch_ID
Branch_Name
VTRCheckList (Master)
CLid
CLName
VTRCheckListDetails (Detail)
CLid
Branch_ID
VTRValue
vtrRespDate
Actually when i run the following query it does comes with all the Checklist names alongwith all branch names but shows the value in every branch infact only 1 branch has data in the given date criteria. it should show 0 if there is no data in checklist of the respective branch.
SELECT VTRCheckList.CLName, Hub_Details.BranchName, sum(cast(VTRCheckListDetails.VtrValue as int)) as 'Total'
FROM VTRCheckListDetails
INNER JOIN VTRCheckList ON VTRCheckListDetails.CLid = VTRCheckList.CLid CROSS JOIN
Hub_Details
where Convert(date,VTRCheckListDetails.vtrRespDate, 105) >= convert(date,'01-01-2011',105) and Convert(date, VTRCheckListDetails.vtrRespDate, 105) <= convert(date,'30-01-2011',105)
GROUP BY VTRCheckList.CLName, Hub_Details.BranchName
