January 21, 2014 at 11:51 am
I'm trying to create a cte to return a list of lots and every period between the first and last transaction date for each lot. I've gotten this far:
SELECTIntLotKey
,DATEPART(YYYY, StartDate)StartYear
,DATEPART(MM, StartDate)StartPeriod
,DATEPART(YYYY, EndDate)EndYear
,DATEPART(MM, EndDate)EndPeriod
FROM
(SELECT DISTINCT
IntLotKey
,MIN(IntTranDate) OVER (PARTITION BY IntLotKey)StartDate
,MAX(IntTranDate) OVER (PARTITION BY IntLotKey)EndDate
FROMInventoryTran
INNER JOIN Lot ON LotKey = IntLotKey
INNER JOIN Item ON ItmItemCode = LotItemID AND
ItmType = 'B') LOTDATERANGE ORDER BY IntLotKey
This gives me the following results:
IntLotKeyStartYearStartPeriodEndYearEndPeriod
271532013120135
28468201312201312
2846920131201312
2847020131201312
28472201312201312
593022013120131
593032013120131
Now what I need is something that looks like this:
LotKeyYearPeriod
2715320131
2715320132
2715320133
2715320134
2715320135
28468201312
2846920131
2846920132
2846920133
2846920134
2846920135
2846920136
2846920137
2846920138
2846920139
28469201310
28469201311
28469201312
Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after. Any help would be greatly appreciated.
Thanks.
January 21, 2014 at 11:58 am
With no DDL or sample data, there's not much we can help.
The usual method, would be to do a left join with your periods table.
January 21, 2014 at 12:17 pm
OK, sorry about that. I'll start over. Here's code to build a sample @InventoryTran table:
DECLARE@InventoryTran TABLE (
IntLotKeyINT,
IntTranDateDATE,
IntTranQtyINT)
INSERT INTO @InventoryTran
SELECT27153IntLotKey, '1-1-2013' IntTranDate, 10IntTranQty
UNIONSELECT27153IntLotKey, '3-10-2013' IntTranDate, 20IntTranQty
UNIONSELECT27153IntLotKey, '5-15-2013' IntTranDate, 30IntTranQty
UNIONSELECT28468IntLotKey, '12-10-2013' IntTranDate, 40IntTranQty
UNIONSELECT28469IntLotKey, '1-10-2013' IntTranDate, 50IntTranQty
UNIONSELECT28469IntLotKey, '12-10-2013' IntTranDate, 60IntTranQty
The table looks like this:
IntLotKeyIntTranDateIntTranQty
271531/1/201310
271533/10/201320
271535/15/201330
2846812/10/201340
284691/10/201350
2846912/10/201360
And I need to get results that look like this:
LotKeyYearPeriodBalance
271532013110
271532013210
271532013330
271532013430
271532013560
2846820131240
284692013150
284692013250
284692013350
284692013450
284692013550
284692013650
284692013750
284692013850
284692013950
2846920131050
2846920131150
28469201312110
January 21, 2014 at 1:09 pm
It might not be my day, because I might have overcomplicated myself with this. I haven't noticed that you needed a running total (balance). Here's an option using a fast method called Quirky Update. You should read more about it before using it. Here's the reference: http://www.sqlservercentral.com/articles/T-SQL/68467/. Be sure to play by the rules or you might get in trouble.
You might have to adapt to your real structures, but here's an example.
DECLARE@InventoryTran TABLE (
IntLotKeyINT,
IntTranDateDATE,
IntTranQtyINT)
INSERT INTO @InventoryTran
SELECT27153IntLotKey, '1-1-2013' IntTranDate, 10IntTranQty
UNION ALLSELECT27153IntLotKey, '3-10-2013' IntTranDate, 20IntTranQty
UNION ALLSELECT27153IntLotKey, '5-15-2013' IntTranDate, 30IntTranQty
UNION ALLSELECT28468IntLotKey, '12-10-2013' IntTranDate, 40IntTranQty
UNION ALLSELECT28469IntLotKey, '1-10-2013' IntTranDate, 50IntTranQty
UNION ALLSELECT28469IntLotKey, '12-10-2013' IntTranDate, 60IntTranQty
DECLARE@Periods TABLE (
PeriodDATE)
INSERT INTO @Periods
VALUES('20130101'),('20130201'),('20130301'),('20130401'),('20130501'),('20130601'),
('20130701'),('20130801'),('20130901'),('20131001'),('20131101'),('20131201')
CREATE TABLE #Result(
IntLotKeyint,
Perioddate,
Balanceint)
CREATE INDEX CX_Result ON #Result(IntLotKey, Period)
;WITH Ranges AS(
SELECT IntLotKey,
DATEADD( MONTH, DATEDIFF( MONTH, 0, MIN(IntTranDate)), 0) StartDate,
DATEADD( MONTH, DATEDIFF( MONTH, 0, MAX(IntTranDate)), 0) EndDate
FROM @InventoryTran p
GROUP BY IntLotKey
)
INSERT INTO #Result(
IntLotKey,
Period,
Balance)
SELECTx.IntLotKey, Period, ISNULL( IntTranQty, 0)
FROM @InventoryTran it
RIGHT
JOIN (
SELECT IntLotKey,
p.Period
FROM @Periods p
JOIN Ranges r ON p.Period BETWEEN r.StartDate AND r.EndDate
) x ON it.IntLotKey = x.IntLotKey AND DATEADD( MONTH, DATEDIFF( MONTH, 0, IntTranDate), 0) = Period
DECLARE @LotKey int,
@Balance int
UPDATE r
SET @Balance = Balance = CASE WHEN @LotKey = IntLotKey THEN @Balance ELSE 0 END + Balance,
@LotKey = IntLotKey
FROM #Result r WITH (TABLOCKX)
OPTION (MAXDOP 1)
SELECT IntLotKey, Period, Balance
FROM #Result
DROP TABLE #Result
January 21, 2014 at 1:21 pm
Thanks, Luis. When I originally posted the question, I didn't talk about the running balance, but since I added the test data I thought I'd go ahead and ask for the end results I'm after. I have read about the Quirqy update but I was hoping since I'm on SQL Server 2012 that I can use OVER (PARTITION BY logic instead. Also, I was hoping to use a cte so I could incorporate this into a view rather than a stored procedure without creating temp tables. Your code does return fast results and if I end up having to create a stored procedure then the temp table won't be an issue.
Thanks.
January 21, 2014 at 1:32 pm
You could certainly use the new capabilities to avoid the temp table.
I can't help you on that right now because I don't have an instance of 2012 to test myself, but you're free to try or maybe someone else will come and post something.
January 21, 2014 at 1:37 pm
I've had a go at this using the new windowing options in 2012 to do the running total.
I'm not sure what the performance of this will be like with larger datasets. I suspect the gap filling could be done better, but it's another option.
How would you want months handled where there id more the one row?
WITH cteSmallTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 N -- Count From 0
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
),
LotStartEnd AS (
SELECT IntLotKey, MIN(IntTranDate) StartDate, MAX(IntTranDate) EndDate
FROM @InventoryTran
GROUP BY IntLotKey
),
FillGaps AS (
SELECT IntLotKey, DATEADD(month,N,StartDate) cDate
FROM LotStartEnd
CROSS APPLY (SELECT TOP (DATEDIFF(month,startdate,enddate) + 1) N FROM cteSmallTally) c
)
SELECT f.IntLotKey, YEAR(f.cDate) Year, MONTH(f.cDate) Period, SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING) Balance
FROM FillGaps f
LEFT OUTER JOIN @InventoryTran i ON f.IntLotKey = i.IntLotKey AND YEAR(i.IntTranDate) = YEAR(f.cDate) AND MONTH(i.IntTranDate) = MONTH(f.cDate)
ORDER BY f.IntLotKey, f.cDate;
January 21, 2014 at 1:46 pm
Thanks MickyT, this looks closer to what I'm trying to accomplish. I'd need the IntTranQty summed up to 1 row per period so if there are multiple transactions in the same month/year, I need them returned in 1 row. I tried adding multiple transactions to the same period in your example and they came back as separate rows. My transaction table is pretty big so I'll see how it performs with the live table.
Thanks,
Gina
January 21, 2014 at 2:18 pm
I've made a small change to handle the summing of multiple values in a month
WITH cteSmallTally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
),
LotStartEnd AS (
SELECT IntLotKey, MIN(IntTranDate) StartDate, MAX(IntTranDate) EndDate
FROM @InventoryTran
GROUP BY IntLotKey
),
FillGaps AS (
SELECT IntLotKey, DATEADD(month,N,StartDate) cDate
FROM LotStartEnd
CROSS APPLY (SELECT TOP (DATEDIFF(month,startdate,enddate) + 1) N FROM cteSmallTally) c
)
SELECT f.IntLotKey,
YEAR(f.cDate) Year, MONTH(f.cDate) Period,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING) Balance
FROM FillGaps f
--LEFT OUTER JOIN @InventoryTran i ON f.IntLotKey = i.IntLotKey AND YEAR(i.IntTranDate) = YEAR(f.cDate) AND MONTH(i.IntTranDate) = MONTH(f.cDate)
OUTER APPLY (
SELECT t.IntLotKey, SUM(t.IntTranQty) IntTranQty
FROM @InventoryTran t
WHERE f.IntLotKey = t.IntLotKey AND YEAR(t.IntTranDate) = YEAR(f.cDate) AND MONTH(t.IntTranDate) = MONTH(f.cDate)
GROUP BY IntLotKey, YEAR(t.IntTranDate), MONTH(t.IntTranDate)) i
ORDER BY f.IntLotKey, f.cDate;
January 21, 2014 at 2:19 pm
Here is my attempt at this. It took me a while to dot the i's and cross the t's and I noticed someone else had a posting too. But mine already did the period sum to month I think the OP is looking for:
;WITH N AS (
SELECT 0 AS n UNION ALL SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11)
, a AS (
SELECT IntLotKey,
DATEADD(mm, DATEDIFF(mm, 0, IntTranDate), 0) AS IntTranMonth,
SUM(IntTranQty) AS IntTranQtySUM
FROM @inventorytran
GROUP BY IntLotKey, DATEADD(mm, DATEDIFF(mm, 0, IntTranDate), 0))
, b AS (
SELECT *
, SUM(a.IntTranQtySUM) OVER (PARTITION BY IntLotKey
ORDER BY IntLotKey, IntTranMonth
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RT_IntTranQtySUM
, DATEDIFF(mm, IntTranMonth, LEAD(a.IntTranMonth, 1, NULL) OVER (PARTITION BY IntLotKey
ORDER BY IntLotKey, IntTranMonth))-1 AS DateGap
FROM a)
SELECT IntLotKey,
COALESCE(DATEADD(mm, n, IntTranMonth), IntTranMonth) AS RT_TranMonth,
RT_IntTranQtySUM
FROM b
LEFT JOIN N ON n <= b.DateGap
ORDER BY IntLotKey, RT_TranMonth
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 21, 2014 at 2:56 pm
Wow! Both MickyT and TheSQLGuru results come back very fast with my live tran table. Thank you both so much for your help. This is exactly what I was hoping for.
Thanks so much!
Gina
January 21, 2014 at 2:58 pm
You're welcome
January 21, 2014 at 3:47 pm
One more small request 🙂 The Balance returned from the cte is the Ending balance. Is there a way I can also get the Beginning balance for each period in the results? I thought I could somehow use LAG but not working for me.
January 21, 2014 at 3:55 pm
gcresse (1/21/2014)
One more small request 🙂 The Balance returned from the cte is the Ending balance. Is there a way I can also get the Beginning balance for each period in the results? I thought I could somehow use LAG but not working for me.
You can change the select clause to the following
...
SELECT f.IntLotKey,
YEAR(f.cDate) Year, MONTH(f.cDate) Period,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) BalanceBefore,
SUM(i.IntTranQty) OVER (PARTITION BY f.IntLotKey ORDER BY f.cDate ROWS UNBOUNDED PRECEDING ) BalanceAfter
...
January 21, 2014 at 3:58 pm
Fantastic! I have never used ROWS UNBOUNDED PRECEDING but I'm going to start reading up on that functionality. Very useful.
Thanks, again!
Gina
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply