SQL End of Quarter Date Diff and filter

Question

I have a data set with repeating date values within a quarter, I'd like to select the closest date to the quarter end.

Existing data

The output I'm tryting to acheive would look like this

select
created_at,
account_id,                                                               
account_name,
Score, 
DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, d.Timestamp) + 1, 0)) 
                    AS 'quarterend'
From xyz.database

I'm not sure what the best way is to filter out the unneeded values, had thought about a datediff caluation after creating a quarte end date and choose min score in a having or where clause subselect but could not get that to work. Thanks

Answer 1

ROW_NUMBER can order the account_id's by the datediff between timestamp and quarter end date. Then you just need to select the first row.

with
difs as
(
    select  created_at
          , account_id
          , account_name
          , Score
          , dateadd(d, -1, dateadd(q, datediff(q, 0, d.Timestamp) + 1, 0))                             as 'quarterend'
          , datediff(day, d.Timestamp, dateadd(d, -1, dateadd(q, datediff(q, 0, d.Timestamp) + 1, 0))) as 'quarterenddif'
    from    MyTable
)
select  *
from    (
    select  *
          , row_number() over (partition by quarterend, account_id order by quarterenddif desc) rn
    from    difs
) t
where   rn = 1