Solved

complex sql help

Posted on 2014-01-24
9
302 Views
Last Modified: 2014-01-25
Table A

[ID] [status]

1   4

2   4

3   4

4   4

5   4

6   4

7   4

Table B

 [ID]  [Old_status] [status_changed_date]

1   1   12/12/2012

2   1   12/12/2012

3   1   12/12/2012

4   1   12/12/2012

5   1   12/12/2012

1   2   12/20/2012

2   2   12/20/2012

3   2   12/20/2012

4   2   12/22/2012

5   2   12/22/2012

1   3   12/30/2012

2   3   12/30/2012

3   3   12/30/2012

4   3   12/30/2012

5   3   12/30/2012

6   3   12/10/2012

I need a query that returns: based on Variable date @date = 12/21/2012

I need old_status from table B based on the Min([status_changed_date]) where the [status_changed_date] > @date

When the ID is not in Table B or there is no date > @date then return status From Table A

[ID] [Old_status]

1   3

2   3

3   3

4   2

5   2

6   4

7   4

Thanks,
Magento
0
Comment
Question by:magento
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
this should do:
select a.ID
, nvl(b.status, a.status) status
from tableA a
left join tableB b
   on b.ID = a.ID
  and b.status_changed_date >= @date
  and b.status_changed_date = ( select min(x.status_changed_date)
                    from tableB x
                   where x.id = a.id
              and x.status_changed_date >= @date
                )
 

Open in new window


it can also be written differently, which would happen for eventually performance purposes.
0
 
LVL 5

Author Comment

by:magento
Comment Utility
Thanks Angellll for your quick response.

I will check the code in my server and let you know.
0
 

Expert Comment

by:Duke_George
Comment Utility
I would think you would want to put this in a stored procedure if you are going to keep reusing it, but you can do the following:
Declare @Date DATE = '12/21/2012'

Select a.ID,(Select Case when b.ID is null or b.status_changed_date < @Date then a.status else b.status where b.status_changed_date = Min(b.status_changed_date) End) as Old_Status
from A left outer join
B on a.ID=B.ID

if b.status_changed_date is a datetime type you will need to convert it to date to make it work correctly.
0
 
LVL 5

Author Comment

by:magento
Comment Utility
Hi Angelll,

I am getting multiple row for each id , please advice.

I have changed to NVL to coalesce for Mssql.

select a.ID
, coalesce(b.old_Asset_status, a.Asset_Status_ID ) as astatus
from [reo_web_data].[dbo].tbl_remis_inv a
left join [reo_web_data].[dbo].[tbl_global_status_tracker] b
on b.ID = a.ID
and b.status_changed_date >= @date
and b.status_changed_date = ( select min(x.status_changed_date)
from [reo_web_data].[dbo].[tbl_global_status_tracker] x
where x.ID = a.ID
and x.status_changed_date >= @date
)

Open in new window

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 5

Author Comment

by:magento
Comment Utility
Hi Duke,

I am getting syntax error for your query

Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'where'.

Select a.remis_ID,(Select Case when b.remis_ID is null or b.status_changed_date < @Date then a.asset_status else b.status where b.status_changed_date = Min(b.status_changed_date) End) as Old_asset_Status
from [reo_web_data].[dbo].tbl_remis_inv A left outer join
[reo_web_data].[dbo].[tbl_global_status_tracker] B on a.ID=B.ID

Open in new window

0
 

Expert Comment

by:Duke_George
Comment Utility
Ok.  Try putting the where statement at the end of the query.

Select a.remis_ID,(Select Case when b.remis_ID is null or b.status_changed_date < @Date then a.asset_status else b.status End) as Old_asset_Status
from [reo_web_data].[dbo].tbl_remis_inv A left outer join
[reo_web_data].[dbo].[tbl_global_status_tracker] B on a.ID=B.ID
where b.status_changed_date = Min(b.status_changed_date)
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
try this.
SELECT t1.ID, 
       COALESCE(t2.status, t1.status) status 
  FROM TableA t1 
       LEFT JOIN (SELECT *, 
                         ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY status_changed_date) rn 
                    FROM TableB 
                   WHERE status_changed_date > @date) t2 
              ON t2.rn = 1 
                 AND t1.ID = t2.ID 

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if you get multiple rows for a single ID; this means in table B you have several rows for the same ID + date values.
in that case, do you have another column in tableB that gives a "unique" value?

please see also this article which can help in that case:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 5

Author Comment

by:magento
Comment Utility
Angelll,

Yes, you are right . Thank you very much.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now