Solved

SQL - Stored procedure producing duplicate lines in table

Posted on 2014-07-26
13
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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
 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

615 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