Get the max out of a series of periods
-
Hi all,
Can someone give me some help here?
I have a table with the following data (date, value)
What i need to find out is the highest sum of values out of x dateranges.
The daterange is a specified length of days.
So to give an example:
01-01-2006 -> 15-01-2006 = 1000 (1000 = sum(values) in specified daterange)
06-01-2006 -> 31-01-2006 = 700
01-02-2006 -> 15-02-2006 = 900
(for this example i used just dates, while in reality i need the sum in 20 days periods)
So the number i need to find is 1000.
Anyone have an idea how to accomplish this
Thank in advance
EriSan
-
If you have a query that produces a list of ranges and sums, like so:
select daterange, sum(amt) as sumValue
from myTable
group by daterange
..you can make a virtual table of it, and query for the max sum
select top 1 x.daterange, x.sumValue
from (
select daterange, sum(amt) as sumValue
from myTable
group by daterange
) x
order by x.sumValue desc
/Kenneth
-
You mean like this?
DECLARE @FromDate DATETIME,
@ToDate DATETIME,
@DateRange INT
SELECT @FromDate = '2004-01-15',
@ToDate = '2008-02-20',
@DateRange = 20
SELECT MIN(Date) FirstDate,
MAX(Date) LastDate,
SUM(Value)
FROM Table2
WHERE Date BETWEEN @FromDate AND @ToDate
GROUP BY DATEDIFF(dd, @FromDate, Date) / @DateRange
ORDER BY SUM(Value) DESC,
MIN(Date) DESC
N 56°04'39.16"
E 12°55'05.25" -
Or more specific
DECLARE @FromDate DATETIME,
@ToDate DATETIME,
@DateRange INT
SELECT @FromDate = '2004-01-15',-- This is the starting date in the range
@ToDate = '2008-02-20',-- This is the ending date in the range
@DateRange = 20-- This is the date count in the range. Can be 15 also.
SELECT TOP 1 MIN(Date) FirstDate,
MAX(Date) LastDate,
SUM(Value)
FROM Table2
WHERE Date BETWEEN @FromDate AND @ToDate
GROUP BY DATEDIFF(dd, @FromDate, Date) / @DateRange
ORDER BY SUM(Value) DESC,
MIN(Date) DESC
N 56°04'39.16"
E 12°55'05.25" -
The data is not stored as a date range. See example.
"I have a table with the following data (date, value)"
N 56°04'39.16"
E 12°55'05.25" -
It's a bit more complicated. I forgot to mention some details.
Here is some data:
Date---------------SKU------Value
------------------------------------
01-01-2006..............A.........20
01-01-2006..............B.........20
02-01-2006..............A.........30
02-01-2006..............A.........40
02-01-2006..............B.........10
04-01-2006..............A.........20
04-01-2006..............A.........50
05-01-2006..............A.........40
05-01-2006..............A.........10
Now suppose my daterange is 2 days, so in the above table i need to get the total for :
01-01-2006 - 02-01-2006 (2days) Sum for SKU A = 90
04-01-2006 - 05-01-2006 (next 2days) Sum for SKU A = 120
Basically i want to feed my querry (SP) a timeframe (2) and an SKU (A) and get as result 120.
Thanks for your time.
Eric
-
Here we go again...
DECLARE @FromDate DATETIME,
@ToDate DATETIME,
@DateRange INT,
@SKU VARCHAR(1)
SELECT @FromDate = '2004-01-15',-- This is the starting date in the range
@ToDate = '2008-02-20',-- This is the ending date in the range
@DateRange = 20,-- This is the date count in the range. Can be 15 also. Or 2.
@SKU = 'A'
SELECT TOP 1 MIN(Date) FirstDate,
MAX(Date) LastDate,
SUM(Value)
FROM Table2
WHERE Date BETWEEN @FromDate AND @ToDate
AND SKU = @SKU
GROUP BY DATEDIFF(dd, @FromDate, Date) / @DateRange
ORDER BY SUM(Value) DESC,
MIN(Date) DESC
N 56°04'39.16"
E 12°55'05.25" -
step 1 create a sequential numbers table (I cannot find my script so heres a manual creation)
create table #nbrs (cnt int)
insert into #nbrs
select 0 Union
select 1 Union
select 2 Union
select 3 Union ... (need more)
table to query
create table mytable
( id int identity,
trandate datetime,
amount integer
)
INSERT INTO mytable (trandate, amount)
SELECT '2005/01/01' , 10 UNION
SELECT '2005/01/02' , 14 UNION
SELECT '2005/02/01' , 20 UNION
SELECT '2005/03/01' , 11 UNION
SELECT '2005/04/04' , 14 UNION
SELECT '2005/04/05' , 15
select dateadd(ww, n.cnt, '2005/01/01'),
dateadd(ww, n.cnt + 1, '2005/01/01'),
max(amount)
from mytable dt, #nbrs n
where dt.trandate between dateadd(ww, n.cnt, '2005/01/01')
and dateadd(ww, n.cnt + 1, '2005/01/01')
group by dateadd(ww, n.cnt, '2005/01/01'),
dateadd(ww, n.cnt + 1, '2005/01/01')
Should give you the date range and the max value....
daralick
-
Should the range be as followed?
01-01-2006..............A.........20
01-01-2006..............B.........20
02-01-2006..............A.........30
02-01-2006..............A.........40
02-01-2006..............B.........10
04-01-2006..............A.........20
04-01-2006..............A.........50
05-01-2006..............A.........40
05-01-2006..............A.........10
Now suppose my daterange is 2 days, so in the above table i need to get the total for :
01-01-2006 - 02-01-2006 (2days) Sum for SKU A = 90
04-01-2006 - 04-01-2006 (next 2days) Sum for SKU A = 70
05-01-2006 - 05-01-2006 (next 2days) Sum for SKU A = 50
N 56°04'39.16"
E 12°55'05.25" -
Good point, actualy it would look like this:
01-01-2006 - 02-01-2006 (2days) Sum for SKU A = 90
03-01-2006 - 04-01-2006 (next 2days) Sum for SKU A = 70
05-01-2006 - 06-01-2006 (next 2days) Sum for SKU A = 50
....
(sorry for the confusion, i'm damn tired)
-
This code should be used for fixed daterange (always x virtual days in the calendar for the range)
DECLARE @FromDate DATETIME,
@ToDate DATETIME,
@DateRange INT,
@SKU VARCHAR(1)
SELECT @FromDate = '2004-01-15',-- This is the starting date in the range
@ToDate = '2008-02-20',-- This is the ending date in the range
@DateRange = 20,-- This is the date count in the range. Can be 15 also. Or 2.
@SKU = 'A'
SELECT TOP 1 MIN(Date) FirstDate,
MAX(Date) LastDate,
SUM(Value)
FROM Table2
WHERE Date BETWEEN @FromDate AND @ToDate
AND SKU = @SKU
GROUP BY DATEDIFF(dd, @FromDate, Date) / @DateRange
ORDER BY SUM(Value) DESC,
MIN(Date) DESC
This code should be used for floating daterange (always x present days in the table for the range)
DECLARE @FromDate DATETIME,
@ToDate DATETIME,
@DateRange INT
SELECT @FromDate = '2004-01-15',
@ToDate = '2008-02-20',
@DateRange = 20
DECLARE @Dates TABLE
(
ID INT IDENTITY(0, 1),
Date DATETIME
 
INSERT INTO @Dates
(
Date
 
SELECT Table2.Date
FROM Table2
WHERE Table2.Date BETWEEN @FromDate AND @ToDate
AND Table2.SKU = @SKU
GROUP BY Table2.Date
ORDER BY Table2.Date
SELECT TOP 1 MIN(d.Date) FirstDate,
MAX(d.Date) LastDate,
SUM(Table2.Value)
FROM Table2
INNER JOIN @Dates d ON d.Date = Table2.Date
WHERE Table2.SKU = @SKU
GROUP BY d.ID / @DateRange
ORDER BY SUM(Table2.Value) DESC,
MIN(d.Date) DESC
N 56°04'39.16"
E 12°55'05.25" -
hmmm, both queries return nothing.
Gonna look deeper into this.
Can you explain me how this works: GROUP BY d.ID / @DateRange
-
- Are your dates stored as datetimes/smalldatetimes or varchar?
- Is @FromDate smaller/earlier/older than @ToDate?
I run the queries against a test set of 10 000 rows of DATETIME and INT and they tooks about 2-3 seconds per each query to complete.
"GROUP BY d.ID / @DateRange"
d.ID is the "serial number" for all dates in the table starting from 0 with the oldest (smallest) date.
d.ID Date
0 2005-01-17
1 2005-01-23
2 2005-01-24
The integer division divides the id with the daterange number. If daterange is 5, all dates with id between 0 and 4 becomes slot 0, all dates between 5 and 9 becomes slot 1, beween 10 and 14 becomes slot 2 and so on... This is for putting the dates into the right "slot" of dateranges, starting with slot 0 from @FromDate.
DATEDIFF(dd, @FromDate, Date) does the same thing, now with calender days starting from @FromDate as the "0 day"/"0 slot".
N 56°04'39.16"
E 12°55'05.25"
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply