date fillers - dates in two rows
-
Hello All,
I need a very efficient way to duplicate rows based on dates in rows. table structure is something as follows
CREATE TABLE #OutputResultSet
(
value INT
,dataitemuniverseid INT
,startdate DATETIME
,Pubsourceid VARCHAR(50)
,PubsourceName Varchar(50)
)data in the table is as follows
0 32833 2017-08-07 11:00:00.000 147942 EL
0 32835 2017-08-08 11:00:00.000 147942 EL
230 32835 2017-08-11 14:00:00.000 147942 EL
230 32833 2017-08-12 14:00:00.000 147942 EL
158 32832 2016-08-01 23:00:00.000 280159 ENT
0 32832 2016-08-03 11:00:00.000 280159 ENT
158 32832 2016-08-05 15:00:00.000 280159 ENT
0 32830 2016-08-07 23:00:00.000 280159 ENT
158 32830 2016-08-09 23:00:00.000 280159 ENTdesired output should be
0 32833 2017-08-07 11:00:00.000 147942 EL
0 32833 2017-08-08 11:00:00.000 147942 EL
0 32835 2017-08-09 11:00:00.000 147942 EL
0 32835 2017-08-10 11:00:00.000 147942 EL
230 32835 2017-08-11 14:00:00.000 147942 EL
230 32833 2017-08-12 14:00:00.000 147942 EL
158 32832 2016-08-01 23:00:00.000 280159 ENT
158 32832 2016-08-02 23:00:00.000 280159 ENT
0 32832 2016-08-03 11:00:00.000 280159 ENT
0 32832 2016-08-04 11:00:00.000 280159 ENT
158 32832 2016-08-05 15:00:00.000 280159 ENT
158 32832 2016-08-06 15:00:00.000 280159 ENT
0 32830 2016-08-07 23:00:00.000 280159 ENT
0 32830 2016-08-08 23:00:00.000 280159 ENT
158 32830 2016-08-09 23:00:00.000 280159 ENTPLEASE notice filler rows. if date is missing, copy previous row with day + 1
I am super stuck because of this. please help. I need very efficient solution as the date ranges across many months.
-
Take a close look at your expected output for August 8th, and then tell me how and why it's supposed to be that way, considering the values you use for August 9th.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
technically, if Aug 8 is missing, keep using the value of previous day i.e. Aug 7, if no value in 7, use value of Aug 6 and so on.
So for eq. pubsourcename = EL i want to repeat these duplication process for all startdate.
then for next pubSourceName and then for the next. - ekant_alone - Thursday, August 31, 2017 7:50 AM
That part I get, but look at your data for August 8th. There IS a record for that date, and it's values did NOT get preserved in your expected output. Please clarify if that was just a typo, and if not, what rule one would follow to derive that change.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
that is just a typo. Please excuse me for that.
-
0 32833 2017-08-07 11:00:00.000 147942 EL
0 32835 2017-08-08 11:00:00.000 147942 EL
230 32835 2017-08-11 14:00:00.000 147942 ELit should be
0 32833 2017-08-07 11:00:00.000 147942 EL
0 32835 2017-08-08 11:00:00.000 147942 EL
0 32835 2017-08-09 11:00:00.000 147942 EL
0 32835 2017-08-10 11:00:00.000 147942 EL
230 32835 2017-08-11 14:00:00.000 147942 EL -
First, sample data should be provided as an INSERT statement into your sample table.
The solution below uses a tally table. There is a blazing fast inline table-valued function that takes a value and produces that many rows, but I haven't included that here, because not everyone can create UDFs. Instead, I'm using a table variable for the tally table.
Tally table setup.
DECLARE @Tally TABLE( n INT)/* This uses a zero-based Tally table instead of the typical one-based tally. */
INSERT @Tally(n)
VALUES (0), (1), (2), (3), (4), (5)Sample data (the correct way)
CREATE TABLE #OutputResultSet
(
value INT
,dataitemuniverseid INT
,startdate DATETIME
,Pubsourceid VARCHAR(50)
,PubsourceName Varchar(50)
)INSERT #OutputResultSet(value, dataitemuniverseid, startdate, Pubsourceid, PubsourceName)
VALUES
(0, 32833, '2017-08-07 11:00:00.000', 147942, 'EL'),
(0, 32835, '2017-08-08 11:00:00.000', 147942, 'EL'),
(230, 32835, '2017-08-11 14:00:00.000', 147942, 'EL'),
(230, 32833, '2017-08-12 14:00:00.000', 147942, 'EL'),
(158, 32832, '2016-08-01 23:00:00.000', 280159, 'ENT'),
(0, 32832, '2016-08-03 11:00:00.000', 280159, 'ENT'),
(158, 32832, '2016-08-05 15:00:00.000', 280159, 'ENT'),
(0, 32830, '2016-08-07 23:00:00.000', 280159, 'ENT'),
(158, 32830, '2016-08-09 23:00:00.000', 280159, 'ENT')The solution
;
WITH PubDateRanges AS
(
SELECT *, DATEDIFF(DAY, startdate, LEAD(startdate, 1, DATEADD(DAY, 1, startdate)) OVER(PARTITION BY PubSourceID ORDER BY startdate)) - 1 as missingdays
FROM #OutputResultSet
)
SELECT value, dataitemuniverseid, DATEADD(DAY, n, startdate) AS startdate, Pubsourceid, PubsourceName
FROM PubDateRanges
INNER JOIN @Tally
ON missingdays >= n
ORDER BY Pubsourceid, startdateDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
thanks a ton Drew, however i am still having trouble
see there is a gap between 2016-08-05 rand 2017-01-10. how can i fix this? Can you please help?
-
Hello Drew, i used the below mentioned SQL to get pass through the above issue., does it make sense to you?
THANK YOU for helping me here. i was stuck since last 2 days on this.declare @n int
set @n = ( select DATEDIFF(hh, @StartDate, @EndDate))
declare @cntr int = 0DECLARE @Tally TABLE( n INT)
/* This uses a zero-based Tally table instead of the typical one-based tally. */while @cntr < @n
begin
INSERT @Tally(n)
select @cntrset @cntr = @cntr + 1
end -
Loops are horribly slow. It is much better to use a set-based approach. You can modify the below by adding additional records to the VALUES clause and/or additional CROSS JOINs.
DECLARE @Tally TABLE( n INT)/* This uses a zero-based Tally table instead of the typical one-based tally. */
; WITH CTE AS
(
SELECT n
FROM (VALUES (1), (1), (1), (1), (1), (1)) t(n)
)
INSERT @Tally(n)
SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1
FROM CTE AS a
CROSS JOIN CTE AS b
CROSS JOIN CTE AS cDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - ekant_alone - Thursday, August 31, 2017 11:56 AM
The only way you'll stop writing loops is if you just up and stop writing them. 😉 Are you allowed to use inline Table Valued Functions in your databases?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello Drew
cant thank you enough,
I just met another issue.SELECT value, dataitemuniverseid, DATEADD(DAY, n, startdate) AS startdate, Pubsourceid, PubsourceName
FROM PubDateRanges
INNER JOIN @Tally
ON missingdays >= n
ORDER BY Pubsourceid, startdateI have a dataitemuniverseid where all i have are 2 values. the missingdays value is > 367 days. It is skipping the dates.
as shown in the picture below -
Also change in requirement - i want hourly increment and not daily increment. thus there is an hour part included.
- ekant_alone - Thursday, August 31, 2017 3:11 PM
So answer my question. 😉 Are you allowed to use Inline Table Valued Functions or not?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
yes i am allowed to. But i am confused how i can help my case using IVTF here. Can you please help me. I am super stuck. thank you
wViewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply