0

We got this to work well, but I want to show a column that will have the days since the last actual_date I don't know how to code 'day' to be an output column.

WITH
    cte_ul_ev AS (
        SELECT 
            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
    ue.row_num = 1;
Pas Palter
  • 3
  • 2
  • *"I am not sure where to put this"* In your `SELECT`. What are you *actually* asking here? *Also the semicolon (`;`) is a statement **terminator**, not a "beginningator". It only goes at the end of* all *your statements, not the start of them.* – Thom A Oct 12 '20 at 15:55
  • Also, the `DISTINCT` isn't needed here, as you are using `ROW_NUMBER` to eliminate the other rows. The `DISTINCT` will simply add unnecessary overhead and should be removed. But i already [told you this](https://stackoverflow.com/questions/64318734/looking-to-show-the-latest-datetime-column#comment113735327_64318734). – Thom A Oct 12 '20 at 15:56
  • sorry it is removed. – Pas Palter Oct 12 '20 at 15:59
  • The leading ; is clearly a pet peeve for @Larnu (we've all got them). It does no harm. Take a peek at https://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon – John Cappelletti Oct 12 '20 at 16:06
  • 1
    @JohnCappelletti is right, it does do *no harm*, and it is a [pet peeve](https://wp.larnu.uk/fundamentals-the-semicolon-is-a-statement-terminator/) of mine but it does teach bad habits in my opinion. Especially as it does result in people seeming to think that *all* `WITH` clauses start with a semicolon. – Thom A Oct 12 '20 at 16:10
  • @Larnu Didn't know you had this site. Well done! I'll be watching for more content. – John Cappelletti Oct 12 '20 at 16:18
  • @JohnCappelletti It's not been updated in awhile; COVID moved my attention elsewhere (I need to get back to it though). – Thom A Oct 12 '20 at 17:50

1 Answers1

0

you just missing a comma and , and wrong way of aliasing the column and seesm like distinct is ans extra thing you are doing

;WITH
    cte_ul_ev AS (
        SELECT 
            ev.full_name,
            ev.event_name,
            ev.actual_date,
            ev.service_provider_name,
            datediff(day, actual_date, getdate()) as DaysDiff,
            ROW_NUMBER() OVER (PARTITION BY ev.full_name ORDER BY ev.actual_date DESC)  as row_num  --<<--<<--
        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.
    ue.DaysDiff
    
FROM
    cte_ul_ev ue
WHERE
    ue.row_num = 1;
eshirvana
  • 23,227
  • 3
  • 22
  • 38