-2

I need help, in building SQL (Query or CTE) for below scenario

Problem : I need to join my Master Table A with detail Table B to find out matching record/row(for each record of TableA) from Table B having same itemType, country having max(TransactionTime) && TransactionTime < EntryTime from table A for corresponding record.

OrderTable

 id itemType      country     EntryTime   
 1. Item1           IND      12:01:20:291 
 2. Item2           USA      14:11:22:299
 3. Item4           LON      18:01:17:112 
 4. Item1           SIN      20:05:30:020
 5. Item3           HKG      22:02:23:442

StockPrice Table

id  itemType      country   TransactionTime   Price
1.  Item1           IND      12:01:20:291     10.12
2.  Item2           USA      14:11:22:299     50.12
3.  Item4           LON      18:01:17:112     02.12
4.  Item1           SIN      20:05:30:020     10.67
5.  Item3           HKG      22:02:23:442     11.22
6.  Item1           IND      12:01:20:291     10.14
7.  Item2           USA      14:11:22:299     50.11
8.  Item4           LON      18:01:17:112     02.10
9.  Item1           SIN      20:05:30:020     10.90
10. Item3           HKG      22:02:23:442     11.37
11. Item1           IND      12:01:20:291     10.10
12. Item2           USA      14:11:22:299     50.01
13. Item4           LON      18:01:17:112     02.11
14. Item1           SIN      20:05:30:020     10.89
15. Item3           HKG      22:02:23:442     11.90

Please help to advise, let me know in comment if anymore details are required

Tried Solution

Select o.id, o.itemType, o.country, o.EntryTime, o.sp.price
from OrderTable o join
     StockPrice sp
     ON o.country = sp.country and o.itemType = sp.itemType and
        o.EntryTime = (select top 1 TransactionTime from StockPrice spIN where o.country = spIN.country and o.itemType = spIN.itemType and 
and spIN.spIN.TransactionTime < o.EntryTime order by spIN.TransactionTime)

Somehow result set have more then expected rows

Issues with above query..

  1. From above query i am getting result for only first row from Table A.
  2. In case Table B has more then one row having exact time as max TransactionTime, returned result will be corresponding to number of rows in table b.
Ashz
  • 23
  • 5
  • "I need help" isn't a question. What are you asking? What isn't working about the attempts you've made? What are those attempts? Why have you tagged 2 (3?) completely different RDBMS? – Thom A Aug 25 '20 at 16:31
  • 1
    I've never heard of Kinetica, so I'm guessing that is the tag you really want. Don't put inappropriate tags on questions. – Gordon Linoff Aug 25 '20 at 16:31
  • @GordonLinoff , Kinetica is Database, please see below https://www.kinetica.com/ – Ashz Aug 25 '20 at 16:48
  • @Larnu, Thanks for highlighting, i will correct it. – Ashz Aug 25 '20 at 16:49
  • It's probably because you didn't include an `order by` in the subquery: `(select top 1 TransactionTime from StockPrice spIN where o.country = spIN.country and o.itemType = spIN.itemType and and spIN.TransactionTime < o.EntryTime order by TransactionTime desc)`. It's easier to just use `max()` though. – shawnt00 Aug 25 '20 at 17:47
  • @shawnt00, Thanks for reply, i did included "order by TransactionTime" . but its giving me 19 record (But table A has only 8 and i am expecting 8 only with just matching columns from table b – Ashz Aug 25 '20 at 18:39
  • add `spIN.TransactionTime = sp.TransactionTime` to the subquery above. – shawnt00 Aug 25 '20 at 20:58

1 Answers1

0

See if this works:

select o.id, o.itemType, o.country, o.EntryTime, sp.price
from OrderTable o cross apply (
    select top 1 * from StockPrice spIN
    where o.country = spIN.country and o.itemType = spIN.itemType
       and spIN.TransactionTime <= o.EntryTime
    order by TransactionTime desc
) sp
order by id;
shawnt00
  • 16,443
  • 3
  • 17
  • 22
  • Getting below error "SqlEngine : Correlated sub-queries are not supported with limit operator (S/SDc:943);" – Ashz Aug 25 '20 at 20:16