Joining a table based on date range - Case Statement?

Question

Joining a table based on date range - Case Statement?

Bron Tamulis

Ten Centuries

Points: 1090
More actions
September 7, 2020 at 9:37 pm

#3786828

Okay - Two tables one transnational table with a standard document date
Second table is financial fiscal period table that is 'not standard calendar months'.
CREATE TABLE [dbo].[Sales](
[SOPTYPE] [smallint] NOT NULL,
[SOPNUMBE] [char](21) NOT NULL,
[DOCAMNT] [numeric](19, 5) NOT NULL,
[DOCDATE] [Date] NOT NULL
)
INSERT [Sales]
Values
('6', 'SOP105','0.00','2020-08-30'),
('6', 'SOP101','1250.00','2020-01-16'),
('6', 'SOP102','200.00','2020-09-10'),
('6', 'SOP103','100.00','2020-02-14'),
('6', 'SOP104','1805.00','2020-08-31')
CREATE TABLE [dbo].[FiscalPeriods]
([PERIOD] [smallint] NOT NULL,
[YEAR] [smallint] NOT NULL,
[FIRSTDATE] [date] NOT NULL,
[LASTDATE] [date] NOT NULL)
INSERT FiscalPeriods
VALUES
('1','2020','2020-01-05','2020-02-03'),
('2','2020','2020-02-04','2020-03-07'),
('3','2020','2020-03-08','2020-04-04'),
('4','2020','2020-04-05','2020-05-07'),
('5','2020','2020-05-08','2020-06-08'),
('6','2020','2020-06-09','2020-07-03'),
('7','2020','2020-07-04','2020-08-01'),
('8','2020','2020-08-02','2020-08-31'),
('9','2020','2020-09-01','2020-09-28'),
('10','2020','2020-09-29','2020-10-30'),
('11','2020','2020-10-31','2020-11-30'),
('12','2020','2020-12-01','2021-01-04')
Somehow I need a simple select statement that looks at fiscal table and returns the Period and Year for the corresponding date range:
Select SOPNUMBE, DOCDATE, DOCAMNT, PERIOD (fiscal Table), YEAR(Fiscal Table)
How do you join a table without a direct relationship? Some kind of joining based upon a case type statement?
Desired Results:
SOP105, 2020-08-30, 8,2020
SOP101, 2020-01-16,1,2020
SOP102, 2020-09-10,9,2020
SOP103, 2020-02-14,2,2020
SOP104, 2020-08-31,8,2020
Any guidance is appreciated Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246955 More actions · Answer 1

Try this:

DROP TABLE IF EXISTS #Sales;
DROP TABLE IF EXISTS #FiscalPeriods;

CREATE TABLE #Sales
(
    SOPTYPE SMALLINT NOT NULL
   ,SOPNUMBE CHAR(21) NOT NULL
   ,DOCAMNT NUMERIC(19, 5) NOT NULL
   ,DOCDATE DATE NOT NULL
);

INSERT #Sales
(
    SOPTYPE
   ,SOPNUMBE
   ,DOCAMNT
   ,DOCDATE
)
VALUES
(6, 'SOP105', 0, '20200830')
,(6, 'SOP101', 1250, '20200116')
,(6, 'SOP102', 200, '20200910')
,(6, 'SOP103', 100, '20200214')
,(6, 'SOP104', 1805, '20200831');

CREATE TABLE #FiscalPeriods
(
    PERIOD smallint NOT NULL
   ,YEAR SMALLINT NOT NULL
   ,FIRSTDATE DATE NOT NULL
   ,LASTDATE DATE NOT NULL
);

INSERT #FiscalPeriods
(
    PERIOD
   ,YEAR
   ,FIRSTDATE
   ,LASTDATE
)
VALUES
(1, 2020, '20200105', '20200203')
,(2, 2020, '20200204', '20200307')
,(3, 2020, '20200308', '20200404')
,(4, 2020, '20200405', '20200507')
,(5, 2020, '20200508', '20200608')
,(6, 2020, '20200609', '20200703')
,(7, 2020, '20200704', '20200801')
,(8, 2020, '20200802', '20200831')
,(9, 2020, '20200901', '20200928')
,(10, 2020, '20200929', '20201030')
,(11, 2020, '20201031', '20201130')
,(12, 2020, '20201201', '20210104');

SELECT s.SOPTYPE
      ,s.SOPNUMBE
      ,s.DOCAMNT
      ,s.DOCDATE
      ,fp.Period
      ,fp.YEAR
FROM #Sales s
    CROSS JOIN #FiscalPeriods fp
WHERE s.DOCDATE
BETWEEN fp.FIRSTDATE AND fp.LASTDATE;

I do have a few suggestions/guidance items for you:

Put your code in a code block. It makes it much easier to read.
Use temp tables for sample data.
Do not enclose numeric values in quotes
Use the ISO standard YYYYMMDD format for literal dates
Avoid using reserved words as column names (PERIOD)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pietlinden SSC Guru Points: 63488 More actions · Answer 2

Thanks for the data setup... this matches your expected output...

SELECT *
FROM dbo.Sales s
INNER JOIN dbo.FiscalPeriods fp
ON s.DOCDATE>=fp.FirstDate AND s.DocDate<=fp.LastDate;

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 3

Thank you so much for the prompt reply and for the assistance in properly posting data. It works beautifully.

I will need to study up on the CROSS JOIN. I had been trying to use a case statement and inner join nested query work with no luck.

carsten.saastamoinen Old Hand Points: 341 More actions · Answer 4

It is a theta-join!

SELECT *

FROM dbo.Sales INNER JOIN dbo.FiscalPeriods

ON Sales.DOCDATE BETWEEN FiscalPeriods.FIRSTDATE AND FiscalPeriods.LASTDATE;

ScottPletcher SSC Guru Points: 101085 More actions · Answer 5

5. Avoid using reserved words as column names (PERIOD)

PERIOD is not a reserved word in SQL Server. (Nor is it reserved in ODBC, also not on the list of future reserved words.)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246955 More actions · Answer 6

ScottPletcher wrote:

5. Avoid using reserved words as column names (PERIOD)
PERIOD is not a reserved word in SQL Server. (Nor is it reserved in ODBC, also not on the list of future reserved words.)

Should always double-check these things, shouldn't I 🙂

The reason I thought it must be was because of Temporal Tables, where PERIOD appears to be a defined term (link).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin