asked
Please help: How to report next 6 months' rent
Our accounting department wants to be able to run a report showing the next six months of rent payments due: Bldg 12/2012 01/2013 02/2013 03/2013 04/2013 05/2013 101 2000 2100 2100 2100 2100 2100 102 50100 50200 50300 50400 50500 50600 (and so on) The rent schedules are stored in the RENTSCHED table: BLDG RENT STARTDATE ENDDATE 101 2000 2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 101 2100 2013-01-01 00:00:00.000 2013-06-30 00:00:00.000 101 2200 2013-07-01 00:00:00.000 2013-12-31 00:00:00.000 102 50000 2012-11-01 00:00:00.000 2012-11-30 00:00:00.000 102 50100 2012-12-01 00:00:00.000 2012-12-31 00:00:00.000 102 50200 2013-01-01 00:00:00.000 2013-01-31 00:00:00.000 102 50300 2013-02-01 00:00:00.000 2013-02-28 00:00:00.000 102 50400 2013-03-01 00:00:00.000 2013-03-31 00:00:00.000 102 50500 2013-04-01 00:00:00.000 2013-04-30 00:00:00.000 102 50600 2013-05-01 00:00:00.000 2013-05-31 00:00:00.000 102 50700 2013-06-01 00:00:00.000 2013-06-30 00:00:00.000 (and so on) Although I can get the 6 months' rent for any single building (e.g. 102) ... DECLARE @BLDG char(12) ,@nextMo DATETIME, @mo2 DATETIME, @mo3 DATETIME ,@mo4 DATETIME, @mo5 DATETIME, @mo6 DATETIME ,@nextMoRent FLOAT, @mo2Rent FLOAT, @mo3Rent FLOAT ,@mo4Rent FLOAT, @mo5Rent FLOAT, @mo6Rent FLOAT SELECT @BLDG='102' ,@nextMo=DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) + 1, 0) ,@mo2=DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) + 2, 0) ,@mo3=DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) + 3, 0) ,@mo4=DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) + 4, 0) ,@mo5=DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) + 5, 0) ,@mo6=DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) + 6, 0) SELECT @nextMoRent = [RENT] FROM [RENTSCHED] where [STARTDATE] < @nextMo AND @nextMo < [ENDDATE] and [BLDG]=@BLDG SELECT @mo2Rent = [RENT] FROM [RENTSCHED] where [STARTDATE] < @mo2 AND @mo2 < [ENDDATE] and [BLDG]=@BLDG SELECT @mo3Rent = [RENT] FROM [RENTSCHED] where [STARTDATE] < @mo3 AND @mo3 < [ENDDATE] and [BLDG]=@BLDG SELECT @mo4Rent = [RENT] FROM [RENTSCHED] where [STARTDATE] < @mo4 AND @mo4 < [ENDDATE] and [BLDG]=@BLDG SELECT @mo5Rent = [RENT] FROM [RENTSCHED] where [STARTDATE] < @mo5 AND @mo5 < [ENDDATE] and [BLDG]=@BLDG SELECT @mo6Rent = [RENT] FROM [RENTSCHED] where [STARTDATE] < @mo6 AND @mo6 < [ENDDATE] and [BLDG]=@BLDG SELECT @BLDG, @nextMoRent, @mo2Rent, @mo3Rent, @mo4Rent, @mo5Rent, @mo6Rent ... I have been unable to find a way to get it for **all** buildings at once. Any help is greatly appreciated.
Kevin, **thank you** for the response! As soon as I saw "Because you're using 2008" I realized I made a mistake on the tags. (I normally work with 2008 R2 DBs but this one happens to be 2005.) So, I corrected the tag and I'm sorry but I have to ask what needs to change given that I'm working with 2005. So far, I changed this: declare @StartDate date = CURRENT_TIMESTAMP; ... to this: declare @StartDate date; select @StartDate = getdate(); And, changed the inserts into #temp to happen individually. But, what can I do to make the FROM clause (and anything else) work?
I edited my answer to show how you can move from a virtual table back to a 2005-compatible table. The rest, I believe, should work fine in 2005, although you might need to fiddle with the query some.
This is a pretty complicated query, so I'm going to walk through it in detail (and I'm sure somebody here will be able to come up with a better-performing version). First, test data: create table #temp ( bldg int, rent decimal(10,2), startdate date, enddate date ); insert into #temp(bldg, rent, startdate, enddate) values (101, 2000, '2012-01-01 00:00:00.000', '2012-12-31 00:00:00.000'), (101, 2100, '2013-01-01 00:00:00.000', '2013-06-30 00:00:00.000'), (101, 2200, '2013-07-01 00:00:00.000', '2013-12-31 00:00:00.000'), (102, 50000, '2012-11-01 00:00:00.000', '2012-11-30 00:00:00.000'), (102, 50100, '2012-12-01 00:00:00.000', '2012-12-31 00:00:00.000'), (102, 50200, '2013-01-01 00:00:00.000', '2013-01-31 00:00:00.000'), (102, 50300, '2013-02-01 00:00:00.000', '2013-02-28 00:00:00.000'), (102, 50400, '2013-03-01 00:00:00.000', '2013-03-31 00:00:00.000'), (102, 50500, '2013-04-01 00:00:00.000', '2013-04-30 00:00:00.000'), (102, 50600, '2013-05-01 00:00:00.000', '2013-05-31 00:00:00.000'), (102, 50700, '2013-06-01 00:00:00.000', '2013-06-30 00:00:00.000'); Query: declare @StartDate date = CURRENT_TIMESTAMP; declare @cols nvarchar(300); select @cols = COALESCE(@cols + ',[' + convert(varchar(20), DATEADD(month, DATEDIFF(month, 0, @StartDate) + mon, 0), 1) + ']', '[' + convert(varchar(20), DATEADD(month, DATEDIFF(month, 0, @StartDate) + mon, 0), 1) + ']') from ( values(1), (2), (3), (4), (5), (6) ) AS months(mon); declare @query nvarchar(max); set @query = N' declare @StartDate date = CURRENT_TIMESTAMP; select bldg, ' + @cols + ' from ( select t.bldg, t.rent, sm.StartOfMonth from ( SELECT DATEADD(month, DATEDIFF(month, 0, @StartDate) + mon, 0) AS StartOfMonth FROM ( values(1), (2), (3), (4), (5), (6) ) AS months(mon) ) sm cross join #temp t where sm.StartOfMonth between t.StartDate and t.EndDate ) p pivot ( sum(rent) for StartOfMonth in (' + @cols + ') )as pvt order by bldg '; exec(@query); Full description: First, we want to get the next six months. Because you're using 2008 or later, I can do this: declare @StartDate date = CURRENT_TIMESTAMP; SELECT DATEADD(month, DATEDIFF(month, 0, @StartDate) + mon, 0) AS StartOfMonth FROM ( values(1), (2), (3), (4), (5), (6) ) AS months(mon) The SELECT clause gets the beginning of the month. The FROM clause creates a virtual table with the values 1 through 6 in it. You add that value (called mon) in the SELECT clause to get six rows with the next six months. Then, I create a variable called @cols, which contains the columns for those next six months. We'll need that for the pivot table. The only trick behind the pivot table is that we're making it dynamic, using @cols. That way, you can run this table for any set of months and get the date as a column. Alternatively, you could use the numbers 1-6 and turn this into a static pivot table, but your sample results had the actual month names in them. There are two things to point out with this: first, @StartDate is defined twice. That's because I use it both in the internal query and in the external query. So if you change it in one spot, change it in the other as well or else you'll get inconsistent results. Second, this kinda-sorta assumes that you'll have one row per building and start date, and that there aren't any overlapping rows or multiple rent changes in a month. If there are, this gets a bit more complex. **EDIT** - To get this to work in SQL Server 2005, there should only need to be two changes: changing the variable declaration, as you noted. Then, for the from clause, get rid of the virtual derived table: replace `values(1), ...` so that your statement now looks like: from ( select 1 as mon UNION ALL select 2 UNION ALL select 3 UNION ALL select 4 UNION ALL select 5 UNION ALL select 6 ) months At that point, it should work in 2005, although I don't have a test system readily available.
9 People are following this question.
