March 30, 2017 at 12:14 pm
Hi All,
I have a report to develop for which I am writing a query.
I need help with getting correct startdate for member eligibilty..
I have 3 members with different ID's 1003,2003,4003
we have eligibility defined for every year in separate row.. with a startdate and end date.
but for some there is a gap example member :1003 has no eligibility in 2015.
in the report I have to go back only 2 years from today's date i.e. till 3/30/2015. and capture the start date from that span and present it.
but for 1003 as i don't have any data for 2015, it is capturing 2014 dates. I want it to capture the 2016 date instead as we cannot send any data before 2015.
any help with this.
thanks in advance
Sql code is here:
create table #temp
(
id varchar(20), stdate varchar(20),enddate varchar(20)
)
insert into #temp values( '1003','20140501','20141231')
insert into #temp values ( '1003','20160101','20161231')
insert into #temp values ( '1003','20170101','20171231')
insert into #temp values ( '2003','20140401','20141231')
insert into #temp values( '2003','20150101','20151231')
insert into #temp values ( '2003','20160101','20161231')
insert into #temp values ( '2003','20170101','20171231')
insert into #temp values ( '4003','20130101','20151231')
insert into #temp values ( '4003','20160101','20161231')
insert into #temp values ( '4003','20170101','20171231')
-------------------------------------------
DECLARE @BeginningOfYear VARCHAR(20)
SELECT
@BeginningOfYear = CAST(YEAR(GETDATE()) - 2 AS varchar(10)) + '-01-01'
SELECT *
FROM (SELECT *, Ltrim(Rtrim(Str(b.stdate))) AS min_STDT,
Row_number ()
OVER (
partition BY b.id
ORDER BY b.id, b.stdate desc ) AS RN
FROM (
SELECT DISTINCT
id,
stdate ,
enddate
-- else a.estdt
FROM #temp a
where
-- and Ltrim(Rtrim(Str(a.estdt))) >=REPLACE(@BeginningOfYear, '-', '')
REPLACE(@BeginningOfYear, '-', '') BETWEEN Ltrim(Rtrim(Str(a.stdate))) AND CONVERT(VARCHAR(8), Getdate(), 112)
-- and Ltrim(Rtrim(Str(a.estdt))) >= REPLACE(@BeginningOfYear, '-', '')
) b
) c
WHERE rn = 1
Thanks [/font]
March 30, 2017 at 12:56 pm
a few q's......you have posted in SQL 2005 forum...pls confirm this is the version you are working on.
can you post expected results from your sample set up...so we know what you actually wish to achieve to xref our solutions
are you actually storing dates as varchars?...or is this just how you set up your rest data?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 30, 2017 at 12:58 pm
I made a few changes, such as using int for id instead of varchar(20), datetime for instead of varchar(20) for the dates.
does the following provide what you are trying to accomplish?
create table #temp(
id int -- varchar(20),
, stdate datetime -- varchar(20),
, enddate datetime --varchar(20)
)
insert into #temp values(1003,'20140501','20141231');
insert into #temp values(1003,'20160101','20161231');
insert into #temp values(1003,'20170101','20171231');
insert into #temp values(2003,'20140401','20141231');
insert into #temp values(2003,'20150101','20151231');
insert into #temp values(2003,'20160101','20161231');
insert into #temp values(2003,'20170101','20171231');
insert into #temp values(4003,'20130101','20151231');
insert into #temp values(4003,'20160101','20161231');
insert into #temp values(4003,'20170101','20171231');
-------------------------------------------
DECLARE @BeginningOfYear datetime -- VARCHAR(20)
SELECT @BeginningOfYear = DATEADD(year,datediff(year,0,getdate()),0) --CAST(YEAR(GETDATE()) - 2 AS varchar(10)) + '-01-01'
with basedata as (
select
id
, stdate
, enddate
, rn = row_number() over (partition by id order by stdate)
from
#temp
where
DATEADD(year,-2,getdate()) < enddate-- and GETDATE()
)
select
id
, stdate
, enddate
, convert(varchar(10), stdate, 120) MinStartDate
from
basedata
where
rn = 1;
March 30, 2017 at 1:40 pm
Thank you Lynn Pettis
I can't believe this came with such simple condition..i was breaking and fixing for last 2 days to get this right..
I still made one more change to my code as i was having following in Row_number() ORDER BY ID, stdt DESC
i had to remove DESC form here..
You guys are genius!
Thanks for your help again..
Thanks [/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply