Solved

complex sql help

Posted on 2014-01-24
9
305 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
ID: 39807171
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
ID: 39807192
Thanks Angellll for your quick response.

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

Expert Comment

by:Duke_George
ID: 39807213
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 5

Author Comment

by:magento
ID: 39807273
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
 
LVL 5

Author Comment

by:magento
ID: 39807333
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
ID: 39807356
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
ID: 39808030
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]
ID: 39808534
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
ID: 39808553
Angelll,

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore 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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

803 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