How to query last 2 business days only

Question

I'm running a query that pulls the correct information I'm looking for, but I need it to pull the last 2 business days rather than the last 2 days. This comes into play when it's Monday and my results show information for Monday and Sunday rather than Monday and Friday. How can I change my query to pull in business days only?

USE [LetterGeneration]

SELECT  g.LetterGenerationPrintJobId
      ,CAST(t.[TemplateKey] AS VarChar) AS LetterCode
      ,convert(char(12),r.CreatedDate,101) AS CreatedDate
      ,s.LetterGenerationStatusId AS Status
      ,s.StatusKey AS StatusDesc
      ,count(g.LetterGenerationId) as LetterCount
      ,c.BankingDateYorN

  FROM [LetterGenerationTemplateRequest] AS r

  INNER JOIN [LetterGenerationTemplate] AS t
  ON t.[LetterGenerationTemplateId] = r.LetterGenerationTemplateId
  INNER JOIN LetterGeneration g
  ON g.LetterGenerationTemplateRequestId = r.LetterGenerationTemplateRequestId
  INNER JOIN LetterGenerationStatus s 
  ON g.LetterGenerationStatusId = s.LetterGenerationStatusId
  INNER JOIN Enterprise..Calendar C
  ON c.BeginDate = g.LetterDate

WHERE ((DATEDIFF(d, r.CreatedDate, GETDATE()) = 0) OR (DATEDIFF(d, r.CreatedDate, GETDATE()) = 1))
--BankingDateYorN = 1
--AND RelativeTimeValue_BusinessDates =-1
AND t.[TemplateKey] NOT LIKE '%PLTV1%' 
AND s.LetterGenerationStatusId NOT LIKE '4'
AND s.LetterGenerationStatusId NOT LIKE '16'
AND s.LetterGenerationStatusId NOT LIKE '19'
AND s.LetterGenerationStatusId NOT LIKE '20'
AND s.LetterGenerationStatusId NOT LIKE '38'


GROUP BY r.[LetterGenerationTemplateRequestId]
      ,r.LetterGenerationTemplateId
      ,g.Lettergenerationprintjobid
      ,t.[TemplateKey]
      ,r.[Loan_no]
      ,r.CreatedDate
      ,r.[CreatedBy]
      ,s.LetterGenerationStatusId
      ,s.StatusKey
      ,c.BankingDateYorN


  ORDER BY r.CreatedDate DESC

UPDATE: I've recently discovered how to join a calendar table to my current query. The calendar query has a column called BusinessDayYorN with 1's for a business day and 0's for weekends and holidays. I've also updated the old query to now include the join.

Here's a good thread on an idea http://stackoverflow.com/a/9923016/6167855 — S3S, Apr 18 '17 at 16:37
Business day meaning a Monday-Friday date. And I'll check out that thread and see if I can use it. — D. Morley, Apr 18 '17 at 16:52

access_granted · Accepted Answer · 2017-04-20T19:13:14.263

1

select * 
from LetterGenerationTemplateRequest
where createddate >= (
  getdate() -
  case datename(dw,getdate())
  when 'Tuesday' then 5
  when 'Monday' then 4
  else 3
  end 
)  
--and datename(dw,createdDate) not in ('Saturday','Sunday',datename(dw,getdate()))
and datename(dw,createdDate) not in ('Saturday','Sunday')


;

edited Apr 20 '17 at 19:13

answered Apr 18 '17 at 23:52

access_granted

1,807
20
25

I added this to my where clause and it is pulling the correct information for yesterday but won't show any information for today. – D. Morley Apr 20 '17 at 16:10
Oh, I excluded it on purpose. modify your lower where clause as the edit I just did. – access_granted Apr 20 '17 at 19:12
Just tested it out and it's looking like it's working. Thanks for the help! – D. Morley Apr 20 '17 at 19:48

score 0 · Answer 2 · answered Apr 18 '17 at 17:09

Assuming that you always want to include the last two non-weekend days you can try this:

; with aux as (
    select diff = case 
        when datename(weekday, getdate()) in ('Tuesday', 'Wednesday ', 'Thursday', 'Friday') then 1 
        else 
            case datename(weekday, getdate()) 
                when 'Saturday' then 2
                when 'Sunday' then 3
                when 'Monday' then 4
            end
    end
)

SELECT --r.[LetterGenerationTemplateRequestId]
      --,r.LetterGenerationTemplateId
      g.LetterGenerationPrintJobId
      ,CAST(t.[TemplateKey] AS VarChar) AS LetterCode
      ,r.[Loan_no]
      ,convert(char(12),r.CreatedDate,101) AS CreatedDate
     -- ,g.ModifiedDate
     -- ,convert(varchar(18), g.ModifiedDate - r.CreatedDate, 108) AS TimeSpan
      ,s.LetterGenerationStatusId AS Status
      ,s.StatusKey AS StatusDesc
      ,count(g.LetterGenerationId) as LetterCount

  FROM [LetterGenerationTemplateRequest] AS r

  INNER JOIN [LetterGenerationTemplate] AS t
  ON t.[LetterGenerationTemplateId] = r.LetterGenerationTemplateId
  INNER JOIN LetterGeneration g
  ON g.LetterGenerationTemplateRequestId = r.LetterGenerationTemplateRequestId
  INNER JOIN LetterGenerationStatus s 
  ON g.LetterGenerationStatusId = s.LetterGenerationStatusId
WHERE 
    DATEDIFF(day, r.CreatedDate, GETDATE()) <= (select diff from aux)
    AND t.[TemplateKey] NOT LIKE '%PLTV1%' 
    AND s.LetterGenerationStatusId NOT LIKE '4'
    AND s.LetterGenerationStatusId NOT LIKE '16'
    AND s.LetterGenerationStatusId NOT LIKE '19'
    AND s.LetterGenerationStatusId NOT LIKE '20'
    AND s.LetterGenerationStatusId NOT LIKE '38'  
GROUP BY r.[LetterGenerationTemplateRequestId]
      ,r.LetterGenerationTemplateId
      ,g.Lettergenerationprintjobid
      ,t.[TemplateKey]
      ,r.[Loan_no]
      ,r.CreatedDate
     -- ,g.ModifiedDate
      ,r.[CreatedBy]
      ,s.LetterGenerationStatusId
      ,s.StatusKey
  ORDER BY r.CreatedDate DESC

The CTE aux returns a dataset with only one record and only one field, the value of which is the number of days you need to go back in your WHERE statement.

I've copied this exact code into SQL and I get the error code "Invalid object name 'LetterGenerationTemplateRequest'. I see that in your code you have a comment to (select diff from aux), do I need to do something there? Excuse my naivete. — D. Morley, Apr 20 '17 at 16:13
LetterGenerationTemplateRequest is an object from your original code, so you are the one to answer this question I am afraid... If you want you can set up a rextester demo (www.rextester.com), script the tables there, and run the code again. If it does return the same error save it and post the link so we can study it further. — Giorgos Altanis, Apr 20 '17 at 17:28

How to query last 2 business days only

2 Answers2