select
DENSE_RANK() over (order by startDate ) gr, LTRIM(str(year(startDate)))+case when month(startDate) < 10 then ‘-0’ + ltrim(str(month(startDate))) + ‘-01’ else ltrim(str(month(startDate))) + ‘-01’ end, LTRIM(str(year(startDate)))+case when month(startDate) < 10 then ‘-0’ + ltrim(str(month(startDate))) else ltrim(str(month(startDate))) end +‘-‘+ ltrim(str(day((DateAdd(day, –1, DATEADD(MONTH, 1, startDate)))))),code
, point, dt, amt from(
select DATEADD(day, –DAY(date) + 1, date) startDate, code, point, date dt, inc amt from Income where date <=(
select dateadd( MONTH, –2, MAX(date)) from(
select MAX(date) date from outcome unionselect
MAX(date) from income) maxDate)union
allselect
DATEADD(day, –DAY(date) + 1, date) startDate, code, point, date dt, –out amt from Outcome where date <=(
select dateadd( MONTH, –2, MAX(date)) from(
select MAX(date) date from outcome unionselect
MAX(date) from income) maxDate)) x–Output all records from Outcome and Income tables which dates are not less than 2 calendar months distant
–from the maximum date selected from these both tables
–( ex. when max date is "2009-12-05" last output date must be less or equal "2009-09-30").
–Partition these records and assign a sequenced number to every month (in view of a year) which has been selected.
–Result set: the sequenced number of the month, the first day of the month in "yyyy-mm-dd" format,
–the last day of the month in "yyyy-mm-dd" format, code, point, sum (which should be negative for Outcome table).
–num startDate endDate code point date sum
–1 2001-03-01 2001-03-31 1 1 2001-03-14 00:00:00.000 -15348.0000
–1 2001-03-01 2001-03-31 1 1 2001-03-22 00:00:00.000 15000.0000
–1 2001-03-01 2001-03-31 2 1 2001-03-23 00:00:00.000 15000.0000
–1 2001-03-01 2001-03-31 2 1 2001-03-24 00:00:00.000 -3663.0000
–1 2001-03-01 2001-03-31 3 1 2001-03-24 00:00:00.000 3600.0000
–1 2001-03-01 2001-03-31 3 1 2001-03-26 00:00:00.000 -1221.0000
–1 2001-03-01 2001-03-31 4 1 2001-03-28 00:00:00.000 -2075.0000
–1 2001-03-01 2001-03-31 4 2 2001-03-22 00:00:00.000 10000.0000
–1 2001-03-01 2001-03-31 5 1 2001-03-29 00:00:00.000 -2004.0000
–1 2001-03-01 2001-03-31 5 2 2001-03-24 00:00:00.000 1500.0000
–1 2001-03-01 2001-03-31 8 1 2001-03-22 00:00:00.000 15000.0000
–1 2001-03-01 2001-03-31 9 2 2001-03-24 00:00:00.000 1500.0000
–1 2001-03-01 2001-03-31 10 2 2001-03-22 00:00:00.000 -1440.0000
–1 2001-03-01 2001-03-31 11 1 2001-03-24 00:00:00.000 3400.0000
–1 2001-03-01 2001-03-31 11 2 2001-03-29 00:00:00.000 -7848.0000
–1 2001-03-01 2001-03-31 13 1 2001-03-24 00:00:00.000 -3500.0000
–1 2001-03-01 2001-03-31 14 2 2001-03-22 00:00:00.000 -1440.0000
–1 2001-03-01 2001-03-31 15 1 2001-03-29 00:00:00.000 -2006.0000
–2 2001-04-01 2001-04-30 6 1 2001-04-11 00:00:00.000 -3195.0400
–2 2001-04-01 2001-04-30 6 1 2001-04-13 00:00:00.000 5000.0000
–2 2001-04-01 2001-04-30 7 1 2001-04-13 00:00:00.000 -4490.0000
–2 2001-04-01 2001-04-30 8 1 2001-04-27 00:00:00.000 -3110.0000
–2 2001-04-01 2001-04-30 10 1 2001-04-13 00:00:00.000 5000.0000
–2 2001-04-01 2001-04-30 12 2 2001-04-02 00:00:00.000 -2040.0000
–3 2001-05-01 2001-05-31 7 1 2001-05-11 00:00:00.000 4500.0000
–3 2001-05-01 2001-05-31 9 1 2001-05-11 00:00:00.000 -2530.0000