SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL View - Linking Two Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ladowali
Starting Member

Canada
16 Posts

Posted - 11/06/2013 :  10:48:49  Show Profile  Reply with Quote
Hi Group,
I am new to SQL Programming and trying to write a select statement to create the SQL View.
I have a two tables:
Table 1 :
Part Number - Unique
Inventory Qty

Table 2 :
PO Number
Line Number ( PO Number + Line Number - Unique )
Part Number
Price
PO Date

I need to do a select statement on Table 1 unique by part number and show the Latest price by po date for the linked part number from table 2.

Thanks
Lado

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/06/2013 :  11:22:33  Show Profile  Reply with Quote
Perhaps this could get you started:

create view myView
as

select t1.[Part Number]
       ,t1.[Inventory Qty]
       ,t2.Price as LatestPrice
       ,t2.[PO Date] as LatestPODate
from   table1 t1
cross apply (
       select top 1 
              [PO Number]
              ,Price
              ,[PO Date]
       from   table2
       where  [part Number] = t1.[part number]
       order by [PO Date] desc
       ) t2

go


Be One with the Optimizer
TG
Go to Top of Page

ladowali
Starting Member

Canada
16 Posts

Posted - 11/06/2013 :  11:58:46  Show Profile  Reply with Quote
quote:
Originally posted by TG

Perhaps this could get you started:

create view myView
as

select t1.[Part Number]
       ,t1.[Inventory Qty]
       ,t2.Price as LatestPrice
       ,t2.[PO Date] as LatestPODate
from   table1 t1
cross apply (
       select top 1 
              [PO Number]
              ,Price
              ,[PO Date]
       from   table2
       where  [part Number] = t1.[part number]
       order by [PO Date] desc
       ) t2

go


Be One with the Optimizer
TG



Hi TG,

Thanks for the quick reply.

Anyway, I have tested this and in fact it does works but trimming records from the original select statement. I want to show all records from the table 1 and show latest price from table 2 if link found other wise print price as 0.

Thanks
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/06/2013 :  12:07:20  Show Profile  Reply with Quote
I see. Then change the CROSS APPLY to OUTER APPLY and change the t2 columns to COALESCE:

,COALESCE(t2.Price, 0) as LatestPrice

Be One with the Optimizer
TG
Go to Top of Page

ladowali
Starting Member

Canada
16 Posts

Posted - 11/06/2013 :  13:03:34  Show Profile  Reply with Quote
quote:
Originally posted by TG

I see. Then change the CROSS APPLY to OUTER APPLY and change the t2 columns to COALESCE:

,COALESCE(t2.Price, 0) as LatestPrice

Be One with the Optimizer
TG



Hi TG,
Ok, it works.
Can I use LatestPrice ( which is a combination of expression in to another sql column expression like LatestPrice*Qty As TotalValue ?

Thanks
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/06/2013 :  13:14:47  Show Profile  Reply with Quote
sure - like this:

select t1.[Part Number]
       ,t1.[Inventory Qty]
       ,coalesce(t2.Price, 0) as LatestPrice
       ,coalesce(t2.Price, 0) * t1.[Inventory Qty] as TotalValue
       ,t2.[PO Date] as LatestPODate
from   table1 t1
outer apply (
       select top 1 
              [PO Number]
              ,Price
              ,[PO Date]
       from   table2
       where  [part Number] = t1.[part number]
       order by [PO Date] desc
       ) t2


Be One with the Optimizer
TG
Go to Top of Page

ladowali
Starting Member

Canada
16 Posts

Posted - 11/06/2013 :  13:44:19  Show Profile  Reply with Quote
quote:
Originally posted by TG

sure - like this:

select t1.[Part Number]
       ,t1.[Inventory Qty]
       ,coalesce(t2.Price, 0) as LatestPrice
       ,coalesce(t2.Price, 0) * t1.[Inventory Qty] as TotalValue
       ,t2.[PO Date] as LatestPODate
from   table1 t1
outer apply (
       select top 1 
              [PO Number]
              ,Price
              ,[PO Date]
       from   table2
       where  [part Number] = t1.[part number]
       order by [PO Date] desc
       ) t2


Be One with the Optimizer
TG



Thanks a lot TG..

Lado
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000