SQL - Stored procedure producing duplicate lines in table

I am execuring an SQL stored proedure but the resulting table produces duplicates and the time which should be in minutes looks wrong, procedure listed below and output....any help most welcome

----------
INSERT INTO temptbl_Stops (StartDate, StopDate, StopTime, DownTimeCode)
SELECT     TOP (100) PERCENT StartDate, StopDate, SUM(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) AS StopTime, DowntimeCode
FROM         dbo.tbl_MachineData
GROUP BY StartDate, StopDate, DowntimeCode
HAVING      (SUM(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) IS NOT NULL) AND DowntimeCode IS NULL
ORDER BY StartDate
--------------
ID     StartDate                                StopDate                                 StopTime     DowntimeCode
107      2014-01-26 15:01:00.000      2014-01-26 15:01:20.000      1.00                   NULL
108      2014-01-26 15:04:50.000      2014-01-26 15:05:00.000      0.33                   NULL
109      2014-01-26 15:01:00.000      2014-01-26 15:01:20.000      1.00                   NULL
110      2014-01-26 15:04:50.000      2014-01-26 15:05:00.000      0.33                   NULL

Thanks for the help
SweetingAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
We might need to see some sample data that's producing the error, but for now you can try putting the word "DISTINCT" just before "TOP(100)":
INSERT INTO temptbl_Stops (StartDate, StopDate, StopTime, DownTimeCode)
SELECT DISTINCT TOP (100) PERCENT 
	StartDate, 
	StopDate, 
	SUM(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) AS StopTime, 
	DowntimeCode
FROM dbo.tbl_MachineData
GROUP BY StartDate, StopDate, DowntimeCode
HAVING (SUM(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) IS NOT NULL) 
	AND DowntimeCode IS NULL
ORDER BY StartDate

Open in new window

0
SweetingAAuthor Commented:
Distinct does not make a difference.

Below is a snap shot of orginal data, as you can see format and date/time are identical - its as if the group function is not working - the records below should only produce a result of 1 line, but at the moment they produce a result of 2 lines.  Also the lines are in sequence as if the procedure is going through the source table twice.

347      2014-01-26 15:01:00.000      Continental          Red             I10                  18121133                  M330121         16      16      17.50      17.50      34      0      2      False      2014-01-26 15:01:00.000      2014-01-26 15:01:20.000      NULL
348      2014-01-26 15:01:10.000      Continental          Red             I10                  18121133                  M330121         16      16      17.50      17.50      34      0      2      False      2014-01-26 15:01:00.000      2014-01-26 15:01:20.000      NULL
349      2014-01-26 15:01:20.000      Continental          Red             I10                  18121133                  M330121         16      16      17.50      17.50      34      0      2      False      2014-01-26 15:01:00.000      2014-01-26 15:01:20.000      NULL
0
SweetingAAuthor Commented:
By the way there is no error just an incorrect result
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Russell FoxDatabase DeveloperCommented:
Gotcha. If I perform the following test with your sample data, I do just get one result row; however, I noticed that your sample has three sets of dates, and the first one does have three different values:
      347      2014-01-26 15:01:00.000
      348      2014-01-26 15:01:10.000
      349      2014-01-26 15:01:20.000
Is that date used in your query at all? I'm assuming that the 2nd and 3rd datetimes are the start/stop dates. Also, the StopTime not in minutes - see the new fields in the sample below:
DECLARE @tbl_MachineData table(id int identity(1,1), StartDate datetime, StopDate DateTime, DowntimeCode varchar(25))

INSERT INTO @tbl_MachineData(StartDate, StopDate)
VALUES ('2014-01-26 15:01:00.000', '2014-01-26 15:01:20.000'),
	('2014-01-26 15:01:00.000', '2014-01-26 15:01:20.000'),
	('2014-01-26 15:01:00.000', '2014-01-26 15:01:20.000')

SELECT * FROM @tbl_MachineData

SELECT TOP (100) PERCENT StartDate, StopDate, SUM(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) AS StopTime, DowntimeCode
	, DATEDIFF(s, StartDate, StopDate) as StopTimeSeconds
	, DATEDIFF(m, StartDate, StopDate) as StopTimeMinutes
	, SUM(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) AS StopTimeOriginal
FROM  @tbl_MachineData
GROUP BY StartDate, StopDate, DowntimeCode
HAVING (SUM(CAST(DATEDIFF(s, StartDate, StopDate) AS Numeric(4, 0)) / 60) IS NOT NULL)
	AND DowntimeCode IS NULL
ORDER BY StartDate

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SweetingAAuthor Commented:
If i execute the code supplied i get exactly the same result, 2 sets of data instead of 1
The first date is not used for the query
I think the date difference should be in seconds but the answer is not seconds either

thanks for the help but i am still none the wiser
0
SweetingAAuthor Commented:
for the time i think i i should have AVG and not SUM so that it will group correctly but i need to stop the double lines first
0
SweetingAAuthor Commented:
Using AVG solved the problem with the time difference (now calculates correctly - output in minutes)
Still have duplictate date

Thanks for the help so far
0
SweetingAAuthor Commented:
Not sure if its worth to know but this is the vb code i am using to make the table format before i insert the data

SQL.RunQuery("CREATE TABLE temptbl_Stops (ID INT IDENTITY NOT NULL PRIMARY KEY, StartDate DATETIME, StopDate DATETIME, StopTime DECIMAL(4,2), DowntimeCode NCHAR(10))")
0
SweetingAAuthor Commented:
To try and narrow down the problem and see if grouping wa related i deleted the duplicate lines in teh source table but i still go duplicate entries in the target table - conclusion grouping is not the problem but still stuck!
0
Russell FoxDatabase DeveloperCommented:
Are you running my code in SQL Management Studio, or running it through some VBA code? If it's VBA, is the insert code nested in a loop where it could be adding the same values more than once? If you run my example code in SSMS you should only get a single result line.
0
SweetingAAuthor Commented:
Hi Russell,

I'm just a dope, i'm afraid - i was filling the dataset in two different places hence the duplicates.

Sorry about that.

I have a much more interesting one to post now though which needs some thought!
0
SweetingAAuthor Commented:
Hi Russell,

A you solution works perfectly well there is no reason for me not to award the points even though i never used it.

Thanks for the patience.
0
Russell FoxDatabase DeveloperCommented:
No worries, we've all been there!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.