Previous Status with previous date if current status <>...

[rogerzebra]

Hi guys,

I was hoping for some help with my little problem. I’m using previous status together with previous date if the current status is not what I want. The table is joined twice so I can use table b’s date is less then table’s a date to use previous date. The my problem is that when I use the group by clause the b.status column its not taking any regards to the max(date) statement in the where clause. Instead of showing me 1 single result it showing me all different statuses. I know this should be very easy to figure out but I can’t see what I’m doing wrong. So, please how do I filter it out so that the b.status only showing the current status together with the max(date). I appreciate all efforts. Thx

Select 	a.status
	b.status
	max(a.Date)
	max(b.Date)
from 	t1 a
	left join t1 b on a.x = b.x
		and a.Date > b.Date

where a.pol = ‘xxxxx’
and	b.Date = (select max(c.Date)
		    from t1 c
		    where c.id = b.id
		   )
Group by  a.status
	     b.status

 

[r937]

i don't see anything in your query that handles the situation "if the current status is not what I want"

also, i think you've over-generalized your sql, because i can't figure out whether you're joining on id columns or x columns

r937.com | rudy.ca

 

[rogerzebra]

937,
Thanks for taking a look at this. Sorry for my sloppy code version, you are right I forgot to add the case statement. This version should be correct. So, what I’m trying to retrieve is the following;

if the aStatus shows either “closed” or “bound” and if the aSubNo is not showing the same number as bSubNo, then show me the previous status (bStatus) together with the previous “max(bDate)” and just one single status result per policy.

When I comment out the case statement and the status fields, it retrieves the correct result for both a.date and b.date, a.date for current date and b.date for previous date. The problem occurs when I add the case statement and the status field in the select statement.

Select case	when a.status in ('closed','bound')		
			and a.subNo <> b.subNo 
			then(select c.status
			        from t1 c
			        where c.staus = b.status
			        and c.date in ( select max(d.date)
					            from t1 d
					            where d.date = c.date
						and c.date = b.date 
and d.id = c.id
                         			            and c.id = b.id 
					            )
							 
			         ) end as maxStatus
    --a.status
    --b.status
    max(a.Date)
    max(b.Date)
from     t1 a
   	 left join t1 b on a.id = b.id
        	and a.Date > b.Date

where a.pol = ‘xxxxx’
and    a.Date = (select max(a1.Date)
            	 from t1 a1
            	 where a1.id = a.id
           		)
Group by  b.status
         	     a.status
	     a.date
	     b.date
	     a.subNo
	     b.subNo

 

[r937]

sorry, i am lost

r937.com | rudy.ca