We have requirement to check the Auto Parking Membership activity. When an individual becomes member the details will stored initially with MemberID, MemberShipDate, ActivityDate, ActivityMonthEnd, IsActive, ActiveYTD. This data will be stored into DW table. This is daily data which we are storing to DW.
The last field ‘ActiveYTD’ has little complex logic. The default value is ‘Y’, This field value depends on the ‘IsActive’ field. When IsActive = ‘N’ The ‘ActiveYTD’ field value also updated as ‘N’ from the day the IsActive field value changed from Y to N.
But there is a business rule this IsActive field can be Active and Inactive any number of times. I don’t know why that rule is like that.
Note:- If suppose the IsActive field value = ‘N’ on Feb 15 2022 and is inactive until April 20 2022. Certainly the ActivityYTD field will also change from Y to N based on above formula logic. But since the IsActive field changed to ‘Y’ on April 21 2022. The ActivityYTD will also change to ‘Y’. But the business definition is when the IsActive field changes to Y from N. The ActiveYTD field should never be ‘N’ in previous rows. It should be ‘Y’ for all rows from Feb 15 2022 to till date. So in a scenario if the IsActive value = ‘N’ from Feb 15 2022 till end of the year the ActiveYTD will also be ‘N’ starting Feb 15th 2022. Both ActivityDate and ActivityMonthEndDate are keys from a dimension table. (eg: - 20220215, 20220228) Based on the above business logic defined, I have created a query
Select mainData.MemberID, mainData.MembershipDate, mainData.ActivityDateKey, mainData.ActivityMonthEndDateKey, InactiveData.InactiveDate, mainData.IsActive,
Case When mainData.IsAcitve =’N’ and (mainData.ActivityDateKey/100 >= InactiveData.InactiveDate/100) and (mainData.ActivityDateKey/10000 = InactiveData.InactiveDate/10000) then ‘Y’ else ‘N’ end as AcitveYTD
From History_AutoParkingMemberShip mainData
Left Outer Join
(
Select MemberID, Max(ActivityDateKey) as InactiveDate
From AutoParkinigMembership
Where IsActive = ‘N’
Group by MemberID
) InactiveData on InactiveData.MemberID = MainData.MemberID
But this query is not 100% producing correct result. Because I need to also find if any situation when the IsActive value = ‘Y’ then the previous values wherever I have updated the ActiveYTD value ‘Y’ needed to update to ‘N’.
MemberID MemberShipDate ActivityDateKey ActivityMonthEndDateKEy InactiveDate, IsActive ActiveYTD
1001 2019/06/17 20220101 20220131 Null Y Y
1001 2019/06/17 20220102 20220131 Null Y Y
... .... ... ... ... ... ...
1001 2019/06/17 20220201 20220228 Null Y Y
1001 2019/06/17 20220215 20220228 20220420 N N
... .... ... ... ... ... ...
1001 2019/06/17 20220420 20220430 20220420 N N
1001 2019/06/17 20220421 20220430 Null Y Y
1001 2019/06/17 20220422 20220430 Null Y Y
Now you see the above data where ActiveYTD field should never had value = 'N' because this member 1001 become active again from April 21st 2022. This is little confusion. Could someone give your thoughts how to improve the query.
You can do that using Window Function.
The idea is to find the last IsActive value with 'Y' of that year sorted by ActivityDateKey.
Any row previous to that should have ActiveYTD as 'Y'. If there is any record with IsActive='N' after last 'Y', mark those as 'N'.
Below Window Function will give the Count of 'Y' present till the current row.
COUNT(CASE WHEN IsActive='Y' THEN 1 END)
OVER( PARTITION BY MemberID,ActivityDateKey/10000
ORDER BY ActivityDateKey Desc ROWS UNBOUNDED PRECEDING )
Final Query:
SELECT
tab.*
,CASE WHEN
COUNT(CASE WHEN IsActive='Y' THEN 1 END)
OVER( PARTITION BY MemberID,ActivityDateKey/10000
ORDER BY ActivityDateKey Desc ROWS UNBOUNDED PRECEDING ) >0 THEN 'Y' ELSE 'N' END ActiveYTD
FROM AutoParkinigMembership tab
ORDER BY ActivityDateKey
db<>fiddle: Try here
Note: Above fiddle is in sqlserver-2014, but it should work in 2012 as well.
Dataset:
| MemberID | MemberShipDate | ActivityDateKey | ActivityMonthEndDateKEy | IsActive |
|---|---|---|---|---|
| 1001 | 2019-06-17 | 20220102 | 20220131 | Y |
| 1001 | 2019-06-17 | 20220201 | 20220228 | Y |
| 1001 | 2019-06-17 | 20220215 | 20220228 | Y |
| 1001 | 2019-06-17 | 20220420 | 20220430 | N |
| 1001 | 2019-06-17 | 20220421 | 20220430 | Y |
| 1001 | 2019-06-17 | 20220422 | 20220430 | N |
Output:
| MemberID | MemberShipDate | ActivityDateKey | ActivityMonthEndDateKEy | IsActive | ActiveYTD |
|---|---|---|---|---|---|
| 1001 | 2019-06-17 | 20220102 | 20220131 | Y | Y |
| 1001 | 2019-06-17 | 20220201 | 20220228 | Y | Y |
| 1001 | 2019-06-17 | 20220215 | 20220228 | Y | Y |
| 1001 | 2019-06-17 | 20220420 | 20220430 | N | Y |
| 1001 | 2019-06-17 | 20220421 | 20220430 | Y | Y |
| 1001 | 2019-06-17 | 20220422 | 20220430 | N | N |