Task 18 (inc_out)

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

union

select

MAX(date) from income) maxDate)

union

all

select

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

union

select

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