question

Table Master ------------- BillNo nvarchar no 100 BillDate datetime no 8 Data in table Master ----------------------- A0300000160 2010-10-06 19:06:16.000 A0300000161 2010-10-06 19:06:29.000 A0300000162 2010-10-06 19:07:03.000 A0300000163 2010-10-06 19:07:34.000 A0300000164 2010-10-06 19:09:24.000 Table Child ------------------ BillNo nvarchar no 40 ProductID nvarchar no 40 ProductRate real no 4 24 Qty real no 4 24 ccode nvarchar no 8 Data in table Child -------------------- A0300000161 TK009 22.0 2.0 TK A0300000162 CC001 44.09 1.0 CC A0300000162 TK009 22.0 1.0 TK A0300000163 CP008 342.60 0.25 CP A0300000164 TK009 22.0 2.0 TK A0300000165 TK009 22.0 1.0 TK Table Cateogry_Master ---------------------- CCode nvarchar no 40 CName nvarchar no 200 Date in Category_Master ---------------------- FC Coffee CP Coffee Powder SN Snacks ES Hot Coffee CC Cold Coffee MR Merchandise TK Takeaway **Expected Result** CName Total_Bills ---------------------- Takeaway 3 Coffe Powder 1 Mixed 1 Expected Result : Summary of transaction, if each bill has mixed cateogy of products then it will be counted as Mixed otherwise can calculate based on category code **This is my code but am not getting expecting result.** select count(*) as count, c.ccode from tblbilldetails as c, tblbill b where b.billno=c.billno and b.billdate>='2010-10-11 11:43:48.000' and b.billdate<='2010-10-11 12:43:48.000' group by c.ccode