Skip to Content
0
May 07, 2018 at 08:06 AM

How can i get this query in single line

381 Views

Hi Experts

I had a sql query,

But when i run the query, am getting it in multi lines for single item

i run this query in 3 Filters,

1. Whs

2. From - To date

3. Item group

Also in the stock transfer am getting only in, not the out from Selected MN-WH

Pls any one can help me with a new query format or edit the same

Below give is the sql query

Regards




Select DISTINCT x.DocDate,x.Itemcode ,ItemName,GroupName,x.[Whse], sum([GRPO_QTY]) [GRPO_QTY] ,SUM([Retrun_QTY])[Retrun_QTY]  
  , sum ([P.CreditNote_QTY])[P.CreditNote_QTY], sum([GR_Qty])[GR_Qty], SUM([GI_Qty])[GI_Qty],SUM([ST_Qty])[ST_Qty]  
  , SUM ([Del_qty])[Del_Qty],SUM([Del_RET_Qty])[Del_RET_Qty],SUM([S.CreditNote_Qty])[S.CreditNote_Qty]  
  , TA.OnHand WhseOnHand  
from  
(  
  select Distinct OPDN.DocDate AS 'DocDate',OITB.ItmsGrpNam as 'GroupName',PDN1.ItemCode AS 'ItemCode', PDN1.Dscription AS 'ItemName',PDN1.WhsCode as 'Whse' ,sum(PDn1.Quantity)[GRPO_QTY] ,'0' [Retrun_QTY] ,'0'[P.CreditNote_QTY],'0'[GR_Qty] ,'0'[GI_Qty],'0'[ST_Qty] ,'0' [Del_Qty] ,'0'[Del_RET_Qty],'0'[S.CreditNote_Qty]from PDN1  
  Inner join OPDN on PDN1.Docentry = Opdn.docentry  
  inner join OITM on PDN1.ItemCode = OITM.ItemCode                                                                       --GRPO--  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  where OPDN.CANCELED = 'N'  
  group by PDN1.Itemcode,PDN1.WhsCode, OPDN.DocDate,OITB.ItmsGrpNam,PDN1.Dscription  
  Union all  
  select Distinct  ORPD.DocDate AS 'DocDate',OITB.ItmsGrpNam as 'GroupName',RPD1.Itemcode,RPD1.Dscription,RPD1.WhsCode as ' whse','0' [GRPO_QTY],sum(RPD1.Quantity)[Retrun_QTY] ,'0'[P.CreditNote_QTY],'0'[GR_Qty] ,'0'[GI_Qty] ,'0'[ST_Qty],'0' [Del_Qty] ,'0'[Del_RET_Qty],'0'[S.CreditNote_Qty]from RPD1  
  Inner join ORPD ON RPD1.DocEntry = ORPD.DocEntry                                                              --GRPO-Retrun--  
  inner join OITM on RPD1.ItemCode = OITM.ItemCode  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  Where ORPD.CANCELED = 'N'  
  group by RPD1.ItemCode,RPD1.WhsCode, ORPD.DocDate,OITB.ItmsGrpNam,RPD1.Dscription  
  union all  
  select Distinct  ORPC.DocDate AS 'DocDate',OITB.ItmsGrpNam as 'GroupName',RPC1.Itemcode,RPC1.Dscription, RPC1.WhsCode as ' whse' ,'0'[GRPO_QTY],'0' [Retrun_QTY],sum(RPC1.Quantity)[P.CreditNote_QTY],'0'[GR_Qty],'0'[GI_Qty] ,'0'[ST_Qty],'0' [Del_Qty] ,'0'[Del_RET_Qty],'0'[S.CreditNote_Qty]from RPC1  
  Inner Join ORPC on RPC1.DocEntry = ORPC.DocEntry                                                          --Purchase . CN--  
  inner join OITM on RPC1.ItemCode = OITM.ItemCode  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  Where ORPC.CANCELED = 'N'  
  group by RPC1.Itemcode,RPC1.WhsCode,ORPC.DocDate,OITB.ItmsGrpNam,RPC1.Dscription  
  union all  
  select Distinct  OIGN.DocDate as 'DocDate',OITB.ItmsGrpNam as 'GroupName',IGN1.Itemcode,IGN1.Dscription, IGN1.WhsCode as 'whse' ,'0'[GRPO_QTY],'0' [Retrun_QTY],'0' [P.CreditNote_QTY] ,Sum(IGN1.Quantity)[GR_Qty],'0'[GI_Qty] ,'0'[ST_Qty],'0' [Del_Qty] ,'0'[Del_RET_Qty] ,'0'[S.CreditNote_Qty]from IGN1  
  inner join OIGN on IGN1.DocEntry =OIGN.DocEntry  
  inner join OITM on IGN1.ItemCode = OITM.ItemCode                                                                                                 --GR--  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  Where OIGN.CANCELED = 'N'  
  group by IGN1.Itemcode,IGN1.WhsCode,OIGN.DocDate,OITB.ItmsGrpNam,IGN1.Dscription  
  union all  
  select Distinct  OIGE.DocDate as 'DocDate',OITB.ItmsGrpNam as 'GroupName',IGE1.Itemcode,IGE1.Dscription, IGE1.WhsCode as 'whse' ,'0'[GRPO_QTY],'0' [Retrun_QTY],'0' [P.CreditNote_QTY] ,'0' [GR_Qty] ,sum(IGE1.Quantity)[GI_Qty],'0'[ST_Qty],'0' [Del_Qty] ,'0'[Del_RET_Qty] ,'0'[S.CreditNote_Qty]from IGE1  
  Inner Join OIGE on IGE1.DocEntry = OIGE.DocEntry                                                                            --GI--  
  inner join OITM on IGE1.ItemCode = OITM.ItemCode  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  Where OIGE.CANCELED = 'N'  
  group by IGE1.Itemcode,IGE1.WhsCode,OIGE.DocDate,OITB.ItmsGrpNam,IGE1.Dscription  
  union all  
  select Distinct  OWTR.DocDate as 'DocDate',OITB.ItmsGrpNam as 'GroupName',WTR1.Itemcode,WTR1.Dscription,WTR1.WhsCode  as 'whse','0'[GRPO_QTY],'0' [Retrun_QTY],'0' [P.CreditNote_QTY] ,'0' [GR_Qty] ,'0' [GI_Qty] ,SUM(WTR1.Quantity)[ST_Qty],'0' [Del_Qty],'0'[Del_RET_Qty],'0'[S.CreditNote_Qty] from WTR1  
  Inner join OWTR on WTR1.DocEntry = OWTR.DocEntry                                                                    --ST--  
  inner join OITM on WTR1.ItemCode = OITM.ItemCode  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  Where OWTR.CANCELED = 'N'   AND OWTR.Comments <> 'For transfer to bin location.'
  group by WTR1.Itemcode,WTR1.FromWhsCod,WTR1.WhsCode,OWTR.DocDate,OITB.ItmsGrpNam,WTR1.Dscription, WTR1.WhsCode 
  union all  
  select Distinct  ODLN.DocDate as 'DocDate',OITB.ItmsGrpNam as 'GroupName',DLN1.Itemcode,DLN1.Dscription,DLN1.WhsCode  as ' whse' ,'0'[GRPO_QTY],'0' [Retrun_QTY],'0' [P.CreditNote_QTY] ,'0' [GR_Qty] ,'0' [GI_Qty],'0'[ST_Qty] ,SUM(DLN1.Quantity)[Del_Qty],'0'[Del_RET_Qty] ,'0'[S.CreditNote_Qty] from DLN1  
  Inner Join ODLN on DLN1.DocEntry = ODLN.DocEntry  
  inner join OITM on DLN1.ItemCode = OITM.ItemCode                                                                --Delivery--  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  Where ODLN.CANCELED = 'N'  
  group by DLN1.Itemcode,DLN1.WhsCode,ODLN.DocDate,OITB.ItmsGrpNam,DLN1.Dscription  
  union all  
  select Distinct  ORDN.DocDate as 'DocDate',OITB.ItmsGrpNam as 'GroupName',RDN1.Itemcode,RDN1.Dscription,RDN1.WhsCode  as 'whse' ,'0'[GRPO_QTY],'0' [Retrun_QTY],'0' [P.CreditNote_QTY] ,'0' [GR_Qty] ,'0' [GI_Qty],'0'[ST_Qty],'0'[Del_Qty] ,SUM(RDN1.Quantity)[Del_RET_Qty] ,'0'[S.CreditNote_Qty]from RDN1  
  Inner Join ORDN on RDN1.DocEntry = ORDN.DocEntry  
  inner join OITM on RDN1.ItemCode = OITM.ItemCode                                                            --Delivery Return--  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  Where ORDN.CANCELED = 'N'  
  group by RDN1.Itemcode,RDN1.WhsCode,ORDN.DocDate,OITB.ItmsGrpNam,RDN1.Dscription  
  union all  
  select Distinct  ORIN.DocDate AS 'DocDate',OITB.ItmsGrpNam as 'GroupName',RIN1.Itemcode,RIN1.Dscription,RIN1.WhsCode  as 'whse' ,'0'[GRPO_QTY],'0' [Retrun_QTY],'0' [P.CreditNote_QTY] ,'0' [GR_Qty] ,'0' [GI_Qty],'0'[ST_Qty],'0'[Del_Qty] ,'0'[Del_RET_Qty],SUM(RIN1.Quantity)[S.CreditNote_Qty] from RIN1  
  Inner Join ORIN on RIN1.DocEntry = ORIN.DocEntry                                                                                  --Sales C.N--  
  inner join OITM on RIN1.ItemCode = OITM.ItemCode  
  Inner join OITB on OITM.ItmsGrpCod = OITB.ItmsGrpCod  
  Where ORIN.CANCELED = 'N'   AND RIN1.BaseType <> 203
  group by RIN1.Itemcode,RIN1.WhsCode,ORIN.DocDate,OITB.ItmsGrpNam,RIN1.Dscription  
) as X  
  Inner join OITW TA on x.Whse = TA.WhsCode and x.Itemcode = TA.ItemCode  
  --and X.ItemCode = 'OTH000005425'  
  --AND X.Whse = 'MN-WH'  
  --AND DocDate BETWEEN '2015-08-01 00:00:00.000' AND '2016-08-31 00:00:00.000'  
group by x.DocDate,x.Itemcode,x.[Whse],GroupName,ItemName, TA.OnHand