getting null values

Hi,
I want to build up a table with which hours my site is being watched, i have
the data in a table and i can get it out. But what I get is just the hours
that it has been watched and for the hours that haven't been watched I would
like to get 0.
Example
0 0
1 5
2 0
3 0
4 7
5 5
6 8
7 9
8 11
9 12
10 14
11 18
12 19
13 22
14 44
15 22
16 44
17 11
18 7
19 9
20 4
21 3
22 5
23 2
FrCreate a number table
Do a left join and use COALESCE or ISNULL
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Frederik Vanderhaeghe wrote:
> Hi,
> I want to build up a table with which hours my site is being watched, i h=
ave
> the data in a table and i can get it out. But what I get is just the hours
> that it has been watched and for the hours that haven't been watched I wo=
uld
> like to get 0.
> Example
> 0 0
> 1 5
> 2 0
> 3 0
> 4 7
> 5 5
> 6 8
> 7 9
> 8 11
> 9 12
> 10 14
> 11 18
> 12 19
> 13 22
> 14 44
> 15 22
> 16 44
> 17 11
> 18 7
> 19 9
> 20 4
> 21 3
> 22 5
> 23 2
>=20
> Fr=E9|||My query now is:
SELECT COUNT(Site) AS Aantal, DATEPART(year, Datum) AS Jaar,
DATEPART(hour, Datum) AS uur, Connectie
FROM TBL_Stat_Sites
WHERE (DATEPART(year, Datum) = @.year) AND (Site = @.site) AND
(DATEPART(month, Datum) = @.maand) AND (Connectie = @.connectie)
GROUP BY Site, Datum, Connectie
ORDER BY DATEPART(month, Datum), DATEPART(hour, Datum)
What do I need to change when I add a table TBL_Uren?
Fr
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149082222.494751.130590@.c74g2000cwc.googlegroups.com...
Create a number table
Do a left join and use COALESCE or ISNULL
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Frederik Vanderhaeghe wrote:
> Hi,
> I want to build up a table with which hours my site is being watched, i
> have
> the data in a table and i can get it out. But what I get is just the hours
> that it has been watched and for the hours that haven't been watched I
> would
> like to get 0.
> Example
> 0 0
> 1 5
> 2 0
> 3 0
> 4 7
> 5 5
> 6 8
> 7 9
> 8 11
> 9 12
> 10 14
> 11 18
> 12 19
> 13 22
> 14 44
> 15 22
> 16 44
> 17 11
> 18 7
> 19 9
> 20 4
> 21 3
> 22 5
> 23 2
> Fr|||My query now is:
SELECT COUNT(Site) AS Aantal, DATEPART(year, Datum) AS Jaar,
DATEPART(hour, Datum) AS uur, Connectie
FROM TBL_Stat_Sites
WHERE (DATEPART(year, Datum) = @.year) AND (Site = @.site) AND
(DATEPART(month, Datum) = @.maand) AND (Connectie = @.connectie)
GROUP BY Site, Datum, Connectie
ORDER BY DATEPART(month, Datum), DATEPART(hour, Datum)
What do I need to change when I add a table TBL_Uren?
Fr
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149082222.494751.130590@.c74g2000cwc.googlegroups.com...
Create a number table
Do a left join and use COALESCE or ISNULL
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Frederik Vanderhaeghe wrote:
> Hi,
> I want to build up a table with which hours my site is being watched, i
> have
> the data in a table and i can get it out. But what I get is just the hours
> that it has been watched and for the hours that haven't been watched I
> would
> like to get 0.
> Example
> 0 0
> 1 5
> 2 0
> 3 0
> 4 7
> 5 5
> 6 8
> 7 9
> 8 11
> 9 12
> 10 14
> 11 18
> 12 19
> 13 22
> 14 44
> 15 22
> 16 44
> 17 11
> 18 7
> 19 9
> 20 4
> 21 3
> 22 5
> 23 2
> Fr|||How and why to create a numbers table:
http://www.aspfaq.com/show.asp?id=2516
Try this approach after creating the table. Not that I changed your group
by. You will need to adjust it to get the desired results.
SELECT COUNT(Stat.Site) AS Aantal
, DATEPART(year, Stat.Datum) AS Jaar
, DATEPART(hour, Stat.Datum) AS uur
, Stat.Connectie
FROM TBL_Stat_Sites as Stat
left outer join numbers
on numbers.number = DATEPART(hour, Stat.Datum)
WHERE DATEPART(year, Stat.Datum) = @.year)
AND Stat.Site = @.site)
AND DATEPART(month, Stat.Datum) = @.maand)
AND Stat.Connectie = @.connectie)
GROUP BY Stat.Site
, DATEPART(year, Stat.Datum)
, DATEPART(hour, Stat.Datum)
, Stat.Connectie
ORDER BY DATEPART(month, Stat.Datum)
, DATEPART(hour, Stat.Datum)
"Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
news:OFJr%23tLhGHA.3496@.TK2MSFTNGP02.phx.gbl...
> My query now is:
> SELECT COUNT(Site) AS Aantal, DATEPART(year, Datum) AS Jaar,
> DATEPART(hour, Datum) AS uur, Connectie
> FROM TBL_Stat_Sites
> WHERE (DATEPART(year, Datum) = @.year) AND (Site = @.site) AND
> (DATEPART(month, Datum) = @.maand) AND (Connectie = @.connectie)
> GROUP BY Site, Datum, Connectie
> ORDER BY DATEPART(month, Datum), DATEPART(hour, Datum)
> What do I need to change when I add a table TBL_Uren?
> Fr
> "SQL Menace" <denis.gobo@.gmail.com> wrote in message
> news:1149082222.494751.130590@.c74g2000cwc.googlegroups.com...
> Create a number table
> Do a left join and use COALESCE or ISNULL
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Frederik Vanderhaeghe wrote:
hours
>|||I already have my number table it's named TBL_Uren and has one field: Uur
How can I use the coalesce function in my sql commando?
Fr
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ujL$V$LhGHA.4304@.TK2MSFTNGP05.phx.gbl...
> How and why to create a numbers table:
> http://www.aspfaq.com/show.asp?id=2516
> Try this approach after creating the table. Not that I changed your group
> by. You will need to adjust it to get the desired results.
> SELECT COUNT(Stat.Site) AS Aantal
> , DATEPART(year, Stat.Datum) AS Jaar
> , DATEPART(hour, Stat.Datum) AS uur
> , Stat.Connectie
> FROM TBL_Stat_Sites as Stat
> left outer join numbers
> on numbers.number = DATEPART(hour, Stat.Datum)
> WHERE DATEPART(year, Stat.Datum) = @.year)
> AND Stat.Site = @.site)
> AND DATEPART(month, Stat.Datum) = @.maand)
> AND Stat.Connectie = @.connectie)
> GROUP BY Stat.Site
> , DATEPART(year, Stat.Datum)
> , DATEPART(hour, Stat.Datum)
> , Stat.Connectie
> ORDER BY DATEPART(month, Stat.Datum)
> , DATEPART(hour, Stat.Datum)
> "Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
> news:OFJr%23tLhGHA.3496@.TK2MSFTNGP02.phx.gbl...
> hours
>|||Best to look it up in BOL.
Select coalesce(Column1,Column2,Column3, 'NoValue' )
or
Select coalesce(column1,0)
Coalesce will give you the first non-null value in the list. How you use it
in your results depends on what you want. If you post DDL and inserts with
sample data, along with the desired results, we can try yo provide some code
that was tested.
http://www.aspfaq.com/etiquette.asp?id=5006
"Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
news:e4Q2CHMhGHA.3376@.TK2MSFTNGP03.phx.gbl...
> I already have my number table it's named TBL_Uren and has one field: Uur
> How can I use the coalesce function in my sql commando?
> Fr
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:ujL$V$LhGHA.4304@.TK2MSFTNGP05.phx.gbl...
group
message
i
I
>|||underprocessable|||Unfortunately, I cannot access the attachment as an .RAR file type. If you
can attach it as a .TXT or an .SQL file I can access it without having to
change my virus scan settings.
"Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
news:uruhclVhGHA.1264@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In the attachment you will find the scripts, data and results.
> Could you test for me?
> When should I expect an answer?
> yours sincerely,
> Fr
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OlTuKnMhGHA.4776@.TK2MSFTNGP05.phx.gbl...
use
message
Uur
the
watched
>
>|||underprocessable