SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1398 Posts

Posted - 05/17/2013 :  07:13:03  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 05/17/2013 :  07:17:49  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 05/17/2013 :  07:18:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1398 Posts

Posted - 05/17/2013 :  07:19:58  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/17/2013 :  07:21:17  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1398 Posts

Posted - 05/17/2013 :  07:22:34  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/17/2013 :  07:25:22  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 05/17/2013 :  08:05:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1398 Posts

Posted - 05/17/2013 :  08:18:16  Show Profile  Reply with Quote
Thank you all.
I was doing something wrong.
Your solutions are good.
Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000