There Must be an easier way! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

There Must be an easier way!

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-01-15 : 03:18:20

This is a clumsy but working solution to what I think is a pretty challenging problem.
Horror of all horrors it uses cursors and temporary tables, to all those out there who hate these please help a poor developer who is too narrow minded to use anything else.

I have a table of projects, their duration and cost.

CREATE TABLE Projects(
    ProjCode varchar(12) NOT NULL,
    StartDate smalldatetime NOT NULL,
    EndDate smalldatetime NOT NULL,
    Cost money NOT NULL
    )

The problem is as follows:
Given 2 dates what is the total cost per month for each month between those 2 dates?

This is my solution:

Create procedure sp_proposedcashflow
     @StartDate smalldatetime, @EndDate smalldatetime
AS

CREATE TABLE dbo.#cash (
    ProjCode nvarchar (15) NULL ,
    ProjectDate smalldatetime NULL ,
    CostPerDay money NULL
) ON [PRIMARY]

--Selecting the start date, duration and cost-per-day for each project into a temporary table

SELECT
ProjCode,
startdate as ProjectDate,
DateDiff(day, startdate, enddate)+1 as Duration,
Cost / (DateDiff(day, startdate, enddate)+1) as CostPerDay
into dbo.#temp1
FROM dbo. Projects

--Creating a cursor that will write into a temporary table the Project, date and cost for that day for each project

Declare @ProjCode nvarchar(15), @ProjectDate smalldatetime, @CostPerDay money, @Duration int

Declare c2 cursor for
SELECT
ProjCode, ProjectDate, CostPerDay, Duration
FROM dbo.#Temp1

open c2

fetch next from c2
into @ProjCode, @ProjectDate, @CostPerDay, @Duration

While @@Fetch_Status = 0
Begin

      DECLARE @COUNTER NUMERIC, @ProjectDate1 smalldatetime
      SELECT @COUNTER = 1
      select @ProjectDate1 = @ProjectDate
    WHILE (@COUNTER <= @Duration)
    BEGIN
      Insert into dbo.#Cash (ProjCode, ProjectDate, CostPerDay) VALUES (@ProjCode, @ProjectDate1, @CostPerDay)
      SELECT @COUNTER = @COUNTER +1
      Select @ProjectDate1 = @ProjectDate1 + 1
    END
fetch next from c2
into @ProjCode, @ProjectDate, @CostPerDay, @Duration

End

Close c2
Deallocate c2

SELECT max(datename(month, ProjectDate)) AS TheMonth, month([ProjectDate]) as date2, Sum(Costperday) AS Cost
FROM dbo.#cash
where projectdate = between @StartDate and @EndDate
GROUP BY month([ProjectDate])

drop table dbo.#temp1

drop table dbo.#cash
GO

For me this is a working solution, not very scalable and using a lot of server recourses, there must be an easier way! Anyone wanting to take this on it would be much appreciated. Graz perhaps even an article on do’s and don’ts?

Edited by - scott on 01/15/2002 03:21:33

TonyH
Starting Member

29 Posts

Posted - 2002-01-15 : 06:58:58

Hi,

The following code comes with a few caveats. I've had a quick test and in principle it seems to work, but :

- Rounding may be a problem
- The definition of start and end dates (i.e does the project end at the end of the enddate day, or beginning of the enddate day)
- for my quick test I used permanent tables not temp ones
- I haven't done any boundary testing, i.e first/last day of month etc

Here goes :

create table monthtotals (Month varchar(20), total money)

create table tempcost (
ProjCode varchar(12),
startdate datetime,
EndDate datetime,
Duration int ,
CostPerDay decimal(28,18) )
-- to minimize rounding problems, decimal(28,18) ?

insert tempcost
SELECT
ProjCode,
startdate ,
EndDate ,
DateDiff(day, startdate, enddate)+1 ,
cast(Cost as decimal(28,18)) / (DateDiff(day, startdate, enddate)+1)
FROM Projects

declare @lastday datetime

while 1 = 1
begin

-- determine the last day of the month for the earliest startdate
select @lastday = dateadd(mm,1,dateadd(dd,-(datepart(dd,startdate)-1), startdate))
from tempcost
where startdate = (select min(startdate) from tempcost)

-- count cost for the days for projects in that month to end of month or project enddate
insert monthtotals
select datename(mm,startdate),
sum(case
when enddate >= @lastday then CostPerDay*datediff(dd,startdate, @lastday)
else CostPerDay*datediff(dd,startdate, enddate)
end )
from tempcost
where startdate < @lastday
group by datename(mm,startdate)

-- move the start date up to beginning of next month
update tempcost
set startdate = @lastday
where startdate < @lastday

-- delete any projects where enddate passed
delete tempcost
where startdate >= enddate

--check if no rows left to calculate
if not exists (select 'X' from tempcost)
break

end

select * from monthtotals

Hope this helps.

TonyH
www.SQLCoder.com - Free Code generation for SQL Server 7/2000

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-15 : 08:27:08

Try something like this:


SELECT TheMonth, TheYear,
  SUM(COALESCE((DATEDIFF(day, FirstCostedProjectDateOfMonth,
      LastCostedProjectDateOfMonth) + 1) * CostPerDay, 0)) AS Cost
FROM (
  SELECT ProjCode,
    MONTH(FirstCostedDateOfMonth) AS TheMonth,
    YEAR(FirstCostedDateOfMonth) AS TheYear,
    CASE WHEN FirstCostedDateOfMonth > StartDate
      THEN FirstCostedDateOfMonth
      ELSE StartDate
    END AS FirstCostedProjectDateOfMonth,
    CASE WHEN LastCostedDateOfMonth < EndDate
      THEN LastCostedDateOfMonth
      ELSE EndDate
    END AS LastCostedProjectDateOfMonth,
    Cost / (DATEDIFF(day, StartDate, EndDate) + 1) AS CostPerDay
  FROM (
    SELECT
      CASE WHEN @StartDate > FirstDateOfMonth
        THEN @StartDate
        ELSE FirstDateOfMonth
      END AS FirstCostedDateOfMonth,
      CASE WHEN @EndDate < LastDateOfMonth
        THEN @EndDate
        ELSE LastDateOfMonth
      END AS LastCostedDateOfMonth
    FROM (
      SELECT FirstDateOfMonth,
         DATEADD(day, -1, DATEADD(month, 1, FirstDateOfMonth)) AS LastDateOfMonth
      FROM (
        SELECT DATEADD(month, N, CAST(STUFF(CONVERT(varchar, @startDate, 112),
            7, 2, '01') AS smalldatetime)) AS FirstDateOfMonth
        FROM Numbers
        WHERE N <= DATEDIFF(month, @StartDate, @EndDate)
      ) Months
    ) MonthDates
  ) CostedMonthDates
  LEFT JOIN Projects ON StartDate <= LastCostedDateOfMonth
    AND EndDate >= FirstCostedDateOfMonth
) CostedProjectMonthDates
GROUP BY TheYear, TheMonth
ORDER BY TheYear, TheMonth

Where Numbers is a sufficiently large, single column (N) table of non-negative integers.
To explain the subqueries:
Months gets the starts of the months we are reporting.
MonthDates gets the last date of the months (just avoiding subexpression duplication)
CostedMonthDates (which you may want to remove), excludes parts of months outside the @StartDate to @EndDate period.
CostedProjectMonthDates joins in the projects for those months (or month parts) where they are running. The LEFT JOIN ensures all months get included even when no projects are running.
Finally, the cost per day is multiplied by number of days the project was running in that month (or part month).

Edited by - Arnold Fribble on 01/15/2002 14:36:02

Scott
Posting Yak Master

145 Posts

Posted - 2002-01-16 : 02:56:27

Haven't manualy done the math but it seems to work great and performance is miles better, Thanks! :-)

Have added the following:
SELECT TheMonth, TheYear, MonthName,
....
) CostedProjectMonthDates
inner JOIN Months on Months.MonthNum = TheMonth
GROUP BY TheYear, TheMonth, MonthName
ORDER BY TheYear, TheMonth

Where the table months has two fields MonthName, a list of all the months, and MonthNum, an interger list of all the months.
This includes the month names in the result set.

Is my solution to this also perhaps just a quick fix. Can you convert an integer number between 1 and 12 into a month name?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-16 : 04:04:58

DATENAME(month, DATEADD(month, TheMonth, '20011201'))

Scott
Posting Yak Master

145 Posts

Posted - 2002-01-16 : 04:14:16

quote:

DATENAME(month, DATEADD(month, TheMonth, '20011201'))

Very clever, the simpler the better.
Thanks!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-16 : 16:59:21

Hmm. I've been tinkering around with this query, just to see what the performance is like. Running it on the following random data for 10000 projects with @startdate = '20000615', @enddate = '20020614' (on SQL Server 2000), I was very disappointed to see it take over a minute.


SET NOCOUNT ON
DELETE FROM Projects
DECLARE @i int, @s smalldatetime
SET @i = 0
WHILE @i < 10000
BEGIN
  SET @s = DATEADD(day, FLOOR(RAND() * 500.0), '20000101')
  INSERT INTO Projects VALUES (
    'pr' + CONVERT(varchar, @i),
    @s,
    DATEADD(day, FLOOR(RAND() * 500.0), @s), RAND()*10000
  )
  SET @i = @i + 1
END
SET NOCOUNT OFF

It seems to be massively misestimating intermediate row counts and generating a plan that does huge amounts of recalculation and a bogus lazy spool. Breaking out the CostedMonthDates subquery into a temporary table -- which should be no problem as it's only one row per reported month -- improves things greatly: down to 2 seconds!


SELECT
  CASE WHEN @StartDate > FirstDateOfMonth
    THEN @StartDate
    ELSE FirstDateOfMonth
  END AS FirstCostedDateOfMonth,
  CASE WHEN @EndDate < LastDateOfMonth
    THEN @EndDate
    ELSE LastDateOfMonth
  END AS LastCostedDateOfMonth
INTO #CostedMonthDates
FROM (
  SELECT FirstDateOfMonth,
     DATEADD(day, -1, DATEADD(month, 1, FirstDateOfMonth)) AS LastDateOfMonth
  FROM (
    SELECT DATEADD(month, N, CAST(STUFF(CONVERT(varchar, @startDate, 112),
        7, 2, '01') AS smalldatetime)) AS FirstDateOfMonth
    FROM Numbers
    WHERE N <= DATEDIFF(month, @StartDate, @EndDate)
  ) Months
) MonthDates


SELECT DATENAME(month, DATEADD(month, themonth, '20001201')) MonthName,
  TheMonth, TheYear,
  SUM(COALESCE((DATEDIFF(day, FirstCostedProjectDateOfMonth,
      LastCostedProjectDateOfMonth) + 1) * CostPerDay, 0)) AS Cost
FROM (
  SELECT ProjCode,
    MONTH(FirstCostedDateOfMonth) AS TheMonth,
    YEAR(FirstCostedDateOfMonth) AS TheYear,
    CASE WHEN FirstCostedDateOfMonth > StartDate
      THEN FirstCostedDateOfMonth
      ELSE StartDate
    END AS FirstCostedProjectDateOfMonth,
    CASE WHEN LastCostedDateOfMonth < EndDate
      THEN LastCostedDateOfMonth
      ELSE EndDate
    END AS LastCostedProjectDateOfMonth,
    Cost / (DATEDIFF(day, StartDate, EndDate) + 1) AS CostPerDay
  FROM #CostedMonthDates
  LEFT JOIN Projects ON StartDate <= LastCostedDateOfMonth
    AND EndDate >= FirstCostedDateOfMonth
) CostedProjectMonthDates
GROUP BY TheYear, TheMonth
ORDER BY TheYear, TheMonth


DROP TABLE #CostedMonthDates

Edited by - Arnold Fribble on 01/16/2002 17:11:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources