-1

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

enter image description here

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

enter image description here

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

Ricky
  • 123
  • 9

1 Answers1

0

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
JamieD77
  • 13,796
  • 1
  • 17
  • 27
  • Finding this solution drops all records accept the last quarter end transaction for an account, I need to save each quarter end transaction across years so if an account were listed across multiple years and quarters, the output would include 1 value for each year there was a transaction I.e. values for Q12019, Q12022 Q2202, Q42022. For the example data above account BAC should have 1 output for Q3 and 1 output for Q4 – Ricky Mar 10 '23 at 20:45
  • to get all quarters for an account, you just need to include the quarter end date and partition by that as well. Answer updated. – JamieD77 Mar 13 '23 at 13:28
  • OK that works if you change the order by statement to asc rather than desc – Ricky Mar 13 '23 at 16:21