how to merge two querries

[Former Member]

hi experts,,

i have two querries in only one field is different.

querries are:

1.SELECT T0.[WhsCode] AS 'Warehouse Code', T1.[DocDate] AS 'Posting Date', T0.[ItemCode] AS 'Item Code', T0.[Dscription] AS 'Item/Service Description', T0.[Quantity] AS 'Quantity1', T1.[U_No4_5] FROM [dbo].[WTR1] T0 INNER JOIN [dbo].[OWTR] T1 ON T1.[DocEntry] = T0.[DocEntry] WHERE (T1.[Filler] = (N'01' ) OR T1.[Filler] = (N'CI' ) OR T1.[Filler] = (N'FG-Noida' ) OR T1.[Filler] = (N'REJ' ) OR T1.[Filler] = (N'RW' ) OR T1.[Filler] = (N'RW_NonEX' ) OR T1.[Filler] = (N'Scrap' )) AND T1.DocDate >= [%1] AND T1.DocDate <= [%2]

2.SELECT T0.[Whscode] AS 'Warehouse Code', T1.[DocDate] AS 'Posting Date', T0.[ItemCode] AS 'Item Code', T0.[Dscription] AS 'Item/Service Description', T0.[Quantity] AS 'Quantity2', T1.[U_No4_5] AS '4/5NO' FROM [dbo].[WTR1] T0 INNER JOIN [dbo].[OWTR] T1 ON T1.[DocEntry] = T0.[DocEntry] WHERE (T1.[Filler] = (N'JW_AJIT' ) OR T1.[Filler] = (N'JW_ANG' ) OR T1.[Filler] = (N'JW_BWR' ) OR T1.[Filler] = (N'JW_GAD' ) OR T1.[Filler] = (N'JW_KARTI' ) OR T1.[Filler] = (N'JW_PASHU' ) OR T1.[Filler] = (N'JW_RIV' ) OR T1.[Filler] = (N'JW_S.V.K' ) OR T1.[Filler] = (N'JW_SAV' ) OR T1.[Filler] = (N'JW_VICT' ) OR T1.[Filler] = (N'JW_VIMAL' )) AND T1.DocDate >= [%1] AND T1.DocDate <= [%2]

now i want this in single report with quantity2 and conditions are different for both quantities.

Thanks & regards

Manoj S

[Former Member]

I personally like István's query. It is more clear with simple logic Have you tried the query yet?

Thanks,

Gordon

[Former Member]

Hi Manoj,

Just use the keyword JOIN between the 2 queries viz

SELECT T0.WhsCode AS 'Warehouse Code', T1.DocDate AS 'Posting Date', T0.ItemCode AS 'Item Code', T0.Dscription AS 'Item/Service Description', T0.Quantity AS 'Quantity1', T1.U_No4_5 FROM dbo.WTR1 T0 INNER JOIN dbo.OWTR T1 ON T1.DocEntry = T0.DocEntry WHERE (T1.Filler = (N'01' ) OR T1.Filler = (N'CI' ) OR T1.Filler = (N'FG-Noida' ) OR T1.Filler = (N'REJ' ) OR T1.Filler = (N'RW' ) OR T1.Filler = (N'RW_NonEX' ) OR T1.Filler = (N'Scrap' )) AND T1.DocDate >= %1 AND T1.DocDate <= %2
JOIN
SELECT T0.Whscode AS 'Warehouse Code', T1.DocDate AS 'Posting Date', T0.ItemCode AS 'Item Code', T0.Dscription AS 'Item/Service Description', T0.Quantity AS 'Quantity2', T1.U_No4_5 AS '4/5NO' FROM dbo.WTR1 T0 INNER JOIN dbo.OWTR T1 ON T1.DocEntry = T0.DocEntry WHERE (T1.Filler = (N'JW_AJIT' ) OR T1.Filler = (N'JW_ANG' ) OR T1.Filler = (N'JW_BWR' ) OR T1.Filler = (N'JW_GAD' ) OR T1.Filler = (N'JW_KARTI' ) OR T1.Filler = (N'JW_PASHU' ) OR T1.Filler = (N'JW_RIV' ) OR T1.Filler = (N'JW_S.V.K' ) OR T1.Filler = (N'JW_SAV' ) OR T1.Filler = (N'JW_VICT' ) OR T1.Filler = (N'JW_VIMAL' )) AND T1.DocDate >= %1 AND T1.DocDate <= %2

Regards,

Murtaza

[former_member204969]

Try something like this (with filling all the values):

SELECT T0.WhsCode AS 'Warehouse Code', T1.DocDate AS 'Posting Date', T0.ItemCode AS 'Item Code',
 T0.Dscription AS 'Item/Service Description',
 Case when T1.Filler in (N'01', N'CI',N'FG-Noida',N'REJ',N'RW',N'RW_NonEX',N'Scrap')
   then 'Quant11:'
   else 'Quant21:' end,
 T0.Quantity,
 T1.U_No4_5
FROM dbo.WTR1 T0 INNER JOIN dbo.OWTR T1 ON T1.DocEntry = T0.DocEntry
Where T1.DocDate >= [%1 AND T1.DocDate <= [%2]
    and T1.Filler in (N'01', N'CI',N'FG-Noida',N'REJ',N'RW',N'RW_NonEX',N'Scrap',
        N'JW_AJIT', . . .. . ,N'JW_VIMAL')