Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
0
SweetingA
Asked:
SweetingA
  • 9
  • 4
1 Solution
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now