SQLTeam.com | Weblogs | Forums

Column showing how many days since last date of service

Hello- this sql is working but I would like to add a derived column which would give us how many days since the last day of service:
;WITH
cte_ul_ev AS (
SELECT DISTINCT
ev.full_name,
ev.event_name,
ev.actual_date,
ev.service_provider_name,
row_num = ROW_NUMBER() OVER (PARTITION BY ev.full_name ORDER BY ev.actual_date DESC) --<<--<<--
FROM
dbo.event_expanded_view ev
WHERE
ev.full_name IS NOT NULL
AND ev.category_code IN ('OTHER_ACT', 'CONTACTS', 'PEOPLEPLANS', 'PEOPLETESTS', 'PERSONREQ')
)
SELECT
ue.full_name,
ue.event_name,
ue.actual_date,
ue.service_provider_name
FROM
cte_ul_ev ue
WHERE
ue.row_num = 1;

so whats the issue ?? :slight_smile:

select datediff(day, actual_date, getdate()) as actual_date

So which of the columns that you've portrayed in your code contains the date that you want to compare against?

the actual_date agains the current date so we would have a column on each row with that number, be it 1, 50 , 100 etc.

How do I refer to the day calculation?
WITH
cte_ul_ev AS (
SELECT DISTINCT
ev.full_name,
ev.event_name,
ev.actual_date,
ev.service_provider_name,
datediff(day, actual_date, getdate())
row_num = ROW_NUMBER() OVER (PARTITION BY ev.full_name ORDER BY ev.actual_date DESC) --<<--<<--
FROM
dbo.event_expanded_view ev
WHERE
ev.full_name IS NOT NULL
AND ev.category_code IN ('OTHER_ACT', 'CONTACTS', 'PEOPLEPLANS', 'PEOPLETESTS', 'PERSONREQ')
)
SELECT
ue.full_name,
ue.event_name,
ue.actual_date,
ue.service_provider_name

FROM
cte_ul_ev ue
WHERE