September 28, 2018 at 4:19 am
Hi,
Can some one help me in writing query for below scenario.
I have a table like below
| Id | Code | Act_Date | Flag |
| 1 | abc | 13-Nov-06 | N |
| 1 | abc | 14-Nov-06 | Y |
| 1 | abc | 15-Nov-06 | Y |
| 1 | xyz | 25-Nov-06 | N |
| 1 | xyz | 26-Nov-06 | Y |
| 1 | xyz | 27-Nov-06 | Y |
| 1 | xyz | 28-Nov-06 | Y |
In the above table Act_Date is unique, no two records will have same records with same ID.
Act_Date can be same for different Id.
I would need to get all records between 'N' to 'N' i.e,.
| Id | Code | Act_Date | Flag |
| 1 | abc | 13-Nov-06 | N |
| 1 | abc | 14-Nov-06 | Y |
| 1 | abc | 15-Nov-06 | Y |
| Id | Code | Act_Date | Flag |
| 1 | xyz | 25-Nov-06 | N |
| 1 | xyz | 26-Nov-06 | Y |
| 1 | xyz | 27-Nov-06 | Y |
| 1 | xyz | 28-Nov-06 | Y |
Like above i need to get set of records started with Flag 'N' and all records upto next 'N'
Is there any easy way to achieve this?
Regards
BK
September 28, 2018 at 6:46 am
balakrishna 8530 - Friday, September 28, 2018 4:19 AMHi,
Can some one help me in writing query for below scenario.I have a table like below
Id Code Act_Date Flag 1 abc 13-Nov-06 N 1 abc 14-Nov-06 Y 1 abc 15-Nov-06 Y 1 xyz 25-Nov-06 N 1 xyz 26-Nov-06 Y 1 xyz 27-Nov-06 Y 1 xyz 28-Nov-06 Y In the above table Act_Date is unique, no two records will have same records with same ID.
Act_Date can be same for different Id.
I would need to get all records between 'N' to 'N' i.e,.
Id Code Act_Date Flag 1 abc 13-Nov-06 N 1 abc 14-Nov-06 Y 1 abc 15-Nov-06 Y
Id Code Act_Date Flag
1 xyz 25-Nov-06 N 1 xyz 26-Nov-06 Y 1 xyz 27-Nov-06 Y 1 xyz 28-Nov-06 Y Like above i need to get set of records started with Flag 'N' and all records upto next 'N'
Is there any easy way to achieve this?
Regards
BK
Take a good look at the sample data you've provided and you'll soon realize that for the specific data you've presented, SELECT * FROM YourTableNameHere would be sufficient. We can't solve a problem that doesn't exist without knowledge of the various scenarios that can occur within the data. No sample data that is representative of the difficulty and we've got nothing to work with. We can't invent that kind of sample data without potentially distorting the reality of your situation, so you need to provide it. Please post your data as CREATE TABLE statements (please use temp table names) and appropriate INSERT statements for the data. Real production data is not what we are looking for. Just need sample data that clearly represents the difficulty you are experiencing. If this is homework or a test assignment, then you'll have to do a lot more of the work yourself, and only post back when you have tried some code and it didn't work.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 28, 2018 at 1:32 pm
I think your example isn't good, but it looks like you're looking for the first N for each code and then report all Y until you get to the next N. Try this
drop table if exists #t
go
create table #t (id int,
Code char(3),
act_date date,
Flag bit)
insert into #t
values
(1,'abc','13-Nov-06',0),
(1,'abc','14-Nov-06',1),
(1,'abc','15-Nov-06',1),
(1,'abc','16-Nov-06',0),
(1,'xyz','25-Nov-06',0),
(1,'xyz','26-Nov-06',1),
(1,'xyz','27-Nov-06',1),
(1,'xyz','28-Nov-06',1)
;with cte_N as
(
select code, min(Act_date) Act_Date
from #t
where Flag = 0
group by Code
),
cte_y as
( select t.code, min(t.Act_date) Act_Date
from #t t
join cte_N n
on t.Code = n.Code
and t.act_date > n.Act_date
and t.flag = 0
group by t.code
),
cte_all as
(select n.Code, n.Act_date MinDate, IsNull(y.act_date, DateAdd(d, 1, getdate())) MaxDate
from cte_n n
left join cte_y y
on n.code = y.code)
select t.*
from #t t
join cte_all a
on t.code = a.code
and t.Act_Date >= a.MinDate
and t.act_date < a.MaxDate
select * from #t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 28, 2018 at 2:06 pm
Mike01 - Friday, September 28, 2018 1:32 PMI think your example isn't good, but it looks like you're looking for the first N for each code and then report all Y until you get to the next N. Try this
drop table if exists #t
gocreate table #t (id int,
Code char(3),
act_date date,
Flag bit)insert into #t
values
(1,'abc','13-Nov-06',0),
(1,'abc','14-Nov-06',1),
(1,'abc','15-Nov-06',1),
(1,'abc','16-Nov-06',0),
(1,'xyz','25-Nov-06',0),
(1,'xyz','26-Nov-06',1),
(1,'xyz','27-Nov-06',1),
(1,'xyz','28-Nov-06',1);with cte_N as
(
select code, min(Act_date) Act_Date
from #t
where Flag = 0
group by Code
),
cte_y as
( select t.code, min(t.Act_date) Act_Date
from #t t
join cte_N n
on t.Code = n.Code
and t.act_date > n.Act_date
and t.flag = 0
group by t.code
),
cte_all as
(select n.Code, n.Act_date MinDate, IsNull(y.act_date, DateAdd(d, 1, getdate())) MaxDate
from cte_n n
left join cte_y y
on n.code = y.code)select t.*
from #t t
join cte_all a
on t.code = a.code
and t.Act_Date >= a.MinDate
and t.act_date < a.MaxDateselect * from #t
The following will give you the exact same results with only one scan of the table.
WITH CTE AS
(
SELECT *, SUM(CASE WHEN t.flag = 0 THEN 1 ELSE 0 END) OVER(PARTITION BY id, code ORDER BY act_date ROWS UNBOUNDED PRECEDING) AS grp
FROM #t t
)
SELECT c.id, c.Code, c.act_date, c.Flag
FROM CTE c
WHERE c.grp = 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply