Consequtive Rows That Meet Criteria
Oct 3, 2007
Hi
I have the following table in SQL Server 2000.
JobDate PayrollNo Variance JobNumber
1/1/2007 123456 -100 111111
1/2/2007 123456 -200 222222
1/3/2007 123456 100 333333
1/5/2007 123456 -150 555555
1/4/2007 543212 -100 444444
1/8/2007 543212 -500 666666
1/14/2007 543212 -192 777777
I need to show any values where the variance is negative for 3 or more consequtive jobs. So the rows in bold above would be returned.
I've found something here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022586&SiteID=1 But it's not exactly the same as that returns rows where the dates are consequtive and 2 rows have negative numbers.
Anyone got any suggestions on how to achieve this please?
Thanks
View 8 Replies
ADVERTISEMENT
Jun 2, 2015
I am working on a project that was assigned to me that has to do with data in one of our SQL databases. I have the following query that takes information from a single table and averages test scores for each student.
--Group all scores from same student and average them together
with cte_names as
(
SELECT StudentID, MAX(StudentName) AS StudentName
FROM LDCScores
WHERE schoolYear='2014-2015' AND term = 3
GROUP BY StudentID
[code].....
I now need to take the results from the above query and determine the percentage of students, per school that scored a 2 or greater in grade 7 for each test. For grade 8 scored a 2.5 or greater, grade 9 scored a 3 or greater, grade 10 scored a 3 or greater, grade 11 scored a 3.5 or greater, and grade 12 scored a 3.5 or greater.
View 7 Replies
View Related
Sep 2, 2004
Hi there,
Is there a quick way to select all the tables in the DB that don't have certain column in the table, so for example getting a list of all tables that don't have columns: A, B or C?
Thanks
S
View 4 Replies
View Related
Jan 31, 2007
Hi below sample data incoming from a source that cannot be changed. Please ignore the mishandling of zls. Obviously it is not insurmountable - I am just interested in why it is happening because I cannot explain it. DECLARE @t TABLE(the_data CHAR(73)) SET DATEFORMAT dmySET NOCOUNT ON INSERT INTO @tSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'0'+space(10)+'08 01 2002'+space(10)+'04 10 2002'UNION ALLSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'109 08 2004'+space(20)+'21 07 2005'UNION ALLSELECT ' 11 13026721XX198734 1'+space(10)+'0'+space(10)+'XXXXXXXXXX'+space(10)+ '09 01 2003' SELECT CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX',NULL) AS SMALLDATETIME) AS date_1_prob,CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX','') AS SMALLDATETIME) AS date_1_ok_ish,CAST(NULLIF(REPLACE(date1_text,' ','/'),'XXXXXXXXXX') AS SMALLDATETIME)AS date_1_fine, date1_textFROM--derived table - selecting relevant substring(SELECT LTRIM(RTRIM(SUBSTRING(the_data, 44, 10))) AS date1_textFROM @t)AS der_t date_1_prob date_1_ok_ish date_1_fine date1_text----------------------- ----------------------- ----------------------- ----------NULL 2002-01-08 00:00:00 2002-01-08 00:00:00 08 01 2002NULL 1900-01-01 00:00:00 1900-01-01 00:00:00 NULL 1900-01-01 00:00:00 NULL XXXXXXXXXX Can anyone explain the result in the first row first column? Thanks
View 8 Replies
View Related
Oct 22, 2015
Here's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
View 9 Replies
View Related
Mar 21, 2006
Hello again,
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag
1111296.54
3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id
100000001
200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag
1111296.54
3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id
100000001
(Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
Thanks, and im happy to clarify if needed.
View 1 Replies
View Related
May 24, 2004
For example, consider the following queries:
DECLARE @SomeParam INT
SET @SomeParam = 44
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam
SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
Thanks.
View 3 Replies
View Related
Oct 15, 2009
is there a way to update multiple rows in one update query in tsql? what I wanted to do is for example I got a table containing
code : desc
1 : a
2 : b
3 : c
4 : d
1 : e
3 : f
I wanted to update it to
code : desc
1 : x
2 : b
3 : y
4 : d
1 : x
3 : y
how to do it?
View 5 Replies
View Related
Jun 4, 2006
Anyone going to TechEd next week in Boston? Let me know if you want to hook up to discuss disaster recovery, fragmentation or anything else to do with the SQL Server storage engine.
I'll be there all week and will be presenting a deep-dive into CHECKDB internals and usage on Wednesday morning (the basis for my whitepaper on the same that should be out sometime this summer)
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
View 2 Replies
View Related
Aug 5, 2004
Hi there,
I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.
So I just want to use a cursor on a table list that meet the criteria:
1) have qid column name
2) qid >= 5000000 and qid < 1500000000 '
Example
declare @strqry varchar(1000)
declare dailyYear cursor
for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
open dailyYear
fetch next from dailyYear into @DelTable
while @@FETCH_STATUS = 0
begin
Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '
exec(@strqry)
fetch next from dailyYear into @DelTable
end
close dailyYear
deallocate dailyYear
Any help would be greatly appreciated!!
Thanks
S
View 3 Replies
View Related
Jan 16, 2008
User's requirement:
use the SP get the dataset from DB at once.
Want to make an accurate count of paging ( 200 rows /page) at the SSRS side.
Need to provide sorting, user just need to click the according column header's caption.
The design is:
we add group to devide the data into 200 per unit. Choice 'page break at end'.
add 2 Report Parameters, SD & SF, means sorting direction and Field.
In the Table Parameters add:
=IIF(Parameters!SD.Value="Ascending",Fields(Parameters!SF.Value).Value,"")
=IIF(Parameters!SD.Value="Descending",Fields(Parameters!SF.Value).Value,"")
everything seems OK at this time, and the rpt is very quick.
The Bug is:
Test team found out the sorting was broken by group, because we Choice 'page break at end'.
Noe the sorting scope is just the first page (first group)
Help wants:
query DB once , Accurate paging, full scope sorting.
View 5 Replies
View Related
Oct 1, 2015
I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription BugID BugStatus
1 TestCase1 1 Closed
2 TestCase1 2 Open
3 TestCase2 11 Closed
4 TestCase2 12 Closed
5 TestCase2 13 Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
View 3 Replies
View Related
Feb 10, 2006
This doesn't make any sense. I am trying to install SQL Server 2005 on SBS 2003 with Service Pack 1. According to Windows Update, there is nothing left for me to install. However, I am getting this error (while installing SQL Server Express and the Developer Edition):
"Your operating system does not meet Service Pack level requirements for this SQL Server release. Install the Service Pack from the Microsoft Download Center at http://go.microsoft.com/fwlink/?LinkId=50380, and then run SQL Server setup again."
When I go to the link, there is nothing there to download (it takes me to the main Microsoft download page). I meet all the requirements that I have found. What gives?
Thanks,
Scott
View 36 Replies
View Related
Sep 13, 2007
I am trying to upgrade the MSSQL Express with the Advanced Services version. I have:
Mobil Intel Pentium 4-M CPU 2.00 GHz
512 MB or RAM
50.8 GB free space on my C drive
I am getting the following message:
- Minimum Hardware Requirement (Warning)
Messages
Minimum Hardware Requirement
The current system does not meet the minimum hardware requirements for this SQL Server release. For detailed hardware and software requirements, see the readme file or SQL Server Books Online
I have looked at the requirements in the books and it seems that I meet or exceed the minimums.
Does anyone have an idea what I can do now?
Joseph McKown
View 5 Replies
View Related
Jun 18, 2007
I am receiving the following error message when attempting to create a new SQL Authenticated login id.
Password validation failed. The password does not meet the requirements of the password filter DLL. (Microsoft SQL Server, Error: 15119)
I have four servers all running SQL Server 2005 SP2 on Windows 2003 Ent. SP1. Of the four servers, only one received the above error message using the same TSQL below.
CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'pvif9dal' MUST_CHANGE, CHECK_EXPIRATION = ON
All four servers are in the same domain, which if I understand correctly, the password policies are therefore inherited at the OS level by the domain. The password being used is within the password policies of the domain.
Any ideas as to a root cause?
View 5 Replies
View Related
Jun 30, 2007
I tried to install an ALLDATA database which run with SQL Server 2005 express edition. The data base fails to install becase of the following code that come up which is related to AS password requirement. The error that come up is:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
The sa password must meet SQL Server password policy requirements. For strong password guidelines, see Authentication Mode, in SQL Server Books Online.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=28001&EvtType=sqlca%5csqlcax.cpp%40SAPasswordPolicyCheck%40SAPasswordPolicyCheck%40x6d61
------------------------------
BUTTONS:
&Retry
Cancel
------------------------------
I am trying to install this database in a network server operating under Windows Server 2003 R2 with SP2. If anyone knows how to solve this problem, please let me.
Thanks,
Amilcar
View 6 Replies
View Related
Nov 28, 2007
SELECT Wins, Losses, Wins/Games AS WinningPct
FROM standings
Where WinningPct > 0.5
SQL will not allow me to put a column that I created(WinningPct) as criteria for WHERE (I know this is cause Select is evaluated last)
How can I list my results according to criteria I am creating in my query?
View 9 Replies
View Related
Sep 30, 2007
I have a table
GO
CREATE TABLE [dbo].[Speech] ( [SpeechId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkSpeech_SpeechId PRIMARY KEY, [UniqueName] [varchar](52) NOT NULL, [NativeName] [nvarchar](52) NOT NULL, [Place] [nvarchar](52) NOT NULL, [Type] [smallint] NOT NULL, [LanguageId] [char](2) NOT NULL CONSTRAINT FkSpeech_LanguageId FOREIGN KEY (LanguageId) REFERENCES Language(LanguageId) ON UPDATE CASCADE ON DELETE CASCADE, [SpeakerId] [int] NOT NULL CONSTRAINT FkSpeech_SpeakerId FOREIGN KEY (SpeakerId) REFERENCES Speaker(SpeakerId) ON DELETE CASCADE, [IsFavorite] [bit] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDate] [datetime] NULL )
Now I want to search the Table Speech
Sometimes by : SpeechIdSometimes by : SpeakerIdSometimes by : LanguageIdSometimes by : SpeechId And LanguageIdSometimes by : SpeakerId And LanguageId
All can have conditions with IsVisible, IsFavorite and Type columns.
for example
I need all Speeches withany particular SpeakerId and LanguageIdwith IsVisible equals to trueand IsFvaorite No Matterand Type equals to Audio
For these type of queries I think the solution is
GO
CREATE PROCEDURE [dbo].[sprocGetSpeech]
@speechId int = NULL, @uniqueName varchar(52) = NULL, @nativeName nvarchar(52) = NULL, @place nvarchar(52) = NULL, @type smallint = NULL, @languageId char(2) = NULL, @speakerId int = NULL, @isFavorite bit = NULL, @isVisible bit = NULL
AS
SELECT SpeechId, UniqueName, NativeName, Place, Type, LanguageId, SpeakerId, IsFavorite, IsVisible, CreatedDate, ModifiedDate FROM Speech WHERE SpeechId = @speechId AND UniqueName = CASE WHEN @uniqueName IS NULL THEN [UniqueName] ELSE @uniqueName END AND NativeName = CASE WHEN @nativeName IS NULL THEN [NativeName] ELSE @NativeName END AND Place = CASE WHEN @place IS NULL THEN [Place] ELSE @place END AND Type = CASE WHEN @type IS NULL THEN [Type] ELSE @type END AND LanguageId = CASE WHEN @languageId IS NULL THEN [LanguageId] ELSE @languageId END AND SpeakerId = CASE WHEN @speakerId IS NULL THEN [SpeakerId] ELSE @speakerId END AND IsFavorite = CASE WHEN @isFavorite IS NULL THEN [IsFavorite] ELSE @isFavorite END AND IsVisible = CASE WHEN @isVisible IS NULL THEN [IsVisible] ELSE @isVisible END
Can anyone tell me?
Is it right way to do?Do you have any better solution?If my solution is better then Is there any performance loss with that query?
View 1 Replies
View Related
Jul 12, 2002
I am familiar and happy with using BCP to export from SQL Server to a flat file
.. 1) Is there any way to pass a parameter to the sql script file each time so that i can vary the selection critria the script file uses each time?
.. 2) Can i batch the BCP calls together so they all use this parameter with some kind of 'super' BCP cammand?
Thanks in anticipation
View 3 Replies
View Related
Feb 2, 2015
I have a table in the following format
reporting_date interest_payment balance
200401 10 10
200402 20 15
200403 30 20
200404 40 30
200405 50 40
200406 60 50
200407 70 60
i wanted to generate an OUTPUT in the following format :
The output of the query should look like this :
reporting_date interest_payment balance
Q1 -2004 60 10
Q2 -2004 170 30
Q3 -2004 70 60
Q4 -2004 0 0
i.e i wanted to represent data by quarter and year and group by quarter and year for interest_payment column but for balance i need to pick up the value from the first reporting date in that quarter ,so as you can see q1-2004 has 10,15 and 20 but only 10 is accounted as that was the first reporting date in that quarter
I have my query working for interest payment but i am not sure how do i pickup the first reporting value for balance in a quarter
SELECT report_year as "@date",'Q'+CAST(report_quarter+1 as varchar(1)) as "@quarter", SUM(a.balance) as "@balance", SUM(a.interest_payment) as "@interest_payment"
FROM (SELECT *,
(reporting_date%100 - 1)/3 as report_quarter,
reporting_date/100 as report_year
FROM employee) a
GROUP by report_year, report_quarter
order by report_year, report_quarter
View 1 Replies
View Related
Mar 17, 2015
use of Row_Number() over ( partition... but I dont understand how.
Imagine I have a table like
CustomerID, PartNum, QtyinOrder, shipped
1 6 3 0
1 6 2 0
2 6 1 0
2 5 1 0
2 5 2 0
2 5 3 0
2 5 4 1
1 6 4 1
2 6 2 1
But I wanted to return
CustomerID, PartNum, MaxQtyOrderedNotShipped
That would be just the rows
1 6 3 0
2 6 1 0
2 5 3 0
If I use this:
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1
there is no restriction, so I get the shipped...If I alter the where clause to work only on not shipped, I get no records...as below
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1 and shipped=0
View 2 Replies
View Related
Jan 27, 2006
Hi,
While playing with SQL Server 2000 I found you can specify the selection criteria in either the FROM clause or the WHERE clause:
e.g.
select *
from Table1 a inner join Table2 b ON a.key = b.key and a.field = 1
Is logically the same as:
select *
from Table1 a inner join Table2 b ON a.key = b.key
where a.a = 1
Any comments on which is best, and why?
Thanks,
Chris
View 3 Replies
View Related
Jun 7, 2006
jiang writes "Apologies in advance for my inexperience.
I have a SQL table to hold my product information:
prods(prodnum(char(10), prodname(char20), quantity(int))
The values in prodname column are like:
ABCDEF
ADCDEF
BCDEFG
CDEFGH
For those products that sold out, I made a mark in the front of prodname, like *ABCDEF
Then in my query, I want to sort the product name in alphabetic order, in addition, I also want to put prodname start with * at the end of the result list, like:
ADCDEF
BCDEFG
CDEFGH
*ABCDEF
I tried to use:
select prodname from prods order by prodname
this query shows *ABCDEF is on the top of the result, then I tried:
select prodname from prods order by charindex('*', namecode)
this query does put *ABCDEF at the bottom, but other records are not in alphabetic order.
Could you please help me? Many many thanks!
Jiang"
View 3 Replies
View Related
Feb 5, 2007
hi all... how do i write my where clause if i wanna search BETWEEN something to something, but at the same time, find ALL if user send nothing (''), NOT searching for '' column... and also find date if they send a date, and if they dont send date, do not consider date at all(find all at any dates).. is this possible to in one where clause without any IF statement... thanks..
WHERE d.Ownership LIKE '%' + @ClientID +'%' AND
d.WhsID LIKE '%' + @WhsFrom + '%' AND
d.CustomLotNo LIKE '%' + @CustomlotnoFrom+ '%' AND
d.LocID BETWEEN @LocFrom AND @LocTo AND
d.ItemID LIKE '%'+ @ItemFrom + '%' AND
substring(d.LocID,1,1) LIKE '%' + @ZoneFrom AND
d.RecvDate <= @Date
~~~Focus on problem, not solution~~~
View 20 Replies
View Related
Mar 22, 2007
I am trying to filter data from columns and this is just not working. If I select all the criteria below and try to run it - I do not get any records returned.
WHERE (DropDt >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 13, 0)) AND (DropDt <= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0))
and Type IN ('Employee', 'Refinance')
and Chan IN ('XM', 'BN', 'RS', 'MM')
and Seg IN ('Hoc','LeftOver', 'COnly')
View 4 Replies
View Related
Aug 31, 2007
I am just learning SQL server 2005 and I am having trouble with the sql statement of my sqlcommand. I am just trying to query for any ticket that was open yesterday. I need this to run daily
If I run the following it works
SELECT Assigned_Group,
Assigned_Technician,
Date_Created
From "Support Center Ticket" where "Date_Created" > '08/30/2007 00:00:00'
and division = 'Northern'
however when I change it
SELECT Assigned_Group,
Assigned_Technician,
Date_Created
From "Support Center Ticket" where "Date_Created" > convert(varchar, getdate()-1, 101) + ' 00:00:00'
and division = 'Northern'
SSIS package "Package_test.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Error: 0xC0047062 at Data Flow Task, DataReader Source [46]: System.Data.Odbc.OdbcException: ERROR [420] Driver]Unexpected extra token: (
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (46) failed the pre-execute phase and returned error code 0x80131937.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (6856)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package_test: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package_test.dtsx" finished: Failure.
View 2 Replies
View Related
May 6, 2008
I have a query that searches through a 4 million record table. The data is fed from the UNIX flat file systems so the data is not in optimal search format. So I created some views that massaged the data and then index them. I select and join the original table with the view, with NOEXPAND hint on the view. My question is this theory right: If I put the criteria in the FROM join part then it will make the join easier than if I put it in the where clause?
Example (any difference)
SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON m.RecNumber LIKE '112%' AND u.ID = m.ID
versus
SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON u.ID = m.ID WHERE m.RecNumber LIKE '112%'
THANKS!!!
View 3 Replies
View Related
Sep 10, 2007
I am creating a .aspx page that links with Miscrosoft SQL Server 2005 Express. It includes a GridView control that displays all the table data on the page. You can then select a record from the control (currently by clicking an image button to the left of each record- is there any way of selecting the record by clicking anywhere on the row? How would that be done?) and it displays the data in a detailsview control below where the data can be changed etc.
The data is like a phonebook (Name, Telephone number, and some other misc fields) and the user should be able to search by either name or number to filter out the records shown in the gridview control. I have two textboxes for this, and I started with the name text box and it works fine. i.e. with one filterparameter and one filterexpression. So that if you just enter 'Da' it filters out the records displaying only those whose name starts with 'Da'.
I have experimented but have found no way of including filter expressions to use the number as a search. I added the second filter parameter (under sqldatasource control so that:
<FilterParameters>
<asp:ControlParameter Name="DestinationName" ControlID="txtName" /><asp:ControlParameter Name="DestinationNumber" ControlID="txtNumber" />
</FilterParameters>
But I don't know what to do for the FilterExpressions. currently I just have:
FilterExpression="DestinationName LIKE '{0}%'"
i have tried using "DestinationName LIKE '{0}%' OR DestinationNumber LIKE '{0}%'" but it requires that both text boxes have data entered.
What I want is something that allows the user to enter either a name or number or both (all or part of so don't need to enter in full name/number) and it filters out the records accordingly. I.e. if you enterd 'Dav' and '079' it would bring back all the records who had a name starting with Dav and a number starting with 079. However if you enterd just 079 then it should just bring back all records with numbers starting 079 whatever their associated name.
Thanks
View 9 Replies
View Related
Feb 9, 2004
I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg
The symbols are stored in the tables as: A B C D
The query string criteria would look like: IN('A', 'B', 'C', 'D')
The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC
SELECT a_Name_Symbol.Symbol, a_Financials.Revenue
FROM a_Financials INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
WHERE (a_Name_Symbol.Symbol @Symbol)
ORDER BY a_Name_Symbol.Symbol
Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?
Thanks,
Paul
View 1 Replies
View Related
Mar 11, 2005
I need a little insight on how to select the same field from the same table, but for different criteria.
here are example tables...
Categories
CATSUBCATNAME
10MainTitle
11SubTitle #1
12SubTitle #2
20Section
21Section #1
DataTable
CATSUBCATINFO
11Detail Information for subtitle #1
12Detail information for subtitle #2
desired result would be:
MainTitle, SubTitle #1, Detail Information for subtitle #1
MainTitle, SubTitle #2, Detail Information for subtitle #2
Select c1.Name, c2.Name, d.info
from DataTable d, Categories c1, Categories c2
where c1.CAT = d.CAT
and c2.CAT = d.CAT
and c2.SUBCAT = d.SUBCAT
View 1 Replies
View Related
Jan 4, 2006
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
View 5 Replies
View Related
Aug 28, 2000
Is it possible to to view 2 tables with a common field name and display it in the following way
Name telephon no.
John 123-4567
789-4561
987-6543
Peter 159-7536
654-9874
896-3214
456-9874
without repeating the name in each row.
Thanks
View 1 Replies
View Related
Aug 25, 2005
Hi,
I was wondering if it is possible to order a recordset by specific values.
That is, I want my recordset to be ordered alphabetically, but I want to set the order of the alphabet.
For example, I don't want my recordset to be odered by A, B, C, D ...I want it to be ordered by G, V, T, A ... or something.
Can you do something like
Code:
SELECT * FROM myTable ORDER BY myField (G, V, T, A, B, C, Q, X)
...etc
Thanks
e
View 2 Replies
View Related
