Solved

SQL - Stored procedure producing duplicate lines in table

Posted on 2014-07-26
13
265 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Restrict result set 1 35
SQL 2008 Conversion failed 7 20
Getting same value for every field in SQL 2 33
Sql Query with datetime 3 14
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

24 Experts available now in Live!

Get 1:1 Help Now