April 18, 2012 at 3:17 am
Hi,
below is how the data in my table currently looks like:
Select dteStartDate, dteEndDate, txtCode From dbo.dtlLeave
/*
dteStartDatedteEndDatetxtCode
2012-03-29 00:00:00.0002012-03-29 23:59:00.000PAIDS
2012-03-01 00:00:00.0002012-03-02 23:59:00.000AL
2012-04-04 00:00:00.0002012-04-07 23:59:00.000PAIDS
2012-03-15 00:00:00.0002012-03-17 23:59:00.000AL
2012-03-26 00:00:00.0002012-03-27 23:59:00.000AL
2012-03-02 00:00:00.0002012-03-02 23:59:00.000PAIDS
2012-04-08 00:00:00.0002012-04-08 23:59:00.000PAIDS
2012-03-01 00:00:00.0002012-03-01 23:59:00.000PAIDS
2012-04-11 00:00:00.0002012-04-13 23:59:00.000PAIDS
2012-04-05 00:00:00.0002012-04-05 23:59:00.000PAIDS
2012-04-02 00:00:00.0002012-04-04 23:59:00.000AL
*/
I need to be able to select from the table above and only return the data within a date range e.g
/*
dteStartDatetxtCode
2012-03-01PAIDS
2012-03-02PAIDS
2012-03-03PAIDS
2012-03-04PAIDS
2012-03-05PAIDS
2012-04-21AL
2012-04-22AL
2012-04-23AL
*/
I'm not sure how to achieve this, please help
Thanks
April 18, 2012 at 3:42 am
I can help you along with most of it:
---this will get the data out of the table for you
SELECT SUBSTRING(dteStartDate, 0, 10), txtCode
from TABLE
---add this to limit your results to a specific date range.
where dteStartDate>= 2012-03-12
and dteStartDate <= 2012-03-12;
-
April 18, 2012 at 3:48 am
Sorry, but I don't get the data transformation you're after.
I see where the 01-Mar and 02-Mar PAIDS records are within ranges of the input data.
I don't see where the other PAIDS records come from. And I don't see the AL records within a date range of any of the input data.
You should also post DDL and your data in a readily consumable format.
To ChargeIt: I thought the second argument to SUBSTRING starts at 1?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 3:56 am
Hi ChargeIt,
maybe I wasn't clear in my prev post I don't want to limit the results for the specific dates that I posted and the time part of the date doesn't really have to be taken out, that was just an example of how I'd like to return the data(with or without the time part)
what I would like to get is all the codes that fall within the start date and end date range but all those date ranges and codes must be returned in that order.
e.g all the codes within 2012-03-01 and 2012-03-05(from the 1st to the 5th of march) then the codes column must just return 'PAIDS' e.t.c
The different codes must be returned with their dates in an ordered format
I hope I'm making sense.
Thanks
April 18, 2012 at 3:57 am
dwain.c, you're correct. I just tried it. Should be a 1, not 0, to return first 10 characters of the datetime field. Yep, thanks.
-
April 18, 2012 at 4:10 am
Hi dwain.c,
The second result set is just an example not the exact dates in the table, this what I'm trying to achieve
if the dates are as follows:
StartDate EndDate
2012-01-01 2012-01-05
then the results shoud be:
Date Code
2012-01-01 PAIDS
2012-01-02 PAIDS
2012-01-03 PAIDS
2012-01-04 PAIDS
2012-01-05 PAIDS
e.t.c
April 18, 2012 at 4:11 am
To xenophilia: How did your name change from ChargeIT? I could have sworn that's what I saw at first. Even the OP referred to it in his response.
To Teee: I still don't get the data transformation from your latest description. I'd like to help and I'm pretty sure I can, but I simply don't get it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 4:13 am
Hi dwain.c,
The second result set is just an example not the exact dates in the table, this what I'm trying to achieve
if the dates are as follows:
StartDate EndDate
2012-01-01 2012-01-05
then the results shoud be:
Date Code
2012-01-01 PAIDS
2012-01-02 PAIDS
2012-01-03 PAIDS
2012-01-04 PAIDS
2012-01-05 PAIDS
This I understand.
You'll need to use a Calendar or Tally table. I'll see if there is something I can put together for you.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 4:22 am
This I understand.
You'll need to use a Calendar or Tally table. I'll see if there is something I can put together for you.
No loops! No CURSORs! No RBAR! Hoo-uh!
------------------------------------------------------
Thanks, would really appreciate that 🙂
April 18, 2012 at 4:29 am
This will work if your intervals don't exceed 16 days.
DECLARE @t TABLE (dteStartDate DATETIME, dteEndDate DATETIME, txtCode VARCHAR(10))
INSERT INTO @t (dteStartDate,dteEndDate,txtCode)
SELECT '2012-03-29 00:00:00.000','2012-03-29 23:59:00.000','PAIDS'
UNION ALL SELECT '2012-03-01 00:00:00.000','2012-03-02 23:59:00.000','AL'
UNION ALL SELECT '2012-04-04 00:00:00.000','2012-04-07 23:59:00.000','PAIDS'
UNION ALL SELECT '2012-03-15 00:00:00.000','2012-03-17 23:59:00.000','AL'
UNION ALL SELECT '2012-03-26 00:00:00.000','2012-03-27 23:59:00.000','AL'
UNION ALL SELECT '2012-03-02 00:00:00.000','2012-03-02 23:59:00.000','PAIDS'
UNION ALL SELECT '2012-04-08 00:00:00.000','2012-04-08 23:59:00.000','PAIDS'
UNION ALL SELECT '2012-03-01 00:00:00.000','2012-03-01 23:59:00.000','PAIDS'
UNION ALL SELECT '2012-04-11 00:00:00.000','2012-04-13 23:59:00.000','PAIDS'
UNION ALL SELECT '2012-04-05 00:00:00.000','2012-04-05 23:59:00.000','PAIDS'
UNION ALL SELECT '2012-04-02 00:00:00.000','2012-04-04 23:59:00.000','AL'
;WITH cteTally (n) AS (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16)
SELECT DISTINCT DATEADD(day, n, dteStartDate) As dteStartDate, txtCode
FROM @t CROSS APPLY (SELECT n FROM cteTally) x
WHERE DATEADD(day, n, dteStartDate) BETWEEN dteStartDate AND dteEndDate
ORDER BY txtCode, dteStartDate
The DISTINCT handles overlaps. The query would be more efficient without DISTINCT if you could ensure no overlaps.
If your start/end date pairs exceed 16 days, you just need to use a bigger tally table (search the forum, there's many examples available).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 4:39 am
Thank you so much for your help, the last query works perfectly 🙂
April 18, 2012 at 4:42 am
Don't mention it. Just glad it works for you.
One other caveat though. Your start dates need to be at time 00:00. If they're not, you should truncate them.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply