Date Time field thru +1 day through certain time

[debbieg]

I need some help! I have 2 tables:

Hotel_Coupon
Player_ID
RedeemDate

Gaming_Details
Player_ID
GamingDate
StartDateTime
EndDateTime
Actual
Theo
Type (Pit or Slot)

Example of one player:

Player_ID Type Actual Theo GamingDate StartTime EndTime
14841 SLOT $5.42 $0.82 6/8/2010 6/8/2010 9:45:06 AM 6/8/2010 9:46:34 AM
14841 SLOT ($135.60) $4.03 6/8/2010 6/8/2010 9:46:51 AM 6/8/2010 9:50:39 AM
14841 PIT ($150.00) $28.13 6/8/2010 6/8/2010 9:50:00 AM 6/8/2010 10:50:00 AM
14841 SLOT $39.90 $10.85 6/9/2010 6/9/2010 11:30:15 AM 6/9/2010 11:42:10 AM


I am needing to know the total Actual and Theo by Type by Player_ID by RedeemDate. But I need these totals to be from RedeemDate through the next GamingDate with EndDateTime <=12:00:00 pm. The above example should result in:

Player_ID SlotActual SlotTheo PitActual PitTheo GamingDate
14841 ($90.28) $15.69 ($150.00) $28.13 06/08/2010


I've been trying to come up with a solution for days and coming up with nothing. Or do I tell my boss it isn't possible.

Thanks in advance for any help.

Debbie

 

[TheAceMan1]

Formatted for viewing:

[tt][blue]Player_ID Type Actual Theo GamingDate StartTime EndTime
********* **** ********* ****** ********** ******************** ********************[/blue]
14841 SLOT $5.42 $0.82 6/8/2010 6/8/2010 09:45:06 AM 6/8/2010 09:46:34 AM
14841 SLOT ($135.60) $4.03 6/8/2010 6/8/2010 09:46:51 AM 6/8/2010 09:50:39 AM
14841 PIT ($150.00) $28.13 6/8/2010 6/8/2010 09:50:00 AM 6/8/2010 10:50:00 AM
14841 SLOT $39.90 $10.85 6/9/2010 6/9/2010 11:30:15 AM 6/9/2010 11:42:10 AM

[blue] Totals
Player_ID SlotActual SlotTheo PitActual PitTheo GamingDate
********* ********** ******** ********* ******* **********[/blue]
14841 ($90.28) ($15.70) ($150.00) $28.13 6/8/2010[/tt]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[debbieg]

Any ideas or suggestions?

 

[PHV]

Something like this ?

SELECT HC.Player_ID, Sum(IIf(Type='SLOT',Actual,0)) AS SlotActual, Sum(IIf(Type='SLOT',Theo,0)) AS SlotTheo
, Sum(IIf(Type='PIT',Actual,0)) AS PitActual, Sum(IIf(Type='PIT',Theo,0)) AS PitTheo, RedeemDate
FROM Hotel_Coupon AS HC INNER JOIN Gaming_Details AS GD ON HC.Player_ID=GD.Player_ID
WHERE StartTime>=RedeemDate AND EndTime<=RedeemDate+1.5
GROUP BY HC.Player_ID, RedeemDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886