SQL Server TSQL - Matching a date between two dates in another table

azpvetkf 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(106)

I currently have two tables, tbl_Invoices

InvoiceNumber  NextBillingDate
------------------------------
    100            3/15/21
    200            3/31/21
    300            4/15/21
    400            5/15/21

and tbl_GLPeriods:

GLPeriod     PeriodStartDate     PeriodEndDate
----------------------------------------------
  250            3/3/21              4/3/21
  251            4/4/21              5/2/21
  252            5/3/21              6/3/21

I need a view that returns a column where the GL period for the next billing date is provided, ie:

InvoiceNumber  NextBillingPeriod
---------------------------------
    100              250
    200              250
    300              251
    400              252

How do I query to find if one column is between the two columns in another table? I'm blanking on how to do this, thinking something with a CASE .

Edit: where I'm currently at, structurally won't work, but it shows what I'm currently trying to get going:

SELECT
    *,
    CASE
       WHEN tbl_Invoices.NextBillingDate BETWEEN (SELECT PeriodStartDate FROM tbl_GLPeriods) AS stdt 
                                             AND (SELECT PeriodEndDate FROM tbl_GLPeriods) AS endt  
          THEN endt.GLPeriod    
    END AS NextBillingPeriod
FROM
    tbl_Invoices

Solved with this thanks to @Charlieface:

select tbl_Invoices.InvoiceNumber, tbl_GLPeriods.GLPeriod 
from tbl_Invoices
left join tbl_GLPeriods on tbl_Invoices.NextBillingDate between tbl_GLPeriods.PeriodStartDate AND tbl_GLPeriods.PeriodEndDate

sql-server

来源：https://stackoverflow.com/questions/66448096/tsql-matching-a-date-between-two-dates-in-another-table

2条答案

按热度按时间

vmpqdwk31#

You can use AND to connect multiple predicates to check for a range with <= and > (or equivalent). Like that you can use a correlated subquery similar to what you've tried, provided the periods cannot overlap.

SELECT i.invoicenumber,
       (SELECT p.glperiod
               FROM tbl_glperiods p
               WHERE p.periodstartdate <= i.nextbillingdate
                     AND dateadd(DAY, 1, p.periodenddate) > i.nextbillingdate) nextbillingperiod
       FROM tbl_invoices i;

You can also use a left join. Then the periods can overlap, you'll get multiple rows, if a date falls in two or more periods. A join might also perform better.

SELECT i.invoicenumber,
       p.glperiod nextbillingperiod
       FROM tbl_invoices i
            LEFT JOIN tbl_glperiods p
                      ON p.periodstartdate <= i.nextbillingdate
                         AND dateadd(DAY, 1, p.periodenddate) > i.nextbillingdate;

Note that you can shorten dateadd(DAY, 1, p.periodenddate) to just p.periodenddate if tbl_glperiods.periodenddate is meant to be and exclusive upper bound or if it's inclusive but tbl_invoices.nextbillingdate is guaranteed not to be more precise than a day, i.e. it cannot have an hour, minute, second and so on portion. Otherwise you might miss timestamps on the last day past midnight.

赞(0）回复(0）举报 2023-10-15

qc6wkl3g2#

select 
  InvoiceNumber, 
  (
    select 
      GLPeriod 
    from 
      tbl_GLPeriods 
    where 
      NextBillingDate between PeriodStartDate 
      and PeriodEndDate
  ) 'NextBillingPeriod' 
from 
  tbl_Invoices

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server TSQL - Matching a date between two dates in another table

2条答案

相关问题

热门标签

最新问答