September 28, 2012 at 11:48 am
DECLARE @RNum int
DECLARE @DDate datetime
DECLARE @MaxBolTime int
DECLARE @GGallons numeric (14,3)
DECLARE @BGallons numeric (14,3)
DECLARE @NGallons numeric (14,3)
DECLARE db_cursor_update CURSOR FOR
SELECT Rel, DDate, MAX(MaxBolTime)
FROM dbo.Fact
group by Rel, DDate
having count(*) > 1
OPEN db_cursor_update
FETCH NEXT FROM db_cursor_update INTO @RNum, @DDate, @MaxBolTime
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.FACT
SET NGallons = ( SELECT SUM(NGallons) FROM dbo.FACT
WHERE Rel = @RNum andDDate = @DDate ),
BGallons = ( SELECT SUM(BGallons) FROM dbo.FACT WHERE Rel = @ReleaseNum and DDate = @DDate ) ,
GGallons = ( SELECT SUM(GGallons) FROM dbo.FACT WHERE Rel = @RNum and DDate = @DDate )
WHERE MaxBolTime = @MaxBolTime and Rel = @RNum andDDate = @DDate
FETCH NEXT FROM db_cursor_update INTO @RNum, @DDate, @MaxBolTime
END
CLOSE db_cursor_update
DEALLOCATE db_cursor_update
It process 2041310 rows in the fact table and takes 10 mins .. Is there a better way to do this instead of a cusror?
September 28, 2012 at 11:59 am
Yes there is. The answer starts by reading the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 12:03 pm
Yes, and there's a better way for you to post the question so we can help you.
If you give us DDL, Sample Data and Expected Results, we'll be able to give you a correct solution.
September 28, 2012 at 1:24 pm
FACT TAble
( ID int identity(1,1),
OrderID INT,
relNum int,
DDATe DATE,
BGallons numeric (14,3),
GGallons numeric (14,3),
NGallons numeric (14,3),
MAXBolTime INT
)
First case
IF we have same relNum,DDate and same MAXBOLTIME
Insert into FACT
SELECT 1,100,1111,2012-2-27,1234,1111,3333,444
UNION
SELECT 2,200,1111,2012-2-27,2000,2222,1111,444
UNION
SELECT 3,300,1111,2012-2-27,1,2,3,444
If RelNum and DDAte and MaxBolTime is same then add BGallons,GGallons,NGallons for all three rows and Just keep one row and delete the two e.g
we need 3,300,1111,2012-2-27,3235,3336,4447,444
2nd case
IF we have same relNum,DDate and differnt MAXBOLTIME
Insert into FACT
SELECT 4,400,1111,2012-2-27,1234,1111,3333,489
UNION
SELECT 5,500,1111,2012-2-27,2000,2222,1111,499
UNION
SELECT 6,600,1111,2012-2-27,1,2,3,466
we need the row with maxbol (499) and delete the two e.g
we need 5,500,1111,2012-2-27,3235,3336,4447,444
we still need to summ the NGallons,BGallons,GGallons
3rd case :
IF the combination of relNum,DDate is just one , then leave it alone
Insert into FACT
SELECT 7,800,1111,2012-8-27,134,111,333,90
September 28, 2012 at 1:33 pm
We are getting there...your ddl doesn't actually run as is.
Try this for the Fact table
create table FACT
(
ID int identity(1,1),
OrderID INT,
relNum int,
DDATe DATE,
BGallons numeric (14,3),
GGallons numeric (14,3),
NGallons numeric (14,3),
MAXBolTime INT
)
Your insert statements don't work either. You have specified the identity and your dates don't work.
Insert into FACT
SELECT 100,1111,'2012-2-27',1234,1111,3333,444
UNION
SELECT 200,1111,'2012-2-27',2000,2222,1111,444
UNION
SELECT 300,1111,'2012-2-27',1,2,3,444
Am I being picky? Maybe.
Am I trying to help you for free? Yes.
Does your code frustrate me because I have to fiddle with setting up the problem instead of working on the solution? Yes.
Now what we really need is enough sample data to cover all the scenarios. From that sample data what should be the final result?
It seems to me that you are going to end up with duplicate data based on your explanation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 2:10 pm
Updated 2nd case data
IF we have same relNum,DDate and differnt MAXBOLTIME
Insert into FACT
SELECT 4,400,7999,2012-1-27,1234,1111,3333,489
UNION
SELECT 5,500,7999,2012-1-27,2000,2222,1111,499
UNION
SELECT 6,600,7999, 2012-1-27,1,2,3,466
we need the row with maxbol (499) and delete the two e.g
we need 5,500,7999,2012-1-27,3235,3336,4447,444
we still need to summ the NGallons,BGallons,GGallons
September 28, 2012 at 3:23 pm
PSB (9/28/2012)
Updated 2nd case dataIF we have same relNum,DDate and differnt MAXBOLTIME
Insert into FACT
SELECT 4,400,7999,2012-1-27,1234,1111,3333,489
UNION
SELECT 5,500,7999,2012-1-27,2000,2222,1111,499
UNION
SELECT 6,600,7999, 2012-1-27,1,2,3,466
we need the row with maxbol (499) and delete the two e.g
we need 5,500,7999,2012-1-27,3235,3336,4447,444
we still need to summ the NGallons,BGallons,GGallons
Your sample data, description and stated output do not match. The row you stated above as wanting to keep is not in the select (MaxBolTime 444 is not in that group).
You need to realize that I can't see your screen and I am not familiar with your project. Let's try this from the top. I have fixed your ddl and sample data so they will run.
create table FACT
(
ID int identity(1,1),
OrderID INT,
relNum int,
DDATe DATE,
BGallons numeric (14,3),
GGallons numeric (14,3),
NGallons numeric (14,3),
MAXBolTime INT
)
--scenario 1
Insert into FACT
SELECT 100,1111,'2012-2-27',1234,1111,3333,444 UNION all
SELECT 200,1111,'2012-2-27',2000,2222,1111,444 UNION all
SELECT 300,1111,'2012-2-27',1,2,3,444
--scenario 2
Insert into FACT
SELECT 400,1111,'2012-2-27',1234,1111,3333,489 UNION all
SELECT 500,1111,'2012-2-27',2000,2222,1111,499 UNION all
SELECT 600,1111,'2012-2-27',1,2,3,466
--scenario 3
Insert into FACT
SELECT 800,1111,'2012-8-27',134,111,333,90
select * from fact
Your explanation is a little unclear but I think I know what you are after. What would really help is if you could create a temp table like:
create table #FactFinal
(
ID int identity(1,1),
OrderID INT,
relNum int,
DDATe DATE,
BGallons numeric (14,3),
GGallons numeric (14,3),
NGallons numeric (14,3),
MAXBolTime INT
)
Because you are planning on changing data and removing some rows from the Fact table this new temp table is place to hold what your Fact table should look like when your code completes. If you can create the insert statements of what you want in this table it would go a long way to knowing if what we are doing is correct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply