Solved

complex sql help

Posted on 2014-01-24
9
309 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 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

630 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