Skip to Content
avatar image
Former Member

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

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



Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers