Hi All,
i want item based and service based invoices in same query but in this query when user individuality select item or service it shows records how can i get both in same query
SELECT 1 as sr,'Invoice' [Document Type], t0.docentry, T0.[CardCode] as 'BP Number', T0.[CardName] as 'BP name', t0.numatcard as 'Vendor reference No.', t0.u_loc as 'Location name' , t4.state ,--'Null') as 'State' , T0.[DocNum] as 'Document Number', --T0.[DocType], t0.docdate, ISNULL(T1.ItemCode,'Service Item') AS 'Item Code', T1.[Dscription], T1.[Quantity], t3.name as 'MAG', t1.linetotal as 'Net Amount', t1.vatsum as 'Tax Amount', (t1.linetotal + t1.vatsum) as 'Invoice Value' FROM [dbo].[OINV] T0 INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry left outer join owhs t4 on t1.whscode = t4.whscode LEFT JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN [dbo].[@ITMG] T3 ON T2.U_itmg = T3.Code WHERE T0.[DocType] = '[%0]' and t0.cardcode between [%3] and [%4] and t0.docdate >= [%1]and t0.docdate <=[%2] union SELECT 2,'Credit Memo' [Document Type], t0.docentry, T0.[CardCode] as 'BP Number', T0.[CardName] as 'BP Name', t0.numatcard as 'Vendor reference No.', t0.u_loc as 'Location name' , t4.state ,--'Null') as 'State' , T0.[DocNum] as 'Document Number', --T0.[DocType], t0.docdate, ISNULL(T1.ItemCode,'Service Item') AS 'Item Code', T1.[Dscription], -T1.[Quantity], t3.name as 'MAG', -t1.linetotal as 'Net Amount', -t1.vatsum as 'Tax Amount', (t1.linetotal + t1.vatsum) as 'Invoice Value' FROM [dbo].[ORIN] T0 INNER JOIN [dbo].[RIN1] T1 ON T0.DocEntry = T1.DocEntry left outer join owhs t4 on t1.whscode = t4.whscode LEFT JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN [dbo].[@ITMG] T3 ON T2.U_itmg = T3.Code WHERE T0.[DocType] = '[%0]' and t0.cardcode between [%3] and [%4] and t0.docdate >= [%1]and t0.docdate <=[%2]
pls help me
ketan..........