September 1, 2011 at 7:58 am
I have this query that is giving me issues, if you have any suggestions they are apperciated!
Task: Find the monthly charges for all leases from 1/1/12 out 5 yrs to 12/31/16
What we need to do is populate the missing month’s data with the most recent previous month’s charge.
In the below example Lease X has charges to June 2012 and missing July – Nov, we need to have those missing months show $10
To add complications there are leases that can have a “void” where there are months missing charges then having the charges start up again.
In the below lease X starts up again after the void in Dec 2012 and commences in May 2013, we then need to project out the charges to 12/31/16 with the May 2013 amount of $50
CREATE TABLE #LeaseCharges (Lease VARCHAR(1), startdate DATETIME, enddate DATETIME, charge MONEY)
INSERT INTO #LeaseCharges SELECT 'X','1/1/12','1/31/12',10
INSERT INTO #LeaseCharges SELECT 'X','2/1/12','2/29/12',10
INSERT INTO #LeaseCharges SELECT 'X','3/1/12','3/31/12',10
INSERT INTO #LeaseCharges SELECT 'X','4/1/12','4/30/12',10
INSERT INTO #LeaseCharges SELECT 'X','5/1/12','5/31/12',10
INSERT INTO #LeaseCharges SELECT 'X','6/1/12','6/30/12',10
--
INSERT INTO #LeaseCharges SELECT 'X','12/1/12','12/31/12',50
INSERT INTO #LeaseCharges SELECT 'X','1/1/13','1/31/13',50
INSERT INTO #LeaseCharges SELECT 'X','2/1/13','2/28/13',50
INSERT INTO #LeaseCharges SELECT 'X','3/1/13','3/31/13',50
INSERT INTO #LeaseCharges SELECT 'X','4/1/13','4/30/13',50
INSERT INTO #LeaseCharges SELECT 'X','5/1/13','5/31/13',50
--
INSERT INTO #LeaseCharges SELECT 'A','1/1/12','1/31/12',30
INSERT INTO #LeaseCharges SELECT 'A','2/1/12','2/29/12',30
INSERT INTO #LeaseCharges SELECT 'A','3/1/12','3/31/12',30
INSERT INTO #LeaseCharges SELECT 'A','4/1/12','4/30/12',30
INSERT INTO #LeaseCharges SELECT 'A','5/1/12','5/31/12',30
INSERT INTO #LeaseCharges SELECT 'A','6/1/12','6/30/12',30
INSERT INTO #LeaseCharges SELECT 'A','7/1/12','7/31/12',30
INSERT INTO #LeaseCharges SELECT 'A','8/1/12','8/31/12',30
--
INSERT INTO #LeaseCharges SELECT 'A','12/1/12','12/31/12',15
INSERT INTO #LeaseCharges SELECT 'A','1/1/13','1/31/13',15
INSERT INTO #LeaseCharges SELECT 'A','2/1/13','2/28/13',15
INSERT INTO #LeaseCharges SELECT 'A','3/1/13','3/31/13',15
INSERT INTO #LeaseCharges SELECT 'A','4/1/13','4/30/13',15
INSERT INTO #LeaseCharges SELECT 'A','5/1/13','5/31/13',15
INSERT INTO #LeaseCharges SELECT 'A','6/1/13','6/30/13',130
INSERT INTO #LeaseCharges SELECT 'A','7/1/13','7/31/13',130
SELECT *
FROM #LeaseCharges
September 1, 2011 at 11:22 am
Hi Marty,
Here's one way you could do it. Note that this is a bit kludgey. I'll have another look tomorrow.
I've broken the run into simple sections to make it easier to understand. You should be able to combine these up once you understand what is going on.
Note that this also uses a tally table. If you don't have one already, there are much better ways to create one - do a quick search on the site for Jeff Moden's article.
Here's the code, let me know if it works for you!
-- create a temporary tally table
create table #tally (n int)
declare @i int
set @i = 0
while @i <= 100
begin
insert #tally values (@i)
set @i=@i+1
end
create clustered index ix_n on #tally(n)
select * from #tally
-- number the original data
select *
, datediff(m,'01 January 2012',startdate) as MonthIndex
, dense_rank () over (order by lease) as LeaseNumber
into #interim1
from #LeaseCharges
select * from #interim1
-- expand the possible combinations
select *
into #interim2
from #tally t
cross join (
select distinct LeaseNumber as C_LeaseNumber from #interim1
) c
where n <=59 -- 5 years
-- check data
select * from #interim2
-- return the previous month for months without data
with cte as (
select *
from #interim2 t
left join #interim i
on t.n = i.MonthIndex
and t.C_LeaseNumber = i.LeaseNumber
where (i.LeaseNumber = t.C_LeaseNumber or i.lease is null)
)
select cte.*
, coalesce(MonthIndex,(select max(MonthIndex) from #interim c where LeaseNumber = cte.C_LeaseNumber and MonthIndex < cte.n)) as MonthToReturn
into #interim3
from cte
order by C_LeaseNumber,n
-- check data
select * from #interim3
-- join this back to the interim data to get the result
select i1.*
from #interim3 i3
join #interim1 i1
on i3.MonthToReturn = i1.MonthIndex
and i3.C_LeaseNumber = i1.LeaseNumber
order by i3.C_LeaseNumber,n
September 1, 2011 at 2:10 pm
This fails, it looks for #interim table?
September 2, 2011 at 2:34 am
Oops, my bad. Replace:
, coalesce(MonthIndex,(select max(MonthIndex) from #interim c where LeaseNumber = cte.C_LeaseNumber and MonthIndex < cte.n)) as MonthToReturn
With
, coalesce(MonthIndex,(select max(MonthIndex) from #interim1 c where LeaseNumber = cte.C_LeaseNumber and MonthIndex < cte.n)) as MonthToReturn
September 2, 2011 at 3:24 am
Hi Marty,
Here's the combined version to allow you to return the results in one query. I've left you a bit of work to do - you'll need to change the dates that are displayed for the missing months.
You should still work through the broken out example I gave yesterday as this will help you to understand how it works.
Let me know how you get on.
Regards, Iain
-- this index should help
create clustered index ix_lease_start on #LeaseCharges(Lease,startdate)
with cte as (
select *
, datediff(m,'01 January 2012',startdate) as MonthIndex
, dense_rank () over (order by lease) as LeaseNumber
from #LeaseCharges
), cte2 as (
select *
from #tally t
cross join (
select distinct LeaseNumber as C_LeaseNumber from cte
) c
left join cte
on t.n = cte.MonthIndex
and c.C_LeaseNumber = cte.LeaseNumber
where (cte.LeaseNumber = c.C_LeaseNumber or cte.lease is null)
and n <=59
), cte3 as (
select *
, coalesce(MonthIndex,(select max(MonthIndex) from cte c where LeaseNumber = cte2.C_LeaseNumber and MonthIndex < cte2.n)) as MonthToReturn
from cte2
)
select cte.*
from cte3
left join cte
on cte3.MonthToReturn = cte.MonthIndex
where (cte.LeaseNumber = cte3.C_LeaseNumber)
order by C_LeaseNumber,n
September 2, 2011 at 10:04 am
Wooshy tooshy 🙂 i was in love with the requirement so i got coding for this in my flight to North Carolina 🙂 hows that for dedication 😛
First Part of the code :
Setting up the data. Finding missing dates or extra dates. All details are in comments 😎
-- Local Variables
DECLARE @StartDATE DATETIME
,@EndDate DATETIME
SELECT @StartDATE = '01-01-2012' , @EndDate = '01-01-2016'
-- Temp table for our calculation purpose
IF OBJECT_ID('TempDB..#TempLeaseTable') IS NOT NULL
DROP TABLE #TempLeaseTable
-- Notice the iD column; this is going to be the anchor for our entire code
CREATE TABLE #TempLeaseTable
(iD INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Lease VARCHAR(1), startdate DATETIME, enddate DATETIME, charge MONEY)
-- Indexes to support of queries; Must have
CREATE INDEX IX_TempLeaseTable_iDLease
ON #TempLeaseTable
( iD ASC , charge )
CREATE INDEX IX_TempLeaseTable_iDLeaseCharge
ON #TempLeaseTable
( iD ASC , Lease ASC)
INCLUDE ( charge )
-- Inline tally table
;WITH Tens (N) AS
(
SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
Thousands (N) AS
(
SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3
),
Numbers AS
(
-- Numbers tables ; contain numbers from 1 to 1000
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands
)
,MonthDates AS
(
-- Prepare a calendar table with months ( 2012 to 2016 )
SELECT MonthNum =
CASE T.N % 12
WHEN 0 THEN 12
ELSE T.N % 12
END
, DATEPART(YY,DATEADD(M,N-1,@StartDATE)) [YEAR]
, DATENAME(MM,DATEADD(M,N-1,@StartDATE)) [MONTH]
, DATEADD(M,N-1,@StartDATE) [StartDate]
, DATEADD( dd, -1 , DATEADD ( M , 1 , DATEADD(M,N-1,@StartDATE) )) [EndDate]
FROM Numbers T
WHERE DATEPART(YY,DATEADD(M,N-1,@StartDATE)) <= YEAR(@EndDate)
--ORDER BY [YEAR] , MonthNum
),
DistinctLease AS
(
-- Selecting distinct lease
-- this is used to fetch details about each lease and join it with calendar table
-- to find the missing dates / fill the extra dates
SELECT DISTINCT Lease
FROM #LeaseCharges
)
INSERT INTO #TempLeaseTable (Lease , startdate , enddate , charge )
SELECT CrsAppOutput.Lease , CrsAppOutput.startdate , CrsAppOutput.enddate , CrsAppOutput.charge
FROM DistinctLease OuterTable
CROSS APPLY
(
-- Use the Distinct lease, LEFT JOIN with calendar to find missing/extra
-- dates, "union all" with base table so that we get
-- complete set of dates from 2012 - 2016 per lease.
SELECT ISNULL(BTD.Lease , OuterTable.Lease ) Lease
, ISNULL(BTD.startdate , MTD.[StartDate]) startdate
, ISNULL(BTD.enddate , MTD.enddate) enddate
, BTD.charge charge
FROM MonthDates MTD
LEFT JOIN #LeaseCharges BTD
ON BTD.startdate = MTD.[StartDate]
AND BTD.Lease = OuterTable.Lease
) CrsAppOutput
-- Don't skip order by, this is our life saviour
ORDER BY OuterTable.Lease , CrsAppOutput.startdate
Second Part:
Finding charges for missed months and extra months.
--== WHILE LOOP TO UPDATE THE NULL CHARGES
-- Beleive it or not, i am using WHILE LOOP :D
DECLARE @ID INT = 0
,@CurriD INT = 0
,@NextiD INT = 0
,@StartDateFirstNullValue DATETIME
,@CurrLease VARCHAR(10)
,@NextLease VARCHAR(10)
,@Currcharge MONEY
,@NextChargeInSameLease MONEY
-- Loop until no charge with NULL value is found
WHILE EXISTS ( SELECT TOP 1 iD
FROM #TempLeaseTable
WHERE iD > @ID
AND charge IS NULL
)
BEGIN
-- Find the first row with NULL charge value
-- get the corresponding Lease detail also
SELECT TOP 1
@CurrLease = Lease
,@CurriD = iD
FROM #TempLeaseTable
WHERE charge IS NULL
ORDER BY iD ASC
-- Find the NEXT row with NOT NULL charge value
-- a row that is previous NOT NULL will be the last entry in NULL rows
-- this will be used to Update charge values in bulk
-- also pick up information in the NOT NULL row
-- this is used to avoid another scan on table to get the "next" charge value
-- present in the table
SELECT TOP 1
@NextiD = iD - 1
,@NextLease = Lease
,@NextChargeInSameLease = charge
FROM #TempLeaseTable
WHERE charge IS NOT NULL
AND iD >= @CurriD
--AND Lease = @CurrLease
ORDER BY iD ASC
-- This is to fix the problem when the last row of table is NULL value
-- If the last row of is NOT NULL, we will have a "valid next NOT NULL" row
-- If it is NULL then we must the row previous to the current to update
-- charges for all the rows from current row till last row of the table
IF @NextiD < @CurriD
BEGIN
SELECT @NextiD = MAX(iD) FROM #TempLeaseTable
SELECT @Currcharge = NULL
END
-- This logic is to avoid extra scan to the table to get the next available "charge" value
-- When a new Lease start, we need to scan the table to get the next available "charge" value
-- else we just utilize the value @NextChargeInSameLease
-- this avoids extra scan :-)
IF ( @CurrLease <> @NextLease ) OR ( @Currcharge IS NULL )
BEGIN
SELECT @Currcharge = charge
FROM #TempLeaseTable
WHERE iD = @CurriD - 1
AND Lease = @CurrLease
END
ELSE
BEGIN
SELECT @Currcharge = @NextChargeInSameLease
END
-- debug code; you can delete it :)
--SELECT @ID , @CurriD , @NextiD , @CurrLease, @Currcharge
-- Now the update logic
-- we retreived the ID relating to current row, which is the first occurence or NULL charge row
-- we also retreived the ID relating to last occurence or NULL charge row in the same lease
-- We already have mechanism to update only the iDs present in the current Lease
-- to make it fail proof, we can add "AND Lease = @Lease" in where clause
UPDATE T
SET T.charge = @Currcharge
FROM #TempLeaseTable T
WHERE iD >= @CurriD
AND iD <= @NextiD
-- Whenever we move to next lease, reset the counters
IF @CurrLease <> @NextLease
BEGIN
SELECT @Currcharge = NULL
END
-- Set the loop value to the value that is the NextID value
-- the last occurence of the NULL charge value, so that loop starts from this point
SET @ID = @NextiD
END
Third Part:
Cleaning up source table and inserting our new information
-- Truncate the original table
TRUNCATE TABLE #LeaseCharges
-- Insert the new values :-) ; we are all done
INSERT INTO #LeaseCharges
SELECT Lease , startdate , enddate , charge
FROM #TempLeaseTable
That's it 🙂 We are at World's End now :hehe:
Tell me if this code works for you !!
September 8, 2011 at 8:50 am
Hello Marty,
You still on the thread ? Did the code i sent work for you?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply