Count how many days ?

[SpookiePower]

I have an SQL that counts how many records there
is between hour 0 to 5, and groups them into days.
My SQL looks like this -

SELECT DateValue(daReportDate) AS Dato, Count(daReportDate) AS Antal
FROM TReport
WHERE hour(daReportDate) between 0 and 5
GROUP BY DateValue(daReportDate)
ORDER BY count(daReportDate) DESC;

Now I would like too know how many days a year, that have more
than one record, between hour 0 to 5. I think that I have
to change the GROUP and ORDER lines, but I'm not sure.

Can some one help me here ?

 

[SpookiePower]

I just got a bit closer to the right solution.

I have got the days, that have more than one record
between hour 0 to 5. But it shows the days, not counting
how many of them there are.

Can someone help ?


SELECT datevalue(daReportDate)
FROM TReport
WHERE (hour(daReportDate) between 0 and 5) and (year(daReportDate)=2007)
Group by datevalue(daReportDate)

 

[scubadiver]

If each day has its own record then you know how many days there are ;-)

 

[Dale Fye]

Spookie,

If you only want to see the ones from the query you wrote that have
Count(daReportDate) > 1 then you can add a HAVING clause to the end of the
query, it would look like:

SELECT DateValue(daReportDate) AS Dato, Count(daReportDate) AS Antal
FROM TReport
WHERE hour(daReportDate) between 0 and 5
GROUP BY DateValue(daReportDate)
ORDER BY count(daReportDate) DESC
HAVING count(daReportDate) > 1

IF all you want to know is the number of records like this, then try:

SELECT Count(T.DateValue)
FROM (
SELECT DateValue(daReportDate) AS Dato, Count(daReportDate) AS Antal
FROM TReport
WHERE hour(daReportDate) between 0 and 5
GROUP BY DateValue(daReportDate)) as T
WHERE T.Antal > 1

HTH
Dale

 

[John Spencer]

SELECT Count(*)
FROM TReport
WHERE DateValue(TReport.DaReportDate) in
(
SELECT DateValue(daReportDate) AS Dato
FROM TReport
WHERE hour(daReportDate) between 0 and 5
GROUP BY DateValue(daReportDate)
Having COunt(daReportDate) >=1)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[SpookiePower]

If each day has its own record then you know how many days there are ;-)

Each day, can have many records between the hour 0 to 5.
I can group them into days like this -

01-01-2007 12
02-02-2007 34
03-03-2007 22

But I don't want this list, I want the number 3. Because
there is 3 days, with more than one record.

 

[Dale Fye]

Spookie,

Try:

SELECT Count(T.*) as SomeFieldName
FROM (
SELECT datevalue(daReportDate), Count(daReportDate) as RecCount
FROM TReport
WHERE (hour(daReportDate) between 0 and 5) and (year(daReportDate)=2007)
Group by datevalue(daReportDate)
) as T
WHERE RecCount > 1

HTH
Dale