Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mkswanson
Starting Member
21 Posts |
 Posted - 2009-10-01 : 10:55:42
|
| I have a problem, and I'm not sure where to begin.I have two tables: transactions and periods.The transactions table has mnay columns, but the important one is TransactionTime.The periods table has three columns: StartTime, EndTime, and Period.I need to lookup the Period for each transaction, and don't know how to do it. Basically, if the Transaction is between the StartTime and EndTime, then that is the correct Period.I wouldn't think this would be too difficult, but it is proving to be for me.Thanks! |
|
|
mivey4
Yak Posting Veteran
66 Posts |
Posted - 2009-10-01 : 10:59:07
|
| Can you be more specific?Maybe an example of what you're trying to accomplish would be helpful. I can only assume that startTime and EndTime are DATETIME values, so what is Period? The year? A year and month combination? |
 |
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2009-10-01 : 11:04:39
|
| Sorry...I guess I should have included that in the first place. All of the values are actually DATETIME values. The period is the business date that the transaction occurred on. In general the business date runs from 4:00 AM one day to 4:00 AM on the next.Given the following:Transaction Time: 10/01/2009 02:00:00 AMIt would match the following:StartTime: 09/30/2009 04:00:00 AMEndTime: 10/01/2009 04:00:00 AMPeriod: 09/30/2009 00:00:00 AMThe period (business date) would be 9/30.Does that make sense? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-01 : 15:05:04
|
yup. so what you need is thisSELECT LEFT(CONVERT(varchar(10),Period,101),4)FROM YourTableWHERE TransactionTime BETWEEN StartTime AND EndTime |
|
|
|
|
|
|
Date Lookup