Eliminating Records In A Query
Sep 29, 2014
Need to eliminate certain records from my query. The below is a simple query to illustrate my problem
My Query
Select RequestNo,Event_type from Event_log where Event_type in (10,20)
Data
RequestNo Event_type
123456 10
123457 10
123457 20
123458 10
123459 10
123459 20
This above query returns all requests that meets atleast one criteria. How do i edit my query such that i get requests that meet both criteria and the result set looks like below
Data
RequestNo Event_type
123457 10
123457 20
123459 10
123459 20
View 2 Replies
ADVERTISEMENT
May 13, 2008
Hey There.
I'm in the process of doing a major data clean up and I'm just wondering how I would go about eliminating some redundant data.
The Table Layout
Contracts
CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
1 1234567 091885 A
2 1111111 111111 B
3 1234567 081205 A
Equipment
EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
1 DEVICE2 2 B
2 DEVICE3 1 A
3 DEVICE4 3 A
You will notice that each customer may have multiple devices. Each device may be tied to a contract, and each contract may have one or more devices tied to it.
In the example above, you will notice in the contracts table the contracts with the IDs 0 and 1.
Fig 1.
CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
1 1234567 091885 A
These contracts have the exact same information.
Furthermore, if you look down the table you will notice the contract with the ID 3.
Fig 2.
CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
3 1234567 081205 A
This contract shares the same contract and customer number, but has a different start date.
Now lets take a look devices in the equipment table that refer to these records.
EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
2 DEVICE3 1 A
3 DEVICE4 3 A
You will notice that DEVICE1 and DEVICE 3 refer to the contract records that contain identical data. (As shown in 'Fig 1')
My question is as follows:
How do I eliminate the any duplicate records from the contracts table, and update the records in the equipment table with id of the left over contract.
Results Should be as follows:
Contracts
CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
2 1111111 111111 B
3 1234567 081205 A
Equipment
EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
1 DEVICE2 2 B
2 DEVICE3 0 A
3 DEVICE4 3 A
Any help you may provide would be greatly appreciated!
Thanks
--mike
View 11 Replies
View Related
Feb 11, 2007
I am new to sql server and I am having deficulties writing sql script to perform the following:
1) Merging data from two tables A and B
2) Eliminate duplicate present in table B (Conditions to satisfy for dublicate:If similar address is found in both tables AND class type in Table A =1
3) merge data related to dup(eliminated records) to new table.
Not sure if we can eliminate records first before merging two tables. Tables are as follow:
Table A
Fields: ID, NAME, Address, city, zip, Class type
Value:123, John, 123 Main, NY, 71690,1
Value:124, Tom, 100 State, LA, 91070,0
Table B
Field: ID, NAME, Address, city, zip, Class Type
Value:200, Tim, 123 Main, NY, 71690,0 (duplicate; satisfied both conditions and left out in final table)
Value:124, Jack, 100 State, LA, 91070,0 (same condition but second condition is not met)
Value:320,Bob, 344 coast hwy, slc, 807760,0
Final Table:
Field: ID, NAME, Address, city, zip, Class Type
Value:123, John, 123 Main, NY, 71690,1 (should also show t
Value:124, Tom, 100 State, LA, 91070,0
Value:124, Jack, 100 State, LA, 91070,0
Value:320,Bob, 344 coast hwy, slc, 807760,0
Table d:(relate to table A:showing all products that are related to table A)
table_A.ID, Products
123, Paper 1
123, paper 2
Table e:(relate to table B: showing all products that are related to table B)
table_B.ID, Products
200, Paper 3
Final Table:
ID, Product
123, Paper 1
123, Paper 2
123, Paper 3 (changing table b id to table a)
Would appreciate any help writing script to perform such transformation. Thanks
View 5 Replies
View Related
Jul 13, 2007
Hello,
I'm trying to eliminate the duplicate 'URL' rows in the query:
SELECT
ni.[Id],
ni.[Abstract],
ni.[MostPopular],
ni.[URL]
FROM dbo.[NewsCategory] nc WITH (READUNCOMMITTED)
INNER JOIN dbo.[NewsItem] ni WITH (READUNCOMMITTED)
ON nc.[Id] = ni.NewsCategoryId
WHERE
--nc.[ProviderId] = @ProviderId
--AND
ni.[URL] in (
select DISTINCT URL
from dbo.NewsItem
where mostpopular = 1
-- OR mostemailed = 1
)
ORDER BY ni.[DateStamp] DESC
If you look at this line in the query :
select DISTINCT URL
from dbo.NewsItem
where mostpopular = 1
IF i run this query alone it will return 8 unique rows. I expect that the SELECT IN statemnet would help return a distinct set but it doesn't. This entire query returns like 20 rows with duplicate rows.
The reason why I can't do a distinct in the first set of columns is because the column ni.[Abstract] is TEXT and it says that data type is NOT COMPARABLE.
Thanks so much.
View 5 Replies
View Related
Mar 10, 2014
I'm using SQL 2012 express.. and just recently learned how to code.
I wrote a query and keep receiving this error...
Error converting data type varchar to float.
here's the query code
SELECT SUM(cast(lc as float))
FROM [dbo].[LaborCosts]
WHERE ppty = 'ga'
AND PL = 'allctd ktchn expns'
AND ACCT like 'payroll%'
I am trying to sum up the values in column LC, and realized I have unnecessary quotations marks. How can I eliminate the quotations from the column, and only query the numerical values?
View 2 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Jan 18, 2008
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
View 4 Replies
View Related
Feb 11, 2005
Have a pretty simple wuestion but the answer seems to be evading me:
Here's the DDL for the tables in question:
CREATE TABLE [dbo].[Office] (
[OfficeID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentOfficeID] [int] NOT NULL ,
[WebSiteID] [int] NOT NULL ,
[IsDisplayOnWeb] [bit] NOT NULL ,
[IsDisplayOnAdmin] [bit] NOT NULL ,
[OfficeStatus] [char] (1) NOT NULL ,
[DisplayORD] [smallint] NOT NULL ,
[OfficeTYPE] [varchar] (10) NOT NULL ,
[OfficeNM] [varchar] (50) NOT NULL ,
[OfficeDisplayNM] [varchar] (50) NOT NULL ,
[OfficeADDR1] [varchar] (50) NOT NULL ,
[OfficeADDR2] [varchar] (50) NOT NULL ,
[OfficeCityNM] [varchar] (50) NOT NULL ,
[OfficeStateCD] [char] (2) NOT NULL ,
[OfficePostalCD] [varchar] (15) NOT NULL ,
[OfficeIMG] [varchar] (100) NOT NULL ,
[OfficeIMGPath] [varchar] (100) NOT NULL ,
[RegionID] [int] NOT NULL ,
[OfficeTourURL] [varchar] (255) NULL ,
[GeoAreaID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[OfficeBrandedURL] [varchar] (255) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OfficeManagement] (
[OfficeID] [int] NOT NULL ,
[PersonnelID] [int] NOT NULL ,
[JobTitleID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[SeqNBR] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OfficeMls] (
[OfficeID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[OfficeMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Personnel] (
[PersonnelID] [int] IDENTITY (1, 1) NOT NULL ,
[PersonnelDisplayName] [varchar] (100) NOT NULL ,
[FirstNM] [varchar] (50) NOT NULL ,
[PreferredFirstNM] [varchar] (50) NOT NULL ,
[MiddleNM] [varchar] (50) NOT NULL ,
[LastNM] [varchar] (50) NOT NULL ,
[PersonalTaxID] [varchar] (9) NOT NULL ,
[HireDT] [datetime] NOT NULL ,
[TermDT] [datetime] NOT NULL ,
[HomePhoneNBR] [varchar] (15) NULL ,
[HomeADDR1] [varchar] (50) NOT NULL ,
[HomeADDR2] [varchar] (50) NOT NULL ,
[HomeCityNM] [varchar] (50) NOT NULL ,
[HomeStateCD] [char] (2) NOT NULL ,
[HomePostalCD] [varchar] (15) NOT NULL ,
[PersonnelLangCSV] [varchar] (500) NOT NULL ,
[PersonnelSlogan] [varchar] (500) NOT NULL ,
[BGColor] [varchar] (50) NOT NULL ,
[IsEAgent] [bit] NOT NULL ,
[IsArchAgent] [bit] NOT NULL ,
[IsOptOut] [bit] NOT NULL ,
[IsDispOnlyPrefFirstNM] [bit] NOT NULL ,
[IsHideMyListingLink] [bit] NOT NULL ,
[IsPreviewsSpecialist] [bit] NOT NULL ,
[AudioFileNM] [varchar] (100) NULL ,
[iProviderID] [int] NOT NULL ,
[DRENumber] [varchar] (10) NOT NULL ,
[AgentBrandedURL] [varchar] (255) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[IsDisplayAwards] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PersonnelMLS] (
[PersonnelID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[AgentMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Office] ADD
CONSTRAINT [FK_Office_OfficeProfile] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[OfficeProfile] (
[OfficeID]
) NOT FOR REPLICATION
GO
alter table [dbo].[Office] nocheck constraint [FK_Office_OfficeProfile]
GO
ALTER TABLE [dbo].[OfficeManagement] ADD
CONSTRAINT [FK_OfficeManagement_LookupJobTitle] FOREIGN KEY
(
[JobTitleID]
) REFERENCES [dbo].[LookupJobTitle] (
[JobTitleID]
),
CONSTRAINT [FK_OfficeManagement_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OfficeManagement_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) ON DELETE CASCADE
GO
alter table [dbo].[OfficeManagement] nocheck constraint [FK_OfficeManagement_Office]
GO
ALTER TABLE [dbo].[OfficeMls] ADD
CONSTRAINT [FK_OfficeMls_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION
GO
alter table [dbo].[OfficeMls] nocheck constraint [FK_OfficeMls_Office]
GO
ALTER TABLE [dbo].[PersonnelMLS] ADD
CONSTRAINT [FK_PersonnelMLS_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) NOT FOR REPLICATION
GO
alter table [dbo].[PersonnelMLS] nocheck constraint [FK_PersonnelMLS_Personnel]
GO
Here's the query I'm having trouble with:
SELECT distinct Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID AS OfficeBoard,
PersonnelMLS.SourceID AS AgentBoard
FROM Personnel INNER JOIN
OfficeManagement ON
Personnel.PersonnelID = OfficeManagement.PersonnelID
INNER JOIN
Office ON OfficeManagement.OfficeID = Office.OfficeID
INNER JOIN
OfficeMls ON Office.OfficeID = OfficeMls.OfficeID
INNER JOIN
PersonnelMLS ON Personnel.PersonnelID = PersonnelMLS.PersonnelID
where officemls.sourceid <> personnelmls.sourceid
and office.officenm not like ('%admin%')
group by PersonnelMLS.SourceID,
Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID
order by office.officenm
What I'm trying to retrieve are those agents who have source id's that are not in the Office's domain of valid source id's. Here's a small portion of the results:
PersonnelID FirstNM LastNM OfficeNM OfficeID OfficeBoard AgentBoard
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- -----------
18205 Margaret Peggy Quattro Aventura North 650 906 908
18205 Margaret Peggy Quattro Aventura North 650 918 908
15503 Susan Jordan Blackburn Point 889 920 909
15503 Susan Jordan Blackburn Point 889 921 909
15503 Susan Jordan Blackburn Point 889 921 920
15279 Sandra Humphrey Boca Beach North 890 917 906
15279 Sandra Humphrey Boca Beach North 890 906 917
15279 Sandra Humphrey Boca Beaches 626 917 906
15279 Sandra Humphrey Boca Beaches 626 906 917
13532 Michael Demcho Boca Downtown 735 906 917
14133 Maria Ford Boca Downtown 735 906 917
19126 Michael Silverman Boca Glades Road 736 917 906
18920 Beth Schwartz Boca Glades Road 736 906 917
If you take a look at Sandra Humphries, you'll see she's out of office 626. Office 626 is associated with source id's 907 and 916. Sandra Humphries is also associated with those two source id's , but she shows up in the results.
I know this was AWFULLY long winded, but just wanted to make sure made myself as clear as possible.
Any help would be greatly appreciated.
Thanks in advance!
View 8 Replies
View Related
Aug 9, 2000
Hi,
I have a table with four columns. like id,lastname,
firstname,acctname. I have duplicate values for the three columns other
than id column. like
ID FirstNameLastname Acctname
1 john hopkins jh
2 john hopkins Jh
3 david webb dw
4 david webb dw
5 david webb dw
6 Dan Kennedy DK
I want to eliminate the duplicate rows. id can be any one of them.
Can any one suggest me with a query by which i can do this.
Thanks in advance
Mohan
View 2 Replies
View Related
Jun 26, 2000
How do I eliminate others from viewing one of the 2 databases on our production server???Is there any security not to allow all users to including sa and developers not to access one of the 2 databases on our server..
The other of the 2 databases can be accessed....
Please advise
Newbie
View 1 Replies
View Related
Apr 11, 2008
Hi All,
I need to eliminate Duplicates in my Sql Query, tried to use distinct and that doesn't seem to work, can anybody pls.help.
duplicates are in #ddtempC table, and am writing a query to get a country name from the hash table where hash table has duplicates
hash table contains (THEATER_CODE, COUNTRY_CODE, COUNTRY_NAME).
and trying to write condition on THEATER_CODE and COUNTRY_CODE to get Country_name
and THEATER_CODE AND COUNTRY_CODE HAS DUPLICATES. whenever i do a sub query i get the below error.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SELECT USER_FIRSTNAME, USER_LASTNAME,
user_countryCode,
USER_COUNTRY = (SELECT DISTINCT RTRIM(LTRIM(COUNTRY_NAME)) FROM #ddtempC WHERE RTRIM(LTRIM(COUNTRY_CODE)) = USER_COUNTRYCODE AND RTRIM(LTRIM(THEATER_CODE)) = USER_THEATERCODE)
FROM [user]
WHERE USER_USERNAME IS NOT NULL AND User_CreationDate BETWEEN '1/2/2007' AND '4/11/2008'
ORDER BY User_TheaterCode;
Thanks in Advance.
View 3 Replies
View Related
Jul 29, 2013
I have a SQL statement with two left outer joins which connects 3 tables. Vendors, Tracking & Activity. For whatever reason, even though each is a one-to-many relationship, I am able to join 2 tables (from Vendors to Tracking) without an issue. when I then join Activity, I get a Cartesian product.I suspected that 'DISTINCT'.
SELECT DISTINCT CASE
WHEN `vendor`.`companyname` IS NULL then 'No Company Assigned'
ELSE `vendor`.`companyname`
END AS companyNameSQL, `tracking`.`pkgTracking`, CASE
[code]....
View 4 Replies
View Related
Jul 20, 2005
edit: this came out longer than I thought, any comments about anythinghere is greatly appreciated. thank you for readingMy system stores millions of records, each with fields like firstname,lastname, email address, city, state, zip, along with any number of userdefined fields. The application allows users to define message templateswith variables. They can then select a template, and for each variablein the template, type in a value or select a field.The system allows you to query for messages you've sent by specifyingcriteria for the variables (not the fields).This requirement has made it difficult to normalize my datamodel at allfor speed. What I have is this:[fieldindex]id int PKname nvarchartype datatype[recordindex]id int PK....[recordvalues]recordid int PKfieldid int PKvalue nvarcharwhenever messages are sent, I store which fields were mapped to whatvariables for that deployment. So the query with a variable criterialooks like this:select coalesce(vm.value, rv.value)from sentmessages sminner join variablemapping vm on vm.deploymentid=sm.deploymentidleft outer join recordvalues rv onrv.recordid=sm.recordid and rv.fieldid=vm.fieldidwhere coalesce(vm.value, rv.value) ....this model works pretty well for searching messages with variablecriteria and looking up variable values for a particular message. thebig problem I have is that the recordvalues table is HUGE, 1 millionrecords with 50 fields each = 50 million recordvalues rows. The value,two int columns plus the two indexes I have on the table make it into abeast. Importing data takes forever. Querying the records (with a fieldcriteria) also takes longer than it should.makes sense, the performance was largely IO bound.I decided to try and cut into that IO. looking at a recordvalues tablewith over 100 million rows in it, there were only about 3 million uniquevalues. so I split the recordvalues table into two tables:[recordvalues]recordid int PKfieldid int PKvalueid int[valueindex]id int PKvalue nvarchar (unique)now, valueindex holds 3 million unique values and recordvaluesreferences them by id. to my suprise this shaved only 500mb off a 4gbdatabase!importing didn't get any faster either, although it's no longer IO boundit appears the cpu as the new bottleneck outweighed the IO bottleneck.this is probably because I haven't optimized the queries for the newtables (was hoping it wouldn't be so hard w/o the IO problem).is there a better way to accomplish what I'm trying to do? (eliminatethe redundant data).. does SQL have built-in constructs to do stuff likethis? It seems like maybe I'm trying to duplicate functionality at ahigh level that may already exist at a lower level.IO is becoming a serious bottleneck.the million record 50 field csv file is only 500mb. I would've thoughtthat after eliminating all the redundant first name, city, last name,etc it would be less data and not 8x more!-GordonPosted Via Usenet.com Premium Usenet Newsgroup Services----------------------------------------------------------** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **----------------------------------------------------------http://www.usenet.com
View 5 Replies
View Related
Apr 10, 2014
WITH cte_OrderProjectType AS
(
select Orderid, min(TypeID) , min(CTType) , MIN(Area)
from tableA A inner join
tableB B ON A.PID = B.PID left join
tableC C ON C.TypeID = B.TypeID LEFT JOIN
tableD D ON D.AreaID = B.ID
group by A.orderid
)
This query uses min to eliminate duplicates. It takes 1.30 seconds to complete..
Is there any way I can improve the query performance ?
View 9 Replies
View Related
Oct 21, 2014
Being one step removed from innumerate, I was wondering whether there was a more elegant way to avoid divide by zero error instead of trudging through a bunch of isnulls.
My intuition tells me that since multiplication looks like repeated addition, that maybe division is repeated subtraction?
If that's true is there a way to finesse divide by zero errors by somehow reframing the statement as multiplication instead of division?
The sql statement that is eating my kishkas is
cast(1.0*(
(ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0))-(ISNULL(b.dnt,0)+ISNULL(b.rex,0)+ISNULL(b.med,0))/
ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0)) as decimal(10,4)) TotalLossRatio
Is there a way to nucleate the error by restating the division? My assertion underlying this statement is that the a alias represents a premium paid, so between medical, pharmacy and dental, there MUST BE at least one premium paid, otherwise you wouldn't be here. the b alias is losses, so likewise, between medical, pharmacy and dental, there MUST BE at least one loss (actually, it just occurred to me that maybe there are no losses, but that would be inconceivable, but ill check again)) so that's when it struck me that maybe there's a different way to ask the question that obviates the need to do it by division.
View 6 Replies
View Related
May 13, 2008
I am querying several tables and piping the output to an Excel spreadsheet.
Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?
As an example, my query results are as follows (soory if it does not show correctly):
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
I only want the output to be:
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
Thanks,
Walt
View 4 Replies
View Related
Jul 20, 2007
Hi i have a table value which contains
value
-----
a
a
a
b
b
b
c
c
c
Now i need to have the results as
a 1
b 1
c 1
I tried using distinct.But OLEDB returns error that invalid syntax.It doesn't support distinct keyword.Actually i read these table from a file thru OLEDB.Not from a database.Any idea ? Thanks in Advance
View 8 Replies
View Related
Jul 20, 2005
Am I going about this the right way? I want to find pairs of entitiesin a table that have some relationship (such as a field being thesame), so Iselect t1.id, t2.id from sametable t1 join sametable t2 ont1.id<>t2.idwhere t1.fieldx=t2.fieldx ...The trouble is, this returns each pair twice, e.g.B CC BM NN MIs there a way to do this kind of thing and only get each pair once?Kerry
View 2 Replies
View Related
Jul 20, 2005
Suppose I have users that can belong to organizations. Organizationsare arranged in a tree. Each organization has only one parentorganization but a user maybe a member of multiple organizations.The problem that I'm facing that both organizations and individualusers may have relationships with other entities which aresemantically the same. For instance, an individual user can purchasethings and so can an organization. An individual user can havebusiness partners and so can an organization. So it seems that I wouldneed to have a duplicate set of link tables that link a user to apurchase and then a parallel link table linking an organization to apurchase. If I have N entities with which both users and organizationsmay have relationships then I need 2*N link tables. There is nothingwrong with that per se but just not elegant to have two differenttables for a relationship which is the same in nature, e.g.purchaser->purchaseditem.One other approach I was thinking of is to create an intermediateentity (say it's called "holder") that will be used to hold referencesto all the relationships that both an organization and an individualmay have. There will be 2 link tables linking organizations to"holder" and users to "holder". Holder will in turn reference thepurchases, partners and so on. In this case the number of link tableswill be N+2 as opposed to 2*N but it will have a performance cost ofan extra join.Is there a better way of modelling this notion of 2 different entitiesthat can possess similar relationships with N other entities?
View 28 Replies
View Related
Jun 19, 2015
I have an UPDATE statement that joins two table by SendId. One table, I'll call it T1, has a clustered index on SendId ASC. The other table I will call T2 also has a clustered index on SendID ASC. All the columns from T2 are used to update T1. The execution plan shows a Clustered index scan on T2 and a Clustered Index Seek on T1 going into a Nested Loops inner join. Immediately following is a Distinct Sort that is done on SendId ASC. Why the Distinct SORT if the tables are already ordered by SendID?
View 8 Replies
View Related
Jul 27, 2015
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
DateKey MonthNumber HolidayHoursPerMonth
6/29/2015 6 0
6/30/2015 6 0
7/1/2015 7 8
7/2/2015 7 8
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
MonthNumberOfYear HolidayHoursPerMonth
1 8
2 4
3 0
4 0
5 8
6 0
7 8
8 0
9 8
10 4
11 16
12 8
View 3 Replies
View Related
Dec 25, 2007
Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:
Select * from Table1
It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Thanks a lot!
And Merry X'max!!!
View 4 Replies
View Related
May 9, 2008
Hey Guys, I have a contacts table that contains ID, First Name, Last Name, and Phone Number, Date Entered, Changed. Every time, the data is modified and saved, it will insert a new record in the table. So, Ill create a new record for a contact named Ryan, and then come back a day later and update the last name and phone number. So theSQL table would look like...1 Ryan Scott 818-550-0000 05/08/2008 Null2 Ryan Peters 000-000-0000 05/09/2008 Null How do I write a sql query that will run an update after the insert of the second record to fill in the Changed field with the data that changed?So I want to have record 2, end up looking like this... 2 Ryan Peters 000-000-0000 05/09/2008 LastName,PhoneNumberAny ideas?
View 8 Replies
View Related
Jul 9, 2001
Can anyone help me to write a query to show customers who have duplicate accounts with Email address, first name, and last name. this is the table structure is Customer table
customerid(PK)
accountno
fname
lname
Records will be
like this
customerid accountno fname lastname
1 2 lori taylor
2 2 lori taylor
3 1 randy dave
Email
emailid (PK)
customerid
emailaddress
View 2 Replies
View Related
Dec 16, 1999
How can I made a query to show only my duplicate records ?
For some reason that i do not know, i have duplicate entries in my clustered index 21 duplicate records in a table how can i query to know those 21 duplicate records ?
Thanks
View 2 Replies
View Related
Dec 5, 2005
Hi
Im trying to make a top 10 list of col1 and and at the 11:th place it should show a number of record that dosent make it to the top 10 list...
i have this so far, and it dosent give me anything...
col1 is varchar 254
SELECT COL1, COUNT(*) AS number
FROM MYTABLE
WHERE (NOT EXISTS
(SELECT TOP 10 COL1
FROM MYTABLE))
GROUP BY COL1
ORDER BY COUNT(*) DESC)
ex of output
place1 100
place2 50
place3 25
...
place11 500
a query that only gives me the place11 number is enough
thx in advance //Mr
View 1 Replies
View Related
Feb 8, 2008
I want to have all the ethnicities from the Ethnicgroup table to be displayed in a particular area which is in where clause as
aggcourseid
The problem is that even when I have a left join on the EthnicGroup table it will not return all the ethnicities but will only return those who were existing in that particular aggcourseid.
i.e. If there is no African student in E2, it will not show up in the result whereas I want to display all the ethnicities and if there is no record for aggcourseid, it should show up as 0/NULL for that ethnicity.
I have tried all sorts of joins but the problem is whenever I put in the aggcourseid in where clause, it wont bring all the records
declare @mpid char (13)
set @mpid = '011142'
create table #temp_et (
[Year]int NOT NULL,
[Ethnicity] varchar(20),
[Starts]float(4),
[Success]float(4),
[Retention]float(4),
[Achievement]float(4)
)
--Year 05/06
insert into #temp_et
select
CAST(LEFT(pv.pg_expendyrid,2) AS int)[Year],
eg.pg_ethnicgroupname[Ethinicity],
sum([pvstart]) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from [FECAS].Proachieve.dbo.pv_midpoint pv
left join [FECAS].Proachieve.dbo.GN_AggCourseStructure gn on pv.pg_aggcourseid = gn.pg_aggcourseid
left join [FECAS].Proachieve.dbo.PG_ethnicGroup eg on pv.pg_ethnicgroupid = eg.pg_ethnicgroupid
where pv_midpointid = @mpid
and pg_expendyrid = '05/06'
and pv.pg_aggcourseid LIKE 'E2%'
group by
eg.pg_ethnicgroupname,
pv.pg_expendyrid
order by
eg.pg_ethnicgroupname
View 14 Replies
View Related
Apr 7, 2008
Hai
I have written one select statement and it returns the following records.
SELECT a.Name, b.address FROM tbl1 a INNER JOIN tbl2 b ON a.id=b.id
Name address
AAA XYZ
BBB
CCC
DDD
My requirement is to display only if address fields contain values.
For Eg. I want to display AAA XYZ record only. How can I write a query.
Help me.
Kamal..
View 3 Replies
View Related
Nov 20, 2014
I need to query SQL Server Express 2012 records to find 6 serial numbers that are all assigned to one common unique number. Normally in this use case, 12 serial numbers are assigned to one common unique number, so I'm trying to find the odd entry.
View 2 Replies
View Related
Apr 1, 2015
Create a new table named frequent travelers (FreqTrav). It will only store passengers who have taken more than 2 trips with the company. It will record the passenger name, passenger number, address, phone number, total trips and total fare paid. Show your CREATE TABLE query and INSERT query. Please insert the records using subquery (i.e. insert from a SELECT query).
CREATE TABLE FreqTrav (
PASSENGERNUM INT(20)
CHECK (PASSENGERNUM IN
[code]...
but It's not working for me since I tried inserting a passengernum who did't travel more than two times and it was inserted.Is there any way I can answer without using check constraint.
View 3 Replies
View Related
Aug 9, 2007
Hi,
I would like to know how would i display following data.
exa:- (Note:- I wrote '---' so to display properly in to the forum
Table1
ComputerName--IpAddress
ABC1-----------10.1.2.3
ABC1-----------10.1.2.4
ABC1-----------10.1.2.5
ABC2-----------10.1.2.6
ABC2-----------10.1.2.7
ABC3-----------10.1.2.8
ABC3-----------10.1.2.9
ABC4-----------10.1.2.10
Table2
ComputerName--NetCard
ABC1-----------<card1>
ABC1-----------<card2>
ABC2-----------<card3>
ABC2-----------<card4>
ABC3-----------<card5>
ABC3-----------<card6>
Table3
ComputerName--Model
ABC1-----------DEll
ABC2-----------DEll
ABC3-----------DEll
Now i want output should be displayed like this
ComputerName--IpAddress--NetCard Model
ABC1-----------10.1.2.3---<card1>---DEll
----------------10.1.2.4---<card2>
----------------10.1.2.5
ABC2-----------10.1.2.6---<card3>---DEll
----------------10.1.2.7---<card4>
ABC3-----------10.1.2.8---<card5>---DEll
----------------10.1.2.9---<card6>
ABC4-----------10.1.2.10
Black filled should not displayed anything
Is it possible
--------------------
Thanks and regards
Abhishek Joshi
India- Bangalore
View 4 Replies
View Related
Jul 23, 2005
Hi,I am using Merge Replication for synchronizing a bunch of laptopsrunning MSDE with a central SQL Server 2000 production server.This database is used by an application that needs to know whichrecords have not yet been replicated, at any givem time.Here's a simple scenario:User A in laptop X starts his copy of the application. He syncs hisMSDE database through the application, to match the current centralservers' data. He then proceeds to add new records through theapplication and, after he is done, he synchronizes his database withthe central server, through the application.At any given time, the user might kill the application. He might addsome records today, a few more tomorrow or in a week and should be ableto change and delete records that have not yet been merged into theproduction server.The problem is that SQL Server does not have an easy way to tell whichrecords in the subscription tables have been inserted but not yetreplicated. I have been using the following query:SELECT rowguidFROM [MyTable]INNER JOIN (SELECT rowguid AS rep_idFROM msmerge_contentsWHERE generation = joinchangegen AND generation = (SELECT MAX(generation)FROM msmerge_genhistory )) dtableON rowguid_rep = dtable.rep_idThis query works for most cases but fails to identify the first recordcreated immediatly after a synchronization event.What I would like to know is if there is a correct way of identifyingrecords which are "not-yet-replicated" on a given table that is part ofa merge replication process like the one described above.Thanks in advance.
View 1 Replies
View Related
Jan 23, 2007
Hi,I want to create query where I could group records by quarters, and getthe last record in each group.e.gCreate Table MyTable(Value [float],date[datetime])Insert into MyTable (value, [date]) values (100, '1-1-2000')Insert into MyTable (value, [date]) values (110, '1-2-2000')Insert into MyTable (value, [date]) values (120, '1-3-2000')Insert into MyTable (value, [date]) values (130, '1-4-2000')Insert into MyTable (value, [date]) values (140, '1-5-2000')Insert into MyTable (value, [date]) values (150, '1-6-2000')Insert into MyTable (value, [date]) values (160, '1-7-2000')Now I would like to get this data grouped by quarter, and get the lastvalue from each quarter. So here I would like to get result like this(120, q1 -2000)(150, q2 -2000)(160, q3 -2000)I know how to create aggregate functions but I have problem with gettingthat last record from each group.*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
