none
not display the weekend RRS feed

  • Question

  • Hi,

    I have the report send out to display the due date as below and not show the weekend.  For example, today's report I have 

    1= Late,

    2= Today,

    3= Tomorrow,

    4=Tomorrow+1,

    5=Tomorrow+2

    6= Tomorrow +3

    7= Tomorrow +4

    but mine logic is displayed 6/29 instead of 7/1.  I am not sure what i have wrong , please help. Thanks

    declare @testdate date
    set @testdate = '06/25/2019'
    select '1' sortGroupBy --late
          ,'' dt
     
      union all
    select '2' sortGroupBy--due today
          ,convert(varchar, @testdate,101) dt
         
     union all
    select '3' sortGroupBy--due tomorrow
          ,case when datename(dw,dateadd(d,1, @testdate))='Saturday' then convert(varchar,dateadd(d,3, @testdate),101)
                when datename(dw,dateadd(d,1, @testdate))='Sunday' then convert(varchar,dateadd(d,2, @testdate),101)
                else convert(varchar,dateadd(d,1, @testdate),101) end dt
     
     union all
     select '4' sortGroupBy--due tomorrow + 1
          ,case when datename(dw,dateadd(d,1, @testdate))='Saturday' then convert(varchar,dateadd(d,4, @testdate),101)
                when datename(dw,dateadd(d,1, @testdate))='Sunday' then convert(varchar,dateadd(d,3, @testdate),101)
                else convert(varchar,dateadd(d,2, @testdate),101) end dt
       
      union all
      select '5' sortGroupBy--due tomorrow + 2
          ,case when datename(dw,dateadd(d,1, @testdate))='Saturday' then convert(varchar,dateadd(d,5, @testdate),101)
                when datename(dw,dateadd(d,1, @testdate))='Sunday' then convert(varchar,dateadd(d,4, @testdate),101)
               else convert(varchar,dateadd(d,3, @testdate),101) end dt
     --     
     union all
      select '6' sortGroupBy--due tomorrow + 3
          ,case when datename(dw,dateadd(d,1, @testdate))='Saturday' then convert(varchar,dateadd(d,6, @testdate),101)
                when datename(dw,dateadd(d,1, @testdate))='Sunday' then convert(varchar,dateadd(d,5, @testdate),101)
                else convert(varchar,dateadd(d,4, @testdate),101) end dt

     union all
    select '7' sortGroupBy--due tomorrow + 4
          ,case when datename(dw,dateadd(d,1,@testdate))='Saturday' then convert(varchar,dateadd(d,7,@testdate),101)
                when datename(dw,dateadd(d,1,@testdate))='Sunday' then convert(varchar,dateadd(d,6,@testdate),101)
                else convert(varchar,dateadd(d,7,@testdate),101) end dt


    • Moved by ArthurZ Tuesday, June 25, 2019 5:14 PM Not SSIS related
    Tuesday, June 25, 2019 2:01 PM

All replies

  • You will find that it will be easier to deal with this if you have a Calendar table, see https://www.sqlshack.com/designing-a-calendar-table/ for some inspiration.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 25, 2019 10:13 PM
  • Your posting has a few basic problems. The first is that you don't know that ANSI/ISO standard SQL uses only "yyyy-mm-dd" for displaying dates. But you also missed the basic idea of SQL; it is a database language, not a report writer. You write a query in the database layer of a tiered architecture and pass the results set to a reporting layer. 

    SQL also has temporal data types. But you're using the old original Sybase convert functions to handle them as if they were strings. And your formatting your data in the database layer! That's how COBOL used to work, but not SQL. The best way to do this would probably be to build a calendar table. If you don't know what that is you can Google it as it is a standard programming technique and SQL in particular, pay attention to one of the alternate display formats allowed in the ISO standards. The weekly format uses "yyyyW[1-5][0-9]-[1-7]" where the first four digits are the usual year, the W is punctuation. The next two digits are the number of the week within the year (01 thru 52 or 53) the – is more punctuation and the date finally ends with the of the week (1 = Monday). You can find this material online and do a simple cut-and-paste to load it into your calendar. This will also let you make adjustments for three day weekends.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, June 25, 2019 11:41 PM
  • Hi Vicki2017,

     

    Thank you for your issue.

     

    The following script might be simpler than your original script. Please try .

     
    ------the first day in a week change to  Monday  in my system 
    set datefirst 1
    go
    declare @testdate date= '06/25/2019'
    select sortGroupBy, 
    dateadd(dd,sortGroupBy-datepart(weekday,@testdate),@testdate) dt 
    from (values (1),(2),(3),(4),(5),(6),(7)) a(sortGroupBy)
    /*
    sortGroupBy dt
    ----------- ----------
    1           2019-06-24
    2           2019-06-25
    3           2019-06-26
    4           2019-06-27
    5           2019-06-28
    6           2019-06-29
    7           2019-06-30
    */
    
    select sortGroupBy, 
    case when sortGroupBy<datepart(weekday,@testdate) then null
    else dateadd(dd,sortGroupBy-datepart(weekday,@testdate),@testdate) end dt 
    from (values (1),(2),(3),(4),(5),(6),(7)) a(sortGroupBy)
    /*
    sortGroupBy dt
    ----------- ----------
    1           NULL
    2           2019-06-25
    3           2019-06-26
    4           2019-06-27
    5           2019-06-28
    6           2019-06-29
    7           2019-06-30
    */
    
    

     

    Hope it will help you .

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 26, 2019 7:34 AM
  • Hi Vicki2017,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 28, 2019 9:42 AM