I have a Script that I am running to populate data to fill in a pivot table for the upcoming months in the future that we have not happened as of yet. For some odd reason my script is not inserting data into a table, and I have been trying to understand what could be the cause of this. The only months that appear in the table are months 1,2 and 13. Thirteen is a YTD total used to summarize Rx data. However I need months 1-12 to populate a pivot table showing. The only months that should be populated is months 1 and 2. But months 3 thru 12 should be blank, but they should still show in my final table named paulk.dbo.trend_pharmacy.This is causing me much stress and I would like to move on to my next task.Any help would be gladly appreciated. The portion of the script to begin is named ---data fill for future months. This is where the error is probably occuring. I have provided the dates below, if this would help
Begin_trend 2009-01-01 00:00:00
End_trend 2010-02-28 00:00:00
Cutoff_trend 2010-03-01 00:00:
-- NEW DRUG TREND REPORT (from prc_new)
-- month generator for reporting period
--month generator and cutoff for reporting period
declare @cutoff datetime
set @cutoff = (select cutoff_trend from jeffb.dbo.tbl_rpt_date_parameters)
declare @mmonth as int
set @mmonth = (select month(end_trend) from jeffb.dbo.tbl_rpt_date_parameters)
declare @begin datetime
set @begin = (select begin_trend from jeffb.dbo.tbl_rpt_date_parameters)
declare @prior_yr as int
set @prior_yr = (select year(begin_trend) from jeffb.dbo.tbl_rpt_date_parameters)
declare @current_yr as int
set @Current_yr = (select year(end_trend) from jeffb.dbo.tbl_rpt_date_parameters)
/*
-- build therapeutic class table
--drop table #tclass
select substring([Ther Code],0,3) [T-code],master
into #tclass
from Redbook.dbo.therapclassbycat
where master is not null
-- therapeutic class level 2
--drop table #tclass2
select substring([Ther Code],0,5) [T-code2], subcat1
into #tclass2
from Redbook.dbo.therapclassbycat
where subcat1 is not null
--------------------------------
*/
-- data pull
--drop table #raw
select Product
, year(fill_dt) YR
, month(fill_dt) MTH
, substring([desc],0,11) [Desc]
, substring([group],0,6) Group_ID
, Therapeutic_Class_AHFS_Code TCAC
, m.drug_group [Tclass]
, m.drug_class subcat1
, sum(nCount) Fills
, sum(AmtBilled) Paid
, sum(Total_Copay_Amount) Copay
, Generic_Brand_Indicator GBI
, Formulary_Indicator FI
, sum(case when claim_status_flag = 'X' then -1 else 1 end) TestCount
into #raw
from draco.wh.dbo.prc_new p
left outer join jeffb.dbo.medispan_drug_class_test2a m on p.[ndc] = m.ndc_upc_hri
/* UPDATED 8/28/07 to MEDISPAN
left outer join #tclass tc
on substring(Therapeutic_Class_AHFS_Code,0,3) = tc.[T-code]
left outer join #tclass2 t2
on substring(Therapeutic_Class_AHFS_Code,0,5) = t2.[T-code2]
*/
where claim_status_flag in ('P','X') -- = 'P'
and month(fill_dt) <= @mmonth
and year(fill_dt) in (@current_yr, @prior_yr)
group by Product, year(fill_dt), month(fill_dt), substring([desc],0,11),
substring([group],0,6), Therapeutic_Class_AHFS_Code, Generic_Brand_Indicator,
Formulary_Indicator, m.drug_group
, m.drug_class
--select * from #raw where testcount < 1
-- build report data
--drop table #temp
select Product, YR, MTH, [Desc], Group_ID,
case when [group_id] in ('00000','0000P') then 'MCR-DP'
when [group_id] = '00002' then 'STATE'
when [group_id] = 'F0002' then 'SMALL'
when [group_id] = 'F0005' then 'SMALL'
when [group_id] like 's%' then 'SMALL'
when [group_id] = '00090' then 'NON-GROUP'
when [group_id] like '009%' then 'NON-GROUP'
else 'LARGE' end BOB,
TCAC, Tclass, subcat1, sum(Fills) Fills, sum(TestCount) tFills,
sum(Paid) Paid, sum(Copay) Copay, GBI, FI,
case FI when 'Y' then 'Tier 2'/*'Formulary'*/ else 'Tier 3'/*'Non-Formulary'*/ end Indicator
into #temp
from #raw
where YR = @current_yr
group by Product, YR, MTH, [Desc], Group_ID, TCAC, Tclass, subcat1, GBI, FI
-- get YTD Data
insert into #temp
select Product, YR, 13, [Desc], Group_ID,
case when [group_id] in ('00000','0000P') then 'MCR-DP'
when [group_id] = '00002' then 'STATE'
when [group_id] = 'F0002' then 'SMALL'
when [group_id] = 'F0005' then 'SMALL'
when [group_id] like 's%' then 'SMALL'
when [group_id] = '00090' then 'NON-GROUP'
when [group_id] like '009%' then 'NON-GROUP'
else 'LARGE' end BOB,
TCAC, Tclass, subcat1, sum(Fills) Fills, sum(TestCount) tFills,
sum(Paid) Paid, sum(Copay) Copay, GBI, FI,
case FI when 'Y' then 'Tier 2' /*'Formulary'*/ else 'Tier 3'/*'Non-Formulary'*/ end Indicator
from #raw
group by Product, YR, [Desc], Group_ID, TCAC, Tclass, subcat1, GBI, FI
-----
update #temp
set BOB = 'MCR-EG' where product = 'medicare' and group_id not in ('00000', '0000P')
--update #temp
--set BOB = 'SMALL'
--where Grp_ID like 's%' or grp_ID in ('f0002','f0005')
update #temp
set Indicator = 'Tier 1'/*'Generic'*/ where GBI = 'Y'
-- MEMBERSHIP TABLE ----------------------------
--drop table #member
select * into #member from jeffb.dbo.membershiphistory
where [month] <= @mmonth
and [year] in (@current_yr, @prior_yr)
-- and product = 'ALL'
insert into #member
select Product, [year], 13, sum(members) from jeffb.dbo.membershiphistory
where [month] <=@mmonth
and [year] in (@current_yr, @prior_yr)
-- and product = 'ALL'
group by Product, [year]
------------------------------------------------
-- CMCL & MEDICARE products
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trend_pharmacy]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trend_pharmacy]
--drop table paulk.dbo.trend_pharmacy
select t.Product, YR, MTH, Tclass, subcat1, [Desc], BOB, Indicator,
sum(Fills) Fills, sum(Paid) Paid, sum(Copay) Copay, m.members, sum(tFills) tFills,
1 Rcount
into paulk.dbo.trend_pharmacy
from #temp t
left outer join #member m
on t.YR = m.[year]
and t.MTH = m.[month]
and m.product = t.product
group by t.Product, YR, MTH, Tclass, subcat1, [Desc], BOB, Indicator, m.members
-- ALL (COMBINED) product table
insert into paulk.dbo.trend_pharmacy
select 'ALL' Product, YR, MTH, Tclass, subcat1, [Desc], BOB, Indicator,
sum(Fills) Fills, sum(Paid) Paid, sum(Copay) Copay, m.members, sum(tFills) tFills,
1 Rcount
-- case sum(tFills) when 0 then 0 when null then 0 else
-- (sum(Paid)/sum(tFills)) end [Cost/Fill], ((sum(tFills)*12)/(m.members/1000)) [Fills/K],
-- (sum(Paid)/m.members) [PMPM]
from #temp t
left outer join #member m
on t.YR = m.[year]
and t.MTH = m.[month]
and m.product = 'ALL'
group by YR, MTH, Tclass, subcat1, [Desc], BOB, Indicator, m.members
-- sum for t-class report ------------------------------
insert into paulk.dbo.trend_pharmacy
select p.Product, YR, MTH, Tclass, subcat1, 'ALL DRUGS', BOB, Indicator
, sum(Fills) Fills, sum(Paid) Paid, sum(Copay) Copay, m.members, sum(tFills) tFills
, 1 Rcount
from paulk.dbo.trend_pharmacy p
left outer join #member m
on p.YR = m.[year]
and p.MTH = m.[month]
and p.Product = m.Product
group by p.Product, YR, MTH, Tclass, subcat1, BOB, Indicator, m.members
------------------------------
------------------------------------------------------------------------------------------
--**data fill for future months --------------------------------------------------
-- get top Tclass
--drop table #top_t
--select Tclass, sum(Paid) Paid
--into #top_t
--from paulk.dbo.trend_pharmacy
--group by Tclass order by SUM(Paid) desc
-- get top Drug (& coordinating Tclass)
--drop table #top_drug
select [Desc], Tclass, sum(Paid) Paid
into #top_drug from paulk.dbo.trend_pharmacy
group by [Desc], Tclass order by SUM(Paid) desc
-- get top subcat1
--drop table #top_sub
select subcat1, sum(Paid) Paid
into #top_sub from paulk.dbo.trend_pharmacy
group by subcat1 order by sum(Paid) desc
--SELECT TOP 1 * FROM #TOP_T
--SELECT TOP 1 * FROM #TOP_DRUG
DECLARE @tcl as varchar(30)
DECLARE @subcat as varchar(30)
DECLARE @drug as varchar (18)
--SET @tcl = (select top 1 Tclass from #top_t)
SET @tcl = (select top 1 Tclass from #top_drug)
SET @subcat = (select top 1 subcat1 from #top_sub) -- may have to change similar to Tclass
SET @drug = (select top 1 [Desc] from #top_drug)
DECLARE @monthcounter as int
SET @monthcounter = @mmonth + 1
WHILE (@monthcounter < 13) BEGIN
insert into paulk.dbo.trend_pharmacy values
('ALL', @current_yr, @monthcounter, @tcl, @subcat, @drug, 'SMALL','Generic',0,0,0,0,0,0)
insert into paulk.dbo.trend_pharmacy values
('COMMERCIAL', @current_yr, @monthcounter, @tcl, @subcat, @drug, 'SMALL','Generic',0,0,0,0,0,0)
insert into paulk.dbo.trend_pharmacy values
('MEDICARE', @current_yr, @monthcounter, @tcl, @subcat, @drug, 'SMALL','Generic',0,0,0,0,0,0)
-- must also add line for LARGE and STATE (MEDICARE) if reporting by BOB ****
-- also add line(s) for Formulary & non-Formulary *****************
set @monthcounter = @monthcounter + 1
END**
`
I'm lost. Lot of code, but I can't figure out what table you are trying to populate with what? Can you simplify what your are trying to do and then we can build from there.
The table ispaulk.dbo.trend_pharmacy. The while loop in the last section of the script is not working correctly to populate months 3 thru 12 using the insert into statement. Only months 1 and 2 will have data based on the end _trend and cut off dates provided in the script. Months 3 thru 12 should be populated with the data from the tables named #top_t and #top_drug. These values only serve as placeholders.
Hopefully this clarifies it.The portion of the code that should be paid attention too is the last section under data fill for future months.
top_T should be #top_sub. I apologize
Nevermind everyone thanks for all of your help. Won't be using this forum again. I figured it out myself.
Why aren't you going to use this formum again? Have someone misstreated or offended you? Personally I like this site and I am willing to share my knowledge help others.