I have the following two tables...
tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress
I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.
Hi,
I have a table -- Table1.
It has two columns -- Name and Alpha.
Alpha has comma separated values like -- (A,B,C,D,E,F), (E,F), (D,E,F), (F), (A,B,C).
I need to pick the values of column -- Name , where in values of Alpha is less than or equal to 'D'.
I tried <=, but got only values less than 'D', but was not able to get equal to 'D'.
Any suggestions??
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I know I'm missing something....
I have a data set as -
ID Set Date DB Date
100 Null 07/01/15
100 07/05/15 07/02/15
100 07/10/15 07/08/15
I want to able to get 2 unique dates
1. 07/02/15 - As I want the DB date for ID - 100 when set date changed from a null value to non null value.
2. 07/08/15 - As I want the DB date for ID - 100 when a non null set date changes.
The table has such records for lot of different ID's.
I'm trying to use merge data from a staging table to a production table. There are a lot of duplicate values for serverName and I only want to insert one instance where there are duplicates.
How I can adapt the code I have so far to achieve this?
MERGE tblServer AS TARGET
USING tblTemp AS SOURCE
ON (TARGET.serverName = SOURCE.serverName)
WHEN MATCHED THEN
UPDATE SET TARGET.serverName = SOURCE.serverName, TARGET.serverLocation = SOURCE.serverLocation
WHEN NOT MATCHED BY TARGET THEN
INSERT (serverName, serverLocation)
VALUES (SOURCE.serverName, SOURCE.serverLocation)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
Below is the query for my procedure
ALTER PROC [dbo].[sp_GetInvitationStatusTest]
(
@invited_by NVARCHAR (50)
)
AS
BEGIN
-- SELECT * FROM dbo.Merck_Acronym_Invitations WHERE invited_by=@invited_by
select distinct t1.invited_isid as 'ISID', t1.invited_name as 'NAME',t1.invitation_status as 'STATUS',
[Code] ....
If you look at the where clause i have invited by , i get the desired output if i just provide 1 name in the execution such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj' But my requirement is to make the procedure work with more than one input variable such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj','sujith' now i should get the output for 2 people but if i run this i receive the following error Procedure or function sp_GetInvitationStatusTest has too many arguments specified.
how to make my procedure work with more than 1 input variable?
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Thanks
Bob
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
I have table with some columns with no primary key..
I have to find the combination of columns which may the row unique...
How to do that like microsoft...
Below is my sample data. I can't figure out how to select Unique phonenumber contacts for the same Ranked values from the set.
Basically the table is a mix of contactIDs. Some of them have duplicate phone numbers and through a separate mechanism we have ranked them.
It's easier then to pull out max(ranked) CLI_Numbers and their counterpart contactID(s). But I am also getting 2 or more records where the rank happens to be the same. I don't want that. Any one of the contactID will do for me.
The table has also same cliNumbers with different rank values, which are then correctly being picked up in the query below.
Note: ContactId is a unique value for each person in the table.
RecordID is simply RowID.
( I have attempted to populate a sample data suited for this forum - not sure how it comes out on the browser)
if object_id('tempdb..#MyData') is not null
drop table #MyData
create table #MyData
(
RecordID int,
contactID int,
forename varchar(25),
surname varchar(25),
[Code] ....
This is my query attempt
With RankedmobileDuplicateSet
as(
select cliNumber, max(ranked_value) as ranked_max_value
from #temp_UK_mobiledata
group by cliNumber)
[Code] .....
Can somebody please tell me whether the following syntax is supportedor whether it's a "feature" that will someday stop working. It works inboth SQL Server 2000 and 2005 at the moment.declare @var varchar(1000)set @var = ''select @var = @var + colx from some_table where col1 = some_valuecolx is a varchar or at least is cast to one as part of the selectstatement. If the where clause would normally return more than one row,all returned values for colx are concatenated into @var.I've not seen this syntax before but that doesn't make it wrong ;-)Malc.
View 8 Replies View RelatedHi, all,
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
Thanks a lot in advance for any guidance.
Hi all,
I've got a large table (3mil records) with a number of columns, but currently no way to refer to any individual column. I therefore need a primary key, but does anyone know of a SQL statement I can use that will create a column (say, ID) that is automatically filled with an incrementing 'counter'? Or, instead, how can I set unique incremental values after first creating the column?
Many thanks,
Graham
numbers 2 4 2Above is an example of my table with a single column. My problem is how to add all the numbers in that column to make it 8? The rows are also dynamic. Your help is highly appreciated.
View 4 Replies View RelatedHi,
I have a table that has multiple postal codes in one of the columns. Those have to be split up one per line and stored in another table. The zip codes are comma seperated. Is there a function that can do this...?
Example data in ZipCodeTable. (Name and ZipCode are 2 columns in a table)
NameZipCode
Area119930,19970,19971,19944
Area219934,19938,19901,19903,19904
Area319994,19838
output Table should be:
NameZipCode
Area119930
Area119970
Area119971
Area119944
Area219934
Area219938
Area219901
Area219903
Area219904
Area319994
Area319838
Any thoughts on this would be of much help !!..
Thanks
My tables look like this:
Users //
table
UserID // pk
UserName // varchar
UserFamilyName // varchar
User_Friends //
table
FriendsID // pk
UserID // fk
FamilyName // varchar
MY query:
SELECT
U.UserFamilyName, F.FamilyName
FROM
Users U LEFT
JOIN User_Friends
F ON U.UserID =
F.UserID
WHERE
U.UserName = ‘JOHN’
How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.
I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.
Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)
[code]....
I have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....
how does one specify multiple values for a single column in a where clause?
example:
SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')
if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:
Hi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:
I have a sql query which brings me many records.
This records have a column in common which have the same value (COL1)
There is a second column (COL2) which has different values bewteen these records.
I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.
Let me give you an example:
COL1 COL2
RECORD1 1-A HHH
RECORD2 1-A GGG
RECORD3 1-B LLL
RECORD4 1-B MMM
RECORD4 1-B OOO
RECORD5 1-C NNN
Me result should be:
COL1 COL2
RECORD 1-A HHHGGG
RECORD 1-B LLLMMMOOO
RECORD 1-C NNN
It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS.
Thanks for any help you can give me.
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames
887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC
I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.
For example, this one column could contain something like this:
domainuser1;domainuser2;domainuser2;domainuser 3;
The need is to convert to this:
domainuser1;domainuser2;domainuser3;
I know that's a tall order.
Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888
And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3
5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01
Using DB2.
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedI am trying to build a DIM table using a source table that has the following setup...
CREATE TABLE [dbo].[APPL_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[APPL_STATUS_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]....
What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table. I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...
CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[STATUS_START_DATE] [datetime] NULL,
[STATUS_END_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]...
The data I have is as follows -
ID1 ID2 Date Action
100 500 09/08/14 Open
100 500 09/24/14 Close
101 510 07/10/15 Open
101 510 07/19/15 Close
The output I want in a single result set is -
ID1 ID2 Open_Date Close_Date
100 500 09/08/14 09/24/14
101 510 07/10/15 07/19/15
Any way to do this in T-SQL .
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
View 4 Replies View Relatedhow we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
[code]....