February 10, 2014 at 2:03 am
Hi,
;WITH cte2 AS
(
SELECT
a.personkey as APK,
--b.personkey BPK,
a.BaseLocationKey,a.BaseGeographyKey,a.TotalDays,
A.MonthStartDate,a.MonthEndDate,a.DailyFTE,
a.MonthStartDate as ThisMonth,b.MonthStartDate as lastMonth,
a.DailyFTE as thisMonthFTE,b.DailyFTE as LastMonthFte
FROM #DailyFTE_AbovePerson as a
LEFT JOIN #DailyFTE_AbovePerson as b
ONa.personkey=b.personkey
AND b.monthstartdate=(DATEADD(mm,-1,a.Monthstartdate))
where a.personkey in (58973,85448,85606,5)
)
SELECT *,(thisMonthFTE-CASE WHEN LastMonthFte IS NULL THEN 0 ELSE LastMonthFte END) DifferenceofFTE
--Into #DifferenceinFTE
FROM cte2
--where lastMonth is not null
ORDER BY APK,monthstartdate
APKBLkKeyBGeoKeyTotalDaysStartDateEndDateDailyFTEThisMonthlastMonththisMonthFTELastMonthFteDifferenceofFTE
5623393693631 2013-10-01 2013-10-3112013-10-01NULL1NULL1
5623393693630 2013-11-01 2013-11-3012013-11-012013-10-01110
5623393693631 2013-12-01 2013-12-3112013-12-012013-11-01110
5623393693631 2014-01-01 2014-01-3112014-01-012013-12-01110
5897362369981631 2013-10-01 2013-10-3112013-10-01NULL1NULL1
5897362369981630 2013-11-01 2013-11-3002013-11-012013-10-0101-1
5897362369981631 2013-12-01 2013-12-3112013-12-012013-11-01101
8544862369981630 2013-11-01 2013-11-3002013-11-01NULL0NULL0
8544862369981631 2013-12-01 2013-12-3112013-12-012013-11-01101
8544862369981631 2014-01-01 2014-01-3112014-01-012013-12-01110
8560662369981630 2013-11-01 2013-11-3002013-11-01NULL0NULL0
8560662369981631 2013-12-01 2013-12-3112013-12-012013-11-01101
8560662369981631 2014-01-01 2014-01-3112014-01-012013-12-01110
as you can see that for APK=5 i have 4 rows, but for the rest of the row i m missing data for "2014-01-01". i want to include an additional row in the result set with the newly added row having data of its previous month. i.e. value in last month column and Last FTE column
February 10, 2014 at 8:32 am
If you would follow the instructions found under the link at the bottom of my signature, the answer could be more detailed...
Otherwise:
drive your select query from a set of the dates you want for example:
select d.d
,case when c1.datecolumn is null then c2.col1 else c1.col1 end col1
,case when c1.datecolumn is null then c2.col2 else c1.col2 end col2
...
from (VALUES ('20131001'),('20131101'),('20131201'),('20141201') ) d(d)
left join cte2 as c1
on c1.datecolumn = d.d
left join cte2 as c2
on dateadd(month,1,c2.datecolumn) = d.d
I hope you got the idea. However that work of only one mothly row is missing. To make it work for multiple rows missing, you need to do some extra work...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply