select - 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

SQL Server 2008 Forums

Transact-SQL (2008)

select

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-05-17 : 07:13:03

Hello,
Please see the sample data below in table tblMain

CustNo SNo VDate CFDate FNo CValue BS
255 1 2011-02-28 2010-11-01 2 67 1
255 2 2011-09-30 2012-01-01 2 88 1
251 1 2012-04-30 2012-05-01 2 12 1
255 3 2012-04-30 2012-04-01 2 99 -1
251 2 2012-05-30 2012-06-01 2 33 1
251 3 2012-06-30 2012-07-01 2 20 -1

Note that there are two rows with the same VDate which is 2012-04-03
Both these two rows have different CustNo and only one of them has BS as -1
Out of these two rows, I would like to remove the one with BS of -1

Question:
How can I write a sql so that I get the following please?
CustNo SNo VDate CFDate FNo CValue BS
255 1 2011-02-28 2010-11-01 2 67 1
255 2 2011-09-30 2012-01-01 2 88 1
251 1 2012-04-30 2012-05-01 2 12 1
251 2 2012-05-30 2012-06-01 2 33 1
251 3 2012-06-30 2012-07-01 2 20 -1

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 07:17:49

[code]
SELECT *
FROM
(
SELECT *, COUNT(1) OVER (PARTITION BY VDate) AS Cnt
FROM table
)t
WHERE Cnt = 1
OR BS <> -1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-05-17 : 07:18:53

I do not see two rows with the Vdate 2012-04-03. Did you post all set of data?

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-05-17 : 07:19:58

quote:
Originally posted by madhivanan

I do not see two rows with the Vdate 2012-04-03. Did you post all set of data?

Madhivanan

Failing to plan is Planning to fail

Apologiese. I mean 2012-04-30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 07:21:17

quote:
Originally posted by arkiboys

quote:
Originally posted by madhivanan

I do not see two rows with the Vdate 2012-04-03. Did you post all set of data?

Madhivanan

Failing to plan is Planning to fail

Apologiese. I mean 2012-04-30

i've a solution based on posted data
see if thats what you're after

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-05-17 : 07:22:34

quote:
Originally posted by visakh16

SELECT *
FROM
(
SELECT *, COUNT(1) OVER (PARTITION BY VDate) AS Cnt
FROM table
)t
WHERE Cnt = 1
OR BS <> -1

That is not right
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 07:25:22

quote:
Originally posted by arkiboys

quote:
Originally posted by visakh16

SELECT *
FROM
(
SELECT *, COUNT(1) OVER (PARTITION BY VDate) AS Cnt
FROM table
)t
WHERE Cnt = 1
OR BS <> -1

That is not right
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tell us why rather than blindly telling its not right
are you getting some error?
Is output different? in which case show us what you get and what you expect. Otherwise we can only keep guessing!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-05-17 : 08:05:58

select t1.* from table as t1 inner join
(
select Vdate, max(BS) as Bs from table group by Vdate
) as t2 on t1.Vdate=t2.Vdate and t1.Bs=t2.Bs

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-05-17 : 08:18:16

Thank you all.
I was doing something wrong.
Your solutions are good.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources