Need Help Regarding SQL Query

27 Oct 2005 10:07 AM

Atif Iqbal

HI,

I've a database with 250,000+ records records are being logged in
realtime with 5 mins of interval.

Now what i want is to query the database and on the basis of some
calculations i want to display records of every hour total in a 24
hours day separetly.

I want the result in one query such As by just giving the date.

S.No     Total= SUM(a+B)     Hour (00:00 - 59:59)
---------------------------------------------------
1.       45221             1:00
2.       34994             2:00
....
and so on till the day end.

i cannot use stored procedures or TSQL.
If anyone can help me on this.

TIA

27 Oct 2005 10:41 AM

John Bell

Hi

You don't post DDL!

There are several ways to truncate a date to it's date part incluing using
the CONVERT function. You can also use a timeinterval table that will give
start/end times which you can join to:

SELECT CAST(FLOOR(CAST(logdate AS float)) AS datetime),
DATEPART(hour,logdate), SUM(logvalue)
FROM logtable
GROUP BY CAST(FLOOR(CAST(logdate AS float)) AS datetime),
DATEPART(hour,logdate)

Ifyou really can't not use Stored procedures or TSQL then you may want to
download this into excel and use the function available in that! I assume you
mean that you are not very familiar with TSQL and Stored Procedures.

John

Show quoteHide quote

"Atif Iqbal" wrote:

> HI,
>
> I've a database with 250,000+ records records are being logged in
> realtime with 5 mins of interval.
>
> Now what i want is to query the database and on the basis of some
> calculations i want to display records of every hour total in a 24
> hours day separetly.
>
> I want the result in one query such As by just giving the date.
>
> S.No     Total= SUM(a+B)     Hour (00:00 - 59:59)
> ---------------------------------------------------
>   1.       45221             1:00
>   2.       34994             2:00
> ....
> and so on till the day end.
>
> i cannot use stored procedures or TSQL.
> If anyone can help me on this.
>
> TIA
>
>

Thread options