Query To Return Entire Row Based Upon Two Columns?

Aug 12, 2013

I have a database that has a table that contains basic demographics

•First_name
•Last_name
•Address
•City
•State
•Zip phone
•email
•dob
•etc
•etc

Contained in this table are a lot of duplicate names, and even different addresses. I would like to return an entire row of UNIQUE information based upon “first_name + last name”. Now I know just enough of sql to pull out unique first + last names, but not the entire row based upon this unique information.

View 5 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Return Single Row For Matching Columns Based On 3rd Column

Sep 3, 2014

I have data:

Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1

Essentially if Ticket and User are the same I just want the min priority returned.

SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1

I've tried partition and rank but can't get it to return the right output.

View 5 Replies View Related

Transact SQL :: Return A Query Based On Dateadd Function

Aug 3, 2015

I'm trying to return a query based on the dateadd function.  I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1.  The Var1 variable I need to populate from the database too from a column called life_span which is an int data type.  The error I get is An expression of non-boolean type specified in context where a condition is expected near select

My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table

View 5 Replies View Related

How To Return A Numeric Value Based Upon If A Record Is Returned From My Query/Stored Proc.

Oct 2, 2007

 
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT     Name.Active FROM       Name INNER JOIN               NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND               Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
 

View 3 Replies View Related

How Do I Return Results Using An Entire Column As Part Of The Search Parameter

Nov 10, 2007

Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.

eg.
CREATE TABLE [dbo].[tPopupKeywords](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]


INSERT INTO dbo.tPopupKeywords(title, description)
SELECT 'check', 'desc' UNION ALL
SELECT 'for', 'desc' UNION ALL
SELECT 'keywords', 'desc'


select dbo.tpopupkeywords.title
where 'This is an example of a passed in string to check if any keywords are returned.'
LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??

expected results.....:

check
keywords

View 1 Replies View Related

SQL Query To Sum Two Columns Values Based On Its Record

Mar 27, 2007

May i know what is the SQL query to create another intermediate column which is used to store the sum of two columns of each and every record. Thank you.

View 16 Replies View Related

Transact SQL :: How To Return Content Of Table As Columns In Query

Oct 1, 2015

I have

Customer table, tblCust: ID; Name

1 Peter2 Mary
Product table, tblProduct: ID; Name

1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount

1 ;2 ;$20 – means Peter ordered $20 oranges

How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?

Name Banana Orange Apple 

Peter 0 20 0

View 4 Replies View Related

Return Number Of Columns - Pivot Query Into Report Builder

Oct 31, 2014

I have a query which returns number of columns using pivot ( rows into columns -- dynamic sql pivot columns). Since it is dynamic pivot, how can I bind this returned values into report builder matrix reports.

Look at this example :

First time query returns
StudentId | Col1 | Col2 | Col3

Second time query returns
StudentId | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 ...

How to bind this query into report builder 3.0 reports?

View 13 Replies View Related

Dynamically Delete Entire Columns' Values

Aug 3, 2004

Hello all,
I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.

The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.

I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.

If anyone has any idea how to do this, I would greatly appreciate it.

Thanks in advance!

View 5 Replies View Related

Select Query Results In Multiple Columns Based On Type From Another Table

Apr 6, 2008

Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:

Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3


Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road

I'd like the results to be like this:

Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30

Anyone? Thanks in advance!

View 3 Replies View Related

Transact SQL :: Keep Entire Columns Into 2 Rows When There Is A Comma In A Column

Nov 2, 2015

I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.

ID       Name                  State      Country
1       Scott, Ricky            NSW       AUS
2       Martin                   VIC         AUS
3    James, Peter,John     WA        AUS

ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below

ID       Name                  State      Country
1       Scott                     NSW       AUS
1       Ricky                     NSW       AUS
2       Martin                   VIC         AUS
3      James                     WA        AUS
3      Peter                      WA        AUS
3      John                       WA        AUS

How to strip the camma(,) in SQL?

View 3 Replies View Related

Search Entire Database For Keywords Inside Of Columns For Each Table

Sep 18, 2013

I'm trying to create a query that searches an entire database for keywords inside of the columns for each table within the database. For instance my tables have 2 columns one named ID and the other Permission, I'd like it be able to return all the lines that are associated with that keyword. So if I search "Schedule" it returns all the lines containing that word in it within that database.

View 6 Replies View Related

Hiding/Showing Columns Based On The Columns Present In The Dataset

Jun 27, 2007

I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that??

Any reply showing me the right way is appricited.



-Thanks,

Digs

View 3 Replies View Related

Query Entire DB

Aug 13, 2007

I need to search all of the tables in a DB for a specific number (say, 123456). I am unsure of which tables may have this number in it. Is there a way to search an entire DB for a specific criteria?

This is what I want:

Select *
From [db]
where [column] like '%123456%'

I understand why this dosen't work (the DB doesn't have column names), but is there a way to display table names where this number exists? If I had that, then I could search the tables individually.

View 3 Replies View Related

Calculated Columns Based On Multiple Columns?

Aug 12, 2014

MS SQL 2008 R2

I have the following effectively random numbers in a table:

n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i

I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.

if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1

So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.

declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers

View 5 Replies View Related

Is It Possible To Return Last Row Out Of Multiple Based On Row Index?

Jul 20, 2005

Here's what I'm trying to do:I have a table, which I don't have administration over, with 4 interestingfields:RecordCount - an auto incrementing primary keyCardNumber - integerCompanyID - integerAccessPriv - an string that varies constantlyThere could be identical CardNumber's, but they must have differentCompanyId's. A data set might look like this:RecordCount | CardNumber | CompanyID | AccessPriv1 | 1 | 82 | all2 | 2 | 82 | level 13 | 2 | 84 | all4 | 1 | 82 | noneThe table is transactional, so old records will not be flushed even thoughnew records contain the most current data (records 1 and 4 in this case). I'm wondering if there's a way to do a SQL SELECT query that, as it goesfrom the beginning to the end of the table, overwrites previous recordswhen later CardNumber's and CompanyID's match the previous records. So inthis case, the query would only return rows 2-4 because record 4'sCardNumber and CompanyID match record 1's.I know this is possible with application logic, but is it possible with anSQL query?Thanks.

View 4 Replies View Related

How To Return A Substring Based Upon A Character In The String ?

Apr 29, 2008

Is their a way to return only a portion of a string returned from a query? Please look at the following query and what it Yields:
select UserName from MyDirectory
NTSRJoe MeekletonNTSRAnfreesa ClinklHeadBRYAmy KooplosaAKVermian Strikoos
How can I fix this query so that it returns everything to the right of the '' character?
So I want the query to instead return the following:
Joe MeekletonAnfreesa ClinklHeadAmy KooplosaVermian Strikoos

View 2 Replies View Related

How To Return Records Based On Previous Month

Jan 11, 2005

I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.

I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.

I've tried:
'SELECT TODAY'S MONTH
SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"

'SELECT TODAY'S MONTH and the last 2 months
SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"

These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).

Any ideas?

View 7 Replies View Related

Return Value Based On Record With Multiple Types

Jan 15, 2015

I want to return Order records which are one type and don't have the other type.

The issue is I have Orders with which has 2 distriubtion types .

Example
Order 12345 has Type S and Type X.
Order 67891 has Type S

I only want to return Order 67891 that are s Type and does not have type X

View 1 Replies View Related

Return Difference Of Two Stamps In Table Based On Some Condition

Aug 10, 2015

I have table that contains below data

CreatedDate                ID             Message
 2015-05-29 7:00:00      AOOze            abc
 2015-05-29 7:05:00      AOOze            start
 2015-05-29 7:10:00      AOOze            pqy
 2015-05-29 7:15:00      AOOze            stop
 2015-05-29 7:20:00      AOOze            lmn   

 and so on following the series for every set of same ID with 5 entries for each ID

I need to Find Maximum interval time for each ID and for condition in given message (between message like Start and Stop) I used below query and it works fine

select Id, max(CreatedDate) AS 'MaxDate',min(CreatedDate) AS 'MinDate',
DATEDIFF(second,min(CreatedDate),max(CreatedDate)) AS 'MaxResponseTimeinSeconds' from Table where Id in (
SELECT distinct Id
from Table
where Message like  'stop')
group by Id

Above query displays max response time for ID A00ze as 20 minutes, but stop message has occured at 7.15. I would need to modify the query to return max response time  as 15 min(from 7.00 to 7.15).

Difference of starttime(where A00ze id started) and stoptime(where stop string is found in message).

View 7 Replies View Related

Looping A Package Based On A Return Value From A Stored Proc?

Oct 24, 2007

Hi,

I have a package that I need to loop (possibly multiple times) based on a return value from a stored procedure.

I know that in DTS you could use the return value of "execution status" to do this.

Is there a way in SSIS to loop based on a return value?

Thanks much

View 5 Replies View Related

Grouping Data Based On Return From Stored Procedure

Jun 15, 2007

I'm having some difficulty getting the appropriate results for my scenerio. I have two different datasets that I'm using. One is consisting of two joined tables and the other consisting of one sp. The sp's parameters rely on two things- one is the companyNum (inputed when the user runs the report) and two is the ContactNumType. The ContactTypeNum comes from the dataset of tables. I need to have a table consisting of this format:


ContactNumType1 (From the Tables)
File_Name1 (From the sp)
File_Name4 (From the sp)
File_Name3 (From the sp)



ContactNumType2 (From the Tables)
File_Name2 (From the sp)
File_Name7(From the sp)



ContactNumType3 (From the Tables)
File_Name5 (From the sp)



ContactNumType4 (From the Tables)
File_Name6 (From the sp)

File_Name10 (From the sp)
File_Name8(From the sp)
File_Name9 (From the sp)

So essentially what is going on is that every returned File_Name is grouped based upon the type of ContactNumType. My table returns the appropriate ContactNumTypes and the appropriate number of File_Names but returns only the first File_Name for each row. The File_Names should only grouped by the ContactTypeNums and each be unique. Is there any way to do that?


-------------------------------------------------------------------------------------------
Edited: I still am trying to work this out. I've tried a few run-arounds but none have worked. Adding custom code apparently is too risky at this point because of the security precautions that I've been instructed to take. Any help would be greatly appreciated as this project has been going on for days now....

View 3 Replies View Related

Transact SQL :: Function To Return Unit Price Based On Various Criteria

Apr 30, 2015

We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation.  What we need to do is break up the total quantity passed to the function and return the total of prices found.  In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.

Low Qty    High Qty    Fee        Actual Qty        Price
0                  7500        0.025            7500           187.50
7501           15000        0.020            2221          44.42

Below is the table definition that we have to work with (ugghh).

CREATE TABLE [dbo].[BillingPriceTable](
[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[PriceTableCode] [varchar](10) NULL,
[PriceTableName] [varchar](40) NULL,

[Code] ....

What we have so far is shown below.  The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range.  So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628

The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort. 

ALTER FUNCTION [dbo].[fn_GetPrice]
(
@plincdvarchar(3),
@pgrpcodevarchar(4),
@pitmcodevarchar(4),
@qtydecimal(10,1) = 1,
@corpnbrvarchar(9)
)

[Code] ....

View 9 Replies View Related

Return Columns Where Bit Value Is 1

Nov 20, 2012

Have a table with multiple rows, some columns that are bit (0,1).

Code:
INSERT INTO UserAccessRights (
UserID, AllowPhone, AllowTelevision, AllowComputer, AllowVisitation)
SELECT 434, 1, 0, 1, 1 UNION ALL
SELECT 512, 0, 0, 1, 0 UNION ALL
SELECT 982, 0, 1, 1, 1

Now, I'd like to run the query one user at a time. I'm missing something here, as I can't quite figure out how to only reutrn the results the where the value in the column = 1.

My pseudocode query would be something like...

Code:
select uar.userID, sc.columnName
from UserAccessRights uar
JOIN sc.syscolumns sc ON ?? = ??
where uar.userID = 982
and [sc.columnname] = 1

And I would hope to return something like..

Code:
982, AllowTelevision, AllowComputer, AllowVisitation

View 2 Replies View Related

Return Most Recent Membership Based On Year Stored In Joined Table

Jul 23, 2005

Hello-I'm fairly new to writing SQL statements and would greatly appreciatesome help on this one.I'm working on a project for a non-profit that I volunteer for. Partof the database tracks membership using tables like this:PersonInfo-------------------PersonID (primary key)FirstNameLastNameetc..PeopleMemberships-------------------PPLMembershipIP (primary key)PersonIDMembershipTypeIDFeePaidMembershipTypes--------------------MembershipTypeID (primary key)MembershipYearStandardFeeMembershipDescription (varchar)Just because a person is in PersonInfo, doesn't mean they have anythingin PeopleMemberships (they can be in the databse for other reasons andnot have or have ever had a membership).Membership fees vary by year and type of membership and they want toretain a history of a person's memberships.What I'm looking to do here is write a query (a view in SQL Server)that will return the following InfoPersonID, MostRecentMembershipYear, FeePaidForThatMembership,DescriptionOfThatMembershipI'm thinking that I'd use max(MembershipYear), but that requires groupby for the other columns, so I'm getting all of the people'smemberships returned.I'm pretty sure this can be best done with a subquery, but I'm not surehow.Can someone please point me in the right direction or provide a samplethat I can learn from?Kindly,Ken

View 4 Replies View Related

Transact SQL :: Return Preset Data Values Based On User Material ID

Jul 22, 2015

I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:

User Material = Wood-MDF
Preset Material = MDF Panel

I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?

View 2 Replies View Related

Transact SQL :: Retrieve Return Result With Chronological Order Based On Parameter

Jun 23, 2015

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.

Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?

Information: I'm using SQL server 2012

create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values

[Code] .....

View 2 Replies View Related

Return Only Columns That Are Not Null

Feb 20, 2006

johnny writes "I am trying to return only the columns from multiple tables that are NOT NULL for a specific ID. Once I have the tables JOINED is there a way to only get those columns that are populated. Thanks."

View 2 Replies View Related

How To Return A LOT Of Other Columns With Group By

Jan 19, 2008

I have a MyNames table which contains over 100 columns. The Name column may have the same name in many rows. The data looks like this.

Name..........Factor1..........Factor2...
John..........X................Y
John..........X................X
Mary..........T................Y
Mary..........V................Y

I need a result table containing all 100 columns from MyNames but only the unique names in the name column. I don’t care which name row is used.

Name..........Factor1..........Factor2...
John..........X................Y
Mary..........V................Y

I have tried DISTINCT and GROUP BY.
DISTINCT will unique on every column listed so I will end up with the name repetred in the Name column.

SELECT DISIINCT Name, Factor1, Factor2 => I do not want the Factor columns involved in the distinct. This will cause the same name to appear in more than one row.

Group by will requires that I place the remaininf 99 columns in a function in order to have the columns listed.

SELECT Name, MAX(Address)... => adding 99 fields.

Is there a way get only the unique Names with the 99 other columns with out having to manually put the other 99 columns in functions?

View 13 Replies View Related

Return Columns For Different Sql Bases

Nov 3, 2006

I want in one query return columns from tables where exist in different sql bases.



In MS Access can link tables from many defferents sql bases or other MSAccess bases and finally can make a simple query where return columns from tables where exist in different bases.



How can i have the same functionality in Visual Basic .Net 2005 (or Visual Basic 6.0)

View 1 Replies View Related

SQL Server 2008 :: Return Fiscal Year Based On YYYY-week Format

Nov 6, 2015

I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the Year Week is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.

Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.select sum(hours) as total, yearweek from tablename group by yearweek...Then I loop through using C#.I can return the FY using an actual date,how to do it for year-week format for any given year.

select CASE
WHEN CAST(GETDATE() AS DATE) >
SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000)
THEN
DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE())
END AS FY

View 9 Replies View Related

Power Pivot :: Return Time Based Upon Sum Of Personnel Arriving At Fire Scene

Sep 10, 2015

As part of analyzing our fire department's response times, we measure the time it takes to get a certain amount of people on a fire scene. I have two table that contains incident data. The incident table contains one row per incident. The apparatus table contains multiple rows per incident with each row contain the information for each unit. They are connected by the incident key with a one to many relationship. 

The column apparatus.num_personnel contains the count of personnel for each fire truck. The goal is to capture the response time of the unit that is carrying the 10th person to the scene. In the example below, E78 was the unit that the sum of apparatus.num_personnel was => 10. There response time of 0:06:18 is returned to the incident table. 

I realize I will need to create a logical test (calculated column on the incident table) that sums the number of personnel for each incident and then when that number => 10, then return the MIN Response Time of that row. From past projects, I understand I can create a temporary table (ADD COLUMNS) which will iterate over each incident row, comparing the units for that incident. I"m just not sure how to put it all together. 

Overview of data model. The incident and apparatus are my data tables and the rest are lookup tables. The parameter table is used for a percentile measure, but doesn't play a role with this project. 

View 9 Replies View Related

Return A Row With Columns For Each Day In A Date Range

Sep 13, 2006

Given in a record in from a Table called WorkSchedule:

idWorkSchedul StartDate EndDate HoursWorked
1 1/1/2000 1/1/2006 8



I need to return for each record in the WorkSchedule Table
1/1/2000 1/2/2000 1/3/2000 1/4/2000..........1/1/2006
8 8 8 8..................8

Please help.

Thank you.
-Robert

View 5 Replies View Related







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