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
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
By: sarika on July 8, 2008
at 4:37 am
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.
By: R Manimaran on July 8, 2008
at 12:14 pm
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)
By: R Manimaran on July 8, 2008
at 3:03 pm
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
By: Fred Gordy on December 17, 2008
at 2:22 am
[...] – bookmarked by 4 members originally found by susannakuenzl on 2008-12-30 Getting Missing Date Values in SQL Server http://rmanimaran.wordpress.com/2008/07/04/getting-missing-date-values-in-sql-server/ – bookmarked [...]
By: Bookmarks about Missing on January 26, 2009
at 5:15 am