Query Help

Question

Post reply

Query Help

jagat.patel

SSC Eights!

Points: 908
More actions
May 9, 2013 at 3:06 pm

#293767

I have following table

ID ActiveFalg Date

1 0 1/1/2013

1 0 2/1/2013

1 1 3/1/2013

1 0 4/1/2013

2 0 1/1/2013

2 1 2/1/2013

2 1 4/1/2013

I am looking for result output on which date active flag changes from for particular id

like this

1 1 3/1/2012

1 0 4/1/2013

2 1 4/1/2013

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

jagat.patel (5/9/2013)

I have following table

ID ActiveFalg Date

1 0 1/1/2013

1 0 2/1/2013

1 1 3/1/2013

1 0 4/1/2013

2 0 1/1/2013

2 1 2/1/2013

2 1 4/1/2013

I am looking for result output on which date active flag changes from for particular id

like this

1 1 3/1/2012

1 0 4/1/2013

2 1 4/1/2013

...bit puzzled ..why

2 1 4/1/2013

?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

jagat.patel (5/9/2013)

I have following table

ID ActiveFalg Date

1 0 1/1/2013

1 0 2/1/2013

1 1 3/1/2013

1 0 4/1/2013

2 0 1/1/2013

2 1 2/1/2013

2 1 4/1/2013

I am looking for result output on which date active flag changes from for particular id

like this

1 1 3/1/2012

1 0 4/1/2013

2 1 4/1/2013

Can you explain the business rules for this?

What about 2 0 1/1/2013?

This would be a lot easier if you posted ddl and sample data. You have been around here long enough to know what that is helpful.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

Assuming that you really want 4 rows...

Is this close???



create table Something

(

	ID int,

	ActiveFlag bit,

	SomeDate datetime

)

insert Something

select 1, 0, '1/1/2013' union all

select 1, 0, '2/1/2013' union all

select 1, 1, '3/1/2013' union all

select 1, 0, '4/1/2013' union all

select 2, 0, '1/1/2013' union all

select 2, 1, '2/1/2013' union all

select 2, 1, '4/1/2013'

select ID, ActiveFlag, MAX(SomeDate)

from something

group by ID, ActiveFlag

order by ID, ActiveFlag desc

I posted the ddl and sample data for you so you can see what I mean.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jagat.patel SSC Eights! Points: 908 More actions · Answer 4

jagat.patel

SSC Eights!

Points: 908

May 9, 2013 at 3:24 pm

#1613988

Thanks

ddl looks good

O/P i am looks at is all dates when flag changes it's value

so in case of

id = 1 dates are

3/1/2013 and 4/1/2013

and for id 2 date is 4/1/2013

Thanks

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Lynn Pettis

SSC Guru

Points: 442447

May 9, 2013 at 3:28 pm

#1613990

jagat.patel (5/9/2013)

Thanks

ddl looks good

O/P i am looks at is all dates when flag changes it's value

so in case of

id = 1 dates are

3/1/2013 and 4/1/2013

and for id 2 date is 4/1/2013

Thanks

But the Active Flag for ID 2 changed from 0 to 1 on 2/1/2013, so why report 4/1/2013?

jagat.patel SSC Eights! Points: 908 More actions · Answer 6

jagat.patel

SSC Eights!

Points: 908

May 9, 2013 at 3:31 pm

#1613992

sorry you are correct.

mickyT SSChampion Points: 10360 More actions · Answer 7

So something like

SELECT * into #something



FROM (

select 1 ID, 0 ActiveFlag, '1/1/2013' SomeDate union all

select 1, 0, '2/1/2013' union all

select 1, 1, '3/1/2013' union all

select 1, 0, '4/1/2013' union all

select 2, 0, '1/1/2013' union all

select 2, 1, '2/1/2013' union all

select 2, 1, '4/1/2013'

) s;

with seq as (

	SELECT ID, ActiveFlag, SomeDate

		,row_number() OVER (PARTITION BY ID ORDER BY SomeDate) N

	FROM #Something

	)

SELECT a.ID, a.ActiveFlag, a.SomeDate

FROM seq a

	INNER JOIN seq b ON a.ID = b.ID and a.activeFlag <> b.ActiveFlag and a.N = b.N + 1

ORDER BY a.ID, a.somedate

jagat.patel SSC Eights! Points: 908 More actions · Answer 8

jagat.patel

SSC Eights!

Points: 908

May 9, 2013 at 3:58 pm

#1614008

Thanks

this helped