[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

complex sql help

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
magento
Asked:
magento
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
magentoAuthor Commented:
Thanks Angellll for your quick response.

I will check the code in my server and let you know.
0
 
Duke_GeorgeCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
magentoAuthor Commented:
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
 
magentoAuthor Commented:
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
 
Duke_GeorgeCommented:
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
 
SharathData EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
magentoAuthor Commented:
Angelll,

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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now