SQL Challenge - How To Return A Record Set Starting At A Particular Record?
Feb 25, 2007
I have a directory of user information. What I would like to do is
allow someone to search for person X and then return not only the
information for person X, but also the information for the next 15
people following person X sorted alphabetically by lastname.
So if someone searched for the lastname = "Samson", it would return:
Samson, John
Saxton, Greg
Scott, Heather
Sears, Rebecca
.... (15 names following "Samson) ...
How do you in SQL return a record set of X records starting at
particular record (e.g. lastname = "Smith)?
Thanks in advance.
View 4 Replies
ADVERTISEMENT
Jan 31, 2006
I've been looking for examples online to write a SPROC to get some data. Here are the tables.
Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]
I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
View 3 Replies
View Related
Jun 14, 2007
I have a bunch of records of the following layout:
A B 1235
A B 1236
A B 1237
A C 1238
A C 1239
When I run my script, I want to aggregate these together, but preserve the starting and ending record ids in two new columns:
A B 1235 1237
A C 1238 1239
Can I do all of this in an Aggregate Transformer, or do I need to derive some columns first?
Thanks!
Jim Work
View 10 Replies
View Related
Apr 1, 2008
Is there a SQL command that returns every other result or every 3rd result. The reason being is i am using the data to plot a graph with many thousands of points, it would be useful if not every result was return. I.e. too much data to fit within the screens resolution.
Regards Dan Walmsley
View 4 Replies
View Related
Mar 24, 2008
Is there a t-sql shortcut/ quick way to return the full max record.
So basically if I had:
prog_area glh description
HS 200 health and social care
EN 300 engineering
HS 400 health care
EN 250 engineering and construction
so grouping by prog_area and taking max glh would return appropriate descriptions like:
HS 400 health care
EN 300 engineering
This is quite a simple example and I would be using it on much larger datasets. I know how to do it by doing a join to itself with max glh but it seems to me there should be an easier way to return the appropriate full record set. Hopefully somethin fast in t-sql
View 2 Replies
View Related
Jul 20, 2005
i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/
View 6 Replies
View Related
Feb 26, 2008
Hello,
The following query:
SELECT submitRep1 AS Rep, tc, COUNT(tc)AS TCCount
FROM tbl_CYProcessedSales
WHERE
tc NOT LIKE 'T%'
AND
tc NOT LIKE 'R%'
AND ISNUMERIC(TC) = 0
AND NOT submitrep1 = ''
AND Submitrep1 = 'along'
GROUP BY submitrep1, tc
Returns a result like this:
ALONG KL 65
ALONG KP 35
How can I return the one record that contains the MAX(TCCount)?
Thank you for your help!
CSDunn
View 10 Replies
View Related
Oct 21, 2013
I am wanting to run a SQL statement whereby i return the ID of any employee's Director.
The database for employees has a reports to field which enables me to see the hierarchy of managers above any employee.
There is also a IsDirector flag that indicates a director.
So essentially i want to run sql that would return the first instance of a director in the hierarchy above any employee.
eg if A reports to B and B reports to C (who is a director) then it returns C.
I basically want the script to run until a director is found.
how would i do this?
View 5 Replies
View Related
Jan 19, 2007
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
View 4 Replies
View Related
Jun 15, 2007
Hey everybody!
How do i insert a carriage return at the end of an record that's being sent to a flat file? Currently, I get one long string, and would like for SSIS to put carriage returns at the end of each line.
Any ideas?
Thanks!
Jim Work
View 3 Replies
View Related
Sep 3, 2007
Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm
The result of the query should only return 3 rows/records,not 5. Thank you. Greg
Code Snippet
select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age
,pv.WeightPounds, pv.HeightInches
from Patients as p
inner join PatientVisits as pv
ON p.ID = PV.PatientID
order by pv.VisitDate desc
INSERT INTO Patients (ID, FirstName,LastName,DOB)
select '1234-12', 'Joe','Smith','3/1/1960'
union
select '5432-30','Bob','Jones','3/1/1960'
union
select '3232-22','Paul','White','5/12/1982'
INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches)
select '1234-12', '10/11/2001','180','68.5'
union
select '1234-12', '2/1/2003','185','68.7'
union
select '5432-30','11/6/2000','155','63.0'
union
select '5432-30','5/12/2001','165','63.0'
union
select '5432-30','4/5/2000','164','63.5'
union
select '3232-22','1/17/2002','220','75.0'
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[PatientVisits](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [nvarchar](50) NULL,
[VisitDate] [datetime] NULL,
[WeightPounds] [numeric](18, 0) NULL,
[HeightInches] [decimal](18, 0) NULL
) ON [PRIMARY]
View 11 Replies
View Related
Jan 24, 2006
For example:
Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location
I know MS Access you can use a "*" to retrieve all records when there is a parameter. What does SQL Sever query use to retrieve all record?
View 14 Replies
View Related
Dec 18, 2012
Using MSSQL 2008 R2
Given the following table
Code:
{ID, PropClass, OffSet, Amount}
{1, 1, 1, .30}
{2, 1, 2, .45}
{3, 1, 3, .50}
{4, 2, 1, .26}
{5, 2, 2, .15}
If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?
For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3
This is a query that I am trying to work on:
Code:
SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
FROM UPValue v
INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
WHERE v.Year = 2012 and LeaseType = 2
group by v.District, v.PropClass, YearAquired
order by v.District, v.PropClass
Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.
View 4 Replies
View Related
Apr 12, 2015
I have a table with records like that.
Group | Value
Team 1 | 0
Team 1 | 0
Team 1 | 1
Team 1 | 1
Team 2 | 0
Team 2 | 0
Team 2 | 0
I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.
View 1 Replies
View Related
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
Feb 26, 2007
I'm trying to return the total records with my query, but I'm getting the following error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
Here's my query:
set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")
<%response.write rsfind("TotalCount")%>
Thanks for any help!
Dale
View 10 Replies
View Related
May 3, 2007
I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic
and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4
I need to get back something like this
TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4
that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client
this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this
TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray
but I am not sure how this idea could be implemented using the capabilities of SQL.
my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.
any ideas in how to make this better ?
View 8 Replies
View Related
Apr 28, 2015
I have been researching BOL and other online resources but cannot seem to get a definitive answer.
Current Output:
[MemberID][Category][Type]
12345ABCtest
12345XYZtest
12777ABCtest
12888FGDtest
Desired Output:
[MemberID][Category][Type]
12345ABCtest
12777ABCtest
12888FGDtest
Query:
SELECT m.MemberID,
vw.Category,
vw.Type,
FROM dbo.TestVW vw JOIN
dbo.TestMember m ON vw.MemberKey = m.MemberKey
WHERE vw.Type = 'test'
GROUP BY m.MemberID,
[Code] ...
but cannot seem to be able to return one record with its corresponding value criteria.
View 21 Replies
View Related
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Apr 6, 2007
Hey all!
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
Thanks in advance,
Johan
View 5 Replies
View Related
Nov 14, 2007
I run an ASP.NET website with a monthly contest. Members can enter 1 script for each contest. Scripts can then be voted on.
There are three relevant SQL tables:
Contest with the field: ContestIDScript with the fields: ContestID, ScriptID, and TitleScriptComment with the fields: ScriptID and Score
Scripts are grouped by contest and come in place (first, second, third, etc.) based on the average of all the scores they received.
I need a QUICK stored procedure that will accept ScriptID as a parameter and return back the place that script came in, in that particular contest.
Currently, I get this information by creating a virtual table and populating it with joined data from Script and ScriptComment. I order the table by average Score desc. I then use a select statement against that table using the ScriptID and return back the row number. This works, but it is very slow. There are times where I might need to list 300 scripts and I need to do this lookup for each script to display what place they came in.
I need a much faster way to get the place of any script in a contest. I think the Virtual Table is killing the speed. I have been trying to find a solution using a view or the new SQL 2005 command Row_Number(), but I have been unsuccessful.
If anyone has a speedy solution, I would love to hear it.
Thanks a lot,
Chris Messineo
View 3 Replies
View Related
May 7, 2012
I have 3 tables I'm trying to join:
Table 1: Contains ID, Name, Description
Table 2: Contains ID, Keyword
Table 3: Is lookup table for tables 1 & 2
I need a query to return a table having columns of:
Name, Description, Keyword for every record ID in Table 1
The issue is that table 2 contains multiple keywords for each key ID and my query returns an error: Subquery returned more than 1 value.
When I run this query using a specific key, it returns the correct information the way I want it:
DECLARE @ServiceID VARCHAR(10)
SET @ServiceID = '35'
DECLARE @Name VARCHAR(8000) DECLARE @Desc VARCHAR(8000) DECLARE @Keywords VARCHAR(8000)
SELECT @Name = [DefService].[Name],@Desc = [DefService].[Description],@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name] FROM [DefService] INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] WHERE [DefService].[ServiceID] = @ServiceID PRINT @Name + ' | ' + @Desc + ' | ' + @Keywords
SELECT @Name AS Name,@Desc AS Description,@Keywords AS Keywords
Results:
NameDescriptionKeywords
GoToMyPC - Account RequestRequest a Citrix GoToMyPC account.GTMPC, GoTo, application, software, install, Installation, applications
When I run the same query without a specific key it fails. The results only return a single row containing Name, Description and then ALL keywords for every key ID...very odd behavior.
BTW, I need to do this in a single SQL query and not a stored proc or other method.
View 10 Replies
View Related
Nov 26, 2005
We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.
View 8 Replies
View Related
Jan 4, 2007
Hi everyone,
I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.
So.. I need help from you guys, cause I don't know How.
This is my DTS description.
My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.
Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.
BUT How??? Can someone please help?? Thanks
View 10 Replies
View Related
Apr 7, 2008
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
Feb 26, 2008
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
View 3 Replies
View Related
Jul 18, 2007
Dear all,
I am using C# , asp.net and sql server 2005.
Let me explain the situation.
I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can
Get last inserted variable values
Please help
thanks
View 3 Replies
View Related
Apr 16, 2014
I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL
[code]....
View 4 Replies
View Related
Nov 26, 2013
I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items
Group 1
Group 2
Group 3
The other group sum up correctly while others don't. What can explain this? Here is the code.
Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID
left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey
[code]....
View 3 Replies
View Related
Aug 7, 2015
I have a single complex query.
SELECT
Col1, -- Header,
Col2, -- Header,
Col3, -- Detail
Col4, -- Detail
Col5, -- Detail
FROM
TableName;
The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -
SELECT
Cols
FROM
Table Names
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL
This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -
<Header >
<detail 1>
</detail 1>
<Detail 2>
</Detail 2>
<detail 3>
</detail 3>
</Header>
View 2 Replies
View Related
Mar 1, 2006
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
View 6 Replies
View Related
Apr 17, 2014
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
View 2 Replies
View Related
