mad mad - 3 months ago 12
SQL Question

Everyday Inventory If inventory shows negative value select next stocktaking and subtract from it

Stocktaking Table
StoreID Date ProductCode Qty
1 2016-07-30 11 58
1 2016-09-30 11 97
2 2016-08-30 12 15
2 2016-09-22 55 10
3 2016-09-05 55 10


Sale Table
StoreID Date ProductCode Qty
1 2016-08-30 11 40
2 2016-08-30 12 3
2 2016-08-30 55 4
3 2016-08-30 55 6

Desired Output


StoreID Date Productcode Qty
1 2016-08-30 11 18
2 2016-08-30 12 12
2 2016-08-30 55 6
3 2016-08-30 55 4



parameter


@ date1 and @date2 @storeid


I need to show everyday inventory; I can show
stocktakingQty -saleQty = Todayinventory



I got like this


StoreID Date Productcode Qty
1 2016-08-30 11 18
2 2016-08-30 12 12
2 2016-08-30 55 -4
3 2016-08-30 55 -6


I can’t able to show negative value here .
Problem here was they forget to take stocktaking during
8th month
store ID 3,2
but they sold that
product Code 55
on
2016-08-30
. next month they have stock taken that
product Code 55
during
9th month
.
I’m not sure how to do it !!bcoz if its only negative qty value I need to select next month stock taken date or else I have select 8th stock taken date.

maybe somebody could help with it!!

Declare
@date1 date = '2015-06-01'
,@date2 date = '2015-06-30'
,@StoreNo Nvarchar(Max)=' AND S.StoreNo IN (61,68,450,451,430,917,918,919,921,923,925,930,935)'


--AS
BEGIN
IF Object_ID(N'tempdb..#calender') IS NOT NULL DROP TABLE #calender
IF Object_ID(N'tempdb..##Temp') IS NOT NULL DROP TABLE ##Temp
IF Object_ID(N'tempdb..##Inv1') IS NOT NULL DROP TABLE ##Inv1
IF Object_ID(N'tempdb..##Inv2') IS NOT NULL DROP TABLE ##Inv2
IF Object_ID(N'tempdb..#Stock') IS NOT NULL DROP TABLE #Stock
IF Object_ID(N'tempdb..##StoreList') IS NOT NULL DROP TABLE ##StoreList
IF Object_ID(N'tempdb..##final') IS NOT NULL DROP TABLE ##final
IF Object_ID(N'tempdb..##product') IS NOT NULL DROP TABLE ##product

Declare @sql_store nvarchar(max)
SET @sql_store =
'SELECT StoreNo,StoreName INTO ##StoreList FROM Store S WHERE S.StoreNo IN (61,68,450,451,430,917,918,919,921,923,925,930,935)' + @StoreNo
EXECUTE sp_executesql @sql_store


Declare

@date_s char(10) = cast(@date1 as varchar)
,@date_e char(10) = cast(@date2 as varchar)
,@date_index date


create Table #calender (Date date)
SET @date_index = @date1

WHILE @date_index<=@date2
BEGIN
INSERT INTO #calender
SELECT @date_index

SET @date_index = dateadd(day,1,@date_index)

IF @date_index>@date2
Break
ELSE
Continue
END


BEGIN
SELECT
ProductNo as ProductBarCode,Date

INTO ##product
FROM Product,#calender

WHERE StartUseDate <= @date_s AND EndUseDate >=@date_e and IsInUsed = 1

END

create table #inventory (StoreNo int ,Date date, ProductBarCode varchar(14),ProductQty int )



BEGIn

Select
STD.StoreNo As StoreNo
,CheckDate as Date
,ProductBarCode as ProductBarCode
, SUM( StocktakingQty)AS ProductQty
INTO ##Temp
From StockTakingDetail STD
Inner Join
(Select
StoreNo
,CheckNo
,CheckDate
From StockTakingMain SM )StocktakingMain
On STD.CheckNo =StockTakingMain.CheckNo
where STD.StoreNo IN (select StoreNo from ##StoreList )
group By STD.StoreNo,STD.ProductBarCode,CheckDate




SELECT A.StoreNo as StoreNo ,C.[date] as Date,A.ProductBarCode as ProductBarCode,A.ProductQty as ProductQty

INTO ##inv1
FROM #calender C
OUTER APPLY
(
SELECT TOP 100 percent * FROM ##Temp I WHERE I.Date < C.DATE and StoreNo IN (select StoreNo from ##StoreList ) ORDER BY I.Date
) A
OPTION (maxrecursion 0)



INSERT INTO #inventory
Select S.StoreNo,s.Date,s.ProductBarCode,ISNULL (sum(s.ProductQty),0) as ProductQty

From
(Select StoreNo,Date,ProductBarCode,ProductQty from ##inv1 where Date between @date_s and @date_e and StoreNo IN (select StoreNo from ##StoreList )


Union all
--Buy
Select
BuyStore as StoreNo
,BuyDate as Date
,ProductBarCode as ProductBarCode
,SUM(BuyQty)as Qty

from BuyDetail BD
Inner Join
( Select
BuySerialNo
,BuyDate
from BuyMain BM) BuyMain ON BD.BuySerialNo = BuyMain.BuySerialNo
where
BuyDate Between @date_s and @date_e and BuyStore IN (select StoreNo from ##StoreList )
Group BY
BuyDate,BuyStore,ProductBarCode,BuyDate
)S
GROUP BY s.StoreNo,s.Date,s.ProductBarCode

END




BEGIN

select
S.Date as Date,S.StoreNo As StoreNo,(S.ProductBarCode ) as ProductBarCode,sum(S.Qty) as ProductQty
INTO #Stock
from
--- sale
(
select
StoreNo As StoreNo,
PluCode as ProductBarCode,
cast (sum(BuyPoint/100)as int)*(-1) as Qty,
Date as Date
from POS_ItemTran p
where
Date between @date_s and @date_e and p.StoreNo IN(select StoreNo from ##StoreList)
Group by
PluCode,StoreNo,Date

union all

--Transfer IN
select
TD.TargetStoreNo as StoreNo
,TD.ProductBarCode as ProductBarCode
,SUM(TD.AcceptQty) as Qty
,AcceptDate as Date
from TransferDetail TD
Inner join
(Select TransferSerialNo
,AcceptDate
from TransferMain )TM

ON TM.TransferserialNo = TD.TransferserialNo
where
AcceptDate between @date_s and @date_e and TargetStoreNo IN (select StoreNo from ##StoreList )
Group by
TargetStoreNo,TD.ProductBarcode,AcceptDate

union all

-- Transfer out
Select
TD.TransferStoreNo as StoreNo
, TD.ProductBarCode as ProductBarCode
,SUM(TD.TransferQty)*(-1) As Qty
,TransferDate as Date
from TransferDetail TD
Inner Join
(Select TransferSerialNo
,TransferDate
from TransferMain)TMO
On TMO.TransferSerialNo = TD.TransferSerialNo
where
TransferDate between @date_s and @date_e and TransferStoreNo IN (select StoreNo from ##StoreList )
Group by
TD.TransferStoreNo,TD.ProductBarCode,TransferDate

--Loss
UNION ALL

SELECT
StoreNo
,LossProductBarCode
,SUM(LossQty)*(-1) as Qty,
LossDate as Date

FROM LossDetail LD
INNER JOIN
(SELECT
StoreNo
,LossSerialNo
,LossDate
FROM LossMain LM)LossMain ON LD.LossSerialNo = LossMain.LossSerialNo
where
LossDate between @date_s and @date_e and StoreNo IN (select StoreNo from ##StoreList )
GROUP BY
StoreNo,LossProductBarCode,LossDate

Union All
--RCVBackDetail
Select
RcvBackStoreNo as StoreNo
,ProductBarCode as ProductBarCode
,SUM(RcvBackQty) as Qty
,RcvBackDate as Date
from RcvBackDetail RBD
Inner Join
(SELECT RcvBackSerialNo
,RcvBackDate
FROM RcvBackMain)RcvBackMain
ON RcvBackMain.RcvBackSerialNo = RBD.RcvBackSerialNo
Where
RcvBackDate Between @date_s and @date_e and RcvBackStoreNo IN (select StoreNo from ##StoreList )
Group By
RcvBackDate,RcvBackStoreNo,ProductBarCode


)S
Group by S.StoreNo,S.ProductBarCode,S.Date

END

BEGIN

Select
f.StoreNo,f.Date,f.ProductBarCode,SUM(f.ProductQty) as ProductQty
INTO ##Inv2
From
(Select StoreNo,Date,ProductBarCode,ProductQty From #inventory i
union all
Select StoreNo,Date,ProductBarCode,ProductQty From #Stock st)f
Group By f.Date,f.ProductBarCode,f.StoreNo
End


BEGIN
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME([StoreNo])
FROM ##StoreList
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SELECT @query =
'SELECT * FROM
(SELECT
[StoreNo],
p.Date as Date
,p.ProductBarCode
,(ProductQty) as productQty
FROM ##inv2 f
Right Join ##product P ON p.ProductBarCode =f.ProductBarCode and p.Date =f.Date
where
p.ProductBarCode IN(2300028813032,2221400000393,2300011512096,2300021714060)
)X
PIVOT
(
SUM(ProductQty)
for [StoreNo] in (' + @cols + ')
) P'

EXEC SP_EXECUTESQL @query
END
Drop Table #inventory
--Drop Table #calender
IF Object_ID(N'tempdb..#inventory') IS NOT NULL DROP TABLE #inventory
IF Object_ID(N'tempdb..#calender') IS NOT NULL DROP TABLE #calender
IF Object_ID(N'tempdb..##Temp') IS NOT NULL DROP TABLE ##Temp
IF Object_ID(N'tempdb..##Inv1') IS NOT NULL DROP TABLE ##Inv1
IF Object_ID(N'tempdb..##Inv2') IS NOT NULL DROP TABLE ##Inv2
IF Object_ID(N'tempdb..#Stock') IS NOT NULL DROP TABLE #Stock
IF Object_ID(N'tempdb..##StoreList') IS NOT NULL DROP TABLE ##StoreList
IF Object_ID(N'tempdb..##final') IS NOT NULL DROP TABLE ##final
IF Object_ID(N'tempdb..##product') IS NOT NULL DROP TABLE ##product

END

mad mad
Answer
    SELECT A.StoreNo as StoreNo ,C.[date] as Date,A.ProductBarCode as ProductBarCode,A.ProductQty as ProductQty
                                   INTO ##inv1

                                    FROM  #calender C
                                    OUTER APPLY 
                                    (
                                        SELECT DISTINCT TOP 100 percent * FROM ##Temp I WHERE I.Date <= C.DATE and StoreNo IN (select StoreNo from ##StoreList )   ORDER BY I.Date
                                    ) A 
                                    --where A.productBarcode =2300007115072
                                    OPTION (maxrecursion 0)

                    SELECT B.StoreNo as StoreNo ,C.[date] as Date,B.ProductBarCode as ProductBarCode,B.ProductQty as ProductQty
                                INTO ##inv2     

                                    FROM  #calender C
                                    OUTER APPLY 
                                    (
                                        SELECT DISTINCT TOP 100 percent * FROM ##Temp I WHERE I.Date >= C.DATE and StoreNo IN (select StoreNo from ##StoreList )   ORDER BY I.Date
                                    ) B 
                                    --where A.productBarcode =2300007115072
                                    OPTION (maxrecursion 0)
BEGIN 
                           Select 
                                        i.StoreNo
                                         ,i.Date
                                          ,i.ProductBarCode
                                            ,ISNULL(i.ProductQty - (Select SUM(Productqty) from #Stock st where st.Date<=i.Date and i.StoreNo =st.StoreNo and i.ProductBarCode= st.ProductBarCode),i.ProductQty ) as ProductQty
                            INTO ##final1
                                        From #inv1 i
                                        left join #Stock s 
                                             on s.storeno=i.storeno
                                                 and s.productbarcode=i.productbarcode
                                                 and s.date=i.date
                                                --where i.ProductBarCode IN(2221400000393)
                                                 Order By StoreNo,Date ,productBarcode
                              Select 
                                        i.StoreNo
                                         ,i.Date
                                          ,i.ProductBarCode
                                            ,ISNULL(i.Qty - (Select SUM(Productqty) from #Stock st where st.Date<=i.Date and i.StoreNo =st.StoreNo and i.ProductBarCode= st.ProductBarCode),i.Qty ) as ProductQty
                            INTO ##final2
                                        From #inv2 i
                                        left join #Stock s 
                                             on s.storeno=i.storeno
                                                 and s.productbarcode=i.productbarcode
                                                 and s.date=i.date
                                                --where i.ProductBarCode IN(2221400000393)
                                                 Order By StoreNo,Date ,productBarcode

         END



         BEGIN 

            Select f1.StoreNo as StoreNo,f1.Date as Date,f1.ProductBarCode as ProductBarCode,
                   case when f1.ProductQty <0 then f2.ProductQty else f1.ProductQty end as ProductQty 
            INTO  ##final3
             From  ##final1 f1
             Full  Outer Join ##final2 f2 
             ON f2.Date = f1.Date and f2.ProductBarCode = f1.ProductBarCode and f1.StoreNo = f2.StoreNo
       order By Date