Display Null values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Display Null values

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-05-28 : 21:53:48

Hi,

I have the below query which is displaying the counts only for the dates which has data. I want to display the date and 0's if there is no data on that day. The query is getting counts of errors and successes for the past 5 days. I am using SQL Server 2005.

Thanks,
Petronas.

---QUERY
select code,
description,
sum(Counts)Counts,
Date,
[Type],
Error_Type

from
(
select code,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end [site_description],
isnull (count(*),0)counts,
convert(varchar(11),date,101)date,
'Error' as [Type],
case when code in ('12','13') then 'First Error'
when code in ('-1','-12') then 'SEcond Error'
when code in ('7501','7506') then 'Third Error'
else 'Other Error' end error_type
from error_log (nolock)
where section_id in ('AB02', 'CY02')
and convert(varchar(11),date,101)> convert(datetime,convert(varchar(11),getdate()-6,101))
and convert(varchar(11),date,101)< convert(datetime,convert(varchar(11),getdate(),101))
group by code,
convert(varchar(11),date,101),
case when code in ('12','13') then 'First Error'
when code in ('-1','-12') then 'SEcond Error'
when code in ('7501','7506') then 'Third Error'
else 'Other Error' end,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end

union all

select code,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end [site_description],
isnull (count(*),0)counts,
convert(varchar(11),date,101)date,
'Error' as [Type],
'Success' as [Type],
'Successes' error_type
from error_log (nolock)
where
section_id in ('AB01','CY01')
and
convert(varchar(11),date,101)> convert(datetime,convert(varchar(11),getdate()-6,101))
and convert(varchar(11),date,101)< convert(datetime,convert(varchar(11),getdate(),101))
group by code,convert(varchar(11),date,101),
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end
)a

group by code,date,
[type],
error_type,description

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-29 : 00:42:28

quote:
Originally posted by Petronas

Hi,

I have the below query which is displaying the counts only for the dates which has data. I want to display the date and 0's if there is no data on that day. The query is getting counts of errors and successes for the past 5 days. I am using SQL Server 2005.

Thanks,
Petronas.

---QUERY
select code,
description,
sum(Counts)Counts,
Date,
[Type],
Error_Type

from
(
select code,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end [site_description],
isnull (count(*),0)counts,
convert(varchar(11),date,101)date,
'Error' as [Type],
case when code in ('12','13') then 'First Error'
when code in ('-1','-12') then 'SEcond Error'
when code in ('7501','7506') then 'Third Error'
else 'Other Error' end error_type
from error_log (nolock)
where section_id in ('AB02', 'CY02')
and convert(varchar(11),date,101)> convert(datetime,convert(varchar(11),getdate()-6,101))
and convert(varchar(11),date,101)< convert(datetime,convert(varchar(11),getdate(),101))
group by code,
convert(varchar(11),date,101),
case when code in ('12','13') then 'First Error'
when code in ('-1','-12') then 'SEcond Error'
when code in ('7501','7506') then 'Third Error'
else 'Other Error' end,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end

union all

select code,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end [site_description],
isnull (count(*),0)counts,
convert(varchar(11),date,101)date,
'Error' as [Type],
'Success' as [Type],
'Successes' error_type--3columns
from error_log (nolock)
where
section_id in ('AB01','CY01')
and
convert(varchar(11),date,101)> convert(datetime,convert(varchar(11),getdate()-6,101))
and convert(varchar(11),date,101)< convert(datetime,convert(varchar(11),getdate(),101))
group by code,convert(varchar(11),date,101),
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end
)a

group by code,date,
[type],
error_type,description

while using union all u should have equal number of columns and same datatype check it once. post some sample data and expected output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-29 : 14:22:29

what you need to have is a master table with all dates if you want to show dates with 0 count for which no data exist. you could generate a calendar table if you dont have one.

Petronas
Posting Yak Master

134 Posts

Posted - 2009-06-01 : 10:17:20

Hi,

I tried joining to the calendar table. But I am still not getting 0's for the dates when there is no data.

QUERY:
select code,
description,
sum(Counts)Counts,
Date,
[Type],
Error_Type

from
(
select code,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end [site_description],
isnull (count(*),0)counts,
convert(varchar(11),date,101)date,
'Error' as [Type],
case when code in ('12','13') then 'First Error'
when code in ('-1','-12') then 'SEcond Error'
when code in ('7501','7506') then 'Third Error'
else 'Other Error' end error_type
from error_log e (nolock)
left outer join calendar c (nolock) on e.date=c.calendar_date
where section_id in ('AB02', 'CY02')
and convert(varchar(11),date,101)> convert(datetime,convert(varchar(11),getdate()-6,101))
and convert(varchar(11),date,101)< convert(datetime,convert(varchar(11),getdate(),101))
group by code,
convert(varchar(11),date,101),
case when code in ('12','13') then 'First Error'
when code in ('-1','-12') then 'SEcond Error'
when code in ('7501','7506') then 'Third Error'
else 'Other Error' end,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end

union all

select code,
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end [site_description],
isnull (count(*),0)counts,
convert(varchar(11),date,101)date,
'Error' as [Type],
'Success' as [Type],
'Successes' error_type
from error_log e (nolock)
left outer join calendar c (nolock) on e.date=c.calendar_date
where
section_id in ('AB01','CY01')
and
convert(varchar(11),date,101)> convert(datetime,convert(varchar(11),getdate()-6,101))
and convert(varchar(11),date,101)< convert(datetime,convert(varchar(11),getdate(),101))
group by code,convert(varchar(11),date,101),
case when code ='WAH' then 'WAH Site 1'
when code ='ALE' then 'ALE Site 2'
else 'Other' end
)a

group by code,date,
[type],
error_type,description

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 13:02:44

it should be other way round.
ie.

SELECT c.columns,ISNULL(e.code,0),...
FROM calendar c
LEFT JOIN error_log e
...

Petronas
Posting Yak Master

134 Posts

Posted - 2009-06-02 : 14:40:32

Thanks Visakh16,

As usual thank you for your time and help!

Appreciate it,
Petronas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources