asked
How to loop through date variable (run report for multiple dates)
I have a sql stored procedure with 2 date variables that I need to run the report on. @StartDate and @EndDate. The Stored procedure runs a census report for the dates provided. I need to run the SP for current date and the past 15 days. The current date is easy as I can set @StartDate = getdate() but how can I also run it for the past 15 days including todays date. Also in order for the data to be correct the @StartDate and @EndDate have to be the same date so if I am running the report for today @StartDate = 1/24/2017 and @EndDate = @StartDate and yesterdays date would be @StartDate=1/23/2017 and @EndDate=1/23/2017. (It can't be a date range @StartDate = 1/09/2017 @EndDate = 1/24/2017) one way i know is running the SP 15 different times like below but there has to be a better way. exec sp_report '1/24/2017','1/24/2017' exec sp_report '1/23/3017'.'1/23/2017' etc... declare @StartDate smalldatetime declare @EndDate smalldatetime set @StartDate = getdate() set @EndDate = @StartDate is loop the way to go or is there a better approach? All the help is greatly appreciated.
For 15 rows of SQL writing a loop probably isn't worth it, personally I would just have create procedure sp_reportBatch as being declare @StartDate smalldatetime = getdate(); declare @EndDate smalldatetime = @StartDate; execute sp_report @StartDate, @EndDate execute sp_report dateadd(dd,-1,@StartDate), dateadd(dd,-1,@EndDate) execute sp_report dateadd(dd,-2,@StartDate), dateadd(dd,-2,@EndDate) execute sp_report dateadd(dd,-3,@StartDate), dateadd(dd,-3,@EndDate) etc...... end Fill in the extra rows until dateadd -15 and then you just execute sp_reportbatch
Not really sure how to apply the folllowing, here is my sp...... How can I run the sp for multiple dates? ALTER PROCEDURE [dbo].[CRC_OnShift] -- exec [dbo].[CRC_OnShift] AS BEGIN declare @StartDate datetime = getdate() declare @EndDate datetime = @StartDate declare @EntryEndDate datetime = getdate() declare @Test Table ( InternalID VARCHAR(20) NULL , DisplayName VARCHAR(50) NULL , EntitySys INT NULL , EpisodeSys INT NULL , PlanSys INT NULL , PlanID VARCHAR(50) NULL , PayerType VARCHAR(50) NULL , PayerCode CHAR(2) NULL , PolicyNumber VARCHAR(200) NULL , PPSPlanFlg CHAR(1) NULL , GrouperSys INT NULL , AdmissionDate DATETIME NULL , DischargeDate DATETIME NULL , AdmissionID VARCHAR(20) NULL , DeathDate DATETIME NULL , TransType CHAR(2) NULL , TransDateTime smallDATETIME NULL , LeaveType CHAR(2) NULL , LeaveDesc VARCHAR(40) NULL , LeaveStartDate DATETIME NULL , LeaveEndDate SMALLDATETIME NULL , LeavePlanSys INT NULL , CensSeq INT NULL , EndDate smallDATETIME NULL , EndTransType CHAR(2) NULL , DateActive DATETIME NULL , DateInactive DATETIME NULL , NbrDays INT NULL , LastOrgSys INT NULL , BillableLeave CHAR(1) NULL , Sex CHAR(1) NULL , BedType CHAR(2) NULL , EvacType CHAR(2) NULL , CountOnDischargeDate BIT NULL , MPChargeOnDeathDate BIT NULL , MPDeathDischargeDatesSame INT NULL , MPChargeOnDischargeDate BIT NULL , EvacDesc VARCHAR(50) NULL , EntryDate DATETIME NULL , LeaveHoldBedFlg VARCHAR(1) NULL , EntBreak VARCHAR(20) , Name1 VARCHAR(20) NULL , Name2 VARCHAR(20) NULL , Name3 VARCHAR(20) NULL , Name4 VARCHAR(20) NULL , Name5 VARCHAR(20) NULL , Name6 VARCHAR(20) NULL , Name7 VARCHAR(20) NULL , Name8 VARCHAR(20) NULL , Name9 VARCHAR(20) NULL , Name10 VARCHAR(20) NULL , Name11 VARCHAR(20) NULL , Name12 VARCHAR(20) NULL , Name13 VARCHAR(20) NULL , Name14 VARCHAR(20) NULL , Name15 VARCHAR(20) NULL ,EpisodeType char(3) ) insert into @Test exec hmxafo..hmxsp_InpatientCensusReportGetData @OrgSet=1, @OrgEntList = '670,671,672,673,674,675,677,679,680,681,682,683', @StartDate= @StartDate, @EndDate= @EndDate, @EndTime=N'23:59', @StartTime=N'00:00', @EntryStartDate=N'01/01/1900 00:00', @EntryEndDate=@EntryEndDate , @SortOrder=N' Name5' Declare @Onshift table( Census int, Date smalldatetime, FacilityID varchar(10), LocationName varchar(20), Wing varchar(20) ) insert into @Onshift select count(InternalID) , transdatetime, name5 , name4, name3 from @Test group by internalId,transdatetime, name5, name4, name3 select Date, Facilityid, LocationName + ', ' + Wing as LocationName ,sum(census) as Census from @Onshift group by Date, FacilityID, Wing,LocationName, Census order by Facilityid , LocationName ---execute CRC_Onshift end
18 People are following this question.
