January 11, 2023 at 12:53 am
trying to find the best way to automate a report only the current reporting period. Each are 8 months long and start/end on certain dates.
RP 1 : 07/01/2021 to 02/28/2022
RP2: 03/01/2022 to 10/31/2022
RP3: 11/01/2022 to 06/30/2023
RP4: 07/01/2023 to 02/28/2023
The only ID's and values that should show up are 142 and 166 for the current reporting period. Each ID until the end of RP3 will be extracted. On July 1, 2023 a new reporting period will start and only ID's after this date will appear.
TIA
IF OBJECT_ID('TempDB..#value','U') IS NOT NULL
DROP TABLE #value
create table #value
(
PatID varchar(10)
,StartDate date
,Value INT
)
Insert into #value
values
('111','2022-01-03', 4 )
,('122','2022-05-03', 5)
,('145','2022-06-06', 10 )
,('142', '2022-12-03', 34)
,('166', '2023-01-01', 45)
Select
p.PatID
,p.StartDate
,p.Value
from #value p
January 11, 2023 at 2:30 am
wow. for that many points, that's a pretty terrible description.
For starters, here's your code fixed up a little:
use tempdb;
GO
CREATE TABLE ReportingPeriods(
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
PeriodID INT PRIMARY KEY);
GO
create table Pat
(
PatID varchar(10)
,StartDate date
,PatValue INT
);
GO
INSERT INTO ReportingPeriods(StartDate, EndDate, PeriodID)
VALUES ('07/01/2021','02/28/2022',1),
('03/01/2022','10/31/2022',2),
('11/01/2022','06/30/2023',3),
('07/01/2023','02/28/2023',4);
Insert into Pat(PatID, Startdate, PatValue)
values
('111','2022-01-03', 4 )
,('122','2022-05-03', 5)
,('145','2022-06-06', 10 )
,('142', '2022-12-03', 34)
,('166', '2023-01-01', 45);
SELECT p.PatID,
p.PatValue,
p.StartDate,
rp.PeriodID
FROM Pat p
INNER JOIN ReportingPeriods rp
ON (p.StartDate > rp.StartDate AND p.StartDate<=rp.EndDate);
-- what period are we in today?
SELECT PeriodID
FROM ReportingPeriods rp
WHERE StartDate <= GETDATE() AND EndDate > GETDATE();
Please read Jeff's article, and follow his instructions on how to ask a good question:
Forum Etiquette: How to post data/code on a forum to get the best help
I've gotten incredible help from some really smart people here by following Jeff's instructions. Give it a try.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply