November 13, 2009 at 11:53 am
Need assistance on creating a sql statement:
Goal: I have a table that has a single datetime data type that I need to see between two rows the data range list a start data and end date. So what I want to do is when this start and end range is created I can do a search againts the dates for a specific record.
My Table:
CREATE TABLE [dbo].[EFTV](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CMPY_CD] [varchar](10) NULL,
[PRDT_TYPE] [varchar](10) NULL,
[EFTV_DTS] [datetime] NULL,
[DLY_RATE] [numeric](12, 10) NULL
) ON [PRIMARY]
Doing a normal select on the table with and order by of EFTV_DTS results: Take note that ID 5, and 3 and their dates.
100000035004.15000000002009-01-05 00:00:00.000
200000035003.44000000002009-01-25 00:00:00.000
500000035003.44000000002009-02-01 00:00:00.000
300000035003.68158000002009-02-24 00:00:00.000
1000000035004.16000000002009-05-31 00:00:00.000
1700000035004.07000000002009-10-01 00:00:00.000
1900000035001.00000000002009-11-10 00:00:00.000
2000000035004.07000000002009-11-24 00:00:00.000
2400000035003.68158000002010-01-01 00:00:00.000
I've worked out the following SQL statement which seems ok if all the records are written in perfect order but again keep in mind of ID 3 and 5
select a.id, a.CMPY_CD, a.PRDT_TYPE, a.DLY_RATE,
a.EFTV_DTS as [Start Date], b.EFTV_DTS as [End Date]
from EFTV a
inner join EFTV b
on b.Id = a.Id+1
Where a.PRDT_TYPE = '3500'
the results from this query
100000035004.15000000002009-01-05 00:00:00.0002009-01-25 00:00:00.000
200000035003.44000000002009-01-25 00:00:00.0002009-02-24 00:00:00.000
300000035003.68158000002009-02-24 00:00:00.0002009-02-08 00:00:00.000
500000035003.44000000002009-02-01 00:00:00.0002009-03-15 00:00:00.000
1000000035004.16000000002009-05-31 00:00:00.0002009-06-05 00:00:00.000
1700000035004.07000000002009-10-01 00:00:00.0002009-10-31 00:00:00.000
1900000035001.00000000002009-11-10 00:00:00.0002009-11-24 00:00:00.000
2000000035004.07000000002009-11-24 00:00:00.0002009-12-01 00:00:00.000
Two things. One ID, 3 and 5 since are out of date order and second ID 24 does not appear I would like it to be the date of 1/1/2010 for the start and the end to be a default of current date + one day or year not a big deal what that date value is but having that record in the listing is important.
To get around the 3, 5 issue I'm thinking of create a table object with it own id column that would help out a lot in terms of pefect order and the existing query would work well. Ceating that final start and end record (ID 24) with all records I'm not working out correctly.
any suggestions or a better solution for me to find the effective record based on passed in date value?
thanks!
November 13, 2009 at 12:30 pm
Can you also provide some insert scripts to insert data into that table for testing at our end?
That would help to understand more.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 13, 2009 at 12:58 pm
Was able to work it out with some people on another forum
;WITH RowNumsAdded AS
(
SELECT *,RowNum=ROW_NUMBER() OVER (ORDER BY EFTV_Dts)
FROM EFTV
WHERE Prdt_Type='3500'
)
SELECT a.ID, a.Cmpy_Cd, a.Prdt_Type, a.Dly_Rate, a.EFTV_Dts as [Start Date],
Case
WHEN b.EFTV_Dts IS NULL THEN DATEADD(year,10,a.EFTV_Dts)
ELSE b.EFTV_Dts
END
as [End Date]
FROM RowNumsAdded a
LEFT JOIN RowNumsAdded b on b.RowNum=a.RowNum+1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply