Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group SQL QRY 1

Status
Not open for further replies.

mp2admin

Programmer
Apr 17, 2008
10
US
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
 
Try:

Code:
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
 
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
 
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..
 
Thanks.. Some good reading here.. I did get the original qry to work ...the extra reading will help me.

Thanks so much..
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top