Grouping by Time

[Pat Backowski]

Hi Everyone,

SQL Server table 1 - user, product, date/time
Desired Access ouput - User, product, Count(for one hour), date,
hour_widget_was_made(09:00:00 or 10:00:00 etc)

I need to extract from SQL Server and create summary entries in Access that
show how many widgets a user made in any given hour.

Doing a nightly pull of yesterday's production, I can't see how to create
hourly summary entries without running 24 seperate queries.

Can anyone help, please.
Thank you in advance for your kind assistance.
Pat.

 

[Stefan Hoffmann]

hi Pat,

Doing a nightly pull of yesterday's production, I can't see how to create
hourly summary entries without running 24 seperate queries.

This should give you a clue:

SELECT Hour([yourDateTimeField]), ..
FROM [yourTable]
GROUP BY Hour([yourDateTimeField])



mfG
--> stefan <--

 

[Pat Backowski]

Thanks, Stefan. I assume here that "Hour" is a reserved word and utilises the
hour portion of the date/time field?
Thanks Again,
Pat.

hi Pat,

Doing a nightly pull of yesterday's production, I can't see how to create
hourly summary entries without running 24 seperate queries.

This should give you a clue:

SELECT Hour([yourDateTimeField]), ..
FROM [yourTable]
GROUP BY Hour([yourDateTimeField])



mfG
--> stefan <--

 

[Michael Gramelspacher]

Hi Everyone,

SQL Server table 1 - user, product, date/time
Desired Access ouput - User, product, Count(for one hour), date,
hour_widget_was_made(09:00:00 or 10:00:00 etc)

I need to extract from SQL Server and create summary entries in Access that
show how many widgets a user made in any given hour.

Doing a nightly pull of yesterday's production, I can't see how to create
hourly summary entries without running 24 seperate queries.

Can anyone help, please.
Thank you in advance for your kind assistance.
Pat.

another possibilty:

SELECT T.user, T.product,DATEADD("n", (DATEDIFF("n", 0,
T.production_date)\60)*60, 0) AS [Date Hour],
Count (*) AS [Hourly Production]
FROM Table1 AS T
GROUP BY T.user, T.product, (DATEDIFF("n", 0, T.production_date)\60)*60;