Skip to Content
0

help me to add Opening Balance of Stock along with the Query

Jan 04, 2017 at 06:20 AM

27

avatar image

Hi Experts

Pls help me by editing the attached query to attach Opening Balance of Stock

Regards

Select DISTINCT DocDate,Itemcode ,ItemName,GroupName,[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]  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 <> 'Y'
 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 <> 'Y'
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 <> 'Y'
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 <> 'Y'
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 <> 'Y'
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.FromWhsCod  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 <> 'Y'
group by WTR1.Itemcode,WTR1.FromWhsCod,WTR1.WhsCode,OWTR.DocDate,OITB.ItmsGrpNam,WTR1.Dscription


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 <> 'Y'
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 <> 'Y'
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 <> 'Y'
group by RIN1.Itemcode,RIN1.WhsCode,ORIN.DocDate,OITB.ItmsGrpNam,RIN1.Dscription


) as X 
where 
 [Whse] = 'MN-WH' AND DocDate BETWEEN '2016-07-02 00:00:00.000' AND '2016-07-02 00:00:00.000'
 group by DocDate,Itemcode,[Whse],GroupName,ItemName



10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers