Group SQL QRY 1

[mp2admin]

Hi I have the following qry...


select tagname, start_swipe, end_swipe
from
(
select id, tagname, badge, CASE WHEn (ID % 2) <> 0
THEN datetime END as start_swipe,CASE WHEN (ID % 2) = 0
THEN datetime END as end_swipe
from badge_swipe_temp

) a


and it returns the following data...

badge start end
pack1_Dat_badge 10/5/2010 8:12 NULL
pack1_Dat_badge NULL 10/5/2010 8:45
pack1_Dat_badge 10/5/2010 9:46 NULL
pack1_Dat_badge NULL 10/5/2010 10:15
pack1_Dat_badge 10/5/2010 11:14 NULL
pack1_Dat_badge NULL 10/5/2010 11:44
pack1_Dat_badge 10/5/2010 12:45 NULL
pack1_Dat_badge NULL 10/5/2010 13:16
pack1_Dat_badge 10/5/2010 14:15 NULL
pack1_Dat_badge NULL 10/5/2010 14:45


I am trying to get the data returned like this...

badge start end
pack1_Dat_badge 10/5/2010 8:12 10/5/2010 8:45
pack1_Dat_badge 10/5/2010 9:46 10/5/2010 10:15
pack1_Dat_badge 10/5/2010 11:14 10/5/2010 11:44
pack1_Dat_badge 10/5/2010 12:45 10/5/2010 13:16
pack1_Dat_badge 10/5/2010 14:15 10/5/2010 14:45


could someone lead me in the right direction?

Thank You

 

[markros]

Try:

select tagname, badge, min(CASE WHEN (ID % 2) <> 0
THEN datetime END) as start_swipe,max(CASE WHEN (ID % 2) = 0
THEN datetime END) as end_swipe
from badge_swipe_temp
GROUP by TagName, Badge, ID%3

PluralSight Learning Library

 

[mp2admin]

Works Great...Thank you very much...

 

[markros]

I was thinking that I used the wrong condition, it probably should be
(ID+1)/2 instead in the group by. E.g. we need to group 1 and 2 together, then 3 & 4 together, etc.

So, based on the integer math (ID+1)/2 should give us correct result.

PluralSight Learning Library

 

[mp2admin]

I'll try the new one..
Just a thought if i add a value to your qry I get back the following.

Badge Value Start End
pack5_Dat_badge 26805 10/6/2010 11:15 NULL
pack5_Dat_badge 26805 10/6/2010 8:14 10/6/2010 11:45
pack5_Dat_badge 26805 NULL 10/6/2010 8:46
pack5_Dat_badge 27223 NULL 10/6/2010 9:45
pack5_Dat_badge 27223 10/6/2010 12:15 NULL
pack5_Dat_badge 27223 10/6/2010 9:14 NULL
pack5_Dat_badge 28187 10/6/2010 10:16 NULL
pack5_Dat_badge 28187 NULL 10/6/2010 9:45
pack5_Dat_badge 29354 NULL 10/6/2010 10:16
pack5_Dat_badge 29354 10/6/2010 10:47 NULL
pack5_Dat_badge 31388 10/6/2010 11:15 10/6/2010 7:51
pack5_Dat_badge 31388 10/6/2010 8:14 NULL
pack5_Dat_badge 31388 NULL 10/6/2010 10:47
pack5_Dat_badge 58604 NULL 10/6/2010 11:46
pack5_Dat_badge 58604 10/6/2010 12:15 10/6/2010 8:46
pack5_Dat_badge 58604 10/6/2010 9:14 NULL

how would I alter the qry to get ...


Badge Value Start End
pack5_Dat_badge 26805 10/6/2010 11:15 10/6/2010 11:45
pack5_Dat_badge 26805 10/6/2010 8:14 10/6/2010 11:45
pack5_Dat_badge 26805 10/6/2010 12:15 10/6/2010 8:46
pack5_Dat_badge 27223 10/6/2010 12:15 10/6/2010 9:45


Thanks again...I'm try your new suggestion now..

 

[markros]

I see, I thought originally the problem is always just 2 records for each Value. If not so, it's a bit tricky and looks like refactoring ranges problem

http://pratchev.blogspot.com/2010/02/refactoring-ranges.html

PluralSight Learning Library

 

[mp2admin]

Thanks.. Some good reading here.. I did get the original qry to work ...the extra reading will help me.

Thanks so much..