CODEWITHSUNDEEP
sql-server-2008date-range
MHeath
MHeath

Reputation: 81

Use Max date to create a date range

I need to create a date range in a table that houses transaction information. The table updates sporadically throughout the week from a manual process. Each time the table is updated transactions are added up to the previous Sunday. For instance, the upload took place yesterday and so transactions were loaded through last Sunday (Feb 26th). If it had been loaded on Wednesday it would still be dated for Sunday. The point is that I have a moving target with my transactions and also when the data is loaded to the table. I am trying to fix my look back period to the date of the latest transaction then go three weeks back. Here is the query that I came up with:

SELECT distinct TransactionDate 
FROM TransactionTABLE TB
 inner join (
    SELECT distinct top 21 TransactionDate FROM TrasactionTABLE ORDER BY TransactionDate desc
    ) A  on TB.TransactionDate = A.TransactionDate
ORDER BY TB.TransactionDate desc

Technically this code works. The problem that I am running into now is when there were no transactions on a given date, such as bank holidays (in this case Martin Luther King Day), then the query looks back one day too far.

I have tried a few different options including MAX(TransactionDate) but if I use that in a sub-query or CTE then use the new value in a WHERE statement as a reference I only get the max value or the value I subtract that statement by. For instance if I say WHERE TransactionDate >= MAX(TransactionDate)-21 and the max date is Feb 26th then the result is Feb 2nd instead of the range of dates from Feb 2nd through Feb 26th.

IN SUMMARY, what I need is a date range looking three weeks back from the date of the latest transaction date. This is for a daily report so I cannot hardcode the date in. Since I am also using Excel Connections the use of Declare statements is prohibited.

Thank you StackOverflow gurus in advance!

Upvotes: 1

Views: 156

Answers (2)

MHeath
MHeath

Reputation: 81

I just found this for looking up dates that fall within a given week. The code can be manipulated to change the week start date.

select convert(datetime,dateadd(dd,-datepart(dw,convert(datetime,convert(varchar(10),DateAdd(dd,-1/*this # changes the week start day*/,getdate()),101)))+1/*this # is used to change the week start date*/,
       convert(datetime,convert(varchar(10),getdate(),21))))/*also can enter # here to change the week start date*/

I've included a screenshot of the results if you were to include this with a full query. This way you can see how it looks with a range of dates. I did a little manipulation so that the week starts on Monday and references Monday's date.

Query Result

Since I am only looking back three weeks a simple GETDATE()-21 is sufficient because as the query moves forward through the week it will look back 21 days and pick the Monday at the beginning of the week as my start date.

Upvotes: 0

SqlZim
SqlZim

Reputation: 38063

You could use something like this:

;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (21) 
      [Date]=convert(date,dateadd(day, row_number() over (order by (select 1))-1
      , dateadd(day,-20,(select max(TransactionDate) from t) ) ) )
    from         n as deka
      cross join n as hecto
   order by [Date]
)

select Date=convert(varchar(10),dates.date,120) from dates

rextester demo: http://rextester.com/ZFYV25543

returns:

+------------+
|    Date    |
+------------+
| 2017-02-06 |
| 2017-02-07 |
| 2017-02-08 |
| 2017-02-09 |
| 2017-02-10 |
| 2017-02-11 |
| 2017-02-12 |
| 2017-02-13 |
| 2017-02-14 |
| 2017-02-15 |
| 2017-02-16 |
| 2017-02-17 |
| 2017-02-18 |
| 2017-02-19 |
| 2017-02-20 |
| 2017-02-21 |
| 2017-02-22 |
| 2017-02-23 |
| 2017-02-24 |
| 2017-02-25 |
| 2017-02-26 |
+------------+

Upvotes: 1

Related Questions