# Calculation across date periods - Microsoft SQL / MS SQL Server

Do you want to be all inclusive? Meaning, do you want to include prices that may be half in and half out of your range? For example: your toDate on the last row is beyond your requested range, but the fromDate is within the range. If you don't mind including all prices that occured during your range, then you would use something like this: Select sum(price) as 'Summed Price' from priceTBL where toDate BETWEEN '08/10/2003' and '09/25/2003' or fromDate BETWEEN '08/10/2003' and '09/25/2003' This will include all prices that occured during your period, but does not guarantee that these price ...

1. ## Re: Calculation across date periods

Do you want to be all inclusive? Meaning, do you want to include
prices that may be half in and half out of your range? For example:
your toDate on the last row is beyond your requested range, but the
fromDate is within the range. If you don't mind including all prices
that occured during your range, then you would use something like
this:

Select sum(price) as 'Summed Price' from priceTBL where toDate BETWEEN
'08/10/2003' and '09/25/2003' or fromDate BETWEEN '08/10/2003' and
'09/25/2003'

This will include all prices that occured during your period, but does
not guarantee that these price periods did not extend beyond your
designated range.

Random

On Sun, 17 Aug 2003 15:22:31 +0100, "AndrewM" <net>
wrote:

Random Guest

2. ## Calculation across date periods

Hello,

I need to calculate a total based on a date period. My input values are 10
August to 25 September. As you can see from this example the date period
goes across 4 price periods.

Your help will be much appreciated.

Thanks,
Andrew.

CREATE TABLE priceTBL
(fromDate [datetime] NULL,
toDate [datetime] NULL,
price [money] NULL)
GO

INSERT INTO priceTBL VALUES('08/01/2003', '08/15/2003', 15.30)
INSERT INTO priceTBL VALUES('08/15/2003', '09/01/2003', 20.40)
INSERT INTO priceTBL VALUES('09/01/2003', '09/15/2003', 25.50)
INSERT INTO priceTBL VALUES('09/15/2003', '10/01/2003', 30.60)

GO

AndrewM Guest

3. ## Re: Calculation across date periods

Ahh. I think I see now. Are you asking for the sum of prices for all
days in your time span? If you were only concerned with 8/1 - 8/15
would you expect an answer like 214.20?

On Sun, 17 Aug 2003 16:07:03 +0100, "AndrewM" <net>
wrote:
[/ref]
>10
>>[/ref]
>[/ref]

Random Guest

4. ## Re: Calculation across date periods

If my earlier question was on the right track, then this should work
for you, though you may need to convert the dates.... Can't test it
right now because my server is importing.

Random

Select Sum(Case when fromDate > = '08/10/2003' and toDate <=
'09/25/2003' then (toDate - fromDate)*price
when fromDate <= '08/10/2003' and toDate <= '09/25/2003' then (toDate
- '08/10/2003') * price
when fromDate <= '08/10/2003' and toDate >= '09/25/2003' then
('09/25/2003' - '08/10/2003) * price
when fromDate >= '08/10/2003' and toDate >= '09/25/2003' then
('09/25/2003' - fromDate)* price else 0 end) as 'Total Price'
From priceTBL where toDate BETWEEN
'08/10/2003' and '09/25/2003' or fromDate BETWEEN '08/10/2003' and
'09/25/2003'

On Sun, 17 Aug 2003 16:07:03 +0100, "AndrewM" <net>
wrote:
[/ref]
>10
>>[/ref]
>[/ref]

Random Guest

5. ## Re: Calculation across date periods

"AndrewM" <net> wrote in message news:phx.gbl...

CREATE FUNCTION SumPricePeriods
(start_date DATETIME,
end_date DATETIME)
RETURNS TABLE
AS
RETURN(
SELECT start_date AS start_date,
end_date As end_date,
SUM(DATEDIFF(DAY,
CASE WHEN start_date > fromDate
THEN start_date
ELSE fromDate
END,
CASE WHEN end_date < toDate
THEN end_date
ELSE toDate
END) * price) AS total
FROM priceTBL
WHERE toDate >= start_date AND
fromDate < end_date
)

SELECT *
FROM SumPricePeriods('20030810', '20030925')

start_date end_date total
2003-08-10 00:00:00.000 2003-09-25 00:00:00.000 1086.3000

Regards,
jag

John Guest

6. ## Re: Calculation across date periods

Hello Random,

Thank you very much for your answer, but I see that I wasn't clear enough in
the example. Firstly the dates are from 12 noon to 12 noon and this is why
the dates overlap eg. 08/01/2003 to 08/15/2003 then 08/15/2003 to
09/01/2003. Secondly, the price is per day not per period.

Third, its okay if the date period requested extends beyond the date periods
in the table (I think this is what you were asking).

Thanks,
Andrew.

"Random" <Randomnwhere> wrote in message
news:com... [/ref]
10
>[/ref]

AndrewM Guest

7. ## Re: Calculation across date periods

"AndrewM" <net> wrote in message news:phx.gbl...

Functions were introduced in SQL Server 2000 and I suppose you
are running an earlier version? You can use a stored procedure

CREATE PROCEDURE SumPricePeriods
(start_date DATETIME,
end_date DATETIME)
AS
SELECT start_date AS start_date,
end_date As end_date,
SUM(DATEDIFF(DAY,
CASE WHEN start_date > fromDate
THEN start_date
ELSE fromDate
END,
CASE WHEN end_date < toDate
THEN end_date
ELSE toDate
END) * price) AS total
FROM priceTBL
WHERE toDate >= start_date AND
fromDate < end_date

EXEC SumPricePeriods '20030810', '20030925'

start_date end_date total
2003-08-10 00:00:00.000 2003-09-25 00:00:00.000 1086.3000

Regards,
jag

> news:phx.gbl... [/ref]
> 10
> >
> > CREATE FUNCTION SumPricePeriods
> > (start_date DATETIME,
> > end_date DATETIME)
> > RETURNS TABLE
> > AS
> > RETURN(
> > SELECT start_date AS start_date,
> > end_date As end_date,
> > SUM(DATEDIFF(DAY,
> > CASE WHEN start_date >[/ref]
> fromDate
>
>[/ref]

John Guest

8. ## Re: Calculation across date periods

The CREATE FUNCTION statement needs to be in a separate batch. Try
inserting a 'GO' immediately before this line:
[/ref]

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"AndrewM" <net> wrote in message
news:phx.gbl...
it to
error.
> news:phx.gbl... [/ref][/ref]
values are [/ref][/ref]
period
> >
> > CREATE FUNCTION SumPricePeriods
> > (start_date DATETIME,
> > end_date DATETIME)
> > RETURNS TABLE
> > AS
> > RETURN(
> > SELECT start_date AS start_date,
> > end_date As end_date,
> > SUM(DATEDIFF(DAY,
> > CASE WHEN start_date >[/ref]
> fromDate [/ref]
start_date [/ref]
fromDate [/ref]
toDate [/ref]
end_date
>
>[/ref]

Dan Guest

9. ## Re: Calculation across date periods

Hello Jag,

The function looks great, thank you very much, but I can seem to get it to
work. I pasted into Query yzer but it produces the following error.

Server: Msg 156, Level 15, State 1, Procedure SumPricePeriods, Line 23
Incorrect syntax near the keyword 'SELECT'.

Can you help ?

Thanks,
Andrew.

"John Gilson" <org> wrote in message
news:aOM%a.8044\$nyc.rr.com...
news:phx.gbl... [/ref]
10
>
> CREATE FUNCTION SumPricePeriods
> (start_date DATETIME,
> end_date DATETIME)
> RETURNS TABLE
> AS
> RETURN(
> SELECT start_date AS start_date,
> end_date As end_date,
> SUM(DATEDIFF(DAY,
> CASE WHEN start_date >[/ref]
fromDate

AndrewM Guest

10. ## Re: Calculation across date periods

Thanks,

All working now. I'll remember that one for next time.

Andrew.

"Steve Kass" <edu> wrote in message
news:phx.gbl... [/ref]
to
> >news:phx.gbl...
> >
> >
> >10
> >
> > [/ref][/ref]
period
> >fromDate
> >
> > [/ref][/ref]
toDate
> >
> >
> >
> >[/ref]
>[/ref]

AndrewM Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•