CODEWITHSUNDEEP
sqlsql-server
Ridwaan Shaik
Ridwaan Shaik

Reputation: 1

sql repeat rows for weekend and holidays

I have a table A that we import based on the day that it lands on a location. We dont receive files on weekend and public holidays, and the table has multiple countries data so the public holidays vary. In essence we looking to duplicate a row multiple times till it encounters the next record for that ID (unless its the max date for that ID). A typical record looks like this:

Account Datekey Balance
1   20181012    100
1   20181112    100
1   20181212    100
1   20181512    100
1   20181712    100

And needs to look like this (sat, sun & PH added to indicate the day of week):

Account Datekey Balance 
1   20181012    100 
1   20181112    100 
1   20181212    100 
1   20181312    100 Sat
1   20181412    100 Sun
1   20181512    100 
1   20181612    100 PH
1   20181712    100

Also Datekey is numeric and not a date. I tried a couple solutions suggested but found that it simply duplicates the previous row multiple times without stopping when the next dates record is found. I need to run it as an update query that would execute daily on table A and add missing records when its executed (sometimes 2 or 3 days later).

Hope you can assist.

Thanks

Upvotes: 0

Views: 537

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

This question has multiple parts:

  • Converting an obscene date format to a date
  • Generating "in-between" rows
  • Filling in the new rows with the previous value
  • Determining the day of the week

The following does most of this. I refuse to regenerate the datekey format. You really need to fix that.

This also assumes that your setting are for English week day names.

with t as (
      select Account, Datekey, Balance, convert(date, left(dkey, 4) + right(dkey, 2) + substring(dkey, 5, 2)) as proper_date
      from yourtable
     ),
     dates as (
      select account, min(proper_date) as dte, max(proper_date) as max_dte
      from t
      group by account
      union all
      select account, dateadd(day, 1, dte), max_dte
      from dates
      where dte < max_dte
     )
select d.account, d.dte, t.balance,
       (case when datename(weekday, d.dte) in ('Saturday', 'Sunday')
             then left(datename(weekday, d.dte), 3)
             else 'PH'
        end) as indicator
from dates d cross apply
     (select top (1) t.*
      from t
      where t.account = d.account and
            t.proper_date <= d.dte
      order by t.proper_date desc
     ) t
option (maxrecursion 0);

Upvotes: 4

Related Questions