Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

complex sql help

Posted on 2014-01-24
9
Medium Priority
?
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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
Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

 
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 41

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 143

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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

660 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