February 20, 2014 at 7:14 am
Hello,
I need to sum float values, which are different for different periods of time. I have following table: store, product, from_date, to_date, stock
Example data:
1; 1; 2014-02-01; 2014-02-05; 10
1; 1; 2014-02-03; 2014-02-28; 5
1; 1; 2014-02-15; null; 20
2; 1; 2014-01-01; null; 20
Expected result:
1; 1; 2014-02-01; 2014-02-02; 10
1; 1; 2014-02-03; 2014-02-05; 15
1; 1; 2014-02-06; 2014-02-14; 5
1; 1; 2014-02-15; 2014-02-28; 25
1; 1; 2014-03-01; null; 20
2; 1; 2014-01-01; null; 20
Any ideas how do achieve such result not using cursor? This is the only thing which came to my mind, but it is very slow 🙁
Thanks in advance
Edit:
I forgot to mention that there can be gaps in the periods and rows with the same dates for particular store and product. Here is script to create table and insert sample data:
CREATE TABLE dates(
rn int Identity(1,1),
DateCreated datetime default GETDATE(),
store int NOT NULL,
product varchar(20) NOT NULL,
FROM_DATE datetime not NULL,
TO_DATE datetime NULL,
stock float not null default 0
)
go
insert into dates (store, product, from_date, to_date, stock) values (1, '1', convert(datetime, '20140201', 112), convert(datetime, '20140205', 112), 10);
insert into dates (store, product, from_date, to_date, stock) values (1, '1', convert(datetime, '20140203', 112), convert(datetime, '20140228', 112), 5);
insert into dates (store, product, from_date, to_date, stock) values (1, '1', convert(datetime, '20140215', 112), null, 20);
insert into dates (store, product, from_date, to_date, stock) values (2, '1', convert(datetime, '20140101', 112), null, 20);
insert into dates (store, product, from_date, to_date, stock) values (3, '1', convert(datetime, '20140101', 112), null, 30);
insert into dates (store, product, from_date, to_date, stock) values (3, '1', convert(datetime, '20140101', 112), null, 30);
insert into dates (store, product, from_date, to_date, stock) values (4, '1', convert(datetime, '20140101', 112), convert(datetime, '20140131', 112), 50);
insert into dates (store, product, from_date, to_date, stock) values (4, '1', convert(datetime, '20140215', 112), convert(datetime, '20140228', 112), 50);
February 20, 2014 at 7:48 am
What constitutes a period of time? Day/Month/year/etc?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 20, 2014 at 7:50 am
I cannot see from your example, what kind of logic splits set of dates:
FROM 2014-02-01 TO 2014-02-05
FROM 2014-02-03 TO 2014-02-28
FROM 2014-02-15
to
FROM 2014-02-01 TO 2014-02-02
FROM 2014-02-03 TO 2014-02-05
FROM 2014-02-06 TO 2014-02-14
FROM 2014-02-15 TO 2014-02-28
FROM 2014-03-01
Why "FROM 2014-02-01 TO 2014-02-05" split to
"FROM 2014-02-01 TO 2014-02-02" and "FROM 2014-02-03 TO 2014-02-05"
but not to
"FROM 2014-02-01 TO 2014-02-03" and "FROM 2014-02-04 TO 2014-02-05"
?
February 20, 2014 at 7:56 am
+1 Eugene, we need to know how to slice the dates in order to provide the sums...
Without having any idea whatsoever, this might get the OP started ;WITH CTE AS (
SELECT
Stock, Product,
FROM_DATE,
TO_DATE ,
DATEDIFF(d, FROM_DATE, ISNULL(TO_DATE, GETDATE())) Period
FROM #Dates
)
SELECT SUM(Stock) [StockSum], Period
FROM CTE
GROUP BY Period
ORDER BY Period
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 20, 2014 at 7:56 am
I don't know if I understand your question correctly, but period in my case is time between FROM_DATE and TO_DATE.
I'm not interested in time difference, just dates. However period can be "0" when FROM_DATE = TO_DATE, which means this stock is only for 1 day in store or can be "infinite" when TO_DATE is null. It means we don't know when to stop selling this product.
I hope this answers your question.
Edit:
I need to split (2014-02-01, 2014-02-05) into (2014-02-01, 2014-02-02) and (2014-02-03, 2014-02-05) because on 2014-02-03 stock increases +5 pieces
FROM_DATETO_DATESTOCK
2014-02-012014-02-0210
2014-02-032014-02-0515
2014-02-062014-02-145
2014-02-152014-02-2825
2014-03-01null20
February 20, 2014 at 9:28 am
How do you define that it goes from 10 to 15 and then back to 5?
It looks like a running total problem. Before 2012, those problems have several solutions, the fastest is known as Quirky Update[/url]. Some people won't trust it and a well constructed cursor would be the best option for you.
If we can find out how to partition correctly your data, then the rest is easy. It might help if you post your current code.
February 20, 2014 at 9:47 am
Luis Cazares (2/20/2014)
How do you define that it goes from 10 to 15 and then back to 5?
Result stock for partucular date is calculated:
select ISNULL(SUM(stock), 0) from dates WITH (NOLOCK)
where store = @store and product = @product
and from_date <= @date and to_date >= @date
So 2014-02-02 there will be rows:
(2014-02-01, 2014-02-05); 10
But next day 2014-02-03 there will be rows:
(2014-02-01, 2014-02-05); 10
(2014-02-03, 2014-02-28); 5
which gives 15 in total.
I don't know how to describe it in other way. Please let me know if it is clear now.
My code now is a little bit complicated and long :blush: but works (slow - I have more than 14.000.000 records in dates table)
1. Update TO_DATE from null to 2199-12-31
2. Create "calendar table"
3. Loop through all stores and products and calculate stock
update dates set to_date = CONVERT(datetime, '21991231', 112) where to_date is null
insert into #dates_cal(store, product, CAL_DATE)
select distinct * from (
select store, product, from_date from dates
union
select store, product, to_date from dates
) x
DECLARE @store int, @prev_store int
DECLARE @product varchar(20), @prev_prod varchar(20)
DECLARE @from_date datetime, @to_date datetime, @date datetime
DECLARE @Stock float, @prev_stock float, @next_stock float
DECLARE dates_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT c.*
FROM #dates_cal c WITH (NOLOCK)
order by store, product, cal_date
OPEN dates_cursor
FETCH NEXT FROM dates_cursor INTO @store, @product, @date
Set @prev_store = -1
Set @prev_prod = '#'
WHILE @@FETCH_STATUS = 0
BEGIN
if (@store <> @prev_store or @product <> @prev_prod) begin
Set @from_date = @date
Set @prev_stock = null
end
Set @Stock = 0
select @Stock = ISNULL(SUM(stock), 0) from dates WITH (NOLOCK)
where store = @store and product = @product
and from_date <= @date and to_date >= @date
if (ISNULL(@stock, 0) <> ISNULL(@prev_stock, 0) and @prev_stock is not null) begin
insert into dates_out (store, product, FROM_DATE, TO_DATE, stock)
values (@store, @product, @from_date, CASE WHEN (@date-1) = CONVERT(datetime, '21991231', 112) THEN null ELSE (@date-1) END, @prev_stock)
Set @from_date = @date
end
Set @prev_stock = @Stock
Set @next_stock = 0
select @next_stock = ISNULL(SUM(stock), 0) from dates WITH (NOLOCK)
where store = @store and product = @product
and from_date <= (@date+1) and to_date >= (@date+1)
if (ISNULL(@stock, 0) <> ISNULL(@next_stock, 0)) begin
insert into dates_out (store, product, FROM_DATE, TO_DATE, stock)
values (@store, @product, @from_date, CASE WHEN (@date) = CONVERT(datetime, '21991231', 112) THEN null ELSE (@date) END, @Stock)
Set @from_date = @date + 1
Set @prev_stock = @next_stock
end
Set @prev_store = @store
Set @prev_prod = @product
FETCH NEXT FROM dates_cursor INTO @store, @product, @date
END
CLOSE dates_cursor
DEALLOCATE dates_cursor
February 20, 2014 at 9:48 am
...
I need to split (2014-02-01, 2014-02-05) into (2014-02-01, 2014-02-02) and (2014-02-03, 2014-02-05) because on 2014-02-03 stock increases +5 pieces
FROM_DATETO_DATESTOCK2014-02-012014-02-0210
2014-02-032014-02-0515
2014-02-062014-02-145
2014-02-152014-02-2825
2014-03-01null20
What in your "sample data " indicates that the stock changed on the 2014-02-03?
We can see in your expected results only...
Don't you have some other table which contains some sort of daily transactions, so that where your "split" day is coming from?
February 20, 2014 at 10:35 am
This might help others to complete the requirement (and maybe improve it). It's missing the gaps problem (shown on store 4).
If the gaps aren't a problem, then this should do it. If you have any questions, feel free to ask and document the code as needed.
CREATE TABLE #dates(
rn int Identity(1,1),
DateCreated datetime default GETDATE(),
store int NOT NULL,
product varchar(20) NOT NULL,
FROM_DATE datetime not NULL,
TO_DATE datetime NULL,
stock float not null default 0
)
go
insert into #dates (store, product, from_date, to_date, stock) values
(1, '1', convert(datetime, '20140201', 112), convert(datetime, '20140205', 112), 10)
,(1, '1', convert(datetime, '20140203', 112), convert(datetime, '20140228', 112), 5)
,(1, '1', convert(datetime, '20140215', 112), null, 20)
,(2, '1', convert(datetime, '20140101', 112), null, 20)
,(3, '1', convert(datetime, '20140101', 112), null, 30)
,(3, '1', convert(datetime, '20140101', 112), null, 30)
,(4, '1', convert(datetime, '20140101', 112), convert(datetime, '20140131', 112), 50)
,(4, '1', convert(datetime, '20140215', 112), convert(datetime, '20140228', 112), 50);
WITH E1(N) AS(
SELECT N FROM( VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(N)
),
E2(N) AS(
SELECT a.N FROM E1 a, E1 b
),
E4(N) AS(
SELECT a.N FROM E2 a, E2 b
),
Calendar AS(
SELECT TOP( SELECT DATEDIFF( DAY, MIN(FROM_DATE), MAX(TO_DATE)) + 1 FROM #dates)
DATEADD( DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, (SELECT MIN(FROM_DATE) FROM #dates)) caldate
FROM E4
),
Dates AS(
SELECT store, product, caldate, SUM( stock) stock
FROM Calendar t
JOIN #dates d ON t.caldate BETWEEN d.FROM_DATE AND ISNULL( d.TO_DATE, '9999-01-01')
GROUP BY store, product, caldate
)
SELECT store, product, MIN( caldate) FROM_DATE, MAX(caldate) TO_DATE, stock
FROM Dates
GROUP BY store, product, stock
ORDER BY store, product, FROM_DATE
SELECT *
FROM #dates
ORDER BY store, product, FROM_DATE
DROP TABLE #dates
February 20, 2014 at 1:11 pm
Eugene Elutin (2/20/2014)
What in your "sample data " indicates that the stock changed on the 2014-02-03?We can see in your expected results only...
Don't you have some other table which contains some sort of daily transactions, so that where your "split" day is coming from?
System works as follows - there are many factors which calculate how many pieces of product should be in the store for given period of time. There are many factors (let's call them F1, F2 and F3) and when they are calculated stock with given start and end dates is inserted into "dates" table shown above. Total stock of product in store changes in time and logistics should know those changes to properly order goods. So total stock is sum of calculated factors (let's consider product '1' in store 1):
F1: 2014/02/01 - 2014/02/05; stock = 10
F2: 2014/02/03 - 2014/02/28; stock = 5
F3: 2014/02/15 - 9999/12/31; stock = 20
Now my job is to find intersection of such periods and calculate total stock for them (sum all values) like in the picture from attachment.
Luis Cazares (2/20/2014)
This might help others to complete the requirement (and maybe improve it). It's missing the gaps problem (shown on store 4).If the gaps aren't a problem, then this should do it. If you have any questions, feel free to ask and document the code as needed.
Thank you for your suggestion. As soon as I arrive my office, I will try your code. Maybe then I'll have some questions.
February 20, 2014 at 1:42 pm
Here's the final solution with the gaps problem solved (Hoping you won't have dates that go over 25 years in the future).
WITH E1(N) AS(
SELECT N FROM( VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(N) --10 rows
),
E2(N) AS(
SELECT a.N FROM E1 a, E1 b -- 10 * 10 = 100 rows
),
E4(N) AS(
SELECT a.N FROM E2 a, E2 b -- 100 * 100 = 10,000 rows
),
Calendar AS(
SELECT --TOP( SELECT DATEDIFF( DAY, MIN(FROM_DATE), MAX(TO_DATE)) + 1 FROM #dates)
DATEADD( DD, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, (SELECT MIN(FROM_DATE) FROM #dates)) caldate
FROM E4 --We create 10,000 dates starting from the first TO_DATE
),
Dates AS(
SELECT store,
product,
caldate,
SUM( stock) stock, -- joins stocks that have days which overlap
DATEADD(dd, - ROW_NUMBER() OVER ( PARTITION BY store, product ORDER BY caldate), caldate) AS DateGroup --creates a field to group islands of contiguous dates
FROM Calendar t
JOIN #dates d ON t.caldate BETWEEN d.FROM_DATE AND ISNULL( d.TO_DATE, '9999-01-01') --you might consider updating all of your nulls to avoid this function
GROUP BY store, product, caldate
)
SELECT store,
product,
MIN( caldate) FROM_DATE, --first date of group
CASE WHEN MAX(caldate) < DATEADD( YEAR, 25, GETDATE())
THEN MAX(caldate) END TO_DATE, -- last date of group or null if it's the maximum
stock
FROM Dates
GROUP BY store, product, stock, DateGroup
ORDER BY store, product, FROM_DATE
Reference for the gaps solution: http://www.sqlservercentral.com/articles/T-SQL/71550/
February 20, 2014 at 1:57 pm
Very creative Luis, kudos!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 20, 2014 at 2:02 pm
MyDoggieJessie (2/20/2014)
Very creative Luis, kudos!
Thank you! It became clear after realizing it wasn't a running total.
February 24, 2014 at 5:32 am
Luis Cazares (2/20/2014)
Here's the final solution with the gaps problem solved (Hoping you won't have dates that go over 25 years in the future).(...)
Thank you for your solution and sorry for such late response. I'm testing it right, but I have faced problem with TEMPDB size (I have more than 60GB of free space):
Msg 1101, Level 17, State 10, Line 1
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
It runs for 30 minutes and then throws this error. Is there any way to eliminate it?
Also I would be very happy if you could explain your solution a little bit - I'm not so good in SQL programming :blush:
February 24, 2014 at 10:10 am
I updated my post to include some comments on it. I hope it's easier to understand that way. Try running it one part at a time to understand what it's doing.
You could change the CTE calendar to a physical table. I'm not sure if it would help but you could try. Verify the maximum date for the calendar when validating TO_DATE in the last select.
If you have more questions, I'll try to explain further.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply