March 25, 2017 at 8:59 am
SQL 2016
I would like to be able to make the following code "dynamic" so that it is able to produce the required output based on a parameter that determines the number of "LEAD" columns to return.
please see code and explanatory notes below.
This current solution is not set in stone so any alternative suggestions will be much appreciated.
Thanks
here is a small subset of testdata to play withUSE tempdb
GO
CREATE TABLE testdata(
id INT NOT NULL
,detdate DATE NOT NULL
,detail VARCHAR(2) NOT NULL
);
INSERT INTO testdata(id,detdate,detail) VALUES
(1,'2017-01-01','AB'),(1,'2017-01-04','DR'),(1,'2017-01-05','AB'),(1,'2017-01-08','AC')
,(1,'2017-01-09','DF'),(1,'2017-01-12','AC'),(1,'2017-01-13','DF'),(1,'2017-01-16','AB')
,(1,'2017-01-17','AB'),(1,'2017-01-20','DF'),(2,'2017-01-03','DR'),(2,'2017-01-06','AC')
,(2,'2017-01-07','AB'),(2,'2017-01-10','AB'),(2,'2017-01-11','DR'),(2,'2017-01-14','DF')
,(3,'2017-01-03','AC'),(3,'2017-01-06','AB'),(3,'2017-01-07','AC'),(3,'2017-01-10','DR');
--SELECT id,
-- detdate,
-- detail
--FROM testdata
--ORDER BY id, detdate;
The original requirement was that based on each "id" ....to return in a single row the detail for that day PLUS the detail for the next 4 details (ordered by detdate)
The code below works as requested
SELECT *
FROM
(
SELECT id,
detdate,
detail
+'-'+LEAD(detail, 1) OVER(PARTITION BY id ORDER BY detdate)
+'-'+LEAD(detail, 2) OVER(PARTITION BY id ORDER BY detdate)
+'-'+LEAD(detail, 3) OVER(PARTITION BY id ORDER BY detdate)
+'-'+LEAD(detail, 4) OVER(PARTITION BY id ORDER BY detdate)
AS list
FROM testdata
) td
WHERE list IS NOT NULL;
and returns
id detdate list
---- ------------ ----------------
1 2017-01-01 AB-DR-AB-AC-DF
1 2017-01-04 DR-AB-AC-DF-AC
1 2017-01-05 AB-AC-DF-AC-DF
1 2017-01-08 AC-DF-AC-DF-AB
1 2017-01-09 DF-AC-DF-AB-AB
1 2017-01-12 AC-DF-AB-AB-DF
2 2017-01-03 DR-AC-AB-AB-DR
2 2017-01-06 AC-AB-AB-DR-DF
The requirement has now changed and the wish is that a user can enter an aribitary number of rows to return as the "list"
for example...user decides to return 3 rows (current and next two rows)
again the following code works as expected
SELECT *
FROM
(
SELECT id,
detdate,
detail
+'-'+LEAD(detail, 1) OVER(PARTITION BY id ORDER BY detdate)
+'-'+LEAD(detail, 2) OVER(PARTITION BY id ORDER BY detdate)
AS list
FROM testdata
) td
WHERE list IS NOT NULL;
and returns
id detdate list
---- ------------ ----------
1 2017-01-01 AB-DR-AB
1 2017-01-04 DR-AB-AC
1 2017-01-05 AB-AC-DF
1 2017-01-08 AC-DF-AC
1 2017-01-09 DF-AC-DF
1 2017-01-12 AC-DF-AB
1 2017-01-13 DF-AB-AB
1 2017-01-16 AB-AB-DF
2 2017-01-03 DR-AC-AB
2 2017-01-06 AC-AB-AB
2 2017-01-07 AB-AB-DR
2 2017-01-10 AB-DR-DF
3 2017-01-03 AC-AB-AC
3 2017-01-06 AB-AC-DR
My question is how to convert this code so that it can dynamically return only the number of LEAD rows the user requests?
Very possibly I have gone down a rabbit hole on this and cannot see a straightforward solution that maybe I should have ??
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 25, 2017 at 9:37 am
Many ways of doing this, here are two of them. There is a difference in the performance depending on the data so check which fits your environment better.
😎
USE TEEST;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'dbo.testdata02') IS NOT NULL DROP TABLE dbo.testdata02;
CREATE TABLE dbo.testdata02
(
id INT NOT NULL
,detdate DATE NOT NULL
,detail VARCHAR(2) NOT NULL
);
INSERT INTO testdata02(id,detdate,detail) VALUES
(1,'2017-01-01','AB'),(1,'2017-01-04','DR'),(1,'2017-01-05','AB'),(1,'2017-01-08','AC')
,(1,'2017-01-09','DF'),(1,'2017-01-12','AC'),(1,'2017-01-13','DF'),(1,'2017-01-16','AB')
,(1,'2017-01-17','AB'),(1,'2017-01-20','DF'),(2,'2017-01-03','DR'),(2,'2017-01-06','AC')
,(2,'2017-01-07','AB'),(2,'2017-01-10','AB'),(2,'2017-01-11','DR'),(2,'2017-01-14','DF')
,(3,'2017-01-03','AC'),(3,'2017-01-06','AB'),(3,'2017-01-07','AC'),(3,'2017-01-10','DR');
-- CASE METHOD
DECLARE @NUM_LEAD INT = 3;
SELECT
TD.id
,TD.detdate
,TD.list
FROM
(
SELECT id,
detdate,
detail
+ CASE WHEN @NUM_LEAD > 1 THEN '-' + LEAD(detail, 1) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
+ CASE WHEN @NUM_LEAD > 2 THEN '-' + LEAD(detail, 2) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
+ CASE WHEN @NUM_LEAD > 3 THEN '-' + LEAD(detail, 3) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
+ CASE WHEN @NUM_LEAD > 4 THEN '-' + LEAD(detail, 4) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
AS list
FROM dbo.testdata02
) TD
WHERE list IS NOT NULL;
-- SUBQUERY METHOD
SELECT
TD.id
,TD.detdate
,(STUFF((
SELECT TOP(@NUM_LEAD)
'-' + STD.detail
FROM dbo.testdata02 STD
WHERE TD.id = STD.ID
AND TD.detdate <= STD.detdate
ORDER BY STD.detdate ASC
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(100)'),1,1,'')
)
FROM dbo.testdata02 TD;
March 25, 2017 at 10:59 am
Many thanks Eirikur
I am tending towards this solution (for reason not explained in my post)
-- CASE METHOD
DECLARE @NUM_LEAD INT = 3;
SELECT
TD.id
,TD.detdate
,TD.list
FROM
(
SELECT id,
detdate,
detail
+ CASE WHEN @NUM_LEAD > 1 THEN '-' + LEAD(detail, 1) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
+ CASE WHEN @NUM_LEAD > 2 THEN '-' + LEAD(detail, 2) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
+ CASE WHEN @NUM_LEAD > 3 THEN '-' + LEAD(detail, 3) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
+ CASE WHEN @NUM_LEAD > 4 THEN '-' + LEAD(detail, 4) OVER(PARTITION BY id ORDER BY detdate) ELSE '' END
AS list
FROM dbo.testdata02
) TD
WHERE list IS NOT NULL;
can this converted into a table function that can be use with CROSS APPLY?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply