locked
Generate Week Calender Data using SQL Query Only RRS feed

  • Question

  • User-1100884601 posted

    Hi 

    I want to generate Table for WeekDays like below sample  . Table will be generated based 1 Employee table and Date Parameter 

    Employee Table  ID, Name 

    So here in below example 2 rows of data because employee table have 2 rows and Date column is dynamic whatever i pass From Date and To Date.  If anyone done this earlier 

    please help me with sql script.   Thanks in advance. 

    16-03-2020 17-03-2020 18-03-2020 19-03-2020 20-03-2020 21-03-2020 22-03-2020
     Udal  GEN GEN GEN GEN GEN GEN GEN
     Rakesh GEN GEN GEN GEN GEN GEN GEN
    Wednesday, March 18, 2020 8:16 AM

Answers

  • User-2082239438 posted
    --------------------------------TABLE SAMPLE--------------------------------
    Create Table #Table1
    (
    	ID INT,
    	NAME VARCHAR(20)
    )
    insert into #Table1
    Select 1,'yrb'
    Union All
    Select 2,'yogi'
    
    --------------------------------TABLE SAMPLE--------------------------------
    
    --------------------------------------------------QUERY--------------------------------------------------
    
    DECLARE @SqlQuery nVARCHAR(MAX)=''
    DECLARE @Date VARCHAR(MAX)=''
    
    DECLARE @FromDate DATETIME='2020-03-16'
    DECLARE @ToDate DATETIME='2020-03-23'
    
    
    ;WITH CTE AS
    (
    	SELECT @FromDate AS FF,@ToDate AS TT
    
    	UNION ALL
    
    	SELECT DATEADD(DAY,1,CTE.FF) AS FF,TT
    	FROM CTE
    	WHERE DATEADD(DAY,1,CTE.FF)<@ToDate
    ),CTE1 AS
    (
    	SELECT DISTINCT '['+Convert(Char(10),FF,101)+']' As Date
    	FROM CTE
    )
    SELECT @Date=@Date+','+Date
    FROM CTE1
    
    SET @Date=substring(@Date,2,len(@Date))
    
    
    --------------------------------------------------QUERY--------------------------------------------------
    
    SET @SqlQuery =';WITH CTE AS
    (
    	SELECT @FromDate AS FF,@ToDate AS TT
    
    	UNION ALL
    
    	SELECT DATEADD(DAY,1,CTE.FF) AS FF,TT
    	FROM CTE
    	WHERE DATEADD(DAY,1,CTE.FF)<@ToDate
    )
    SELECT *
    FROM
    (
    	SELECT CTE.FF,AA.NAME,''GEN'' AS ID
    	FROM CTE
    	CROSS JOIN #Table1 AS AA
    ) AS BB
    PIVOT 
    (
    	MAX(ID) FOR FF IN ('+@Date+') 
    ) AS pvt '
    
    --PRINT @SqlQuery
    
    EXEC sp_executesql @SqlQuery, N'@FromDate DATETIME, @ToDate DATETIME',@FromDate,@ToDate
    
    --EXECUTE @SqlQuery
    
    DROP TABLE #Table1
    
    ------------------OUTPUT------------------
    --NAME	03/16/2020	03/17/2020	03/18/2020	03/19/2020	03/20/2020	03/21/2020	03/22/2020
    --yogi	GEN			GEN			GEN			GEN			GEN			GEN			GEN
    --yrb	GEN			GEN			GEN			GEN			GEN			GEN			GEN
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 18, 2020 11:18 AM

All replies

  • User-2082239438 posted
    --------------------------------TABLE SAMPLE--------------------------------
    Create Table #Table1
    (
    	ID INT,
    	NAME VARCHAR(20)
    )
    insert into #Table1
    Select 1,'yrb'
    Union All
    Select 2,'yogi'
    
    --------------------------------TABLE SAMPLE--------------------------------
    
    --------------------------------------------------QUERY--------------------------------------------------
    
    DECLARE @SqlQuery nVARCHAR(MAX)=''
    DECLARE @Date VARCHAR(MAX)=''
    
    DECLARE @FromDate DATETIME='2020-03-16'
    DECLARE @ToDate DATETIME='2020-03-23'
    
    
    ;WITH CTE AS
    (
    	SELECT @FromDate AS FF,@ToDate AS TT
    
    	UNION ALL
    
    	SELECT DATEADD(DAY,1,CTE.FF) AS FF,TT
    	FROM CTE
    	WHERE DATEADD(DAY,1,CTE.FF)<@ToDate
    ),CTE1 AS
    (
    	SELECT DISTINCT '['+Convert(Char(10),FF,101)+']' As Date
    	FROM CTE
    )
    SELECT @Date=@Date+','+Date
    FROM CTE1
    
    SET @Date=substring(@Date,2,len(@Date))
    
    
    --------------------------------------------------QUERY--------------------------------------------------
    
    SET @SqlQuery =';WITH CTE AS
    (
    	SELECT @FromDate AS FF,@ToDate AS TT
    
    	UNION ALL
    
    	SELECT DATEADD(DAY,1,CTE.FF) AS FF,TT
    	FROM CTE
    	WHERE DATEADD(DAY,1,CTE.FF)<@ToDate
    )
    SELECT *
    FROM
    (
    	SELECT CTE.FF,AA.NAME,''GEN'' AS ID
    	FROM CTE
    	CROSS JOIN #Table1 AS AA
    ) AS BB
    PIVOT 
    (
    	MAX(ID) FOR FF IN ('+@Date+') 
    ) AS pvt '
    
    --PRINT @SqlQuery
    
    EXEC sp_executesql @SqlQuery, N'@FromDate DATETIME, @ToDate DATETIME',@FromDate,@ToDate
    
    --EXECUTE @SqlQuery
    
    DROP TABLE #Table1
    
    ------------------OUTPUT------------------
    --NAME	03/16/2020	03/17/2020	03/18/2020	03/19/2020	03/20/2020	03/21/2020	03/22/2020
    --yogi	GEN			GEN			GEN			GEN			GEN			GEN			GEN
    --yrb	GEN			GEN			GEN			GEN			GEN			GEN			GEN
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 18, 2020 11:18 AM
  • User288213138 posted

    Hi udal_cse,

    So here in below example 2 rows of data because employee table have 2 rows and Date column is dynamic whatever i pass From Date and To Date.  If anyone done this earlier 

    16-03-2020 17-03-2020 18-03-2020 19-03-2020 20-03-2020 21-03-2020 22-03-2020
     Udal  GEN GEN GEN GEN GEN GEN GEN
     Rakesh GEN GEN GEN GEN GEN GEN GEN

    Can you tell me how these two rows of data come from? 

    What is Udal and GEN?

    Best regards,

    Sam

    Thursday, March 19, 2020 3:10 AM
  • User-1100884601 posted

    Thanks for your help. It worked great for me. 

    Friday, March 20, 2020 3:08 PM