January 24, 2012 at 1:13 pm
Hi all
I have a table with following data
ID AsOdDate
1 12/2/2010
2 1/18/2011
3 2/24/2011
4 3/7/2011
I need result o/p like this
RecordId ID RecordAsAsOfDate
1 1 12/2/2010
2 1 1/18/2011
2 2 1/18/2011
3 1 2/24/2011
3 2 2/24/2011
3 3 2/24/2011
4 1 3/7/2011
4 2 3/7/2011
4 3 3/7/2011
4 4 3/7/2011
Thanks for your help
January 25, 2012 at 12:23 pm
A couple things:
- It's ugly
- If you have more than 32 rows in your base table, you will have to use the MAXRECURSION query hint
- If you have more than 32,767 rows it won't work at all
but here is something that will work given the limited amount of data you posted, assuming that the base table is named AsOfDates
WITH CTE
AS (
SELECT
1 AS RecordID,
[ID] AS ID,
AsOfDate AS RecordAsOfDate
FROM AsOfDates A
WHERE A.ID = 1
UNION ALL
SELECT C.RecordID + 1,
CONVERT( INT, RANK() OVER(ORDER BY A.ID) ) AS ID,
(
SELECT AsofDate
FROM AsOfDates A
WHERE A.ID = C.RecordID+1
)
FROM CTE C
JOIN AsOfDates A
ON A.[ID] <= ( C.[ID] + 1 )
AND EXISTS (
SELECT *
FROM AsOfDates B
WHERE B.[ID] = ( C.RecordID + 1 )
)
)
SELECT DISTINCT RecordID,
[ID],
RecordAsOfDate
FROM CTE
ORDER BY RecordID, ID
more information about what you're trying to do would be helpful when trying to come up with a solution
January 25, 2012 at 12:53 pm
this isn't much better, but it gets rid of the DISTINCT in the SELECT
WITH CTE
AS (
SELECT
1 AS RecordID,
[ID] AS ID,
AsOfDate AS RecordAsOfDate
FROM AsOfDates A
WHERE A.ID = 1
UNION ALL
SELECT C.RecordID + 1,
A.ID,
(
SELECT AsofDate
FROM AsOfDates A
WHERE A.ID = C.RecordID+1
)
FROM CTE C
JOIN AsOfDates A
ON A.[ID] = C.[ID]
AND EXISTS (
SELECT *
FROM AsOfDates B
WHERE B.[ID] = ( C.RecordID + 1 )
)
UNION ALL
SELECT C.RecordID + 1,
C.RecordID + 1,
A.AsOfDate
FROM CTE C
JOIN AsOfDates A
ON A.ID = C.RecordID + 1
AND C.ID = C.RecordID
)
SELECT RecordID,
[ID],
RecordAsOfDate
FROM CTE
ORDER BY RecordID, ID
January 25, 2012 at 1:51 pm
does this work...
would be interested to learn about the requirement for this....have you over simplified the process?
--- Look up "Tally Table.....http://qa.sqlservercentral.com/articles/T-SQL/62867/ by Jeff Moden
SELECT ID AS RecordID,
ROW_NUMBER()OVER( PARTITION BY AsOdDate ORDER BY AsOdDate )AS ID ,
AsOdDate
FROM
TESTDATA INNER JOIN Tally
ON TESTDATA.ID > Tally.N;
RecordID ID AsOdDate
----------- -------------------- -----------------------
1 1 2010-12-02 00:00:00.000
2 1 2011-01-18 00:00:00.000
2 2 2011-01-18 00:00:00.000
3 1 2011-02-24 00:00:00.000
3 2 2011-02-24 00:00:00.000
3 3 2011-02-24 00:00:00.000
4 1 2011-03-07 00:00:00.000
4 2 2011-03-07 00:00:00.000
4 3 2011-03-07 00:00:00.000
4 4 2011-03-07 00:00:00.000
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 25, 2012 at 2:02 pm
Please forgive my earlier posts where I was attempting to kill a mouse with a tank. This is much simpler. I don't know why I didn't think of it before:
SELECT
A.[ID] AS RecordID,
B.[ID],
A.AsofDate
FROM AsOfDates A
JOIN AsOfDates B
ON B.[ID] <= A.[ID]
ORDER BY RecordID, ID
January 25, 2012 at 2:05 pm
Thanks all for you help
this worked out grate for me
January 25, 2012 at 2:11 pm
which reply was "grate" ??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 25, 2012 at 2:17 pm
I used tried both way ony with telly table workedout grate perfomance wise.
Thanks once again fro your help
January 31, 2012 at 5:14 am
Hi,
I have two tables . One for Schedulling and other for bus quantity.
I have set schedule for the busses in schedule table.
now if i reduce the quantity than i have to check if the enterd quanity is
valid or not .
for example :
if bus1 have 3 quantity.
and the schedule for these are
1-jan-2010 to 20-jan-2010
21-jan-2010 to 20-feb-2010
2-Feb-2010 to 20-Feb-2010
10-Feb-2010 to 20-Feb-2010
Know for above schedule i cann't reduce the quantity to 2 as for days 10-feb
to 20 -feb all 3 busses are scheduled so i have to maintain the minimum
quantiy 3.
For this i do not want to use cursor to track each of schedule row . for
performance reson.
Is there any idea.
Please help
Ads by Google
1. JaySQL by Synametrics - A Must-Have tool for database programmers and administrators - synametrics.com
2. NServiceBus - Open source service bus for .NET. Enterprise ready, Startup friendly - NServiceBus.com
3. Washington DC Buses - For All Your Group's Needs Contact Us for a Price Quote! - fleettransportation.com
4. NuoDB - NewSQL Leader - Elastic SQL database for the cloud Download the Beta today - nuodb.com
5. Pour Tout Vos Transports - Cdg.Orly.Beauvais.Paris.Disney 01 39 58 70 03; 06 20 50 28 97 - transfershuttleservice.com
6. 6-Core 4-Way SuperServers - Energy-efficient Supermicro Server! Featuring Intel® Xeon® Processors - Supermicro.com
7. Product information. Free - for your webshop or ERP system. More than 2000 brands supported. - icecat.biz
8. Qlikview Apps - Business Intelligence Reporting Dynamic and Flexible BI - dyflex.com.au
9. Singapore-Malaysia Coach - Largest Express Bus Tickets Portal Singapore to & fro Malaysia - easibook.com
10. San Francisco Tour Bus - Economical and reliable bus. Large SF Fleet since 1978! - sfminibus.com
Search Engine Optimizing | Search Engine Marketing | Social Media Marketing | Pay Per Clicks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply