Subset rows with opening and closing balance
-
Hello All,
I trying to get result as mentioned below (excel format for illustration purpose only).
I try using my script and achieved below result.
Please give all your valuable suggestions and support to improve query results. Refer to the attachment for sample data
DECLARE
@startDate DATETIME,
@endDate DATETIME,
@OpenBalance intSET @startDate = '09/01/2018'
SET @endDate = '12/30/2018'; WITH cteDates AS
(
SELECT
@startDate 'new_date',
@startDate 'end_date',
1 as level
UNION ALL
SELECT
DATEADD(DD,1,new_date) 'new_date',
DATEADD(DD,1,new_date),
level+1
FROM cteDates
WHERE DATEADD(dd,1,new_date) < @endDate
)
, cteInvoice AS
(
SELECT ProductID
,InvoiceNo
,InvoiceDate
,InvoiceQty
,LEAD(InvoiceDate) OVER (PARTITION BY ProductID ORDER BY InvoiceDate) AS NextDate
FROM ##tmpInvoice AS h
JOIN cteDates ON InvoiceDate BETWEEN cteDates.new_date and cteDates.end_date
)
, cteUsage AS
(
SELECT ProductID, TransactionDate, Quantity
FROM ##tmpUsage
JOIN cteDates ON TransactionDate BETWEEN cteDates.new_date and cteDates.end_date
)SELECT cteInvoice.*
,o.UsageQty
,ROW_NUMBER() OVER (PARTITION BY cteInvoice.ProductID,CteInvoice.InvoiceDate ORDER BY cteInvoice.InvoiceNo) AS RowNumber
FROM cteInvoice
OUTER APPLY (SELECT SUM(Quantity) AS UsageQty
FROM cteUsage
WHERE (cteInvoice.ProductId = cteUsage.ProductID)
AND (TransactionDate >= cteInvoice.InvoiceDate AND TransactionDate < cteInvoice.NextDate)
GROUP BY cteUsage.ProductID
) AS o
OPTION (MAXRECURSION 0);
GOThank you.
-
I'm guessing that given that your data makes no sense, no one is interested in helping you further propagate the nonsense. Take a close look at your first three rows. How do you bring in no Invoice Qty, use 72,742, and have no change to an opening balance of 0 to end up with a closing balance of 0 ? There's no way that is going to make sense. The same kind of problem occurs three times, and I stopped being interested beyond that point. The math doesn't work, and there's no way you'll get a query to reproduce that behavior without doing things it would make no sense to do. Splain, Lucy... (please pardon the reference to a US-based 1960's sit-com named I Love Lucy).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Thursday, March 7, 2019 1:57 PM
Hello,
I am trying to get usage qty in between invoices (1st to 2nd to 3rd...) whether its high or low.
First 3 transaction closing balance set to 0 due to closing balance went to negative value. If closing balance available only transfer to next transaction as opening balance.P.S. please don't push yourself to help others if you are not interested.
- kiran 4243 - Thursday, March 7, 2019 7:11 PM
It's not that I don't want to help. You're just asking for help with something that you don't even take the time to explain why there's such a glaring inconsistency in, which tends to suggest you are way over your head in something you don't understand. Helping such a person can be challenging, because they may simply not have the necessary background to understand why there's a problem. There's no conceivable way to get the data you seek and have an accurate answer for any of those rows until you resolve the problem with what amounts to "negative inventory". Failure to accurately account for that kind of problem will not lead to a good result. I'm not about to help someone get fired for providing a totally wrong answer when there's clear evidence that there's a problem that's not been dealt with that could be a rather serious issue.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
The first thing I would suggest is getting rid of your cteDates. It is doing absolutely nothing except wasting CPU time. If you decide that you need one in the future, you should also use a different approach. An rCTE is a very expensive way to create a calendar table. You should use a tally table instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
I don't get the opening and closing balance figures, where/how/what is the business logic there? I would expect to have some attribute of a client or invoice that holds a balance figure.
Also the usage quantity of 8346 and 29156 are correct based on the usage table data so I suspect the problem is the way the data has been modeled in excel!
select sum(Quantity)
from ##tmpUsage where TransactionDate > = '2018-09-19' and TransactionDate < '2018-09-23'Otherwise,
This produces the remaining columns you're looking for:
;
WITH Invoices AS (
SELECT I.*
, NextInvoice = LEAD(InvoiceDate,1) OVER (PARTITION BY ProductId order by InvoiceDate)
, INVOICEDATERANKSORT = ROW_NUMBER() OVER (PARTITION BY InvoiceDate ORDER BY InvoiceDate)
FROM ##tmpInvoice I
),
Usage AS (
SELECT U_ProductId = ProductID
, TransactionDate
, UsageQty = SUM(Quantity)
FROM ##tmpUsage
GROUP BY ProductID
, TransactionDate
)
SELECT I.ProductID
, I.InvoiceNo
, I.InvoiceDate
, OpeningBalance = NULL
, I.InvoiceQty
, UsageQty = SUM(u.UsageQty)
, ClosingBalance = NULL
, RowNumber = row_number() over (partition by InvoiceDate order by InvoiceDate)
FROM Invoices I
LEFT JOIN Usage U ON I.ProductID = U.U_ProductId
AND (U.TransactionDate >= I.InvoiceDate AND U.TransactionDate < I.NextInvoice)
GROUP BY I.ProductID
, I.InvoiceNo
, I.InvoiceDate
, I.InvoiceQty -
Hello All,
Finally able to generate expected output result with below script. Hope this script will help as a reference to beginners like me.
DECLARE @startDate DATETIME,
@endDate DATETIMESET @startDate = '09/01/2018'
SET @endDate = '12/30/2018';WITH cteInvoice AS
(
SELECT ProductID
,InvoiceNo
,InvoiceDate
,InvoiceQty
,LEAD(InvoiceDate) OVER (PARTITION BY ProductID ORDER BY InvoiceDate) AS NextDate
FROM ##tmpInvoice AS h
WHERE h.InvoiceDate BETWEEN @startDate and @endDate
),
cteUsage AS
(
SELECT ProductID, TransactionDate, Quantity
FROM ##tmpUsage
WHERE TransactionDate BETWEEN @startDate and @endDate
),
cteQuery as
(
SELECT
P.ProductID,
P.InvoiceNo,
P.InvoiceDate,
P.InvoiceQty,
ISNULL(o.UsageQty,0) AS UsageQty,
SUM(CASE WHEN o.UsageQty>=P.InvoiceQty THEN 0 ELSE P.InvoiceQty-ISNULL(o.UsageQty,0) END) OVER (PARTITION BY P.ProductID ORDER BY P.InvoiceDate,P.InvoiceNo) AS [Closing Balance]
,ROW_NUMBER() OVER (PARTITION BY P.ProductID,P.InvoiceDate ORDER BY P.InvoiceNo) AS RowNumber
FROM cteInvoice P
OUTER APPLY (
SELECT SUM(Quantity) AS UsageQty
FROM cteUsage
WHERE (P.ProductID = cteUsage.ProductID)
AND (TransactionDate >= P.InvoiceDate AND TransactionDate < P.NextDate)
GROUP BY cteUsage.ProductID
) AS o
)
SELECT
ProductID,
InvoiceNo,
InvoiceDate,
LAG([Closing Balance],1,0) OVER (PARTITION BY ProductID ORDER BY InvoiceDate,InvoiceNo) AS [Opening Balance],
InvoiceQty,
UsageQty,
[Closing Balance],
RowNumber
FROM cteQuery;
P.S. Thanks to Will_Kong
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply