March 24, 2010 at 5:40 pm
I have this table of dates:
mark_per start_date end_date
-------- ---------- ----------
M1 07/24/2009 10/02/2009
M2 10/20/2009 12/18/2009
M3 01/04/2010 03/12/2010
M4 03/29/2010 05/28/2010
S1 07/24/2009 01/04/2010
S2 01/05/2010 05/28/2010
Notice the gaps between the [font="Courier New"]end_date[/font] of one row and the [font="Courier New"]start_date[/font] of the following row except the last two; this is intentional. I want to get the rows where [font="Courier New"]getdate()[/font] is between the start and end dates (easy to do) or, if [font="Courier New"]getdate()[/font] is in one of the gaps, get the row for the closest start date.
For example, today is 03/24/2010, so I want my query to return
mark_per start_date end_date
-------- ---------- ----------
M4 03/29/2010 05/28/2010
S2 01/05/2010 05/28/2010
because 03/24/2010 is in the gap between the [font="Courier New"]end_date[/font] for M3 and the [font="Courier New"]start_date[/font] for M4, and is also between the dates for S2.
Another example: if [font="Courier New"]getdate()[/font] returns 12/23/2009, I want my query to return
mark_per start_date end_date
-------- ---------- ----------
M3 01/04/2010 03/12/2010
S1 07/24/2009 01/04/2010
Again, 12/23/2009 is after the [font="Courier New"]end_date[/font] for M2, so I want M3; it's between the dates for S1, also.
Here's what I have so far, but it's not quite working; using the example date of 12/23/2009 gives me M3, M4, S1 and S2.
select * from #mp_dates a
where '2009-12-23' between start_date and end_date
or
'2009-12-23' <
(
select min(b.end_date)
from #mp_dates b
where b.mark_per = a.mark_per and '2009-12-23' < b.end_date
)
My brain is numb looking at this. Help! Sample T-SQL attached.
March 24, 2010 at 6:17 pm
Looking for something like this?
create table #TestTable (
MarkPer char(2),
StartDate datetime,
EndDate datetime
);
insert into #TestTable
select 'M1','07/24/2009','10/02/2009' union all
select 'M2','10/20/2009','12/18/2009' union all
select 'M3','01/04/2010','03/12/2010' union all
select 'M4','03/29/2010','05/28/2010' union all
select 'S1','07/24/2009','01/04/2010' union all
select 'S2','01/05/2010','05/28/2010'
;
select
tt.*
from
#TestTable tt
where
GETDATE() >= tt.StartDate
and GETDATE() < tt.EndDate
union all
select
*
from
#TestTable tt
where
tt.MarkPer = (
select MIN(tt1.MarkPer) from #TestTable tt1 where GETDATE() < tt1.StartDate)
order by
tt.MarkPer
;
drop table #TestTable;
March 24, 2010 at 7:36 pm
I am thinking something like this might be what you are looking for:
;With cteGrouped (MarkPer, StartDate, EndDate, RowNum)
As (
Select MarkPer
,StartDate
,EndDate
,row_number() over(partition By Left(MarkPer, 1) Order By EndDate)
From #TestTable
Where EndDate >= '20091223'
-- Where EndDate >= '20100324'
)
Select *
From cteGrouped
Where RowNum = 1;
The assumption here is that you want the first row where the EndDate is greater than the current date, regardless of any gaps.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2010 at 8:58 am
Thanks to both of you, those both do just what I wanted. Now if I can only wrap my head around the [font="Courier New"]with[/font] statement. 🙂
March 26, 2010 at 1:45 pm
DakotaPaul,
The WITH statement is the best thing that came out with SS2005.
And there is no need to wrap your head around it. It's really simple!
It's like preparing your parenthetical statements (sub-queries) before you actually use them in your final argument (your main query).
DO IT!
DON'T ASK QUESTIONS!
March 26, 2010 at 1:59 pm
Michael Meierruth (3/26/2010)
DakotaPaul,The WITH statement is the best thing that came out with SS2005.
And there is no need to wrap your head around it. It's really simple!
It's like preparing your parenthetical statements (sub-queries) before you actually use them in your final argument (your main query).
DO IT!
DON'T ASK QUESTIONS!
I disagree - ask all the questions you can. 😀
The WITH statement starts the setup of a common table expression. In this usage, it is essentially the same as an in-line view or derived table and works the same way. I used it here to provide the row number so we could filter on RowNum = 1.
There are a lot of other uses and it is worth the time to review the other uses in Books Online.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2010 at 1:59 pm
Michael Meierruth (3/26/2010)
DakotaPaul,The WITH statement is the best thing that came out with SS2005.
And there is no need to wrap your head around it. It's really simple!
It's like preparing your parenthetical statements (sub-queries) before you actually use them in your final argument (your main query).
DO IT!
DON'T ASK QUESTIONS!
I'll have to disagree. If you don't understand what something is, ask questions. Don't do something just because that is the way it was given to you. You are the one that will need to support it, therefore you need to understand it.
March 26, 2010 at 2:06 pm
Oops, it appears I misused the term "don't ask questions". Note, I have changed it to lower case. This should help in calming down everyone.
I was simply trying to say that you should not suffer 'wrapping your head around something' like WITH.
:hehe:
March 26, 2010 at 2:13 pm
Michael Meierruth (3/26/2010)
DakotaPaul,The WITH statement is the best thing that came out with SS2005.
And there is no need to wrap your head around it. It's really simple!
It's like preparing your parenthetical statements (sub-queries) before you actually use them in your final argument (your main query).
DO IT!
DON'T ASK QUESTIONS!
I think I disagree with the part about 'The WITH statement is the best thing that came out with SS2005' as well. It's handy and it's really nice for formatting, but it doesn't actually give you any capabilities (recursive CTE's excluded) that weren't available in 2000. Tough choice.
March 26, 2010 at 2:39 pm
Michael Meierruth (3/26/2010)
Oops, it appears I misused the term "don't ask questions". Note, I have changed it to lower case. This should help in calming down everyone.I was simply trying to say that you should not suffer 'wrapping your head around something' like WITH.
:hehe:
That's okay - just don't want to give someone the impression that they shouldn't ask questions. That's what we are here for - to answer questions and help each other out.
And I have to agree with Seth - maybe not the best thing, but I do like using them to organize my code. For me, it's a lot easier to organize than using derived tables - but that's just a personal choice.
One of the biggest things is ROW_NUMBER() and the other windowed functions. So many thiings that can be done with those that are just painful to write in SQL Server 2000. 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2010 at 3:02 pm
Windowed functions are definitely high on my list, but I think I'd have to go with DMV's for the win.
March 26, 2010 at 3:53 pm
Absolutely - the DMV's open up a lot of good information and make Performance Dashboard a real useful tool 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2010 at 4:07 pm
DECLARE@Sample TABLE
(
MarkPer CHAR(2),
StartDate DATETIME,
EndDate DATETIME
)
SET DATEFORMAT MDY
INSERT@Sample
SELECT'M1', '07/24/2009', '10/02/2009' UNION ALL
SELECT'M2', '10/20/2009', '12/18/2009' UNION ALL
SELECT'M3', '01/04/2010', '03/12/2010' UNION ALL
SELECT'M4', '03/29/2010', '05/28/2010' UNION ALL
SELECT'S1', '07/24/2009', '01/04/2010' UNION ALL
SELECT'S2', '01/05/2010', '05/28/2010'
DECLARE@Today DATETIME
SET@Today = '03/24/2010'
--SET@Today = '12/23/2009'
;WITH cteSource(MarkPer, StartDate, EndDate, Delta)
AS (
SELECTMarkPer,
StartDate,
EndDate,
DATEDIFF(DAY, @Today, StartDate) AS Delta
FROM@Sample
WHERE@Today <= EndDate
)
SELECT TOP(2)WITH TIES
MarkPer,
StartDate,
EndDate
FROMcteSource
ORDER BYDelta * (1 + SIGN(Delta)) / 2
N 56°04'39.16"
E 12°55'05.25"
March 26, 2010 at 4:14 pm
To SwePeso:
Show off :w00t:
I like it - nice solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2010 at 4:20 pm
Jeffrey Williams-493691 (3/26/2010)
I like it - nice solution.
Thank you.
Now when I read the code, I see that the "/ 2" is not even necessary...
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply