x

WHILE LOOP to Insert Future dates

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**

`
more ▼

asked Mar 09, 2010 at 12:06 PM in Default

Bubbles gravatar image

Bubbles
11 1 1 1

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.
Mar 09, 2010 at 02:51 PM Lynn Pettis

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.
Mar 09, 2010 at 04:04 PM Bubbles
#top_T should be #top_sub. I apologize
Mar 09, 2010 at 04:06 PM Bubbles
Nevermind everyone thanks for all of your help. Won't be using this forum again. I figured it out myself.
Mar 09, 2010 at 05:35 PM Bubbles
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.
Mar 10, 2010 at 04:55 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I haven't looked through your code, but I can give you a lightning fast solution for generating future dates.

CREATE FUNCTION [DBO].[fnNumsTable](
     @pStartValue BIGINT= 1,
     @pEndValue     BIGINT= 1000000,
     @pIncrement    BIGINT= 1 
 )
 RETURNS TABLE
 AS
 -- +----------------------------------------------------------------------------------------------------------------
 -- ! O b j e c t         : [DWH].[fnNumsTable]
 -- ! R e t u r n s       : A table with number from startvalue to end value
 -- ! P a r a m e t e r s : Name                    DataType       Description
 -- +                       ======================= ============== ==================================================
 -- !                                               @pStartValue                        BIGINT= 1,
 -- !                                               @pEndValue                          BIGINT= 1000000,
 -- !                                               @pIncrement                         BIGINT= 1 
  -- + ---------------------------------------------------------------------------------------------------------------
  -- ! O b j e c t i v e   : Return a list of all numbers between startvalue to end value
  -- !                                           The table may be used to avoid cursors, generate a list of dates etc. 
  -- + ---------------------------------------------------------------------------------------------------------------
  -- ! H i s t o r y       :
  -- + ---------------------------------------------------------------------------------------------------------------
  -- !                       Date       Who   What
  -- +                       ========== ===== ========================================================================
  -- !                       2009-09-21 HAWI
  -- +----------------------------------------------------------------------------------------------------------------
    --Select n from dbo.[fnNumsTable](1000,2000,1) 
    --Select DATEADD(D,N,GETDATE()) from dbo.[fnNumsTable](1,365,7) 
 RETURN(
     WITH BaseNum (
         N 
     ) AS (
     SELECT 1 UNION ALL
     SELECT 1 UNION ALL
     SELECT 1 UNION ALL
     SELECT 1 UNION ALL
     SELECT 1 UNION ALL
     SELECT 1 UNION ALL
     SELECT 1 UNION ALL
     SELECT 1 UNION ALL
     SELECT 1 UNION ALL
     SELECT 1 
     ),
     L1 (
         N 
     ) AS (
     SELECT
         bn1.N 
     FROM
         BaseNum bn1 
         CROSS JOIN BaseNum bn2 
     ),
     L2 (
         N 
     ) AS (
     SELECT
         a1.N 
     FROM
         L1 a1 
         CROSS JOIN L1 a2 
     ),
     L3 (
         N 
     ) AS (
     SELECT TOP ((ABS(CASE WHEN @pStartValue < @pEndValue
                          THEN @pEndValue
                          ELSE @pStartValue
                      END - 
                      CASE WHEN @pStartValue < @pEndValue
                          THEN @pStartValue
                          ELSE @pEndValue
                      END))/ABS(@pIncrement)+ 1)
         a1.N 
     FROM
         L2 a1 
         CROSS JOIN L2 a2 
     ),
     Tally (
         N 
     ) AS (
     SELECT
         row_number() OVER (ORDER BY a1.N)
     FROM
         L3 a1 
     )
     SELECT
         ((N - 1) * @pIncrement) + @pStartValue AS N 
     FROM
         Tally 
 );
GO

This function generates a list of numbers from a given start value to a given end value with a given increment value. To generate the dates you can use a code like this:

--This code generates all dates from today and a year forward Select DATEADD(D,N,GETDATE()) from dbo.fnNumsTable 
more ▼

answered Mar 10, 2010 at 04:50 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x69

asked: Mar 09, 2010 at 12:06 PM

Seen: 2661 times

Last Updated: Mar 09, 2010 at 12:11 PM