March 17, 2014 at 2:20 pm
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?
March 17, 2014 at 3:02 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2014 at 3:08 pm
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
March 17, 2014 at 3:11 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2014 at 3:43 pm
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
March 17, 2014 at 6:34 pm
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 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
March 18, 2014 at 2:08 am
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
August 17, 2017 at 8:03 am
serg-52 - Tuesday, March 18, 2014 2:08 AMIf i got it right ...Suppose there's date range lookup table with N datescreate table #RangeLookup (dt date)declare @strtDate date = '20140101'declare @step int = 30declare @N int = 20insert #RangeLookup select top (@N) dateadd(dd, @step*(row_number() over (order by (select null))-1), @strtDate)from sys.all_columnsStep 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 tagsselect 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 ajoin c1 b on a.rn= b.rn-1)select * from RangeLookup2Then just compare myDate in question against interval to get the tag.RgdsSerg
a simpler and i think faster aproach
DECLARE @strtDate DATE = '20140101'
DECLARE @step INT = 30
DECLARE @N INT = 20
;WITH RANGES AS (
SELECT 1 AS id
, @strtDate AS dt_ini
, DATEADD(DAY, @step - 1, @strtDate) AS dt_end
, @step AS mlst
UNION ALL
SELECT R.id + 1 AS id
, DATEADD(DAY, @step, R.dt_ini) AS dt_ini
, DATEADD(DAY, @step, R.dt_end) AS dt_end
, R.mlst + @step AS mlst
FROM RANGES R
WHERE R.id < @step -- if you want @step to be more than 100 you need to add "option (maxrecursion 0)" at the end of statment
)
SELECT *
FROM RANGES
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply