NEED URGENT HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NEED URGENT HELP

Author

Topic

R0H0NS
Starting Member

6 Posts

Posted - 2012-04-16 : 16:35:47

Hi,

I have a data in "myTable" as below.

seq_no Date Flag
1 01-Apr-2012 Y
2 02-Apr-2012 N
3 03-Apr-2012 N
4 04-Apr-2012 Y
5 05-Apr-2012 Y

Now, I want to get 'seq_no' when last time 'flag' went to 'Y'. That means, in my case last time Flag went to 'Y' on '04-Apr-2012' and after that it is 'Y' only.

So, please help me urgently to develop a sql query to obtain the same.
Please note that I have only query access to database, so I can write only SELECT queries.

Please please please help me urgently as I have to reply on highly escalated matter.

Thanks,
Rohan Desai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 16:44:15

quote:
Originally posted by R0H0NS

Hi,

I have a data in "myTable" as below.

seq_no Date Flag
1 01-Apr-2012 Y
2 02-Apr-2012 N
3 03-Apr-2012 N
4 04-Apr-2012 Y
5 05-Apr-2012 Y

Now, I want to get 'seq_no' when last time 'flag' went to 'Y'. That means, in my case last time Flag went to 'Y' on '04-Apr-2012' and after that it is 'Y' only.

So, please help me urgently to develop a sql query to obtain the same.
Please note that I have only query access to database, so I can write only SELECT queries.

Please please please help me urgently as I have to reply on highly escalated matter.

Thanks,
Rohan Desai


SELECT TOP 1 [Date]
FROM table t
OUTER APPLY (SELECT COUNT(*) AS Cnt
             FROM table
             WHERE Date > t.Date
             AND Flag='N'
            )t1
WHERE Flag='Y'
AND COALESCE(Cnt,0) = 0
ORDER BY [Date]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-16 : 16:54:16

WI'm posting this anyway since by the time I understood the problem Visakh had already posted the answer!

select top 1 t2.*
from @myTable t1
inner join @myTable t2 on t1.seq_no + 1 = t2.seq_no
where t1.Flag ='N' and t2.Flag = 'Y'
order by t2.seq_no desc

Jim

Everyday I learn something that somebody else already knew

R0H0NS
Starting Member

6 Posts

Posted - 2012-04-16 : 16:54:23

Visakh,

Thanks for the quick help.

Currently, I don't have platform to test your code. I will test this and let you know the results.
Just want to ensure that will your code give me an output as '4'(seq_no)? Because, I will use the output of this query as an input to my original query....that's why I am asking this....

Thanks again for your prompt response.

Regards,
Rohan

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-16 : 16:57:57

You have to reply urgently to an escalated matter but you can't test the code?

Jim

Everyday I learn something that somebody else already knew

R0H0NS
Starting Member

6 Posts

Posted - 2012-04-16 : 17:05:50

Jim,
Actually the system is down for maintenance purpose and it will be up in next 2 hours....I was trying since whole day to get this done....but didn't met the success. So, in this downtime I thought to obtain some advises from experts.

Thanks to you as well for your timely help.

Regards,
Rohan

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-16 : 17:06:53

You're welcome, hope it all turns out okay.

Jim

Everyday I learn something that somebody else already knew

R0H0NS
Starting Member

6 Posts

Posted - 2012-04-17 : 06:34:46

Hi experts,

None of the above 2 codes are working for me...

Error is "ORA-00923: FROM keyword not found where expected"

Please help urgently.

Regards,
Rohan

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-17 : 06:40:19

This is a MICROSOFT SQL SERVER site. You are using ORACLE. Try http://www.dbforums.com/oracle/

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

R0H0NS
Starting Member

6 Posts

Posted - 2012-04-17 : 07:15:45

Hi,

I will use link mentioned by you, but if you are aware of the solution, please help me.

I have to reply on highly escalated matter.

Thanks,
Rohan

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-04-17 : 08:36:35

There are probably better ways but the following should have a good chance of working in later versions of Oracle:


WITH Grps
AS
(
	SELECT Flag, "Date"
		,ROW_NUMBER() OVER (ORDER BY Flag, "Date")
		  - ROW_NUMBER() OVER (ORDER BY "Date") AS Grp
	FROM myTable
)
,MinYDates
AS
(
	SELECT Flag, Grp
		,MIN("Date") AS "Date"
	FROM Grps
	WHERE Flag = 'Y'
	GROUP BY Flag, Grp
)
SELECT MIN(Seq_No) AS Seq_No
FROM myTable
WHERE Flag = 'Y'
	AND "Date" = (SELECT MAX("Date") FROM MinYDates);

R0H0NS
Starting Member

6 Posts

Posted - 2012-04-17 : 10:37:22

Hi Ifor,

I have tried your code, but its not giving any output.

Its showing as '0 rows selected'

Thanks,
Rohan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:53:13

quote:
Originally posted by R0H0NS

Hi Ifor,

I have tried your code, but its not giving any output.

Its showing as '0 rows selected'

Thanks,
Rohan

then post it in some Oracle forums

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources