Query help

J

jpBless

TimeEntered
Amt

55.44
6.25am

60.99
6.26am

77.99
6.20am

40.99
7.10am

100.20
7.50am

100.50
8.00am

120.35
8.25am

100.00
8.56am

120.25
8.59am

100.55
10.00am

20.22
10.41am




I have a table as above... I would like to make a query to return records as
below (SQL Query to return 2 fields (time range and total))... I will
appreciate help/pointers



TimeEntered Amt

6am-7.59AM 335.61

8am-9.59AM 441.11

10Am-11.59 120.77
 
J

jpBless

Lost some formating in my post... apologozie... More like

TimeEntered Amount

55.44 6.20am

60.99 6.25am

70.99 6.26AM

40.99 7.10AM

100.20 7.50AM

100.50 8.00AM

120.35 8.25AM

100.00 8.56AM

120.25 8.59AM

100.55 10.00AM

20.22 10.41AM



SQL Query to return 2 fields (time range and total)

TimeEntered Amt

6am-7.59AM 335.61

8am-9.59AM 441.11

10Am-11.59 120.77
 
P

Plamen Ratchev

It is best to store time values as DATETIME data type (or in SQL Server
2008 as TIME). Alternatively it can be stored as minutes from midnight.

Otherwise you end up with unpleasant conversions. Here is one way to get
the results you need:

CREATE TABLE Foo (
amount DECIMAL(6, 2),
time_entered VARCHAR(10) PRIMARY KEY);

INSERT INTO Foo VALUES (55.44, '6.20AM');
INSERT INTO Foo VALUES (60.99, '6.25AM');
INSERT INTO Foo VALUES (70.99, '6.26AM');
INSERT INTO Foo VALUES (40.99, '7.10AM');
INSERT INTO Foo VALUES (100.20, '7.50AM');
INSERT INTO Foo VALUES (100.50, '8.00AM');
INSERT INTO Foo VALUES (120.35, '8.25AM');
INSERT INTO Foo VALUES (100.00, '8.56AM');
INSERT INTO Foo VALUES (120.25, '8.59AM');
INSERT INTO Foo VALUES (100.55, '10.00AM');
INSERT INTO Foo VALUES (20.22, '10.41AM');

SELECT CAST((DATEDIFF(MI, '19000101', time_entered2) / 120) * 2
AS VARCHAR(10)) +
'.00 ' + RIGHT(time_entered, 2) + ' - ' +
CAST((DATEDIFF(MI, '19000101', time_entered2) / 120) * 2 + 1
AS VARCHAR(10)) +
'.59 ' + RIGHT(time_entered, 2) AS time_range,
SUM(amount) AS amt
FROM (
SELECT amount,
time_entered,
CAST(
STUFF(REPLACE(time_entered, '.', ':'),
LEN(time_entered) - 1, 0, ' ')
AS DATETIME) AS time_entered2
FROM Foo) AS F
GROUP BY CAST((DATEDIFF(MI, '19000101', time_entered2) / 120) * 2
AS VARCHAR(10)) +
'.00 ' + RIGHT(time_entered, 2) + ' - ' +
CAST((DATEDIFF(MI, '19000101', time_entered2) / 120) * 2 + 1
AS VARCHAR(10)) +
'.59 ' + RIGHT(time_entered, 2);

/*

Results:

time_range amt
----------------------------------- -------
10.00 AM - 11.59 AM 120.77
6.00 AM - 7.59 AM 328.61
8.00 AM - 9.59 AM 441.10

*/
 
J

jpBless

Actually the time was stored as long date... like 1/2/2009 7:22:38 PM Used
short time just for posting this. Anyway thanks for your input... I am
trying to figure it out. Thanks a lot
 
P

Plamen Ratchev

In that case you can remove the expression I used to convert the VARCHAR
to DATETIME and directly use the column you have:

SELECT CAST((time_diff / 120) * 2
AS VARCHAR(10)) +
':00 - ' +
CAST((time_diff / 120) * 2 + 1
AS VARCHAR(10)) +
':59' AS time_range,
SUM(amount) AS amt
FROM (
SELECT time_entered,
amount,
DATEDIFF(MI, DATEADD(DAY, 0,
DATEDIFF(DAY, 0, time_entered)),
time_entered) AS time_diff
FROM Foo) AS F
GROUP BY CAST((time_diff / 120) * 2
AS VARCHAR(10)) +
':00 - ' +
CAST((time_diff / 120) * 2 + 1
AS VARCHAR(10)) +
':59';


Note the derived table defines the column 'time_diff' which calculated
minutes since midnight. If you have multiple days you may want to group
by date using expression like DATEADD(DAY, 0, DATEDIFF(DAY, 0,
time_entered)).
 
Top