none
MIssing Dates RRS feed

  • Question

  • Thanks to all for your replays. These solution are very helpfull for me. And i would like to ask one more thing, insted of insert data inti signin table..
    I alredy have the signin table with large date so that how can i use that existing table. because i need to gereate report in the web site .any one could help me on this. 


    Select * from Signintable
    I will get the below data

    No Emp_Name Date    In Time   
    1 Praveen -  5/4/2009 6:08 AM  
    2 Praveen -  5/5/2009 9:41 PM 
    3 Praveen -  5/6/2009 9:46 PM  
    4 Praveen -  5/7/2009 9:59 PM  
    5 Praveen -  5/8/2009 1:45 PM  
    6 Praveen -  5/11/2009 1:39 PM  
    7 Praveen -  5/12/2009 1:50 PM  

    But I want to dispaly missing dates like 5/1/2009,5/2/2009 and In time column should be null or empty.

    The final Result  should be like this.

    No Emp_Name Date    In Time  
    1 Praveen -  5/1/2009 ---   
    2 Praveen -  5/2/2009 ---   
    3 Praveen -  5/3/2009 ----   
    4 Praveen -  5/4/2009 6:08 AM   
    5 Praveen -  5/5/2009 9:41 PM  
    6 Praveen -  5/6/2009 9:46 PM   
    7 Praveen -  5/7/2009 9:59 PM   
    8 Praveen -  5/8/2009 1:45 PM   
    9 Praveen -  5/9/2009 ---   
    10. Praveen -  5/10/2009 ---
    11 Praveen -  5/11/2009 1:39 PM  
    12 Praveen -  5/12/2009 1:50 PM
    Any one Could you please help on this.
    • Edited by karadhyula Thursday, June 18, 2009 12:33 PM
    Wednesday, June 17, 2009 10:26 AM

Answers


  • I think you will need to use a calendar table to solve your issue.

    I spotted this link in another post... you should find it helpful

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    Regards


    Jon Royales
    Wednesday, June 17, 2009 10:54 AM
  • The calendar table is a good option (probably the best option if you can be allowed to create a calendar table).  If you cannot create a calendar table another alternative is to use either a table of numbers or a numbers generator.  Again, if you cannot create a calendar table it is likely you cannot create a table of numbers and you will need to use a numbers generator.  For example:

     

    declare @signin table

    ( No integer,

      Emp_Name varchar(10),

      Date datetime)

    insert into @signin 

    select 1, 'Praveen', '5/4/2009 6:08 AM' union all

    select 2, 'Praveen', '5/5/2009 9:41 PM' union all

    select 3, 'Praveen', '5/6/2009 9:46 PM' union all

    select 4, 'Praveen', '5/7/2009 9:59 PM' union all

    select 5, 'Praveen', '5/8/2009 1:45 PM' union all

    select 6, 'Praveen', '5/11/2009 1:39 PM' union all

    select 7, 'Praveen', '5/12/2009 1:50 PM'

     

    declare @start datetime   set @start = '5/1/9'

    declare @end datetime     set @end = '5/12/9'

     

    ;WITH -- CTE Borrowed from many others

    L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows

    L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows

    L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows

    L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows

    L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows

    L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows

    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)

    select

      N as No,

      max(isnull(Emp_Name, '')) over() as Emp_Name,

      DATEADD(DAY, N - 1, @start) as Date,

      isnull(CONVERT(varchar(20), Date, 108), '---') as 'In Time'

    from Numbers

    left join @signin

      on Date >= DATEADD(DAY, N - 1, @start)

     and Date <  DATEADD(DAY, N, @start)

    where N <= DATEDIFF(DAY, @start, @end) + 1

     

    /* -------- Sample Output: --------

    No                   Emp_Name   Date                    In Time

    -------------------- ---------- ----------------------- --------------------

    1                    Praveen    2009-05-01 00:00:00.000 ---

    2                    Praveen    2009-05-02 00:00:00.000 ---

    3                    Praveen    2009-05-03 00:00:00.000 ---

    4                    Praveen    2009-05-04 00:00:00.000 06:08:00

    5                    Praveen    2009-05-05 00:00:00.000 21:41:00

    6                    Praveen    2009-05-06 00:00:00.000 21:46:00

    7                    Praveen    2009-05-07 00:00:00.000 21:59:00

    8                    Praveen    2009-05-08 00:00:00.000 13:45:00

    9                    Praveen    2009-05-09 00:00:00.000 ---

    10                   Praveen    2009-05-10 00:00:00.000 ---

    11                   Praveen    2009-05-11 00:00:00.000 13:39:00

    12                   Praveen    2009-05-12 00:00:00.000 13:50:00

    */







    Kent Waldrop
    Wednesday, June 17, 2009 11:05 AM
  • Hi all,

    Here is a solution which benefits from the script that I used for creating a numbers table using t-sql code on SQL Server.
    Using the script at numbers table, I build a dates table and with a left join we can filter the missing dates

    WITH CTE_DatesTable 
    AS 
    (
    	SELECT CAST('20090501' as datetime) AS i
    	UNION ALL
    	SELECT DATEADD(dd, 1, i) FROM CTE_DatesTable WHERE DATEADD(dd, 1, i) <= '20090531'
    )
    SELECT * 
    FROM CTE_DatesTable
    LEFT JOIN Signintable ON CTE_DatesTable.i = Signintable.date
    WHERE Signintable.date is null


    Eralper
    SQL Server and T-SQL Solutions
    http://www.kodyaz.com http://www.eralper.com
    Wednesday, June 17, 2009 1:14 PM
    Moderator
  • Trying my hand at this. I doubt it's the best answer, but i wanted to post what i came up with:

    WITH
        Signintable([No], Emp_Name, Date, [In Time])
    AS
        (
         SELECT 1, 'Praveen -', '5/4/2009',        '6:08 AM' UNION ALL
         SELECT 2, 'Praveen -', '5/5/2009',        '9:41 PM' UNION ALL
         SELECT 3, 'Praveen -', '5/6/2009',        '9:46 PM' UNION ALL
         SELECT 4, 'Praveen -', '5/7/2009',        '9:59 PM' UNION ALL
         SELECT 5, 'Praveen -', '5/8/2009',        '1:45 PM' UNION ALL
         SELECT 6, 'Praveen -', '5/11/2009',    '1:39 PM' UNION ALL
         SELECT 7, 'Praveen -', '5/12/2009',    '1:50 PM'
        ),
        Emp_Start_Finish
    AS
        (
         SELECT
                Emp_Name,
                DATEDIFF
                (
                 day,
                 0,
                 DATEADD
                 (
                  MONTH,
                  DATEDIFF
                  (
                   MONTH,
                   0,
                   MAX(Date)
                  ),
                  0
                 )
                )                                    Date_Min,
                DATEDIFF
                (
                 day,
                 0,
                 MAX(CONVERT(DATETIME, Date, 101))
                )                                    Date_Max
         FROM
                Signintable
         GROUP BY
                Emp_Name
        ),
        Emp_Date
    AS
        (
         SELECT
                Emp_Start_Finish.Emp_Name,
                Emp_Start_Finish.Date_Min,
                Emp_Start_Finish.Date_Max,
                Emp_Start_Finish.Date_Min            N,
                CONVERT
                (
                 DATETIME,
                 Emp_Start_Finish.Date_Min,
                 101
                )                                    N_Date
         FROM
                Emp_Start_Finish
         UNION ALL
         SELECT
                Emp_Name,
                Date_Min,
                Date_Max,
                N + 1,
                DATEADD(DAY, 1, Emp_Date.N_Date)    N_Date
         FROM
                Emp_Date
         WHERE
                N < Date_Max
        )
    SELECT
            Emp_Date.N - Emp_Date.Date_Min    + 1        [No],
            Emp_Date.Emp_Name,
            Emp_Date.N_Date                            Date,
            ISNULL(Signintable.[In Time], '---')    [In Time]
    FROM
            Emp_Date
    LEFT OUTER JOIN
            Signintable
    ON
            Emp_Date.Emp_Name        = Signintable.Emp_Name
      AND    Emp_Date.N_Date            = CONVERT(DATETIME, Signintable.Date, 101);
    

    Wednesday, June 17, 2009 1:17 PM
    Moderator

All replies


  • I think you will need to use a calendar table to solve your issue.

    I spotted this link in another post... you should find it helpful

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    Regards


    Jon Royales
    Wednesday, June 17, 2009 10:54 AM
  • The calendar table is a good option (probably the best option if you can be allowed to create a calendar table).  If you cannot create a calendar table another alternative is to use either a table of numbers or a numbers generator.  Again, if you cannot create a calendar table it is likely you cannot create a table of numbers and you will need to use a numbers generator.  For example:

     

    declare @signin table

    ( No integer,

      Emp_Name varchar(10),

      Date datetime)

    insert into @signin 

    select 1, 'Praveen', '5/4/2009 6:08 AM' union all

    select 2, 'Praveen', '5/5/2009 9:41 PM' union all

    select 3, 'Praveen', '5/6/2009 9:46 PM' union all

    select 4, 'Praveen', '5/7/2009 9:59 PM' union all

    select 5, 'Praveen', '5/8/2009 1:45 PM' union all

    select 6, 'Praveen', '5/11/2009 1:39 PM' union all

    select 7, 'Praveen', '5/12/2009 1:50 PM'

     

    declare @start datetime   set @start = '5/1/9'

    declare @end datetime     set @end = '5/12/9'

     

    ;WITH -- CTE Borrowed from many others

    L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows

    L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows

    L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows

    L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows

    L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows

    L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows

    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)

    select

      N as No,

      max(isnull(Emp_Name, '')) over() as Emp_Name,

      DATEADD(DAY, N - 1, @start) as Date,

      isnull(CONVERT(varchar(20), Date, 108), '---') as 'In Time'

    from Numbers

    left join @signin

      on Date >= DATEADD(DAY, N - 1, @start)

     and Date <  DATEADD(DAY, N, @start)

    where N <= DATEDIFF(DAY, @start, @end) + 1

     

    /* -------- Sample Output: --------

    No                   Emp_Name   Date                    In Time

    -------------------- ---------- ----------------------- --------------------

    1                    Praveen    2009-05-01 00:00:00.000 ---

    2                    Praveen    2009-05-02 00:00:00.000 ---

    3                    Praveen    2009-05-03 00:00:00.000 ---

    4                    Praveen    2009-05-04 00:00:00.000 06:08:00

    5                    Praveen    2009-05-05 00:00:00.000 21:41:00

    6                    Praveen    2009-05-06 00:00:00.000 21:46:00

    7                    Praveen    2009-05-07 00:00:00.000 21:59:00

    8                    Praveen    2009-05-08 00:00:00.000 13:45:00

    9                    Praveen    2009-05-09 00:00:00.000 ---

    10                   Praveen    2009-05-10 00:00:00.000 ---

    11                   Praveen    2009-05-11 00:00:00.000 13:39:00

    12                   Praveen    2009-05-12 00:00:00.000 13:50:00

    */







    Kent Waldrop
    Wednesday, June 17, 2009 11:05 AM
  • Hi all,

    Here is a solution which benefits from the script that I used for creating a numbers table using t-sql code on SQL Server.
    Using the script at numbers table, I build a dates table and with a left join we can filter the missing dates

    WITH CTE_DatesTable 
    AS 
    (
    	SELECT CAST('20090501' as datetime) AS i
    	UNION ALL
    	SELECT DATEADD(dd, 1, i) FROM CTE_DatesTable WHERE DATEADD(dd, 1, i) <= '20090531'
    )
    SELECT * 
    FROM CTE_DatesTable
    LEFT JOIN Signintable ON CTE_DatesTable.i = Signintable.date
    WHERE Signintable.date is null


    Eralper
    SQL Server and T-SQL Solutions
    http://www.kodyaz.com http://www.eralper.com
    Wednesday, June 17, 2009 1:14 PM
    Moderator
  • Trying my hand at this. I doubt it's the best answer, but i wanted to post what i came up with:

    WITH
        Signintable([No], Emp_Name, Date, [In Time])
    AS
        (
         SELECT 1, 'Praveen -', '5/4/2009',        '6:08 AM' UNION ALL
         SELECT 2, 'Praveen -', '5/5/2009',        '9:41 PM' UNION ALL
         SELECT 3, 'Praveen -', '5/6/2009',        '9:46 PM' UNION ALL
         SELECT 4, 'Praveen -', '5/7/2009',        '9:59 PM' UNION ALL
         SELECT 5, 'Praveen -', '5/8/2009',        '1:45 PM' UNION ALL
         SELECT 6, 'Praveen -', '5/11/2009',    '1:39 PM' UNION ALL
         SELECT 7, 'Praveen -', '5/12/2009',    '1:50 PM'
        ),
        Emp_Start_Finish
    AS
        (
         SELECT
                Emp_Name,
                DATEDIFF
                (
                 day,
                 0,
                 DATEADD
                 (
                  MONTH,
                  DATEDIFF
                  (
                   MONTH,
                   0,
                   MAX(Date)
                  ),
                  0
                 )
                )                                    Date_Min,
                DATEDIFF
                (
                 day,
                 0,
                 MAX(CONVERT(DATETIME, Date, 101))
                )                                    Date_Max
         FROM
                Signintable
         GROUP BY
                Emp_Name
        ),
        Emp_Date
    AS
        (
         SELECT
                Emp_Start_Finish.Emp_Name,
                Emp_Start_Finish.Date_Min,
                Emp_Start_Finish.Date_Max,
                Emp_Start_Finish.Date_Min            N,
                CONVERT
                (
                 DATETIME,
                 Emp_Start_Finish.Date_Min,
                 101
                )                                    N_Date
         FROM
                Emp_Start_Finish
         UNION ALL
         SELECT
                Emp_Name,
                Date_Min,
                Date_Max,
                N + 1,
                DATEADD(DAY, 1, Emp_Date.N_Date)    N_Date
         FROM
                Emp_Date
         WHERE
                N < Date_Max
        )
    SELECT
            Emp_Date.N - Emp_Date.Date_Min    + 1        [No],
            Emp_Date.Emp_Name,
            Emp_Date.N_Date                            Date,
            ISNULL(Signintable.[In Time], '---')    [In Time]
    FROM
            Emp_Date
    LEFT OUTER JOIN
            Signintable
    ON
            Emp_Date.Emp_Name        = Signintable.Emp_Name
      AND    Emp_Date.N_Date            = CONVERT(DATETIME, Signintable.Date, 101);
    

    Wednesday, June 17, 2009 1:17 PM
    Moderator