SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


pulling records from a table between date ranges in another table


pulling records from a table between date ranges in another table

Author
Message
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 126
This seems simple enough but for some reason, my brain isn't working.

I have a lookup table:

Table A: basically dates every 30 days

1/1/2014
2/3/2014
3/3/2014
4/3/2014

I have Table b that has records and dates created assocated with each record

I want all records that fall between the 1st 30 days to have an additional column that indicates 30

union

records with additional column indicating 60 days that fall between the 30 and 60 day

union

records with additional column indicating 90days that fall between the 60 and 90 day mark.

Is there an easy way to do this?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25969 Visits: 17525
OlyKLin (3/17/2014)
This seems simple enough but for some reason, my brain isn't working.

I have a lookup table:

Table A: basically dates every 30 days

1/1/2014
2/3/2014
3/3/2014
4/3/2014

I have Table b that has records and dates created assocated with each record

I want all records that fall between the 1st 30 days to have an additional column that indicates 30

union

records with additional column indicating 60 days that fall between the 30 and 60 day

union

records with additional column indicating 90days that fall between the 60 and 90 day mark.

Is there an easy way to do this?



Pretty sparse on the details here but why not just add this column to your lookup table?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 126
I can't add the column b/c I need to query all records between the 1st and 2nd date of the lookup table and add the column 30 to it to represent that these records happened during the first 30 days.

Then I need to get a 2nd set of records between the 2nd and 3rd dates and tag a column that has 60 meaning that those records were created in the last 60 days

etc. But, they need to be all in record set.

If I didn't need the look up table to dictate the 30 days, that would fabulous and I could just update each row based on a beginning date and calculate whether it's in a 30 day range, 60 day range or 90 day range of a start date
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25969 Visits: 17525
OlyKLin (3/17/2014)
I can't add the column b/c I need to query all records between the 1st and 2nd date of the lookup table and add the column 30 to it to represent that these records happened during the first 30 days.

Then I need to get a 2nd set of records between the 2nd and 3rd dates and tag a column that has 60 meaning that those records were created in the last 60 days

etc. But, they need to be all in record set.

If I didn't need the look up table to dictate the 30 days, that would fabulous and I could just update each row based on a beginning date and calculate whether it's in a 30 day range, 60 day range or 90 day range of a start date


Right, add the extra data point to the lookup table. Then it will just be in the second column from the lookup table.


select MyColumns, ExtraColumnFromLookupTable
from SomeTable
join LookupTable on SomeConditions
Where SomeConditions



If that doesn't work or make sense you need to post more information. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 126
Sorry about that...let's try this:

CREATE TABLE #tMILESTONE  (

CustomerID INT NULL,
LoadDate datetime NULL

);

WITH CTE AS
(
SELECT 11011 AS CUSTOMERID, GETDATE() AS LoadDate
UNION ALL
SELECT 11011, LoadDate + 1
FROM CTE

)
INSERT #tMILESTONE ( CustomerID, LoadDate )
SELECT TOP 100 CustomerID, convert(varchar, LoadDate, 101) FROM CTE



I want the recordset to look like (This is just a subset of what you would load above but to give you the idea):
CustomerID LoadDate Milestone
11011 2014-03-17 00:00:00.000 30
11011 2014-03-18 00:00:00.000 30
11011 2014-03-19 00:00:00.000 30
11011 2014-04-17 00:00:00.000 60
11011 2014-04-18 00:00:00.000 60
11011 2014-04-19 00:00:00.000 60
11011 2014-05-17 00:00:00.000 90
11011 2014-05-18 00:00:00.000 90
11011 2014-05-19 00:00:00.000 90
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7245 Visits: 6431
Something like this perhaps?


WITH CTE AS
(
SELECT 11011 AS CUSTOMERID, GETDATE() AS LoadDate
UNION ALL
SELECT 11011, LoadDate + 1
FROM CTE

),
Milestones AS
(
SELECT TOP 100 CustomerID, LoadDate=CAST(LoadDate AS DATE)
FROM CTE
),
DateRanges AS
(
SELECT CustomerID,LoadDate
,rn=30*(1+(ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY LoadDate)-1)/30)
FROM Milestones
)
SELECT *
FROM DateRanges;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
serg-52
serg-52
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1187 Visits: 1826
If i got it right ...
Suppose there's date range lookup table with N dates

create table #RangeLookup  (
dt date
)
declare @strtDate date = '20140101'
declare @step int = 30
declare @N int = 20

insert #RangeLookup
select top (@N) dateadd(dd, @step*(row_number() over (order by (select null))-1), @strtDate)
from sys.all_columns



Step is not supposed to be always fixed as it is in the above example.
Now one can build a view or cte kind of

;with c1 as (
select dt, rn= row_number() over (order by dt)
from #RangeLookup
),
RangeLookup2 as ( -- N-1 intervals with tags
select dStart = a.dt
, dEnd = dateadd (dd, -1, b.dt)
, tag = datediff (dd, (select top 1 dt from #RangeLookup order by dt), b.dt)
from c1 a
join c1 b on a.rn= b.rn-1
)
select * from RangeLookup2



Then just compare myDate in question against interval to get the tag.

Rgds
Serg
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search