Solved

SQL - Stored procedure producing duplicate lines in table

Posted on 2014-07-26
13
262 Views
Last Modified: 2014-07-27
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
0
Comment
Question by:SweetingA
  • 9
  • 4
13 Comments
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40221942
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
 

Author Comment

by:SweetingA
ID: 40221997
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
 

Author Comment

by:SweetingA
ID: 40222000
By the way there is no error just an incorrect result
0
 
LVL 13

Accepted Solution

by:
Russell Fox earned 500 total points
ID: 40222166
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
 

Author Comment

by:SweetingA
ID: 40222254
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
 

Author Comment

by:SweetingA
ID: 40222328
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:SweetingA
ID: 40222330
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
 

Author Comment

by:SweetingA
ID: 40222351
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
 

Author Comment

by:SweetingA
ID: 40222370
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
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40222894
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
 

Author Comment

by:SweetingA
ID: 40223250
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
 

Author Closing Comment

by:SweetingA
ID: 40223275
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
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40223289
No worries, we've all been there!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now