January 13, 2012 at 3:10 pm
I have a problem where i have data for last 5 year and its 7 days apart each date.Say i have data like:
Hotel| date| expense
XXX | 1 Jan| 23
XXX | 7 Jan | 34
YYY| 7 Jan | 34
YYY| 21 Jan 34
XXX| 28 Jan | 45
Data is missing for XXX for 21 Jan and for YYY for 1 and 28th Jan. What i need to do is YYY never existed before 7 Jan , expense for YYY for 1 Jan is zero but if record existed last week or any week before current week just insert missing weeks data with last occured data.
Any suggestions deeply apprciated.
January 13, 2012 at 4:40 pm
I have more questions than answers:
Why aren't the data for Jan 14 declared as "missing" for both hotels?
Why does it start on a Sunday (Jan 1st) -assuming we're talking about this year- but continues with Saturdays?
And where's the year information in that data??? How do you know the value for 7 Jan refers to a specific year?
What do you define as "last occured data"? Should the value of 21 Jan be used to expand Jan 1st for hotel YYYY or should it be based on the value of Jan 7?
When does your "current week" start (Sunday, Monday, or any other day)?
January 13, 2012 at 5:14 pm
Lutz has great questions. You haven't well defined the problem. I think you probably know what is missing, but from our perspective it doesn't make sense.
If you can list out the rules that mark the data, we can probably help you find the answer.
January 13, 2012 at 10:08 pm
So the data is like
Hotel | Date | Expense
XXX | 1 Jan 2001 | 23
XXX| 7 Jan 2001| 23
YYY| 7 Jan 2001 | 32
XXX| 21 Jan 2001| 43
YYY | 28 Jan 2001 | 43
Year is part of date and records are just seven days apart, and that makes it weekly. 14th Jan is missing for both and YYY is missing 21 and XXX is missing 28th.
What I want the process to do is:
Hotel | Date | Expense
XXX | 1 Jan 2001 | 23
YYY| 1 Jan 2001| 0
XXX| 7 Jan 2001| 23
YYY| 7 Jan 2001 | 32
XXX| 14 Jan 2001| 23
YYY|14 Jan 2001| 32
XXX| 21 Jan 2001| 43
YYY | 21Jan 2001 | 32
XXX| 28 Jan 2001 | 43
YYY | 28 Jan 2001 | 43
So if the record is missing, process should create row for that hotel for missing date with expense as last know expense. like XXX| 28 Jan 2001 | 43 is created.
And when record is seen first time ( YYY occured first time on Jan 7th) , then create record for YYY for all previous date like YYY| 1 Jan 2001| 0
I hope it make sense now.
January 14, 2012 at 4:28 am
Here's my first approach. The result is a list of dates per hotel in the given date range with already known values.
While coding this solution I came across a question I didn't ask at the very beginnig but that ould make a big difference regarding the solution: Do you use SQL 2000 as indicated by the forum you posted in or do you use SQL2005/2008/R2?
Also, please note I used a Tally table. If you don't know what that is or how it can be used, I recommend to have a look at the related link in my signature. Jeff Moden did an outstanding job describing how to build and use it.
CREATE TABLE #temp
(
Hotel CHAR(3), ExpenseDate DATETIME, Expense INT
)
INSERT INTO #temp
SELECT 'XXX','1 Jan 2001',23 UNION ALL
SELECT 'XXX','7 Jan 2001',23 UNION ALL
SELECT 'YYY','7 Jan 2001',32 UNION ALL
SELECT 'XXX','21Jan 2001',43 UNION ALL
SELECT 'YYY','28 Jan 2001',43
SELECT *
FROM #temp
SELECT hotel, ExpenseDate
INTO #tempNewRange
FROM
(
SELECT DATEADD(dd,-(N-1)*7,LastDate) AS ExpenseDate
FROM
(
SELECT
MAX(ExpenseDate) AS LastDate ,
DATEADD(YY,YEAR(MAX(ExpenseDate))-1900,0) AS FirstDate
FROM #temp
) subDateRange
CROSS JOIN
(
SELECT N
FROM Tally
) Tally
WHERE (N-1) <= DATEDIFF(dd,FirstDate,LastDate)/7
UNION ALL
SELECT DATEADD(YY,YEAR(MAX(ExpenseDate))-1900,0) AS FirstDate
FROM #temp
) DateRange
CROSS JOIN
(
SELECT DISTINCT hotel
FROM #temp
) hotels
ORDER BY ExpenseDate, hotel
SELECT *
FROM #tempNewRange
CREATE UNIQUE INDEX UX_#tempNewRange_ExpenseDate_hotel
ON #tempNewRange(ExpenseDate,hotel);
SELECT t1.*,t2.expense
FROM #tempNewRange t1
LEFT JOIN #temp t2
ON t1.hotel=t2.hotel
AND t1.ExpenseDate=t2.ExpenseDate
DROP TABLE #temp
DROP TABLE #tempNewRange
January 14, 2012 at 1:01 pm
shivani.kataria (1/13/2012)
...Year is part of date and records are just seven days apart, and that makes it weekly. 14th Jan is missing for both and YYY is missing 21 and XXX is missing 28th.
...
Much better explanation, but Jan 1 - Jan 7 is not a week. Jan 1 - Jan 8 is a week. I don't know if this is a mistake, or you have special rules for the start of the year, but you might double check your rules against the data.
Lutz' solution looks good to me.
January 14, 2012 at 1:11 pm
Steve Jones - SSC Editor (1/14/2012)
shivani.kataria (1/13/2012)
...Year is part of date and records are just seven days apart, and that makes it weekly. 14th Jan is missing for both and YYY is missing 21 and XXX is missing 28th.
...
Much better explanation, but Jan 1 - Jan 7 is not a week. Jan 1 - Jan 8 is a week. I don't know if this is a mistake, or you have special rules for the start of the year, but you might double check your rules against the data.
Lutz' solution looks good to me.
It's not the complete solution yet since I'd like to confirm the version the solution should be based on. A cte or the CROSS APPLY approach would definitely make things easier. But at least the code so far is something to start with...
And I agree: the stated requirement and the sample data still don't match 100%.
January 14, 2012 at 1:21 pm
I am using sql server 2008.Here the exact data :
YEARDATE1 BUILDING COMMON NAMEPOWER LOAD
20072007-01-02 00:00:00.000200 Magellan200 M SLR91
20072007-07-10 00:00:00.000200 Magellan200 M SLR157
20072007-07-17 00:00:00.000200 Magellan200 M SLR158
20072007-07-24 00:00:00.000200 Magellan200 M SLR158
20072007-07-31 00:00:00.000200 Magellan200 M SLR158
20072007-08-07 00:00:00.000200 Magellan200 M SLR158
20072007-08-14 00:00:00.000200 Magellan200 M SLR155
20072007-08-21 00:00:00.000200 Magellan200 M SLR156
20072007-08-28 00:00:00.000200 Magellan200 M SLR157
20072007-09-04 00:00:00.000200 Magellan200 M SLR156
20072007-09-11 00:00:00.000200 Magellan200 M SLR156
20072007-09-18 00:00:00.000200 Magellan200 M SLR156
20072007-09-25 00:00:00.000200 Magellan200 M SLR156
20072007-10-02 00:00:00.000200 Magellan200 M SLR155
20072007-10-09 00:00:00.000200 Magellan200 M SLR155
20072007-10-16 00:00:00.000200 Magellan200 M SLR155
20072007-10-23 00:00:00.000200 Magellan200 M SLR156
20072007-10-30 00:00:00.000200 Magellan200 M SLR155
20072007-11-06 00:00:00.000200 Magellan200 M SLR157
20072007-11-13 00:00:00.000200 Magellan200 M SLR155
20072007-11-20 00:00:00.000200 Magellan200 M SLR151
20072007-11-27 00:00:00.000200 Magellan200 M SLR155
20072007-12-04 00:00:00.000200 Magellan200 M SLR154
20072007-12-11 00:00:00.000200 Magellan200 M SLR155
20072007-12-18 00:00:00.000200 Magellan200 M SLR153
20072007-12-25 00:00:00.000200 Magellan200 M SLR154
20082008-01-01 00:00:00.000200 Magellan200 M SLR154
20082008-01-08 00:00:00.000200 Magellan200 M SLR154
20082008-01-15 00:00:00.000200 Magellan200 M SLR154
20082008-01-22 00:00:00.000200 Magellan200 M SLR154
20082008-01-29 00:00:00.000200 Magellan200 M SLR154
20082008-02-05 00:00:00.000200 Magellan200 M SLR152
20082008-02-12 00:00:00.000200 Magellan200 M SLR152
20082008-02-19 00:00:00.000200 Magellan200 M SLR150
20082008-02-26 00:00:00.000200 Magellan200 M SLR150
20082008-03-04 00:00:00.000200 Magellan200 M SLR151
20082008-03-11 00:00:00.000200 Magellan200 M SLR150
20082008-03-18 00:00:00.000200 Magellan200 M SLR151
20082008-03-25 00:00:00.000200 Magellan200 M SLR150
20082008-04-01 00:00:00.000200 Magellan200 M SLR149
20082008-04-08 00:00:00.000200 Magellan200 M SLR150
20082008-04-15 00:00:00.000200 Magellan200 M SLR150
20082008-04-22 00:00:00.000200 Magellan200 M SLR148
20082008-04-29 00:00:00.000200 Magellan200 M SLR144
20082008-05-06 00:00:00.000200 Magellan200 M SLR146
20082008-05-13 00:00:00.000200 Magellan200 M SLR143
20082008-05-20 00:00:00.000200 Magellan200 M SLR144
20082008-05-27 00:00:00.000200 Magellan200 M SLR145
20082008-06-03 00:00:00.000200 Magellan200 M SLR145
20082008-06-10 00:00:00.000200 Magellan200 M SLR143
20082008-06-17 00:00:00.000200 Magellan200 M SLR141
20082008-06-24 00:00:00.000200 Magellan200 M SLR142
20082008-07-01 00:00:00.000200 Magellan200 M SLR141
20082008-07-08 00:00:00.000200 Magellan200 M SLR141
20082008-07-15 00:00:00.000200 Magellan200 M SLR139
20082008-07-22 00:00:00.000200 Magellan200 M SLR139
20082008-07-29 00:00:00.000200 Magellan200 M SLR138
20082008-08-05 00:00:00.000200 Magellan200 M SLR137
20082008-08-12 00:00:00.000200 Magellan200 M SLR137
20082008-08-19 00:00:00.000200 Magellan200 M SLR136
20082008-08-26 00:00:00.000200 Magellan200 M SLR133
20082008-09-02 00:00:00.000200 Magellan200 M SLR134
20082008-09-09 00:00:00.000200 Magellan200 M SLR132
20082008-09-16 00:00:00.000200 Magellan200 M SLR131
20082008-09-23 00:00:00.000200 Magellan200 M SLR133
20082008-09-30 00:00:00.000200 Magellan200 M SLR133
20082008-10-07 00:00:00.000200 Magellan200 M SLR133
20082008-10-14 00:00:00.000200 Magellan200 M SLR131
20082008-10-21 00:00:00.000200 Magellan200 M SLR131
20082008-10-28 00:00:00.000200 Magellan200 M SLR139
20082008-11-04 00:00:00.000200 Magellan200 M SLR132
20082008-11-11 00:00:00.000200 Magellan200 M SLR132
20082008-11-18 00:00:00.000200 Magellan200 M SLR133
20082008-11-25 00:00:00.000200 Magellan200 M SLR132
20082008-12-02 00:00:00.000200 Magellan200 M SLR133
20082008-12-09 00:00:00.000200 Magellan200 M SLR133
20082008-12-16 00:00:00.000200 Magellan200 M SLR132
20082008-12-23 00:00:00.000200 Magellan200 M SLR134
20082008-12-30 00:00:00.000200 Magellan200 M SLR134
20092009-01-06 00:00:00.000200 Magellan200 M SLR134
20092009-01-13 00:00:00.000200 Magellan200 M SLR131
20092009-01-20 00:00:00.000200 Magellan200 M SLR134
20092009-01-27 00:00:00.000200 Magellan200 M SLR130
20092009-02-03 00:00:00.000200 Magellan200 M SLR130
20092009-02-10 00:00:00.000200 Magellan200 M SLR130
20092009-02-17 00:00:00.000200 Magellan200 M SLR132
20092009-02-24 00:00:00.000200 Magellan200 M SLR131
20092009-03-03 00:00:00.000200 Magellan200 M SLR132
20092009-03-10 00:00:00.000200 Magellan200 M SLR133
20092009-03-17 00:00:00.000200 Magellan200 M SLR133
20092009-03-24 00:00:00.000200 Magellan200 M SLR133
20092009-03-31 00:00:00.000200 Magellan200 M SLR134
20092009-04-07 00:00:00.000200 Magellan200 M SLR131
20092009-04-14 00:00:00.000200 Magellan200 M SLR131
20092009-04-21 00:00:00.000200 Magellan200 M SLR133
20092009-04-28 00:00:00.000200 Magellan200 M SLR132
20092009-05-05 00:00:00.000200 Magellan200 M SLR132
20092009-05-12 00:00:00.000200 Magellan200 M SLR131
20092009-05-19 00:00:00.000200 Magellan200 M SLR131
20092009-05-26 00:00:00.000200 Magellan200 M SLR129
20092009-06-02 00:00:00.000200 Magellan200 M SLR128
20092009-06-09 00:00:00.000200 Magellan200 M SLR128
20092009-06-16 00:00:00.000200 Magellan200 M SLR128
20092009-06-23 00:00:00.000200 Magellan200 M SLR128
20092009-06-30 00:00:00.000200 Magellan200 M SLR128
20092009-07-07 00:00:00.000200 Magellan200 M SLR129
20092009-07-14 00:00:00.000200 Magellan200 M SLR128
20092009-07-21 00:00:00.000200 Magellan200 M SLR128
20092009-07-28 00:00:00.000200 Magellan200 M SLR129
20092009-08-04 00:00:00.000200 Magellan200 M SLR128
20092009-08-11 00:00:00.000200 Magellan200 M SLR129
20092009-08-18 00:00:00.000200 Magellan200 M SLR129
20092009-08-25 00:00:00.000200 Magellan200 M SLR127
20092009-09-01 00:00:00.000200 Magellan200 M SLR127
20092009-09-08 00:00:00.000200 Magellan200 M SLR124
20092009-09-15 00:00:00.000200 Magellan200 M SLR116
20092009-09-22 00:00:00.000200 Magellan200 M SLR117
20092009-09-29 00:00:00.000200 Magellan200 M SLR117
20092009-10-06 00:00:00.000200 Magellan200 M SLR117
20092009-10-13 00:00:00.000200 Magellan200 M SLR116
20092009-10-20 00:00:00.000200 Magellan200 M SLR117
20092009-10-27 00:00:00.000200 Magellan200 M SLR116
20092009-11-03 00:00:00.000200 Magellan200 M SLR117
20092009-11-10 00:00:00.000200 Magellan200 M SLR116
20092009-11-17 00:00:00.000200 Magellan200 M SLR118
20092009-11-24 00:00:00.000200 Magellan200 M SLR116
20092009-12-01 00:00:00.000200 Magellan200 M SLR116
20092009-12-08 00:00:00.000200 Magellan200 M SLR114
20092009-12-15 00:00:00.000200 Magellan200 M SLR114
20092009-12-22 00:00:00.000200 Magellan200 M SLR115
20092009-12-29 00:00:00.000200 Magellan200 M SLR115
20102010-01-05 00:00:00.000200 Magellan200 M SLR115
20102010-01-12 00:00:00.000200 Magellan200 M SLR112
20102010-01-19 00:00:00.000200 Magellan200 M SLR112
20102010-01-26 00:00:00.000200 Magellan200 M SLR106
20102010-01-26 00:00:00.000200 Magellan200 M PTR15
20102010-02-02 00:00:00.000200 Magellan200 M SLR106
20102010-02-09 00:00:00.000200 Magellan200 M SLR106
20102010-02-16 00:00:00.000200 Magellan200 M SLR107
20102010-02-23 00:00:00.000200 Magellan200 M SLR102
20102010-03-02 00:00:00.000200 Magellan200 M SLR102
20102010-03-09 00:00:00.000200 Magellan200 M SLR101
20102010-03-16 00:00:00.000200 Magellan200 M SLR102
20102010-03-23 00:00:00.000200 Magellan200 M SLR98
20102010-03-30 00:00:00.000200 Magellan200 M SLR97
20102010-04-06 00:00:00.000200 Magellan200 M SLR95
20102010-04-13 00:00:00.000200 Magellan200 M SLR96
20102010-04-20 00:00:00.000200 Magellan200 M SLR96
20102010-04-27 00:00:00.000200 Magellan200 M SLR96
20102010-05-04 00:00:00.000200 Magellan200 M SLR98
20102010-05-11 00:00:00.000200 Magellan200 M SLR96
20102010-05-18 00:00:00.000200 Magellan200 M SLR95
20102010-05-25 00:00:00.000200 Magellan200 M SLR95
20102010-06-01 00:00:00.000200 Magellan200 M SLR94
20102010-06-08 00:00:00.000200 Magellan200 M SLR93
20102010-06-15 00:00:00.000200 Magellan200 M SLR94
20102010-06-22 00:00:00.000200 Magellan200 M SLR92
20102010-06-29 00:00:00.000200 Magellan200 M SLR94
20102010-07-06 00:00:00.000200 Magellan200 M SLR93
20102010-07-13 00:00:00.000200 Magellan200 M SLR94
20102010-07-20 00:00:00.000200 Magellan200 M SLR93
20102010-07-27 00:00:00.000200 Magellan200 M SLR93
20102010-08-03 00:00:00.000200 Magellan200 M SLR93
20102010-08-10 00:00:00.000200 Magellan200 M SLR91
20102010-08-17 00:00:00.000200 Magellan200 M SLR92
20102010-08-17 00:00:00.000200 Magellan200 M PTR14
20102010-08-24 00:00:00.000200 Magellan200 M SLR88
20102010-08-31 00:00:00.000200 Magellan200 M SLR88
20102010-09-07 00:00:00.000200 Magellan200 M SLR88
20102010-09-14 00:00:00.000200 Magellan200 M SLR88
20102010-09-21 00:00:00.000200 Magellan200 M SLR88
20102010-09-28 00:00:00.000200 Magellan200 M SLR88
20102010-10-05 00:00:00.000200 Magellan200 M SLR88
20102010-10-12 00:00:00.000200 Magellan200 M SLR88
20102010-10-19 00:00:00.000200 Magellan200 M SLR88
20102010-10-26 00:00:00.000200 Magellan200 M SLR88
20102010-11-02 00:00:00.000200 Magellan200 M SLR86
20102010-11-09 00:00:00.000200 Magellan200 M SLR85
20102010-11-16 00:00:00.000200 Magellan200 M SLR86
20102010-11-23 00:00:00.000200 Magellan200 M SLR84
20102010-11-30 00:00:00.000200 Magellan200 M SLR84
20102010-12-07 00:00:00.000200 Magellan200 M SLR84
20102010-12-14 00:00:00.000200 Magellan200 M SLR84
20102010-12-21 00:00:00.000200 Magellan200 M SLR84
20102010-12-28 00:00:00.000200 Magellan200 M SLR84
20112011-01-04 00:00:00.000200 Magellan200 M SLR84
20112011-01-11 00:00:00.000200 Magellan200 M SLR81
20112011-01-18 00:00:00.000200 Magellan200 M SLR70
20112011-01-25 00:00:00.000200 Magellan200 M SLR70
20112011-02-01 00:00:00.000200 Magellan200 M SLR69
20112011-02-08 00:00:00.000200 Magellan200 M SLR70
20112011-02-15 00:00:00.000200 Magellan200 M SLR70
20112011-02-22 00:00:00.000200 Magellan200 M SLR69
20112011-03-01 00:00:00.000200 Magellan200 M SLR69
20112011-03-08 00:00:00.000200 Magellan200 M SLR69
20112011-03-15 00:00:00.000200 Magellan200 M SLR65
20112011-03-22 00:00:00.000200 Magellan200 M SLR64
20112011-03-29 00:00:00.000200 Magellan200 M SLR64
20112011-04-05 00:00:00.000200 Magellan200 M SLR64
20112011-04-05 00:00:00.000200 Magellan200 M PTR13
20112011-04-12 00:00:00.000200 Magellan200 M SLR63
20112011-04-19 00:00:00.000200 Magellan200 M SLR63
20112011-04-26 00:00:00.000200 Magellan200 M SLR63
20112011-05-03 00:00:00.000200 Magellan200 M SLR63
20112011-05-10 00:00:00.000200 Magellan200 M SLR61
20112011-05-17 00:00:00.000200 Magellan200 M SLR60
20112011-05-24 00:00:00.000200 Magellan200 M SLR60
20112011-05-31 00:00:00.000200 Magellan200 M SLR60
20112011-06-07 00:00:00.000200 Magellan200 M SLR62
20112011-06-14 00:00:00.000200 Magellan200 M SLR60
20112011-06-21 00:00:00.000200 Magellan200 M SLR60
20112011-06-28 00:00:00.000200 Magellan200 M SLR60
20112011-07-05 00:00:00.000200 Magellan200 M SLR59
20112011-07-12 00:00:00.000200 Magellan200 M SLR58
20112011-07-19 00:00:00.000200 Magellan200 M SLR57
20112011-07-26 00:00:00.000200 Magellan200 M SLR57
20112011-08-02 00:00:00.000200 Magellan200 M SLR57
20112011-08-09 00:00:00.000200 Magellan200 M SLR57
20112011-08-16 00:00:00.000200 Magellan200 M SLR56
20112011-08-23 00:00:00.000200 Magellan200 M SLR56
20112011-08-30 00:00:00.000200 Magellan200 M SLR43
20112011-09-06 00:00:00.000200 Magellan200 M SLR43
20112011-09-13 00:00:00.000200 Magellan200 M SLR43
20112011-09-20 00:00:00.000200 Magellan200 M SLR43
20112011-09-27 00:00:00.000200 Magellan200 M SLR43
20112011-10-04 00:00:00.000200 Magellan200 M SLR43
20112011-10-11 00:00:00.000200 Magellan200 M SLR33
20112011-10-18 00:00:00.000200 Magellan200 M SLR31
20112011-10-25 00:00:00.000200 Magellan200 M SLR31
20112011-11-01 00:00:00.000200 Magellan200 M SLR31
20112011-11-08 00:00:00.000200 Magellan200 M SLR30
20112011-11-15 00:00:00.000200 Magellan200 M SLR31
20112011-11-22 00:00:00.000200 Magellan200 M SLR27
20112011-11-29 00:00:00.000200 Magellan200 M SLR27
20112011-12-06 00:00:00.000200 Magellan200 M SLR26
20112011-12-13 00:00:00.000200 Magellan200 M SLR26
20112011-12-20 00:00:00.000200 Magellan200 M SLR26
20112011-12-27 00:00:00.000200 Magellan200 M SLR13
20122012-01-03 00:00:00.000200 Magellan200 M SLR13
you will see in above data,Common name '200 M SLR' have data for more dates rather than '200 M PTR'
So now we can get list of dates for each Comman name.And what needed to be done is create data for '200 M PTR' for all the dates for which it doenst have data.
January 14, 2012 at 3:20 pm
Ok, 2008 version definitely makes it a little easier...
Here's my completed version. Please note that I didn't use your latest sample data since it would have taken too long to convert it into ready to use sample data.
I also figured the data are based on each Tuesday, so your previous information was slightly misleading...
The rather complicated calculation
(9-DATEDIFF(dd,0,DATEADD(YY,YEAR(MIN(ExpenseDate))-1900,0))%7)%7-1 is used to find the first Tuesday of the first year in the data.
DATEADD(YY,YEAR(MIN(ExpenseDate))-1900,0) will return Jan 1st for that year,
DATEDIFF(dd,0,...)%7 is used to calculate the weekday offset for the start of the year
(9-...)%7-1 finally will return the number of days to add to Jan 1st to get the first Tuesday of that year.
There is a better performing method to fill the gaps called "quirky update" but I decided against using it here to avoid yet another level of complexity.
CREATE TABLE #temp
(
Hotel CHAR(3), ExpenseDate DATETIME, Expense INT
)
INSERT INTO #temp
SELECT 'XXX','10 Jan 2012',1 UNION ALL
SELECT 'XXX','17 Jan 2012',2 UNION ALL
SELECT 'YYY','17 Jan 2012',11 UNION ALL
SELECT 'XXX','24 Jan 2012',3 UNION ALL
SELECT 'YYY','31 Jan 2012',12
; WITH CteSubDateRange as -- first and last date, data to calculate the first Tuesday
(
SELECT
MAX(ExpenseDate) AS LastDate ,
MIN(ExpenseDate) AS FirstDate,
DATEADD(YY,YEAR(MIN(ExpenseDate))-1900,0) AS YearStart,
(9-DATEDIFF(dd,0,DATEADD(YY,YEAR(MIN(ExpenseDate))-1900,0))%7)%7-1 offset
FROM #temp
),
CteHotels AS -- hotel names
(
SELECT DISTINCT hotel
FROM #temp
),
CteSubtotal AS -- one row for each Tueday in the given date range and each hotel
(
SELECT hotel,
DATEADD(dd,-(N-1)*7,LastDate) AS ExpenseDate
FROM CteSubDateRange
CROSS JOIN CteHotels
CROSS JOIN
(
SELECT N
FROM Tally
) Tally
WHERE (N-1) <= DATEDIFF(dd,DATEADD(dd,offset,YearStart),LastDate)/7
)
SELECT t1.*,t2.expense -- date range with known expense values
INTO #tempNewRange
FROM CteSubtotal t1
LEFT JOIN #temp t2
ON t1.hotel=t2.hotel
AND t1.ExpenseDate=t2.ExpenseDate
ORDER BY t1.ExpenseDate, t1.hotel
CREATE UNIQUE INDEX UX_#tempNewRange_ExpenseDate_hotel
ON #tempNewRange(hotel,ExpenseDate);
SELECT *
FROM #tempNewRange
ORDER BY hotel, ExpenseDate
-- fill the gaps
; WITH cte AS -- list of missing values
(
SELECT hotel, ExpenseDate,expense
FROM #tempNewRange
WHERE expense IS NULL
)
UPDATE
cte
SET expense = x.expense
FROM cte
CROSS APPLY -- find the latest value in the original data
(
SELECT TOP 1 expense
FROM #temp t1
WHERE cte.hotel = t1.hotel
AND cte.expenseDate > t1.expenseDate
ORDER BY t1.expenseDate DESC
)x
-- finally, set the first unknown values to zero
UPDATE #tempNewRange
SET expense = 0
WHERE expense IS NULL
SELECT *
FROM #tempNewRange
ORDER BY hotel, ExpenseDate
-- cleanup
DROP TABLE #temp
DROP TABLE #tempNewRange
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply