July 6, 2016 at 5:29 am
Hi,
Below is the sample table structure and sample data.
CREATE TABLE #Log
(
pageno int
, batchid int
, loadid int
, lnidmin int
, lnidmax int
, lncount int
, batch int
)
INSERT INTO #Log
VALUES
(1,1,3,7,15,9,2)
,(2,1,4,16,20,5,2)
,(3,2,5,21,27,7,2)
,(4,2,6,28,37,10,2)
SELECT * FROM #Log;
DROP TABLE #Log;
Requirement : Based on the number mentioned in the Column Batch, each row should be splitted into rows with the difference between column data difference of 2 columns lnidmin & lnidmax.
Expected Output is as follows:
pagenobatchidloadidlnidminlnidmax
11379
1131012
1131315
2141618
2141920
3152123
3152426
3152727
4262830
4263133
4263436
4263737
Expected output in table format:
CREATE TABLE #Output
(
pageno int
, batchid int
, loadid int
, lnidmin int
, lnidmax int
)
INSERT INTO #Output
values
(1,1,3,7,9)
,(1,1,3,10,12)
,(1,1,3,13,15)
,(2,1,4,16,18)
,(2,1,4,19,20)
,(3,1,5,21,23)
,(3,1,5,24,26)
,(3,1,5,27,27)
,(4,2,6,28,30)
,(4,2,6,31,33)
,(4,2,6,34,36)
,(4,2,6,37,37)
SELECT * FROM #Output
DROP TABLE #Output
July 6, 2016 at 6:25 am
Here's an option using a tally table. If you don't know what a tally table is, read the following article:
http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
I'm not sure if my math is more complex that it should, but I'm lacking some coffee.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n
FROM E4
)
SELECT l.pageno,
l.batchid,
l.loadid,
l.lnidmin + (n*(batch + 1)) AS lnidmin,
CASE WHEN l.lnidmin + (n*(batch + 1)) + batch > l.lnidmax
THEN l.lnidmax
ELSE l.lnidmin + (n*(batch + 1)) + batch END AS lnidmax
FROM #Log l
JOIN cteTally t ON l.lnidmin + (n*(batch + 1)) <= l.lnidmax
ORDER BY pageno, n;
July 6, 2016 at 6:50 am
Hey Luis,
Thanks a lot for your solution. Yes I know the Tally table.
I came up with the below solution. Instead of using Tally table, I have used UDF db.GetNums ( whose original author is Itzik Ben-Gan).
SELECT pageno
,batchid
,loadid
,MIN(SeqNo) - 1 AS lnidmin
,CASE
WHEN MAX(SeqNo) + 1 > lnidmax
THEN lnidmax
ELSE MAX(SeqNo) + 1
END AS lnidmax
FROM (
SELECT pageno
,batchid
,loadid
,lnidmin
,lnidmax
,SeqNo
,RN = SeqNo - ROW_NUMBER() OVER (
PARTITION BY pageno
,batchid
,loadid
,lnidmin
,lnidmax ORDER BY SeqNo
)
FROM (
SELECT pageno
,batchid
,loadid
,lnidmin
,lnidmax
,n + 1 AS SeqNo
FROM (
SELECT *
,n - lnidmin AS diff
FROM #Log AS t
CROSS APPLY dbo.Getnums(t.lnidmin, t.lnidmax) c
) A
WHERE diff % (batch + 1) = 0
) B
) C
GROUP BY pageno
,batchid
,loadid
,lnidmin
,lnidmax
,RN
July 6, 2016 at 7:17 am
You're over complicating yourself. Here's a simpler version of your query.
SELECT l.pageno,
l.batchid,
l.loadid,
n AS lnidmin,
CASE WHEN n + batch > lnidmax
THEN lnidmax
ELSE n + batch END
FROM #Log l
CROSS APPLY dbo.Getnums(l.lnidmin, l.lnidmax) t
WHERE (n - lnidmin) % (batch + 1) = 0;
July 6, 2016 at 7:25 am
You're over complicating yourself. Here's a simpler version of your query.
It seems that I need a coffee sip badly. 😀
That was awesome! Thanks
Sometimes we do easy things the hard way!
July 6, 2016 at 11:59 am
Luis Cazares (7/6/2016)
You're over complicating yourself. Here's a simpler version of your query.
SELECT l.pageno,
l.batchid,
l.loadid,
n AS lnidmin,
CASE WHEN n + batch > lnidmax
THEN lnidmax
ELSE n + batch END
FROM #Log l
CROSS APPLY dbo.Getnums(l.lnidmin, l.lnidmax) t
WHERE (n - lnidmin) % (batch + 1) = 0;
That's really slick Luis!! Once I get some coffee in me I hope I can actually understand it. :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 6, 2016 at 12:12 pm
TheSQLGuru (7/6/2016)
Luis Cazares (7/6/2016)
You're over complicating yourself. Here's a simpler version of your query.
SELECT l.pageno,
l.batchid,
l.loadid,
n AS lnidmin,
CASE WHEN n + batch > lnidmax
THEN lnidmax
ELSE n + batch END
FROM #Log l
CROSS APPLY dbo.Getnums(l.lnidmin, l.lnidmax) t
WHERE (n - lnidmin) % (batch + 1) = 0;
That's really slick Luis!! Once I get some coffee in me I hope I can actually understand it. :hehe:
It's not that hard, and uravindarreddy did the math.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply