Output Duplicate Row Occurrences In A Table

Aug 18, 2013

I want to retrieve staff who attend less than 80% for a meeting type assuming we have 10 meetings per list.

Meeting Table:

staffID list date
------------------------
1 A 2013-01-15
2 B 2013-01-17
1 B 2013-01-17
1 A 2013-01-18
2 B 2013-01-19
1 A 2013-01-20
2 C 2013-01-21

* 1 - Dan
* 2 - Jane

When the staffID occur 3 times (70%), query will Output:

staffName list Participation%
------------------------
Dan A 70

SELECT a.staffName, b.list,
(100 - ((COUNT(c.staffID) * 100) / 10)) AS 'Participation%'
from Staff AS a, listType AS b, Meeting AS c
where a.staffID = c.staffID AND
b.list = c.list
GROUP BY a.staffName, b.list
HAVING COUNT(c.staffID) > 2

View 2 Replies


ADVERTISEMENT

CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714

Oct 2, 2007

Hello Everyone:

I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.

When I try to create the tables, for the query, I am getting the following error:




Msg 2714, Level 16, State 4, Line 12

There is already an object named 'UserID' in the database.

Msg 1750, Level 16, State 0, Line 12

Could not create constraint. See previous errors.


I have duplicated this error with the following script:


USE [testing]

IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users1]

CREATE TABLE [testing].[dbo].[users1] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)

IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users2]

CREATE TABLE [testing].[dbo].[users2] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)

IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users3]

CREATE TABLE [testing].[dbo].[users3] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)



I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.

I think that the schema is only allowing a single UserID primary key.

How do I fix this?

TIA

View 4 Replies View Related

Output And Error Output Write The Same Table At The Same Time, Stall The Process.

Aug 30, 2006

Hi

I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....

The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.

What can I do to avoid such situation?

Thanks in advance,

JD

View 4 Replies View Related

Reporting Services :: Open Report In Excel But Need Raw Data Not Duplicate Of Report Output

Nov 23, 2015

We are about to implement SSRS reports (SQL Server 2012), and have found one issue we don't like. The report looks great when we open it in pdf format. However, when we open it in Excel format, we want it to be the raw data, not look like the pdf report. I tried the simpl headers, and that removed the page header/footer. However, the report format was still in play.In other words, we would like the report when opened in Excel, to look just like it does when we open it as a CSV.I have tried to modify the config file a couple of times. Here is my latest attempt:

Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
     <Name Language="en-US">CSV (comma delimited)</Name>
    </OverrideNames>
   </Extension>
   <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
     <Name Language="en-US">Excel (csv)</Name>
    </OverrideNames>
   </Extension>

Essentially I have set Visible = "false" for the Excel type, and tried to duplicate the CSV, changing the name to make it look to the user like it is "Excel". This fails. I checked the event viewer, and I see two entries.. The RSReportServer.config file has been modified. (Information)The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.I literally took the file, opened it in Notepad, pasted in my changes, from another notepad window, and tried to run the report. I did not see EITHER of the two names I expected, using the OverrideNames tag. As I said earlier, I was able to affect the outcome, by setting SimpleHeaders to true (or however it is identified). That did work in that the page header and footer was removed.

View 6 Replies View Related

Multiple Occurrences

Jun 19, 2007

If I want to return records where a column has similar data and occurs more than once what is the function?

Ex. If a database contains:
Column 1 Column 2 Column 3
1 Dog White
2 Cat Brown
3 Dog Black
4 Mouse Black
5 Cat White

and I want to show all records where the 2nd column has more than one occurrences so that I get both records where Dog appeared and both records where Cat appeared (since each appeared more than 1 time) - what do i need to write as my function?

View 4 Replies View Related

Counting Occurrences Of A Value

Jul 19, 2007

I have a column in a report with values Y or N or V. In the header, I have to show the number of times each value appeared.



I used RepeatingValue() with IIF() but it's counting all the values as same, e.g., if there are four occurrences of Y, two of N and one of V, I want something like this:



Y Cnt=4, N Cnt=2, V Cnt=1



My assumption was that this will work (white spaces added for readability:



=iif(

Fields!myCol.Value = "Y",

"Y Cnt=" & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

iif(Fields!myCol.Value = "N",

"N Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

iif(Fields!myCol.Value = "V",

"V Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),

"NULL"

)

)

)



Please help.

View 3 Replies View Related

Find Consecutive Occurrences

Jul 25, 2006

Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate

I have a details table where I capture the following details

CustID, DateofPurchase, PurchaseDetails

I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.

Thanks for your help in advance.
Regards
Dinesh

View 2 Replies View Related

Replace All Integers With Continuous 6 Or More Occurrences With X

Oct 4, 2012

I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.

create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO

EXPECTED RESULT:

1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS

drop table t1

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

View 9 Replies View Related

T-SQL (SS2K8) :: Counting Number Of Occurrences?

Sep 23, 2015

I'm trying to figure out how to do the following:

Number of People receiving their second speeding ticket during this time frame

4 Jun 06 -3 Jun 07
4 Jun 07 -3 Jun 08
4 Jun 08 -3 Jun 09
4 Jun 09 -3 Jun 10
4 Jun 10 -3 Jun 11
4 Jun 11 -3 Jun 12

The table would contain historical data and look something like this

CREATE TABLE [dbo].[test](
[person_id] [NCHAR](10) NULL,
[ticket_date] [DATE] NULL,
[ticket] [BIT] NULL
) ON [PRIMARY]
GO

View 9 Replies View Related

COUNT Occurrences Of Value In Aggregate Query

Jun 1, 2008

I have a table as below:
COMPUTERNAME, COUNTER, REASON
WXP-13Failed
WXP-1113Failed
WXP-38Failed
WXP-910Failed
WXP-117Success

What I would like to do is create a percentage of Success vs. the Failed for the same Computername.
Problem is when I use Group by I cannot tell the COUNTER of the Success anymore. This is what I have been using:

SELECT COMPUTERNAME, SUM(COUNTER) AS COUNTERTOTAL
FROM
ReturnTable
GROUP BY COMPUTERNAME

Thank you. The above query actually targets a result table from another query, but that should not matter.

View 11 Replies View Related

SQL XML :: Multiple Occurrences Of Same Sibling - How To Get Only Desirable One

Sep 9, 2015

I am working with a pretty complicated xml document but I am only trying to grab specific information. 

The issue I am having is that the info I am after is in a node>parent>sibling where there are similar repeating siblings.

<ClinicalDocument xmlns="urn:hl7-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="1.2.840.114350.1.72.1.51693" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<templateId root="2.16.840.1.113883.10.20.22.1.2" />
<id assigningAuthorityName="EPC" root="1.2.840.114350.1.13.291.2.7.8.688883.87504" />

[code].....

Here is my query:

WITH XMLNAMESPACES(DEFAULT 'urn:hl7-org:v3')
SELECT t.document_id,
t.person_id,pref.value('title[1]', 'varchar(255)') AS Title,

[Code] ....

This almost gives me what I need but I am only concerned with, in this case, the first sibling component but it is also picking up Information from the second.  in this case it is picking up the caption containing "Strep A Antigen Scrn, Cult if Indicated (09/07/2015  6:35 PM EDT)" Also the number of components siblings change from document to document and although in this example I am trying to get the first component sibling, in actuality the component sibling is more towards the bottom. 

Is there a way to only grab the info under the <title> in the component sibling I am after?

It will always be <title>Visit Diagnoses</title>. Is there a way to pinpoint this in the above query? Or am I going at it all wrong?

View 2 Replies View Related

Counting Of Occurrences Of A Word In A Text Field

Oct 27, 2003

Finding numbers of occurrences of a string of characters
in a column of TEXT datatype.
DDL of involved table txt:
create table txt (pk int, txtcol text) -- datatype of pk doesn't matter

declare @word varchar(80) set @word='help'
declare @pk int, @count int, @i int, @dl int, @wl int
set @wl=len(@word)
declare abc cursor for select pk from txt
where patindex('%'+@word+'%',txtcol)>0 order by pk
open abc fetch next from abc into @pk
while @@fetch_status=0
begin
select @dl=datalength(txtcol) from txt where pk=@pk
select @i=patindex('%'+@word+'%',txtcol)+@wl from txt where pk=@pk
set @count=1
while @i<@dl
begin
select @count=@count+(len(substring(txtcol,@i,8000))-
len(replace(substring(txtcol,@i,8000),@word,'')))/@wl
from txt where pk=@pk
set @i=@i+8001-@wl
end
select pk=@pk, occurrences=@count
fetch next from abc into @pk
end
close abc deallocate abc
pk occurrences
----------- -----------
1 1

pk occurrences
----------- -----------
2 2

pk occurrences
----------- -----------
3 11
Edit: as suggested-reminded by jsmith8858.

View 7 Replies View Related

How To Count # Of Occurrences Of A Character Inside A String?

Jan 24, 2008

I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:

NNNNNRJKSURNNNEJNNNN

Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?

View 7 Replies View Related

Transact SQL :: Multiple Occurrences Of Same Product And Same Customer

Nov 5, 2015

I am trying to build a query to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates.  All of the information needed is in the same db.  How do I do this?

Select db1.customer_id,
db1.product_id,
db1.purchase_date
From db1

View 20 Replies View Related

Adding Up Occurrences Of Reports On Days Of Week - Get MAX Values

Oct 13, 2014

In my database I am adding up the occurrences of reports on days of the week. If I am not using the MAX value then I get something returned such as

EmployeeID DaysOfWeek ReportID
1001 1 201
1001 2 201

As I just need the highest value returned I'm attempting to use MAX. However, the problem is that all values are being returned in the DaysOfWeek column as 5 even though they may range anywhere from 1-7. The DaysOfWeek should be for a unique EmployeeID/ReportID combination.

WITH sub AS(
SELECT Shifts1.EmployeeID, X.*, Schedule.ReportID
FROM
(
SELECT
CASE WHEN [M] = '1' THEN 1 ELSE 0 END +

[Code] .....

View 8 Replies View Related

SQL Server 2012 :: Retrieve String Between Two Delimiters For Multiple Occurrences?

Oct 21, 2015

How can we get the result set as TESTING1,TESTING2 from below

DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $

View 6 Replies View Related

Transact SQL :: Multiple Occurrences Of Same Product And Same Customer Filtered By Region

Nov 18, 2015

I successfully used the query below to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates.  Now I need to only pull the purchases from a particular region (client_cd = '593') that purchased a particular product on different dates.  How can I do this?

select distinct T.* from db1 T1
where exists (select 1 from db1 T2 where T2.CustomerId = T1.CustomerId and T2.ProductId = T1.ProductId and T2.PurchaseDate
<> T1.PurchaseDate) and T1.ProductId = '123'<o:p></o:p>

View 3 Replies View Related

Power Pivot :: Find Percent Of One Of Last Occurrence Compared To All Last Occurrences (DAX)

Jun 3, 2015

I have a fact -REVENUE  table of accounts, each account can have multiple instances . And I have an Aggregate that summarize the latest occurrence of a revenue for each account ( in a chosen Period )   : 

AccountLastRevenue:=SUMX( 
VALUES('scd_FactAccountRevenue'[Account]),
    CALCULATE(
        SUM('scd_FactAccountRevenue'[Revenue]),
        LASTDATE('scd_FactAccountRevenue'[Revenue_Date])   ) 
)

How can I find the percent of one of the lastest accounts Compared to all accounts? Assuming i have connect Dim_Time ( Y-Q-M-D )  to Revenue_Date, how can i find the percent of one Month Compared to all the months in the Quarter ( And so on hierarchies ) ?

scd_FactAccountRevenue:

powerpivot :

View 11 Replies View Related

SQL Server Admin 2014 :: Where To Find Availability Group Fail Over Occurrences

Nov 14, 2014

Where can I find dates and times to when an availability group was moved outside of the SQL error log?

View 1 Replies View Related

Duplicate Table

Jun 11, 2007

Hi,In my program i need to duplicate a table in a current data base.I'm thinkin' of reading the data base columns and then rows and so icreate another tableIs there any other easy and fast method with SQL Server 2005, becausemy idea is so slowI'm using VB 2005 Express with SQL Server 2005 ExpressThanks

View 2 Replies View Related

Duplicate Table

Sep 7, 2007

Hi,

I have a table with data in it, (tblT1)

A. How do I create a table tblT2 with the same columns in tblT1, but without data in (blank tblT2 table with columns).
B. And also how do I copy the enitre table's data (tblT1) into tblT2.


Thank you,




View 5 Replies View Related

Duplicate Records In A Table

Oct 5, 2007

How do i remove duplicate records from a table with a single query without using cursors or anything like that.Sample :tempCol11221P.S The table has only one column  

View 2 Replies View Related

Duplicate In Large Table

Mar 1, 2002

Hi,

I am absolutely innocent as far as T-SQL is concerned. I need to detect all duplicates (key consists of 5 fields) in the table and delete the duplicates.
I tried different approaches like joins etc but nope.
Any help is appreciated
Thanks

View 2 Replies View Related

Creating Duplicate Table

Dec 12, 2006

ravi writes "plz tell me code for
1. i want to create a table with same structure and key value as an existing table.

2. How can i modify the primary key there i new table"

View 1 Replies View Related

Del. Duplicate Rows From A Table Having No Pk

Mar 7, 2008

hi,

create TABLE #t ( id int,names varchar(50))
INSERT INTO #t VALUES(1,'master')
INSERT INTO #t VALUES(2,'tempdb')
INSERT INTO #t VALUES(2,'tempdb')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(1,'master')
SELECT * FROM #t

Iam using sql server 2005. I want to delete the duplicate records from the table.Please specify a method which is very good at performance.

Thanks in Advance..........

View 11 Replies View Related

Creating Duplicate Table

Aug 9, 2007

Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....

View 12 Replies View Related

Duplicate Entries In The Resulting Table

Sep 20, 2007

Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks.
Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins]
@Userid uniqueidentifier OUTPUT,@DisplayName varchar(200)
 
AS
 
SELECT *
FROM dbo.UserProfile INNER JOIN
dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName
and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date
Return

View 7 Replies View Related

Find Duplicate Records In Table

Oct 5, 2007

Hello friends,
I have a one problem, i have a table in that some reocrds are duplicate.i want to find which records are duplicate.
for exp. my table is as follows
emp_id              emp_name
1                          aa
2                          bb
3                          cc
1                          aa
3                          cc
3                          cc
and i want the result is like
emp_id              emp_name
1                       aa
1                       aa
3                       cc
3                       cc
3                       cc
 

View 6 Replies View Related

Replicate (Duplicate) A Row's Data Into Same Table

Apr 20, 2008

 I'm working on a web app that needs to be able to take a row in the database and duplicate it, creating a new row in the same table with the same data except for the ID field and reference field.So basically: table1.row1 references table2.row1. I need to duplicate the data in table1.row1 (creating table1.row2) with the same reference to table2.row1.Is there any easy way to do this in SQL? I'm just looking for some ideas or a framework to accomplish this. 

View 1 Replies View Related

Prevent Duplicate Entries In A Table

Nov 11, 2003

I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember.

The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID.

Here is a sample of some data:

User1 Group1
User1 Group2
User2 Group2
User3 Group1
User3 Group3

Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example:

User1 Group1
User2 Group2
User1 Group1

I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome.

Is there a simpler way to prevent duplicate entries in a table using sql?

Thanks a lot,

Chris

View 3 Replies View Related

Remove Duplicate Value From One Cloumn Table

Aug 16, 2005

I am working SQL Server 2005 and One Table Which contain only one column without primary keyNow I want to remove all duplicate value from that table with only single query

View 2 Replies View Related

Syntax For Creating Duplicate Table

Nov 29, 2000

Hi
Is there a syntax similar to the oracle in SQL Server?
Oracle statement: create table table2 as select * from table1

which will create table2 with exactly the same structure
and records as table 1

thanks
Liju

View 2 Replies View Related

Duplicate Names In Sysindexes Table

Mar 29, 2001

WHen researching an index name problem, I found duplicate index names in sysindexes, referencing the same table. I notice that one of the indexes has a status = 2097154 and a indid of 2 while the other has a status of 0 and a indid of 0. I believe these are duplicates. Anybody have an idea if these really are and what the status and indid fields mean?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved