how to add hours to day

Question

Post reply

how to add hours to day

vinay.varaala

Mr or Mrs. 500

Points: 576
More actions
September 6, 2012 at 4:06 pm

#261580

my question is here is the example first
hour date sale
0 2012-09-05 10
1 2012-09-05 20
3 2012-09-05 30
4 2012-09-05 40
-- ------------- ----
23 2012-09-05 70
I want to sum up all the hours sale to day sale on this date
can any one help me pls..
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Is this what you require ?

CREATE TABLE #T(Hr INT,D DATE,S INT)

INSERT INTO #T

SELECT 0, '2012-09-05', 10 UNION ALL

SELECT 1, '2012-09-05', 20 UNION ALL

SELECT 3, '2012-09-05', 30 UNION ALL

SELECT 4, '2012-09-05', 40 UNION ALL

SELECT 1, '2012-09-04', 20 UNION ALL

SELECT 1, '2012-09-03', 200 UNION ALL

SELECT 5, '2012-09-04', 2 UNION ALL

SELECT 23, '2012-09-05', 70

SELECT D AS 'Sales date',SUM(S) AS 'Total Sales' FROM #T GROUP BY D

Results:

Sales dateTotal Sales

2012-09-03200

2012-09-0422

2012-09-05170

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 2

this is the query

select DatePart(hour,Reading_Date) as hour, convert(Date,Reading_Date,103) as date, avg(Fuel_Level)as fuel_level,avg(Fuel_Volume)as fuel_volume from Levels

where Simmons_PanelID=5479 AND convert(Date,Reading_Date,103)='2012-09-05'

group by convert(Date,Reading_Date,103),DatePart(hour,Reading_Date)

order by DatePart(hour,Reading_Date)

outpur was :

02012-09-051081.91500010556.072500

12012-09-051081.45000010550.667500

22012-09-051080.94000010543.880000

32012-09-051073.56500010411.868500

42012-09-051063.21000010234.175500

52012-09-051056.00500010110.270500

62012-09-051041.2950009876.589000

72012-09-051022.1000009611.096000

82012-09-05998.0900009309.596000

92012-09-051413.25500013972.345500

102012-09-051535.83500015867.740000

112012-09-051516.81500015654.350500

122012-09-051499.52000015470.388000

132012-09-051485.86000015313.367000

142012-09-051469.06500015125.176000

152012-09-051450.85000014887.597000

162012-09-051431.66500014641.704500

172012-09-051407.25500014346.442500

182012-09-051374.46500013908.481500

192012-09-051354.15000013639.561000

202012-09-051344.24000013511.662500

212012-09-051336.61000013415.504500

222012-09-051330.81500013343.453500

232012-09-051327.36000013301.044500

my question is I want to sum up each hour to day

expected output:

hour date fuel_level fuel_volume

24 2012-09-05 540622 425621

after coming that I am trying to display week1 to week4 of that month

how to convert days to weeks pls

thanks

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

to add hours to days use the DATEADD function

today

select getdate()

today + 3 hours

select dateadd(hh, 3, getdate())

today - 3 hours

select dateadd(hh, -3, getdate())

today + 3 hours 1 week in the future

select DATEADD(ww, 1, DATEADD(hh, -3, getdate()))

My results were

2012-09-07 09:09:13.030

2012-09-07 12:09:13.030

2012-09-07 06:09:13.030

2012-09-14 06:10:27.403

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

vinay.varaala (9/7/2012)
this is the query
where Simmons_PanelID=5479 AND convert(Date,Reading_Date,103)='2012-09-05'

Your where clause has rendered this nonSARGable. You don't need to convert your date to a formatted date string for this comparison.

where Simmons_PanelID=5479 AND Reading_Date = '2012-09-05'

Depending on your dateformat this would be fine. This way the optimizer can convert your constant to a datetime and then it can compare to the datetime in the table and your index will still be utilized (assuming you have one on Reading_Date).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/