Hi,
This is similar to a thread posted a couple of years ago, but this has a slightly
different aspect.

I have a table with the Columns [System (string) & TakenOn (Timestamp)]

I need to create a query that produces something similar to :

SnapDate   SnapHour           Count
06/16/09    08:00 AM            100
06/16/09    09:00 AM            23
06/16/09    10:00 AM            32
.
.
09/02/09    08:00 AM            45

etc.

I'm using the follwing

"select cast(TakenOn As DATE) as SnapDate, extract (hour from TakenOn) as SnapHour,
count(*) as Count from WpStats group by SnapDate, SnapHour";

and I get the date, but the hour is a single value e.g. "8" or "14" or "22" etc. Is there
a way to format the hour within the query so it appears like that in the report above?

Thanks,
Dar..

Dar

> I have a table with the Columns [System (string) & TakenOn (Timestamp)]
>
> I need to create a query that produces something similar to :
>
> SnapDate   SnapHour           Count
> 06/16/09    08:00 AM            100
> 06/16/09    09:00 AM            23
> 06/16/09    10:00 AM            32

How about

select cast(TakenOn as date) as snapdate, extract(hour from TakenOn) as
snaphour,count(*) as Count from Table
group by snapdate,snaphour

John

"John Hay" wrote:

Dar

> I have a table with the Columns [System (string) & TakenOn (Timestamp)]
>
> I need to create a query that produces something similar to :
>
> SnapDate   SnapHour           Count
> 06/16/09    08:00 AM            100
> 06/16/09    09:00 AM            23
> 06/16/09    10:00 AM            32

How about

select cast(TakenOn as date) as snapdate, extract(hour from TakenOn) as
snaphour,count(*) as Count from Table
group by snapdate,snaphour

John


Hi John,
Thanks a million for your reply. I had the same SQL as yourself, but what I was hoping to find was a way to format the hour into something
similar to "08:00" or "14:00". The SQL I had already (and yours included) returns the hour as "8" or "14".

Thanks for your help John.
Dar

Dar,

> but what I was hoping to find was a way to format the hour into
something
> similar to "08:00" or "14:00". The SQL I had already (and yours included) returns the hour as "8" or "14".

You can write a function that formats the hour into the desired way and
use this function within your sql.

Uli

Dar


Can there be several dates involved? What is the role of System in the report?

From what you've posted I'm guessing that you're grouping on System, Date, hour is that right?

Roy Lambert [Team Elevate]

Dar
> Hi John,
> Thanks a million for your reply. I had the same SQL as yourself, but what
I was hoping to find was a way to format the hour into something
> similar to "08:00" or "14:00". The SQL I had already (and yours included)
returns the hour as "8" or "14".
>

As Uli suggested a format function is the better way to go but for a quick
workaround replace extract(hour from TakenOn) to

cast(cast("TakenOn" as time) as char(2))+':00'

John

Roy Lambert wrote:

Dar


Can there be several dates involved? What is the role of System in the report?

From what you've posted I'm guessing that you're grouping on System, Date, hour is that right?

Roy Lambert [Team Elevate]

Thanks for your response Roy.
Yep, there are several dates invloved  and System is effectively a hostname or unique
identifier
Indeed, I'm grouping on System, Date & Hour.

Essentially I'll be taking statistics on each system and this table records the System the
stats were taken on and the date and I was looking for a SQL statement that would give me
a count of the stats by System, by Date and Hour. Something similar to what I posted
initially.

Thanks again Roy..
Dar..

Dar


OK so you've pretty much got it from John's post. But here's the other bit you asked for

CAST(CAST(EXTRACT(HOUR FROM TakenOn) AS VARCHAR(2))+':00' AS TIME) AS SnapHour


Roy Lambert [Team Elevate]

Roy,

> CAST(CAST(EXTRACT(HOUR FROM TakenOn) AS VARCHAR(2))+':00' AS TIME) AS SnapHour

which doesn't consider the format he wants: e.g. 02:00 p.m.

Thus a function will do the trick.

Uli

Uli

>which doesn't consider the format he wants: e.g. 02:00 p.m.

errr

<<format the hour into something
similar to "08:00" or "14:00".>>

But just to show willing

IF(
EXTRACT(HOUR FROM _Timestamp) < 10
THEN '0'+CAST(EXTRACT(HOUR FROM _Timestamp) AS VARCHAR(1))+':00 AM'
ELSE IF(EXTRACT(HOUR FROM _Timestamp) <=12
  THEN CAST(EXTRACT(HOUR FROM _Timestamp) AS VARCHAR(2))+':00 AM'
  ELSE IF(EXTRACT(HOUR FROM _Timestamp)-12 < 10
    THEN '0'+CAST(EXTRACT(HOUR FROM _Timestamp)-12 AS VARCHAR(1))+':00 PM'
    ELSE CAST(EXTRACT(HOUR FROM _Timestamp)-12 AS VARCHAR(2))+':00 PM')))
AS RightFormat

However, it won't sort/group correctly

Roy Lambert [Team Elevate]