• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

Query

I have 2 tables the first one for items and the second is for prices , I need to get 2 columns for prices from same column base on date filter for eg.:

1- T1                                                2-T2
ID      Name                                    T1ID        Price     Date
--------------------                               ----------------------------------
1      Book                                          1            10         1/1/2012  
2      Pen                                            1            11          1/8/2012
                                                           2            5            1/8/2012

If the filter is Where date >= 1/1/2008, the result be:
ID    Name      Price Before         Price After
------------------------------------------------------------
1      Book           10                             11
2       Pen             0                               5

Thanks
0
samprg
Asked:
samprg
  • 4
  • 4
  • 2
2 Solutions
 
lwadwellCommented:
try
SELECT t1.ID, t1,Name, ISNULL(t2b.Price,0) [Price Before], ISNULL(t2a.Price,0) [Price After]
FROM T1
LEFT JOIN T2 t2b ON t1.ID = t2b.T1ID AND t2b.Date < '01-01-2008'
LEFT JOIN T2 t2a ON t1.ID = t2a.T1ID AND t2a.Date >= '01-01-2008'

Open in new window

0
 
Habib PourfardSoftware DeveloperCommented:
DECLARE @date DATETIME
SET @date = '1/1/2012'

SELECT DISTINCT
        T1.ID
       ,T1.NAME
       ,ISNULL((SELECT TOP 1 Price FROM T2 WHERE T1.ID = T2.T1ID AND T2.Date <= @Date ORDER BY T2.Date DESC), 0) AS [Price Before]
       ,ISNULL((SELECT TOP 1 Price FROM T2 WHERE T1.ID = T2.T1ID AND T2.Date > @Date ORDER BY T2.Date DESC), 0) AS [Price After]
FROM    T1 INNER JOIN T2 ON T1.ID = T2.T1ID

Open in new window

0
 
samprgAuthor Commented:
Guys,
Sorry,I had to move T1.Name to T2 table, so T2 now like
T2
Name        Price          Date
------------------------------------
  Book         10         1/1/2012  
  Book         11          1/8/2012
  Pen             5            1/8/2012
 So can you update your query work on T2 only?  please , Thanks for help
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
lwadwellCommented:
Three example rows does not make a good basis to design a statement that may be used in the real world.  You have the 'no price before the date' scenario, but what about:
  - multiple prices before the date?
  - multiple prices after the date?
  - no prices after the date?

Here is a selection of possible solutions ...  they have different strengths and weaknesses.
with t2 as (
select 'Book'  as Name, 10 as Price, cast('1/1/2012' as date) as [date] union all  
select 'Book'  as Name, 11 as Price, cast('1/8/2012' as date) as [date] union all
select 'Pen'   as Name,  5 as Price, cast('1/8/2012' as date) as [date] union all
select 'Quill' as Name, 55 as Price, cast('1/2/1932' as date) as [date] union all
select 'Quill' as Name, 35 as Price, cast('7/5/2005' as date) as [date] union all
select 'Quill' as Name, 15 as Price, cast('3/1/2012' as date) as [date] union all
select 'Quill' as Name,  6 as Price, cast('7/6/2012' as date) as [date] union all
select 'Leads' as Name, 33 as Price, cast('9/9/2000' as date) as [date]
)
/* --------------------------------------------------------
   In the simplest form ... with only the 3 rows this 
   will work.  Not so good for Quill's ...
-------------------------------------------------------- */
select 'Method 1' as example, Name
     , MAX(CASE WHEN [date] < '1/8/2012' THEN Price ELSE 0 END) as price_before
     , MAX(CASE WHEN [date] < '1/8/2012' THEN 0 ELSE Price END) as price_after
from t2
group by Name
UNION ALL
/* --------------------------------------------------------
   But in a real world scenario, the items could have many
   prices before and after the date - if you want the 
   prices nearest the date then try this.
-------------------------------------------------------- */
select 'Method 2' as example, Name
     , MAX(CASE WHEN dayssign < 0 THEN Price ELSE 0 END) as price_before
     , MAX(CASE WHEN dayssign < 0 THEN 0 ELSE Price END) as price_after
from (select Name, Price, [date], sign(datediff(d,'1/8/2012',[date])) as dayssign
           , row_number()over(partition by Name, sign(datediff(d,'1/8/2012',[date])) 
                                  order by abs(datediff(d,'1/8/2012',[date]))) as rn
        from t2)v
where rn = 1
GROUP BY Name
UNION ALL
/* --------------------------------------------------------
   Similar to Method 2 EXCEPT - if you want the prices
   nearest before the date and the most recent price
   to now (assuming no future prices).
-------------------------------------------------------- */
select 'Method 3' as example, Name
     , MAX(CASE WHEN dayssign < 0 THEN Price ELSE 0 END) as price_before
     , MAX(CASE WHEN dayssign < 0 THEN 0 ELSE Price END) as price_after
from (select Name, Price, [date], sign(datediff(d,'1/8/2012',[date])) as dayssign
           , row_number()over(partition by Name, sign(datediff(d,'1/8/2012',[date])) 
                                  order by datediff(d,'1/8/2012',[date]) DESC) as rn
        from t2)v
where rn = 1
GROUP BY Name
UNION ALL
/* --------------------------------------------------------
   Method 2 Alternative - different technique.
-------------------------------------------------------- */
select DISTINCT 'Method 4' as example, Name
     , ISNULL((SELECT TOP 1 Price FROM T2 b WHERE T2.Name = b.Name AND b.Date <  '1/8/2012' ORDER BY b.Date DESC), 0) as price_before
     , ISNULL((SELECT TOP 1 Price FROM T2 b WHERE T2.Name = b.Name AND b.Date >= '1/8/2012' ORDER BY b.Date), 0) as price_after
from t2
UNION ALL
/* --------------------------------------------------------
   Method 3 Alternative - different technique.
-------------------------------------------------------- */
select DISTINCT 'Method 5' as example, Name
     , ISNULL((SELECT TOP 1 Price FROM T2 b WHERE T2.Name = b.Name AND b.Date <  '1/8/2012' ORDER BY b.Date DESC), 0) as price_before
     , ISNULL((SELECT TOP 1 Price FROM T2 b WHERE T2.Name = b.Name AND b.Date >= '1/8/2012' ORDER BY b.Date DESC), 0) as price_after
from t2

Open in new window

0
 
Habib PourfardSoftware DeveloperCommented:
DECLARE @date DATETIME
SET @date = '1/1/2012'

SELECT DISTINCT
       T2.NAME
       ,ISNULL((SELECT TOP 1 Price FROM T2 A WHERE T2.Name = A.Name AND A.Date <= @Date ORDER BY A.Date DESC), 0) AS [Price Before]
       ,ISNULL((SELECT TOP 1 Price FROM T2 B WHERE T2.Name = B.Name AND B.Date > @Date ORDER BY B.Date DESC), 0) AS [Price After]
FROM    T2 

Open in new window

0
 
samprgAuthor Commented:
lwadwell:

 I have tried use your query "Method 4" without DISTINCT, because I need multiple prices after the datev but I did not get the right numbers
, so Please I nedd multiple prices after the date, can you update your query, it will be perfect .thanks
0
 
lwadwellCommented:
Please provide example data and expected results so we can understand the requirement.
0
 
samprgAuthor Commented:
as your last post
with t2 as (
select 'Book'  as Name, 10 as Price, cast('1/1/2012' as date) as [date] union all  
select 'Book'  as Name, 11 as Price, cast('1/8/2012' as date) as [date] union all
select 'Pen'   as Name,  5 as Price, cast('1/8/2012' as date) as [date] union all
select 'Quill' as Name, 55 as Price, cast('1/2/1932' as date) as [date] union all
select 'Quill' as Name, 35 as Price, cast('7/5/2005' as date) as [date] union all
select 'Quill' as Name, 15 as Price, cast('3/1/2012' as date) as [date] union all
select 'Quill' as Name,  6 as Price, cast('7/6/2012' as date) as [date] union all
select 'Leads' as Name, 33 as Price, cast('9/9/2000' as date) as [date]
)

The expected results with date filter '1/8/2012' be :
example        Name      price_before       price_after
Method 4   Book      10                       11
Method 4        Leads      33                       0
Method 4        Pen      0                        5
Method 4        Quill      35                      15
                   Quill      35                      6
0
 
lwadwellCommented:
with t2 as (
select 'Book'  as Name, 10 as Price, cast('1/1/2012' as date) as [date] union all  
select 'Book'  as Name, 11 as Price, cast('1/8/2012' as date) as [date] union all
select 'Pen'   as Name,  5 as Price, cast('1/8/2012' as date) as [date] union all
select 'Quill' as Name, 55 as Price, cast('1/2/1932' as date) as [date] union all
select 'Quill' as Name, 35 as Price, cast('7/5/2005' as date) as [date] union all
select 'Quill' as Name, 15 as Price, cast('3/1/2012' as date) as [date] union all
select 'Quill' as Name,  6 as Price, cast('7/6/2012' as date) as [date] union all
select 'Leads' as Name, 33 as Price, cast('9/9/2000' as date) as [date]
)
/* --------------------------------------------------------
   Method 2 Alternative - different technique.
-------------------------------------------------------- */
select 'Method 4' as example, Name
     , ISNULL((SELECT TOP 1 Price FROM T2 b WHERE T2.Name = b.Name AND b.Date <  '1/8/2012' ORDER BY b.Date DESC), 0) as price_before
     , Price as price_after
from t2
where t2.Date >= '1/8/2012'

Open in new window

0
 
samprgAuthor Commented:
Awesome.
Thank you
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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