How we can add week days like Monday , Tuesday so on and time with it.

RXR 121 Reputation points
2020-10-01T22:21:44.717+00:00

This query shows week by week data collection, But how we can add week days like Monday , Tuesday so on and time with it.

Declare @beginDate int = 0;   
Declare @pendDate int = 1;  
  
CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15))  
While (@beginDate < 9 and @pendDate < 10)  
   BEGIN  
    INSERT INTO #Test  
    
          Select Datepart(wk,DATEADD(WEEK, -1 * @beginDate ,'2017-12-31')),Datepart(wk,DATEADD(WEEK, -1 * @pendDate ,'2017-12-31'))  
  
     
            SET @beginDate += 1  
 SET @pendDate +=1  
        END  
  
SELECT * FROM #Test  

29706-capture.gif

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,259 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-02T02:42:31.123+00:00

    Hi @RXR ,

    Please refer below which is mostly close to your expectation.

    drop table if exists  #Test  
      
    SET DATEFIRST 1 ;    
      
    Declare @beginDate int = 0;   
    Declare @pendDate int = 1;  
          
     CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15))  
     While (@beginDate < 7 and @pendDate < 8)  
        BEGIN  
         INSERT INTO #Test  
            
         Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
    	 Datepart(wk,DATEADD(day, -1 * @pendDate-6 ,'2017-12-31')),  
    	 DATENAME(DW,Dateadd(day,1 *@beginDate,'2017-12-31'));  
         
      SET @beginDate += 1  
      SET @pendDate +=1  
    END  
          
     SELECT * FROM #Test  
    

    Output:
    WeekStart WeekEnd WeekDay
    53 52 Sunday
    53 52 Monday
    53 52 Tuesday
    53 52 Wednesday
    53 52 Thursday
    53 52 Friday
    53 52 Saturday

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


3 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-02T02:18:48.093+00:00

    Hi @RXR ,

    Thanks for posting here.

    The output of your query is different from your desired output.

    WeekStart WeekEnd
    53 52
    52 51
    51 50
    50 49
    49 48
    48 47
    47 46
    46 45
    45 44

    Besides, what is the first day of one week in your requirement? Monday ,Sunday or other day?

    You could use below command to set the first day as you like .

    SET DATEFIRST { number | @number_var }     
      
    --For example   
    SET DATEFIRST 7; --Sunday is the first day of one week  
      
    SET DATEFIRST 3; --Wednesday is now considered the first day of the week  
    

    I updated your query and added one WeekDay column. I used the default first day of one week as Sunday.

    Please refer below and check whether it is helpful to you.

    drop table if exists  #Test  
      
    Declare @beginDate int = 0;   
    Declare @pendDate int = 1;  
          
     CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15))  
     While (@beginDate < 9 and @pendDate < 10)  
        BEGIN  
         INSERT INTO #Test  
            
         Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
      Datepart(wk,DATEADD(day, -1 * @pendDate ,'2017-12-31')),  
      DATENAME(DW,Dateadd(day,1*@beginDate,'2017-12-31'));  
         
      SET @beginDate += 1  
      SET @pendDate +=1  
    END  
          
     SELECT * FROM #Test  
    

    Output:
    WeekStart WeekEnd WeekDay
    53 52 Sunday
    52 52 Monday
    52 52 Tuesday
    52 52 Wednesday
    52 52 Thursday
    52 52 Friday
    52 52 Saturday
    52 51 Sunday
    51 51 Monday

    After checking, actually '2017-12-30' should be Saturday instead of Monday.

    Please also refer below:

    drop table if exists  #Test  
      
    Declare @beginDate int = 0;   
    Declare @pendDate int = 1;  
          
     CREATE TABLE #Test(WeekStart varchar(15), WeekEnd varchar(15),[WeekDay] varchar(15))  
     While (@beginDate < 9 and @pendDate < 10)  
        BEGIN  
         INSERT INTO #Test  
            
         Select Datepart(WK,DATEADD(day, -1 * @beginDate ,'2017-12-31')),  
      Datepart(wk,DATEADD(day, -1 * @pendDate ,'2017-12-31')),  
      DATENAME(DW,Dateadd(day,-1 *@beginDate,'2017-12-31'));  
         
      SET @beginDate += 1  
      SET @pendDate +=1  
    END  
          
     SELECT * FROM #Test  
    

    Output:
    WeekStart WeekEnd WeekDay
    53 52 Sunday
    52 52 Saturday
    52 52 Friday
    52 52 Thursday
    52 52 Wednesday
    52 52 Tuesday
    52 52 Monday
    52 51 Sunday
    51 51 Saturday

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Tom Phillips 17,706 Reputation points
    2020-10-02T16:18:32.167+00:00
    0 comments No comments

  3. Erland Sommarskog 98,901 Reputation points
    2020-10-02T21:53:35.95+00:00

    I can't say that I find your question crystal clear, but maybe this is what you are looking for:

         SELECT * FROM #Test
         CROSS JOIN (VALUES('Sunday'), ('Monday'), ('Tuesday'), ('Wednesday'), 
                           ('Thursday'), ('Friday'), ('Saturday'), ('Sunday')) AS V(Days)
    
    0 comments No comments