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 |
|
mackaboogie
Starting Member
4 Posts |
 Posted - 2011-02-16 : 12:19:24
|
| I have a table of events with start dates and end dates.If I have a record that looks like this...eventid eventDate endDate1 1/15/2011 2/5/2011If I query the table like so...SELECT * FROM events WHERE eventDate BETWEEN '1/1/2011' AND '1/31/2011'I get the expected result which is a single record.How could I get the query to return a record for each day between 1/15/2011 and 2/5/2011?Basically I would get 21 rows in my result set instead of the one. |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2011-02-16 : 13:03:30
|
quote:
Originally posted by mackaboogie
I have a table of events with start dates and end dates.If I have a record that looks like this...eventid eventDate endDate1 1/15/2011 2/5/2011If I query the table like so...SELECT * FROM events WHERE eventDate BETWEEN '1/1/2011' AND '1/31/2011'I get the expected result which is a single record.How could I get the query to return a record for each day between 1/15/2011 and 2/5/2011?Basically I would get 21 rows in my result set instead of the one.
You should be able to modify your query's date parameters.SELECT * FROM events WHERE eventDate > '1/15/2011' AND endDate < '2/05/2011'I havent tested but I think it will be along these lines.. |
 |
|
|
mackaboogie
Starting Member
4 Posts |
Posted - 2011-02-16 : 13:14:23
|
| That example would return the same single record. I am trying to force the query to return a record for each dateDiff between start and end date.For example if my startdate is 1/1/2000 and enddate is 1/10/2000 and selection date is 1/5/2000 (which falls between) then the query would return that record 10 times along with all other records that fit that criteria. |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2011-02-16 : 14:35:27
|
quote:
Originally posted by mackaboogie
That example would return the same single record.
Hmm I am not so sure.. Did you examine what I wrote? I am checking the input date against two columns.. Start and End.. Your query is only checking for one columen (The start time).My Query:SELECT * FROM events WHERE eventDate > '1/15/2011' AND endDate < '2/05/2011'I have tested my query on a temp table based on what you provided and it does indeed work. If you want to return records that satisfy either start or end condition then replace the AND in my query with an OR.r&r |
|
|
|
mackaboogie
Starting Member
4 Posts |
Posted - 2011-02-16 : 14:49:58
|
| I think you are still missing my point. I do want to return those records between those dates, but I want to return duplicates of a record as many times as the dateDiff between start and end date. |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2011-02-16 : 16:26:22
|
quote:
Originally posted by mackaboogie
I think you are still missing my point. I do want to return those records between those dates, but I want to return duplicates of a record as many times as the dateDiff between start and end date.
Ok yes that is a bit different. So if the datediff for a given Record X is 10 days you want to display that record 10 times. Is that correct? |
|
|
|
mackaboogie
Starting Member
4 Posts |
Posted - 2011-02-16 : 17:19:01
|
| YES!My reasoning was to simplify the output of the records but in the meantime I queried the resultset and then appended records to the query for items with an event span. Kind of ugly, but it worked.Thanks. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2011-02-17 : 05:15:31
|
| Use a Number or Calendar table.Searching the internet will give you many examples on how to use these. |
|
|
|
|
|
|
need multiple rows returned using start - end