3
votes

Comment sélectionner une valeur dans un tableau en fonction de la plage de dates qui peut changer

Je n'arrive pas à trouver une question qui répond spécifiquement aux exigences de ce que j'essaie de faire ici, mais je m'excuse si ce n'est pas le cas.

J'ai un exemple de tableau:

customerId|transaction_date|item   |quantity|cost|

abc123    |2019-01-25      |bananas|4       |100
abc126    |2019-02-06      |bananas|4       |95
abc128    |2019-02-09      |juice  |1       |55

Et ainsi de suite. Le point du tableau est que la `` nourriture '' ou quoi que ce soit d'autre, peut être un prix différent basé sur une plage de temps différente, mais il y a certains articles, par exemple. jus, qui ne sont pas affectés lorsque la date change (c'est-à-dire qu'ils restent constants).

Dans un autre tableau, je veux rechercher une valeur basée sur la plage de dates dans le tableau ci-dessus.

Par exemple:

customerId|transaction_date|item   |quantity|

abc123    |2019-01-25      |bananas|4
abc126    |2019-02-06      |bananas|4
abc128    |2019-02-09      |juice  |1

Les deux premiers clients ont donc acheté des bananes, mais un en janvier et un en février. Puis un autre client a acheté du jus. Comment puis-je retourner le coût correspondant correct en fonction de mon autre table.

J'ai essayé de faire où la date est ENTRE les deux, mais cela aliène toutes les autres dates qui ne le sont pas existent dans le tableau des prix. J'ai également essayé d'utiliser CASE pour aborder cela, mais en vain.

Quelle serait la meilleure approche à ce sujet?

Je m'attendrais le résultat pour donner une table jointe avec ce qui suit:

    type|start_date|end_date  |cost|

    bananas|2019-01-01|2019-01-31|100
    bananas|2019-02-01|2019-02-28|95
    juice  |null      |null      |55

Où la colonne cost est jointe par les critères ci-dessus?

p >

sql sql-server

0 commentaires

3 Réponses :

1
votes

Vous pouvez utiliser une sous-requête corrélée:

select t2.*, t1.cost
from table2 t2 left join
     table1 t1
     on t1.type = t2.type and
        (t2.transaction_date >= t1.start_date or
         t1.start_date is null
        ) and
        (t2.transaction_date <= t1.end_date or
         t1.end_date is null
        );

Ou, vous pouvez utiliser une jointure gauche :

select t2.*, t1.cost
from table2 t2 left join
     table1 t1
     on t1.type = t2.type and
        t2.transaction_date >= t1.start_date and
        t2.transaction_date <= t1.end_date ;

EDIT:

Vous pouvez gérer les valeurs NULL avec OR:

select t2.*,
       (select t1.cost
        from table1 t1
        where t1.type = t2.type and
              t2.transaction_date >= t1.start_date and
              t2.transaction_date <= t1.end_date 
       ) as cost
from table2 t2;

0 commentaires

1
votes

Vous pouvez utiliser une application externe:

  SELECT p.*,
  c.cost
  FROM Purchases AS p
      OUTER APPLY (
          SELECT TOP 1 c.cost
              FROM Product_Costs AS c
              WHERE p.transaction_date BETWEEN c.start_date AND c.end_date
                  AND c.type = p.Item
      ) AS c;

J'ai créé un Sql Fiddle en utilisant votre exemple de données: http://sqlfiddle.com/#!18/e366b/2/0

0 commentaires

2
votes

Vous pouvez utiliser ISNULL sur la jointure gauche.

SELECT P.*, C.cost
FROM Purchases P 
LEFT JOIN Product_Costs C ON P.item = C.type 
      AND P.transaction_date >= ISNULL(C.start_date, P.transaction_date) 
      AND P.transaction_date <= ISNULL(C.end_date, P.transaction_date);

Le violon de Nick modifié: http://sqlfiddle.com/#!18/e366b/6

0 commentaires