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
 General SQL Server Forums
 New to SQL Server Programming
 how to select these rows.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sur200
Starting Member

India
7 Posts

Posted - 05/20/2013 :  02:07:48  Show Profile  Reply with Quote
Below are sample tables and sample date, please help
--create table c (c_id integer, p_id varchar(10))
--create table f (c_id integer,f_id integer,e_dt date,amt_gross integer,amt_matched integer)
--create table bf(bf_id integer,actual_dt date,c_id integer,p_id varchar(10))
--create table bfm(bfm_id integer, bf_id integer,f_id integer,amt integer,c_id integer,alloc_dt date)


/*

insert into c values(1,'A')
insert into c values(2,'B')
insert into c values(3,'A')
insert into c values(4,'C')
*/


/*

insert into f values(1,1,'1/1/2013',100,100)
insert into f values(1,2,'2/1/2013',100,0)
insert into f values(1,3,'3/1/2013',200,0)
insert into f values(1,4,'4/1/2013',50,0)

insert into f values(2,5,'1/10/2013',400,400)
insert into f values(2,6,'2/10/2013',500,0)
insert into f values(2,7,'3/10/2013',100,0)

insert into f values(3,8,'5/1/2013',100,0)
insert into f values(3,9,'6/1/2013',300,0)
*/


/*
insert into bf values (1,'1/10/2013',1,'A')
insert into bf values (2,'1/25/2013',1,'A')
insert into bf values (3,'3/01/2013',1,'A')
insert into bf values (5,'01/20/2013',2,'B')

*/


/*
insert into bfm values(1,1,1,25,1,'1/10/2013')
insert into bfm values(2,2,1,25,1,'1/27/2013')
insert into bfm values(3,3,1,50,1,'3/01/2013')
insert into bfm values(5,5,5,400,2,'01/20/2013')

*/

Below is query that I am trying but want to select different data:
declare @runDate date
set @runDate = '6/11/2013'

SELECT c.c_id,
f.e_dt,@runDate as RunDate,bfm.alloc_dt,bfm.amt as match,f.amt_gross as gross,
case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end as Days

from c c
inner join f f on c.c_id = f.c_id
left join bfm bfm on bfm.f_id = f.F_id
left join bf bf on bf.bf_id = bfm.bf_id

where f.e_Dt <= @runDate
order by c_id, days desc

so for c_id = 1 none of rows having MATCH and GROSS equal, so minimum e_dt record should come.
for c_id = 2, 400 amount row is matching so, I want to select next minimum e_dt for c_id = 2 and ignore row with 400 amount.
for c_id = 3, there are no rows having MATCH and GROSS same, so minimum e_dt record should come
current result is like this:
1 2013-01-01 2013-06-11 2013-01-10 25 100 152 --- this one should come
1 2013-01-01 2013-06-11 2013-01-27 25 100 137
1 2013-02-01 2013-06-11 NULL NULL 100 130
1 2013-03-01 2013-06-11 NULL NULL 200 102
1 2013-01-01 2013-06-11 2013-03-01 50 100 102
1 2013-04-01 2013-06-11 NULL NULL 50 71
2 2013-01-10 2013-06-11 2013-01-20 400 400 142
2 2013-02-10 2013-06-11 NULL NULL 500 121--- this one should come
2 2013-03-10 2013-06-11 NULL NULL 100 93
3 2013-05-01 2013-06-11 NULL NULL 100 41--- this one should come
3 2013-06-01 2013-06-11 NULL NULL 300 10

I want only highlighted records as explained above.

Any help or suggestions are highly appreciable.

TIA,
Surinder

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/20/2013 :  02:42:55  Show Profile  Reply with Quote
Hmm..Your explanation is not same as sample data

For ex you say for c_id = 1 none of rows having MATCH and GROSS equal but I cant see the row below in f table
insert into f values(1,1,'1/1/2013',100,100)

it has gross amd match values same

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

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/20/2013 :  13:49:00  Show Profile  Reply with Quote
Here you go:


SELECT * FROM 
(SELECT ROW_NUMBER() OVER (PARTITION BY c.c_id Order by COALESCE(alloc_dt, '1/1/3000'), 
(case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end) DESC) as RN, c.c_id,
f.e_dt,@runDate as RunDate,bfm.alloc_dt,COALESCE(bfm.amt, 0) as match,COALESCE(f.amt_gross, 0) as gross,
case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end as Days

from @c c
inner join f f on c.c_id = f.c_id
left join bfm bfm on bfm.f_id = f.F_id 
left join bf bf on bf.bf_id = bfm.bf_id 

where f.e_Dt <= @runDate and (COALESCE(bfm.amt, 0) <> COALESCE(f.amt_gross, 0))) A
WHERE RN = 1 order by c_id, days desc

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.05 seconds. Powered By: Snitz Forums 2000