Locate Maximum Date 1

[willdevelope]

Product
Warehouse_ID product_enter_dt
1 2008-07-01
1 2007-07-01
2 2008-10-01

Inventory
Warehouse_ID Inventory_Enter_dt
1 2002-01-01
1 2009-05-01
2 2007-11-30

Store
Warehouse_ID Store_Enter_Dt
1 2005-04-01
1 2008-07-01
2 2005-11-30


I would like to create a stored procedure that return the latest date of all three tables by passing the global_Id as the parameter

For example , if I pass Warehouse_Id = 1 in a stored Procedure , it should return 2009-05-01

 

[gmmastros]

Untested:

Create Procedure dbo.GetMaxDate
  @Warehouse_Id
As

Set Nocount On

Select Max(AliasName.ThisDate) As MaxDate
From   (
        Select Max(Product_enter_dt) As ThisDate
        From   Product
        Where  Warehouse_Id = @Warehouse_id

        Union All
 
        Select Max(Inventory_Enter_Dt)
        From   Inventory
        Where  Warehouse_ID = @Warehouse_Id
   
        Union All

        Select Max(Store_Enter_Dt)
        From   Store
        Where  Warehouse_ID = @Warehouse_ID
        ) As AliasNAme

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[willdevelope]

Thanks alot gmastro, once again you have save the day

 

[willdevelope]

Just for the record, anybody looking at this code there is only one little flaw the parameter @Warehouse_Id was not assign a datatype such as datetime or varchar .But the logic is on point

example @Warehouse_Id as varchar