CODEWITHSUNDEEP
sqlvisual-studiosql-server-2008
user3705485
user3705485

Reputation: 3

hourly sales report with time formatting

This is my code:

select 
    SYSTEM_TIME as OR_TIME, 
    SUM (AMOUNT) as HOURLY_SALES 
from 
    T_SALES_detail
where 
    OUTLET_NO = 20193 and SYSTEM_DATE  = '6/2/2014' and VOID = 'N' 
group by 
    SYSTEM_TIME

and the output is this:

OR_TIME   HOURLY_SALES
12:00   295
13:00   2122.86
14:00   2230
15:00   1800
16:00   3090
17:00   880
18:00   652.86

but the output I want to see is this:

OR_TIME             HOURLY_SALES
12:00pm-12:59pm     295
1:00pmpm-1:59       2122.86
2:00pm-2:59pm           2230
3:00pm-3:59pm       1800
4:00pm-4:59pm       3090
5:00pm-5:59pm       880
6:00pm-6:59pm       652.86

I'm using Visual Studio 2010 and SQL Server 2008.

Thank you so much.

Upvotes: 1

Views: 92

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

You can use DateAdd Function.

Try this

Select Cast(SYSTEM_TIME As Varchar(100))+ '-' + Cast(DATEADD(MINUTE, 59 , SYSTEM_TIME)As Varchar(100))  As OR_TIME, SUM (AMOUNT) as HOURLY_SALES 
From T_SALES_detail
Where OUTLET_NO = 20193 and SYSTEM_DATE  = '6/2/2014' and VOID = 'N' 
Group By SYSTEM_TIME

Fiddle Demo

O/P:

+-------------------+--------------------+
|    OR_TIME        |     HOURLY_SALES   |
+-------------------+--------------------+
| 12:00pm-12:59pm   |     295            |
| 1:00pm-1:59pm     |     2122.86        |
| 2:00pm-2:59pm     |     2230           |
| 3:00pm-3:59pm     |     1800           |
| 4:00pm-4:59pm     |     3090           |
| 5:00pm-5:59pm     |     880            |
| 6:00pm-6:59pm     |     652.86         |
+-------------------+--------------------+

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

You can use DATEADD to get the end range, and use CONVERT to convert the time to 12 hour time;

SELECT CONVERT(VARCHAR(7), SYSTEM_TIME, 100) + '-' +
       CONVERT(VARCHAR(7), DATEADD(s, 3599, SYSTEM_TIME), 100) OR_TIME,
       SUM (AMOUNT) as HOURLY_SALES from T_SALES_detail
WHERE OUTLET_NO = 20193 and SYSTEM_DATE  = '6/2/2014' and VOID = 'N' 
GROUP BY SYSTEM_TIME

Upvotes: 1

Related Questions