September 7, 2020 at 9:37 pm
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
September 7, 2020 at 10:00 pm
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:
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 7, 2020 at 10:03 pm
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;
September 8, 2020 at 12:22 am
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.
September 14, 2020 at 8:44 pm
It is a theta-join!
SELECT *
FROM dbo.Sales INNER JOIN dbo.FiscalPeriods
ON Sales.DOCDATE BETWEEN FiscalPeriods.FIRSTDATE AND FiscalPeriods.LASTDATE;
September 14, 2020 at 9:03 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 14, 2020 at 9:32 pm
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).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply