CODEWITHSUNDEEP
sqlsql-servert-sql
BIReportGuy
BIReportGuy

Reputation: 817

TSQL only pull data if another date field is the previous business day

1) I'm need only to pull data if the the openddate is equal to the PrevBiz date. I think the where/and statement would be Openddate = PrevBiz, but not sure. It wasn't working for me and could be because the date format isn't matching. Any Suggestions?

   DECLARE @TODAY       DATE    = GETDATE()
    DECLARE @PREVFIRST  CHAR(8) = CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0), 112)
DECLARE @PREVLAST   CHAR(8) = CONVERT(CHAR(8), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1), 112) 
        DECLARE @PREVBIZ    CHAR(12) = DATEADD(DAY, CASE DATENAME(WEEKDAY, CONVERT(CHAR(12), @TODAY,112))
                            WHEN 'SUNDAY' THEN -2
                            WHEN 'MONDAY' THEN -3
                            ELSE -1 END, DATEDIFF(DAY, 0, CONVERT(CHAR(12), @TODAY, 112)))

          SELECT TOP 10
          CURRENTDATE       =@TODAY, 
          FIRST_OF_MONTH    =@PREVFIRST,
          LASTDAY_OFMONTH   =@PREVLAST,
          PREVBIZ           =@PREVBIZ,

           DATEADD(DAY, CASE DATENAME(WEEKDAY, CONVERT(DATE, @TODAY,101))
            WHEN 'SUNDAY' THEN -2
            WHEN 'MONDAY' THEN -3
            ELSE -1 END, DATEDIFF(DAY, 0, CONVERT(DATE, @TODAY, 101))) AS PREVIOUSBIZDATE,

          OpendDate 

          FROM [USBI_DW].[USBI].[vw_NameAddressBase]
          where IsCurrent = 1

Here's my results: enter image description here

Upvotes: 0

Views: 39

Answers (1)

DimaSUN
DimaSUN

Reputation: 921

declare @TODAY datetime   = convert(date,GETDATE())
declare @PREVBIZ   datetime =  DATEADD(DAY, CASE DATENAME(WEEKDAY,@TODAY)
                            WHEN 'SUNDAY' THEN -2
                            WHEN 'MONDAY' THEN -3
                            ELSE -1 END,@TODAY)
declare @iToday int = convert(nvarchar(8),@TODAY, 112)
      , @iPrevBiz int = convert(nvarchar(8),@PREVBIZ, 112)

select top 10
      CURRENTDATE       =@iToday,
      PREVBIZ           =@iPrevBiz,
      OpendDate 

from [USBI_DW].[USBI].[vw_NameAddressBase]
where IsCurrent = 1
and OprendDate = @iPrevBiz

hope your view contains int date attribute ( because of DateWarehouse specific)

Upvotes: 2

Related Questions