September 2, 2011 at 12:07 am
i have a dim table having efct_start_dt and efct_end_dt and strategy_id.i want to pick up the record
with efct_end_dt as null (active record) and the strategy_id should have appeared only once i.e. it should not have expired before
September 2, 2011 at 12:13 am
Has dim table with multiple entries with same stratogy id with date field as null?
Please provide the table structure and sample data to help you better.
September 2, 2011 at 12:13 am
select count,strategy_id from (select count(*) count ,strategy_id from publish.dim_strategy
group by strategy_id) sub
where count=1
This would give the strategy_id that appeared once but i want to make sure that efct_end_dt is null
September 2, 2011 at 12:15 am
the strategy_id has multiple entries .One of these entry may have efct_end_dt as null if the strategy_id has expired.The unique key in table is strategy_key which is a surrogate key
September 2, 2011 at 12:22 am
To help you better please provide you table structure....
I design what I got from your post. Let me know is this correct?
Create Table #T1 (Stratogy_ID int, efct_end_dt Date)
Insert into #T1 values (1,NULL)
Insert into #T1 Values(1,Getdate())
Insert into #T1 Values (2,NULL)
Insert into #T1 Values (3,NULL)
Select * From #T1
Select Stratogy_id,efct_end_dt From #T1 Where efct_end_dt is null
September 2, 2011 at 12:39 am
yes it is like this..i want efct_start_dt,strategy_id of 2 and 3..i dont want strategy_id 1 as it has 2 rows in the table
September 2, 2011 at 12:45 am
Select Stratogy_id,efct_end_dt From #T1
Where Stratogy_ID in
(
Select Stratogy_ID
From #T1
Where efct_end_dt is null
Group by Stratogy_ID having COUNT(Stratogy_ID)=1
)
Will the baove help you. (It looks like a home work!!!);-)
September 2, 2011 at 1:11 am
your query is giving me strategy_id 1 also.I dont want this because it has already expired and not coming for the first time.
My below query gives me the result but i was thinking of a simpler query
select pub.efct_end_dt,sub1.stratogy_id from (select count,stratogy_id from (select count(*) count ,stratogy_id from #t1
group by stratogy_id) sub
where count=1)sub1
inner join
#t1 pub
on sub1.stratogy_id=pub.stratogy_id
and pub.efct_end_dt is null
September 2, 2011 at 1:27 am
Sorry to say that my query and your query returns the same data with the design I provided.
It does not return 1. I am not sure how 1 is returning for you. There might be different data for you then probably in your table.
Its always better to give your table structure and sample data. Also provide the data you may want as resultset. I can only help you by providing this.
September 2, 2011 at 1:37 am
Your query is giving the same results as mine..just efct_end_dt is null should have been outside the brackets..(i commented the text in your query)
Anyways,your idea has solved my problem.Many thanks:)
Select Strategy_id,efct_end_dt From publish.dim_strategy
Where Strategy_ID in
(
Select Strategy_ID
From publish.dim_strategy
--Where efct_end_dt is null
Group by Strategy_ID having COUNT(Strategy_ID)=1
)
and efct_end_dt is null
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply