Posted by: R Manimaran | July 4, 2008

Getting Missing Date Values in SQL Server

Getting Missing Dates With Values a Zero

In some table we will insert values for the daily dates. In some cases will omit the values if a date should not have any value. Afterwards while taking the report we want all the dates and values as 0 for the missing month. For here we can use the temp table and get our need.

Example:

–Off the Intermediate information message

Set nocount on

–Declare a Table which contains the values for Dates

Declare @Table table(Dayid DateTime,Valuefld int)

–Insert values into the table

insert into @Table values(‘1-Jan-2008’,30)

insert into @Table values(‘3-Jan-2008’,53)

insert into @Table values(‘5-Jan-2008’,70)

insert into @Table values(‘8-Jan-2008’,81)

insert into @Table values(’12-Jan-2008′,22)

insert into @Table values(’16-Jan-2008′,30)

insert into @Table values(’19-Jan-2008′,34)

insert into @Table values(’22-Jan-2008′,64)

insert into @Table values(’27-Jan-2008′,66)

insert into @Table values(’31-Jan-2008′,52)

–Here comes the Query for getting the values

–Declare another table with colum id as dateTime data type

Declare @table_days table(id datetime)

Declare @day_index datetime

select @day_index=‘1-JAN-2008’

Declare @index int

Select @index=1

while @index <365

Begin

Insert into @table_days values(@day_index)

SELECT @day_index=DATEADD(day, 1, @day_index)

Select @index=@index+1

end

–As we are using DateAdd it will automatically count the dates for leap year and months

–Query for the Jan month only

Select CONVERT(VARCHAR(10),b.id,101),isnull(Valuefld,0) From @Table A Right outer join

@table_days B on A.Dayid=B.id where b.id <‘1-Feb-2008’

Output:

Missing Date with 0 as Value

Missing Date with 0 as Value

Advertisements

Responses

  1. hi

    i have a problem.
    i have a table named IOData.Fields are
    name ,id,date ,gatename.
    i have to find absent names,dates excluding weekends for all employees for a month group by name. anyone pls help me

    regards
    sarika

  2. I think your table is not a normalized one. Is there any separate table for Employees.. If yes, then IOData table should not contain name in it. just a reference to id in the employee table.
    After that you can easily get the required result.

  3. For your Question Try like this..
    First Create a Function which will return the WeekDate name i..e Sunday, Monday..

    Function:

    Create Function [dbo].[DayOfWeek](@dtDate Datetime)
    Returns Varchar(10)
    AS
    Begin
    Declare @rtDayofWeek varchar(10)
    Declare @weekDay INT
    Set @WeekDay=((DatePart(dw,@dtDate)+@@DateFIRST-7)%7)
    Select @rtDayofWeek=Case @weekDay
    when 0 then ‘Saturday’
    WHEN 1 THEN ‘Sunday’
    WHEN 2 THEN ‘Monday’
    WHEN 3 THEN ‘Tuesday’
    WHEN 4 THEN ‘Wednesday’
    WHEN 5 Then ‘Thursday’
    when 6 Then ‘Friday’

    End
    return(@rtDayofWeek)
    End

    Create a Temp Table with Column
    EntryDate DateTime
    DayName Varchar(12)

    Populate the Table with date values as follows
    Declare @day_index datetime
    Select @day_index=’1-Jan-2008′
    Declare @index int
    select @index=1
    while @index < 365
    begin
    insert into dbo.TempDatetbl
    Select @day_index, dbo.DayOfWeek(@day_index)
    SELECT @day_index=DATEADD(day, 1, @day_index)
    Select @index=@index+1
    end

    The above statements will insert the date values and corresponding day value.

    Normalize your IOData table to contain id,Entrydate,gatename

    Create a Table for Employee as id and name as Columns

    Now Query using the all the three tables to get the output(Emp Table,IOData Table and TempDatetbl)

  4. We are building a query to count the number of events per hour, per day. Most days there are hours that do not have any activity and therefore where the query is run the count of activities per hour show up but there are gaps and the query excludes these. We still want to show the hours that do not have activity and display a zero so that zero value can then be charted. The query we using looks like this …

    select datepart(Year, dev_time) as Year,
    datepart(Month, dev_time) as Month,
    datepart(Day, dev_time) as Day,
    datepart(Hour, dev_time) as Hour,
    count(tdm_msg) as Total_Incoming_Vehicles
    from TCKT_ACT
    where tdm_msg = ‘4162’ and dev_time >= DATEADD(day, – 1, GETDATE())
    group by datepart(Year, dev_time) ,
    datepart(Month, dev_time) ,
    datepart(Day, dev_time),
    datepart(Hour, dev_time)
    order by datepart(Year, dev_time) asc,
    datepart(Month, dev_time) asc,
    datepart(Day, dev_time) asc,
    datepart(Hour, dev_time) asc

  5. […] – bookmarked by 4 members originally found by susannakuenzl on 2008-12-30 Getting Missing Date Values in SQL Server https://rmanimaran.wordpress.com/2008/07/04/getting-missing-date-values-in-sql-server/ – bookmarked […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: