Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

pulling records from a table between date ranges in another table Expand / Collapse
Author
Message
Posted Monday, March 17, 2014 2:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 10:57 AM
Points: 16, Visits: 83
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?

Post #1551902
Posted Monday, March 17, 2014 3:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's 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)
Post #1551922
Posted Monday, March 17, 2014 3:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 10:57 AM
Points: 16, Visits: 83
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
Post #1551927
Posted Monday, March 17, 2014 3:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's 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)
Post #1551929
Posted Monday, March 17, 2014 3:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 10:57 AM
Points: 16, Visits: 83
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
Post #1551947
Posted Monday, March 17, 2014 6:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 PM
Points: 3,422, Visits: 5,366
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!
Post #1551968
Posted Tuesday, March 18, 2014 2:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:00 AM
Points: 238, Visits: 502
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
Post #1552048
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse