Prorating no of units

Question

Prorating no of units

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Almost done... hang in there...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Ok... here 'tis... should be at least 30 times faster because it only produces the internal rows it needs for the final output. I've included the setup of the test data, again, just to make your life a little easier. For the production job, don't include the test data setup and don't forget to change the table name "#yourtable" to the name of your actual table. Recommend that you leave the #Dates table as a temp table, in this case... and do take the time to read the embedded documentation... especially the part about the INSERT/SELECT and the ORDER BY

Now... THAT WAS FUN!!! And, "LK MA", NO GROUP BY!!!

--========================================================================================
--      PREPARE THE TEST DATA (not included in final production)
--========================================================================================

--===== If the test table exists, drop it
     IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL
        DROP TABLE #yourtable

--===== Create the test table
 CREATE TABLE #yourtable
        (
        [Service Start] DATETIME,
        [Service End] DATETIME,
        [FMIS Code] INT,
        [No of Units] INT,
        [Year] INT,
        [Month] INT,
        Volumes DECIMAL(15,6)
        )

--===== Populate the test table 
 INSERT INTO #yourtable
        ([Service Start],[Service End],[FMIS Code],[No of Units])
 SELECT '01-Oct-00','15-Aug-01',6440,32   UNION ALL
 SELECT '01-Oct-00','30-Sep-02',6441,40   UNION ALL
 SELECT '01-Oct-02','22-May-03',6440,78   UNION ALL
 SELECT '01-Oct-02','23-May-03',6990,87   UNION ALL
 SELECT '06-Mar-03','31-Jul-03',6997,102  UNION ALL
 SELECT '07-Mar-03','31-Jul-03',6744,3    UNION ALL
 SELECT '01-May-03','31-May-03',6440,789  UNION ALL
 SELECT '23-Jun-03','31-Aug-03',6447,1000 UNION ALL
 SELECT '29-Jun-03','30-Jun-03',6440,981  UNION ALL
 SELECT '30-Jun-03','31-Jul-03',6000,50   UNION ALL
 SELECT '01-Jul-03','08-Jul-03',6002,54   UNION ALL
 SELECT '01-Jul-03','13-Jul-03',6000,562

--========================================================================================
--      SOLVE THE PROBLEM (uses an on-the-fly Dates table, kinda like a Tally table)
--========================================================================================
--===== If the temporary dates table exists, drop it
     IF OBJECT_ID('TempDB..#Dates') IS NOT NULL
        DROP TABLE #Dates

--===== Declare local variables
DECLARE @MinMonth INT
DECLARE @Months   INT

--===== Find the values for the local variables
 SELECT @MinMonth = MIN(DATEDIFF(mm,0,[Service Start])),
        @Months   = MAX(DATEDIFF(mm,0,[Service End]))
                  - MIN(DATEDIFF(mm,0,[Service Start]))+ 1
   FROM #yourtable

--===== Restrict the number of rows to the number of months we found
     -- so we can populate the dates table just the right size.
    SET ROWCOUNT @Months

--===== Create and populate the dates table making it just the right size.
     -- We'll fill in the dates later 'cause they're based on the RowNum.
 SELECT IDENTITY(INT,1,1) AS RowNum,
        CAST(NULL AS DATETIME) AS MonthStart,
        CAST(NULL AS DATETIME) AS MonthEnd 
   INTO #Dates
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Allow normal processing, again
    SET ROWCOUNT 0

--===== Add a Primary Key to the dates table to maximize performance
  ALTER TABLE #Dates
        ADD CONSTRAINT PK_Dates_RowNum PRIMARY KEY CLUSTERED (RowNum)

--===== Populate the dates table with dates
 UPDATE #Dates
    SET MonthStart = DATEADD(mm,RowNum+@MinMonth-1,0),
        MonthEnd   = DATEADD(mm,RowNum+@MinMonth,0)-1

--===== Produce the output same as before but MUCH faster.
     -- Could make this part of an INSERT/SELECT to populate a results table.
 SELECT --Here's the base data, again
          d.[Service Start],d.[Service End],d.[FMIS Code],d.[No of Units],
          YEAR(d.StartDate) AS [Year],
          MONTH(d.StartDate) AS [Month],
          CAST((DATEDIFF(dd,d.StartDate,d.EndDate)+1.0)*DailyUnits AS DECIMAL(19,9)) AS VOLUMES
   FROM (--Derived table "d" finds the base data, overall daily units, and StartDate/EndDate of month segment
         SELECT --Copy the base data so don't have to join later
                  y.[Service Start],y.[Service End],y.[FMIS Code],y.[No of Units],
                --Find the daily units overall
                  y.[No of Units]/(DATEDIFF(dd,y.[Service Start],y.[Service End])+1.0) AS DailyUnits,
                --Find the start date for the month segment
                  (CASE WHEN y.[Service Start] > d.MonthStart THEN y.[Service Start] ELSE d.MonthStart END) AS StartDate,
                --Find the end date for the month segment
                  (CASE WHEN y.[Service End] < d.MonthEnd THEN y.[Service End] ELSE d.MonthEnd END) AS EndDate
           FROM #yourtable y,
                #Dates d
          WHERE d.MonthEnd  >= y.[Service Start]
            AND d.MonthStart < y.[Service End]
        ) d
  ORDER BY d.[FMIS Code],d.[Year],d.[Month] --BIG PERFORMANCE EATER... TAKE ORDER BY OUT OF PRODUCTION CODE

"Send beer... I already have enough pretzels."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nuts SSCrazy Points: 2547 More actions · Answer 3

Good stuff.

Thanks for that!!It works..

U are a legend

Cheers buddy

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Thanks Mita... I learn something new everytime I do something like this, so thanks for posting the problem.

Lemme know how long it takes to do the 14 million rows... I'm very interested in the performance of this query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)