Merge two rows with using date and time

Question

Merge two rows with using date and time

SPtrt

SSChasing Mays

Points: 603
More actions
January 28, 2011 at 8:40 am

#233570

Hi,
Help to give me an idea how to query to solve the below table
Combine 1 and 1/2 an hour slot to one hour slot and sum the value.
For eg. combine
1/20/2011 12:00 and 1/20/2011 12:30 this need to produce 30(19+11)
Result will be
1 1/20/2011 12:00 30
TABLE
IDDate and Time Value
11/20/2011 12:0019
11/20/2011 12:3011
11/20/2011 1:0020
11/20/2011 1:3020
11/20/2011 2:0010
11/20/2011 2:3010
11/20/2011 3:0012
21/20/2011 12:0012
21/20/2011 12:3013
21/20/2011 1:0012
21/20/2011 1:3014
21/20/2011 2:0016

Viewing 9 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are you familiar with the various date and time functions in T-SQL?

You could query the table Where datepart(minute) = 0 and get all the rows where it's on-the-hour, and then use DateAdd to get the half-hour mark.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SPtrt SSChasing Mays Points: 603 More actions · Answer 2

Hi,

sorry i'm new to this... i dono that much.

Could you kindly explian little more

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 3

Something like this:

select *

from MyTable

where datepart(minute, [Date and Time]) = 0;

That should give you the rows that end on-the-hour.

select *,

(select case when exists (select 1 from MyTable as MT2 where MT2.[Date and Time] = DateAdd(minute, 30, MyTable.[Date and Time]) then 30 else null end)

from MyTable

where datepart(minute, [Date and Time]) = 0;

The sub-query will get you a 30 if there's a row 30 minutes later.

Since I don't have your table definition, I can't write real code this, just the sample above.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SPtrt SSChasing Mays Points: 603 More actions · Answer 4

HI,

Thank you so much. i got the result.

And one more help.

i want to update the same table to Hour slot

For eg,

1/20/2011 12:00 and 1/20/2011 12:30 this need to produce 30(19+11)

Result will be

1 1/20/2011 12:00 30 )replace above 2 rows into single row)

i have 100 rows like this. I need to change 30min to 1hr slot. The time is 24hr clock

Thanks

tertiusdp SSCommitted Points: 1569 More actions · Answer 5

SELECT

CASE DATEPART(MINUTE , [Date and Time])

WHEN 30 THEN DATEADD(MI,-30 , [Date and Time])

ELSE [Date and Time]

END

,SUM(Value)

FROM @test-2

GROUP BY

CASE DATEPART(MINUTE , [Date and Time])

WHEN 30 THEN DATEADD(MI,-30 , [Date and Time])

ELSE [Date and Time]

END

SPtrt SSChasing Mays Points: 603 More actions · Answer 6

HI,

Thanks for your kind reply.

Still i find difficult to get out the output.

Any other ideas,

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

To get the best help possible from this forum (or any other) please read and follow the instructions in the first article I have referenced below in my signature block regarding asking for help. In addition, be sure to include the expected results. This will give us something to test agaist.

What you get back in return is better answers and tested code.

Bottom line, help us help you. We can't see what you don't show us.

GSquared SSC Guru Points: 260824 More actions · Answer 8

shankarntu (1/28/2011)
HI,
Thank you so much. i got the result.
And one more help.
i want to update the same table to Hour slot
For eg,
1/20/2011 12:00 and 1/20/2011 12:30 this need to produce 30(19+11)
Result will be
1 1/20/2011 12:00 30 )replace above 2 rows into single row)
i have 100 rows like this. I need to change 30min to 1hr slot. The time is 24hr clock
Thanks

I honestly have to say I don't understand what you're asking for. Can you clarify?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon