SQL capture data by yearly monthly

by lokointhehouse2

JavaScript

-- select * from Stage_SpecialtyArgus_Dashboard_Nursing_Result
-- select * from opsdatab.dbo.SpecialtyArgus_Dashboard_Result_DEV

declare @startDate datetime,@endDate datetime
set @startDate = convert(date, getdate()-7)
set @endDate = getdate()

select @startDate
select @endDate


IF OBJECT_ID('tempdb..#PINSTemp') IS NOT NULL DROP TABLE #PINSTemp

SELECT * 
INTO #PINSTemp
FROM
	(
		SELECT 
			patient_id,
			claim_center_seq,
			claim_center_no,
			group_no,
			effective_date,
			dense_rank() over (partition BY patient_id 
		ORDER BY claim_center_seq,effective_date desc ) AS ranking
		FROM OpsDataB.dbo.rxhome_prod_patient_insurance WITH (NOLOCK)
	)qry
WHERE ranking = 1

--Add Indexes to #PINSTEMP
CREATE CLUSTERED INDEX [IX_patientclaim] ON #PINSTemp
(
	patient_id ASC,
	claim_center_no ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_patientID] ON #PINSTemp
(
	patient_id ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_claim] ON #PINSTemp
(
	claim_center_no ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]




/**********************************************/
/*************DAILY ***************************/
/**********************************************/



	delete from opsdatab.dbo.SpecialtyArgus_Dashboard_Result_DEV
	where thedate >= @startDate
	and metric_id = 14
	and period = 'D'


truncate table opsetl.dbo.Stage_SpecialtyArgus_Dashboard_Nursing_Result

--		Daily
insert into opsetl.dbo.Stage_SpecialtyArgus_Dashboard_Nursing_Result
(
thedate
,metric_id
,period
,numerator
,denominator
,therapy_type
,patient_type
)...