Searching Dates
Oct 17, 2007
Hi,
I have a table that looks similar to this. How would i write a query that will show me the price of the product on mondays?
Thanks
Date Price
16/10/2007 16.75
15/10/2007 16.25
12/10/2007 15.7
11/10/2007 15.64
10/10/2007 15.17
09/10/2007 14.9
05/10/2007 14.75
04/10/2007 14.52
03/10/2007 14.84
02/10/2007 14.9
01/10/2007 14.84
28/09/2007 15.3
View 8 Replies
ADVERTISEMENT
Feb 20, 2006
after more than one week asking on the forum .. i get allways an erro searching on dates in MS SQL 2000
myDate = smalldatetime
SELECT myDate FROM dbo.myTable WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')
i get the error :conversion from CHAR to SMALLMONEY out of the limits
why smallmoney ??
if i try
myDate = datetime
SELECT myDate FROM dbo.myTable WHERE (myDate >= '2003-12-15') AND (myDate < '2003-12-16')
i get the error :conversion from CHAR to DATETIME out of the limits
so much time and energy lost for those dates !
thank you for helping again
View 14 Replies
View Related
Dec 7, 2007
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
Can anyone help me with this code. I am having a SCD (Stupid Coder Day) and can't seem to do anything that is scalable - like accounting for leap years (Feb issue) and the fact that Scenario # 2 above is between 2 different calender years.
Your help is much appreciated.
JJOSHI
View 3 Replies
View Related
Dec 11, 2007
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
View 4 Replies
View Related
Feb 22, 2008
I have a DB named zCIFRecord with a column named CIFUpdateDate which is of datatype varchar. The data is a date MM/DD/YYYY 01/30/2008, this is al that is in this column. I can search this colum for individual dates and for a range of dates. My problem is with a range of dates that is not within the same year, such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '12/01/2007' and '01/30/2008'
will return nothing because it seems to only search on the 12 then the 01 then the 2008. this search can be performed properly on dates within the same year such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '01/01/2008' and '01/30/2008'
will return the proper values because now all the numbers are in correct order. How can i create a search that lets me perform the first query as well as the second query. I tried to convert to a float but you cant convert a varchar to a float.
View 17 Replies
View Related
Feb 25, 2008
I am doing a search on a column of type varchar, the columns all contain dates in the future. When i perform a query
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
,[CIFExpDate]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE CAST([CIFExpDate] AS smalldatetime) between '12/01/2000' and '01/30/2015'
I get error:
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I can use this same query on an exactly same other field that contains dates in the past. Is there some kind of limitation on the dates as far as them being in the future or past?
View 4 Replies
View Related
Mar 2, 2006
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
View 3 Replies
View Related
Jul 18, 2014
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
View 9 Replies
View Related
Feb 28, 2015
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
View 7 Replies
View Related
Jun 21, 2005
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies
View Related
Nov 8, 2006
Hi,
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Any advice will be greatly appreciated!
View 1 Replies
View Related
Mar 4, 2008
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
View 13 Replies
View Related
Feb 28, 2008
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks
View 13 Replies
View Related
Apr 24, 2008
I am trying to find all the email addresses with a " ._" I use '%._%' but it returns all records. What is the correct syntax? Also, is there a way to search for a field where the underscore is followed by a single alpha letter and then another underscore? like bla_A_bla or bla_Z_bla.thanksMilton
SELECT DISTINCT fname, lname, inet
FROM ocadbo.notes
where inet like '%._%'
View 6 Replies
View Related
May 8, 2004
Dear ASP.NET
How can I find records that contain a STRING from some (more than one) other fields ?
for example, I have:
Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"
Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)
How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?
Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)
Thanks in advance, Yovav.
View 3 Replies
View Related
Feb 13, 2006
Let say a user wants to search for the name Joe Soap
I have two column's in my table, firstname and lastname
So if I do:
Code:
SELECT firstname, lastname FROM table WHERE firstname LIKE '%Joe Soap%' OR lastname LIKE '%Joe Soap%'
it returns nothing! So do I have to split the string Joe Soap or something ?
View 1 Replies
View Related
Nov 28, 2004
my app use a registration table
StudReg
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stRegDt smalldatetime,
stRegNo bigint,
courseId smallint
)
the application registers the student details to a course.
each student gets a new registration no during registration.
the app should identify repeaters to a particular course by checking another
table RegHistory, which stores the details of student registrations for the previous 5 years.
RegHistory
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stPrevRegDt smalldatetime,
stPrevRegNo bigint,
courseId smallint
)
the application must search the RegHistory table and list out those students who are the repeaters.
the sample entries in the two tables are as follows
StudReg
stName stDOB stGuardianName stRegDt stRegNo courseid
-------------------------------------------------------------------------
abc 01/01/1979 def 20/11/2004 12345 1
def 01/01/1976 xyz 20/11/2004 12346 1
... ..... ... ...... .... ...
mno 24/18/1976 pqr 20/11/2004 12400 1
RegHistory
stName stDOB stGuardianName stPrevRegDt stPrevRegNo courseId
abc 01/01/1979 def 20/11/2001 2345 1
ghi 01/01/1976 xyz 20/11/2001 2346 1
... ..... ... ...... .... ...
dfg 24/18/1976 pqr 20/11/2001 2400 1
to determine whether a student is a repeater or not, we have to search for an exact match in RegHistory table (where the student name, guardian name and date of birth in both tables match with the corresponding entries in Registration table).
here is my question,
if there are 100,000 students registering in each academic year, we will have
500,000 records in RegHistory Table and 100,000 records in studReg table
if i start searching for a repeater, i guess i will have to loop through all records in studReg, for an exact match in RegHistory, which wil be a time consuming process.
is there any other options to search for repeaters ?
pl discuss
View 11 Replies
View Related
Mar 16, 2004
hi all,
I need help in selecting records from a table based on the given search criteria.
spec:
select * from table where col1='x' and col2='y'... and col6='q'
i may give any combination of column values.
I mean I can't provide 6 values in 'where' condition all the time i submit query.
help me with a stored proc which has 6 input parameters for the 6 columns.
View 9 Replies
View Related
Dec 11, 2006
Maybe a dumb question.. but is there a way to search all tables in a database for a particular word or phrase?
View 4 Replies
View Related
Feb 18, 2008
I am searching for an add-in to ssms which lets me choose which server to deploy my code.
For example, I have a complete statement with CREATE PROCEDURE and all code in my query window.
Now I want to run/deploy this code to many servers at once.
Instead of having to reconnect to all server (5), I want an add-in which have checkboxes for me to select.
Anyone heard of this?
E 12°55'05.25"
N 56°04'39.16"
View 6 Replies
View Related
Jul 23, 2005
Hi,I have come to point in my db design where I'm trying to figure whichis the best approach in making it generic (does this really matter?!?)Senario - I have a table called JOBS and this table contains fieldsuch as JobTitle, JobDescription, Salary etcI want to add to this table other attributes which are specific to acertain Job Industries.Solution - Add a join table for each type of industry containingattributes (db is now not generic) OR add a new table with a IndustryType field and a XML field containing the industry specificattributes.If I go the XML way will this just make it complex and slow to query?If not, what is the best way to query an XML field?Thanks,Jack
View 2 Replies
View Related
Jul 23, 2005
I am having a problem with seaching my tables. On my asp.net page ihave 3 text boxes. One for an ID and NAME and ADDRESS. I want to beable to search a table by using all or any combination of the 3 tosearch. But i cant get it right. i am using c# and sql server.any help ideas would be a great helpthanks in advanceructions
View 1 Replies
View Related
May 26, 2008
How do we make a search in dbase where in the data you are searching has a " ' " for example Int'l Airport or other wildcards
Regards
View 4 Replies
View Related
Aug 31, 2006
i'm making a web page for a clinic.it needs to be able to search for patients by first name, surname, date of birth and patient number.i'm using visual web developer and i have my database, my data source and GridView grid.i want 4 text boxes for my first name, surname etc. when u click enter on any of them i want to retrieve all their data and display it in the gridview.at the moment i have one text box on the web page and through the "Configure data source" option on the grid view i can retrieve the specified data but for only this one item, e.g. SELECT * FROM [Patients] WHERE ([DOB] = @DOB). if i add another text box to my web page, and don't do anything to it, the query wont run. if i add and "AND" statement to the query, e.g. SELECT * FROM [Patients] WHERE (([DOB] = @DOB) AND ([FirstName] = @FirstName)), again it won'r run or return and data. any ideas on what i can do or where i'm going wrong. thanks
View 1 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
Jan 7, 2008
hello all..i have make a searching, but is not good. my code like that:Public Class getall Public Function getitem(ByVal id As String) As DataSet Dim con As SqlConnection = New SqlConnection("Data Source=BOYsqlexpress;Initial Catalog=GAMES;User ID=ha;Password=a") Dim ds As New DataSet() Dim adapter As New SqlDataAdapter("select * from [item] where name like '%" & id & "%'", con) Try con.Open() adapter.Fill(ds, "user") Return ds Catch ex As Exception Console.Write(ex.Message) Finally con.Close() con = Nothing End Try ' Next Return ds End Functionand class my item in database is containning dragon ball 3, counter strikeif i insert dragon, it can display dragon ball 3.but if i insert dragon 3, it not display dragon ball 3.it should display dragon ball 3 .how should i change my code?thx...
View 1 Replies
View Related
Apr 18, 2008
hello everyone
i need C# code in wh we sent "UserID" to table named "Users"
and if such "UserID" exists in "User" table we get his name from the table else we got message that no such record exists.
i have tried to write that code ,it works only if the value exists . in case of no value an exception is thrown.
please do sent me that c# code.
thanks for your consideration.
View 3 Replies
View Related
Dec 8, 2004
Hi All,
Just wanted to run this idea past u all before I have a go at it.
I have two tables A & B that are similar to the below
Table A
Name1 Name2 Name3
Tom Bill John
Gary Harry Eric
TableB
Name1 Name2 Name3
John Bill Tom
Tom Eric john
Leslie Philip Colin
What I wanted to do is see if the the records from tableA row 1 exist in tableB
As you can see they can appear in any order, partially or not at all.
What I propose to do is take tableA Name1 and see if it matches tableb row1 name 1 OR name 2 OR name 3 and if I find a match use a variable to assign the the value 1 (so I can then see if the match is full (score three) partial socre 1 and two or not at all (0)
Then I will need do the same for tableA row 2
Then goto row 2 of table a and start again.
Does this make sense? Are there beter ways of doing this.
I am using SQL server to do the searching....?
All comments appreciated and welcome.
rgs
Tonuy
View 4 Replies
View Related
May 25, 2005
hi i am working on sql server200.I m using "LIKE" to search the records.There is freetexttable and containstable table also.just like to know the difference between them.Could anyone provide me a good link regarding this??Thanks
View 3 Replies
View Related
Jun 29, 2005
Hi again!
I have a products table with product attributes in a second table,
together they describe a full product. I have a product title, a
list of providers, description text, and keywords. I would like
to do a search across these fields, and so far my research has shown
that the Full Text Search component of SQL Server is the way to
go. However, I am not sure this will be possible based on what is
installed on the hosted server, so I am wondering if there is a unique,
cool way of doing this without Full Text Search?
Thanks,
jr.
View 5 Replies
View Related
Oct 11, 2005
i currently have a function and a storedpro in my sql database they are:CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50))
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, 3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+ 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+ 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+ 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+ 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description) END * CASE WHEN @Word3 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description) END * CASE WHEN @Word5 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) END AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, RankFROM #SearchedProductsWHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1 AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN
/* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0
/* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word)
/* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ')
/* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase)
/* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT
SELECT @Matches = 0
WHILE LEN(@Phrase)>0 BEGIN SELECT @NextSpacePos = CHARINDEX(' ', @Phrase) IF @NextSpacePos = 0 BEGIN SELECT @ExtractedWord = @Phrase SELECT @Phrase='' END ELSE BEGIN SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1) SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos) END
IF @SoundexWord = SOUNDEX(@ExtractedWord) SELECT @Matches = @Matches + 1 END
/* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?
View 2 Replies
View Related
Nov 8, 2005
I want to search all of the employeeid #'s in the employee table for any mis-typed information. so if they put a . in the string i want to return that employee's info. as well as any letter a - z. I need to do this in sql and return it to a dataset for my crystal report. any ideas?
View 7 Replies
View Related
Dec 15, 2005
Hello,How do I search through the gridview? Would I do this at the sqldatasourcelevel?I figured that I sould search with the datatable.select but how do I accessthe datatable of the sqldatasource?I am using ASP.NET 2.0 with VB.Thanks for your helpJ
View 2 Replies
View Related
