having some issues trying to create a query in excel 2013. I can get the data I want from sql, but I get individual transactions and I want to sum them by plu number. here is my query, I tried using group by but every time I add the field, I get an error that some other field is invalid because it's not contained in an aggregate or group by clause. btw, I didn't name these fields.
SELECT RPT_ITM_D.F254 as [Date], RPT_ITM_D.F01 As [PLU], RPT_ITM_D.F64 As [Qty Sold], RPT_ITM_D.F65 As [SOLD], OBJ_TAB.F17 As [RCode], OBJ_TAB.F29 As [Description], PRICE_TAB.F30 As [EL Price], PRICE_TAB.F31 As [Qty]
FROM STORESQL.dbo.OBJ_TAB OBJ_TAB, STORESQL.dbo.PRICE_TAB PRICE_TAB, STORESQL.dbo.RPT_ITM_D RPT_ITM_D
WHERE OBJ_TAB.F01 = RPT_ITM_D.F01 AND OBJ_TAB.F01 = PRICE_TAB.F01 AND PRICE_TAB.F01 = RPT_ITM_D.F01 AND ((RPT_ITM_D.F254>=? And RPT_ITM_D.F254<=? )AND (OBJ_TAB.F17=25))
I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.
How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)
First name (exact match) Last name (exact match) Address line 1 (exact match) Postal code/zip (exact match)
First Initial (exact match) Last name (exact match) DOB exact match Postal code/zip (exact match)
I have to search the records after the records populated.
I mean to say, i have displayed records in report, if i enter some strings in the textbox and clicked find, then it will highlight the particular records, instead of highlighting the values, is it possible to display only those particular records.
For example, say i have 50 records in a page,i entered some strings in the textbox and clicked find, then it will highlight the particular 5 records one by one which match the criteria i have entered in the texbox, instead of that i have to display only those 5 records.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
Hi !! We are doing a project where we have a table with 800,000 records. We need to implement a search on this 800,000 records. How do we do that? Assue State = NJ Criteria: Select County, Select City, Enter Zip Code, First Name and Last Name This should bring up Information about the person. How can we implement it efficiently ? Thanks
Hello I am trying to create a stored procedure where the users can either type in the last name, or a wild card in order to get the values they are looking for. Sometimes they are not sure how to spell the last name and they will type in the first 2 letters or an * for all to see if they can find it that way. How can I do this??
Here is what I have so far
Code Block ALTER PROCEDURE [dbo].[AdvSearchRevocations] (@Enter_LastName nvarchar(25)) AS SET @Enter_LastName = REPLACE(@Enter_Lastname, '*', '%') SELECT LastName, FirstName, ReasonOfRevocation, TM#, [I/R #], Date FROM dbo.Revocations_Tbl
I have to create a search textbox where if i key in the word "dog",it will search against a field called "Name" in a table.Then it will return all records where the text/data in the "Name" field contains the word "dog".For example,it will return the records where the text/data in the "Name" field has words such as "hotdog","doggie","dog barking","big dog" etc etc.
Can i use simple SQL for this or do i need to configure the full text search service on SQL server 2000 and use the FREETEXT predicate?Thank you in advance for any replies.
Just like Unique/Distinct command, is these some way I could list just the duplicate records from a table . The field is numeric. Thanks a lot for you help.
I'm just learning to join tables and I'm trying to construct a query to tell me the following:
Table names and related columns: Table1.HdrSys Table2.HdrSysNum and HdrSys Table3.HdrSysNum
Table3 contains the detail info for Table2. Table1 is the header table for Table2. So.. If I want all records in Table3 that don't have a related record in Table1, is this even possible?
I started with: select * from Table3 a join Table2 b on a.HdrSysNum = b.HdrSysNum join Table1 c on b.HdrSys= c.HdrSys -- Now.. how do I qualify the statement.. or can I with just this. Thx! Hope I wasn't too confusing.. because I tend to get that way when I'm confused!
Hey folks...So I have a table that looks like this:CREATE TABLE [tblStation] ([CAMPAIGN] [varchar] (8),[LISTNUM] [varchar] (10),[PHONE] [varchar] (10),[EVENTTIME] [datetime] ,[STATION] [int],[OPERATOR] [varchar] (16),[EVENTCODE] [varchar],[CALLSPAN] [decimal](18, 0),[FDISP] [int],[RECORDNUM] [varchar],[STC] [varchar],[PROMOC] [varchar],[EXP_CAMP] [varchar],[PROMO3] [varchar],[MAXATT] [char],[LISTNAME] [varchar],[SITENAME] [char],[Row_id] [int] IDENTITYIt's taking nine seconds to run the following command:SELECT count([fdisp])FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)WHERE fdisp IS NULLAnyone familiar with a table of this size having performance likethis? The [fdisp] column has a non clustered index on it.Thanks in advance...
Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny.
I have a table full of items which can be searched. I also have another table with the ID of each item and columns for no of times details shown, no of times saved etc.
What I would like to do is increment a value in this second table for each item every time it is returned in a search.
I have a form with a dropdown or combo box, the user can select <All>or pick a user name. If they pick a user name my where clause worksfine, buts what's the best way to write "Select All" if they choosethe <All>This is what I have so far, but I don't think I should be using theLIKE operator.WHERE tblCase.qarep LIKE CASE @myqarep WHEN '<All>' THEN '%' ELSE@myqarep ENDand tblOffice.officecode LIKE CASE @myoffice WHEN -1 THEN '%' ELSE@myoffice ENDthanks for your help!!
Hello all, I am making a query which will select those records from table where a column data ends with a particular extension,i.e. I want the query to fetch the rocords where FileName(column name) ends with .JPEG or .GIF or .BMP. I have five records in table and FileName(columns name) contains three .JPEG ,one .GIF & one .BMP files name. How can i get for a particular extension? Thanks in advance
I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric or non-printable characters. For instance, if I have a table with a 20 character name column with names with beginning ranges from A-Z, why doesn't the following return any rows: select * from table where name < CHAR(126). In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?
I'm assuming this has something to do with the default collation sequence being used which somehow does not include characters outside the alphanumeric range. Any ideas ?
I have a new client with an existing system that has just over 2 million business listings in one table. Each business listing is associated with one business category.
* Company Table (around 20 fields):
companyID companyName categoryID state postCode etc.
* Category Table (5 fields)
categoryID categoryName etc.
We are using MSSQL 2005 Express Edition with Advanced Services
A free text search needs to be performed on the companyName and categoryName limited by region (state and or postcode).
1) What kind of response times should I expect for the free text search (I have not used the free text search before)
2) How should I index the companyName and categoryName so they are both used in a joined query? i.e. Do I just configure the free text search index on each field separately and it should work?
I have one table with 300,000 records and 30 columns.For example columns are ID, COMPANY, PhONE, NOTES ...ID - nvarchar lenth-9COMPANY - nvarchar lenth-30NOTES - nvarchar length-250Select * from databasewhere NOTES like '%something%'Is there a way to get results from this query in less then 1-2 secondand how?
Hi everyone,My company has a website, use ms sql server. One table has more than 1000000 records. When users search data from this table(such as search records which contain the word "school" in NewsTile field.And the server often occurred deadlock error.How can I improve it?Thanks.P.S. The table has these fields:NewsIDNewsTitleNewsContentNewsClickTimesNewsInsertTime
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for? 2) CTE doesn't work on SQL 2000. I need a query that does. 3) I cannot modify the database itself in the process.
;WITH cte AS ( SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number() OVER(PARTITION BY [LogProcessName] ORDER BY [LogDate] DESC) AS RN FROM [LOGTABLE] WHERE [LogTypeGUID] IN ( SELECT LogTypeGUID FROM LOGTYPE WHERE LogTypeName = 'Process Status Start' OR LogTypeName = 'Process Status End' ) ) SELECT * FROM cte WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6 ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
I am having hard time figuring out what query I need to write.
What I want to do is, when users enter a word into a textbox, it displays results similar to it. Something similar to what Google does.
This is what I am doing now. For example I will use "John Smith"
1. I get the first 3 letters of the string "Joh" 2. Get the last letter of the string "h" 3. and this is what I search for "Joh%h%"
This works, but I still have some problems. I want to be able to search like Google, where it recognizes words. Does anybody know how I can do this, or send me a link with an example?
I would like to write query which can1. ignore whether the search keyword is Upper case or lower Case2. deal with tense or verb form related problem eg. comparing --> also search compareAny Idea
hi all I have the follwoing search page: several text boxes in case they want to search for SSN, first name, last name, zipcode, several dorpdown lists, and a set of radio buttons. I want to create the SQL statement to display the results I am checking if any of the text boxes has anything in it, if yes add "where something = value" to the query, if not then skip... now let's say it goes through textbox 1 and finds it empty, so it jumps to the second one it find info in it, how do I deal with the "and" clause within the query? any tips are fine.. (im using c#) thanks
I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox. I want the results to only return results that begin with the letter/letters specified. Below I will put the code that I came up with that isn't working quite how I expected. I am new to this so any assistance and short explanation would be very much apperciated. sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 " sql=sql & "FROM cmmst cm " sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC" companyNameBegins is the string passed in by the user
I am having a difficult time figuring out the differences between the following 2 queries. The first query ( which should return around 8000 records) when run against a Sql7 DB with 70000 records will always timeout (timeout set at 30 secs), but the 2nd query will return the correct amount of records ( about 8000 ) in under 8 secs.
Could someone shed some light on this for me?
Thank you Robert
******Start Query 1****** Select Lot, Title, UserId, HighBidder, HighBid, BidTimes, DateIn, EndDate, Price, Category, Options, Reserve, StartDate From Auctions A Where ( ((title LIKE '%STUB%' )) or ((description LIKE '%STUB%' )) ) And (Status In ('A', 'P') Or (Status In ('B', 'C') And EndDate >= '02/25/2001') ) And ((A.Reserve > A.Price And A.Reserve Between 0 And 999999.99) Or (A.Price Between 0 And 999999.99)) Order By Lot Asc
*****End Query 1*****
******Start Query 2****** Select Lot, Title, UserId, HighBidder, HighBid, BidTimes, DateIn, EndDate, Price, Category, Options, Reserve, StartDate From (Select Lot, Title, UserId, HighBidder, HighBid, BidTimes, DateIn, EndDate, Price, Category, Options, Reserve, StartDate,Description From Auctions A Where (A.Status In ('A', 'P')
Or (A.Status In ('B', 'C') And A.EndDate >= '02/25/2001')
) And ((A.Reserve > A.Price And A.Reserve Between 0 And 999999.99) Or (A.Price Between 0 And 999999.99)) ) as X Where (
I'm building a serch page on website. It basicly searches 2 tables. Right now, the way I have it so the user can enter a value in one of the fields, and not the others. And it will return the value. For example, put in '34' in the age field, it will return all people with 34. Now if you add 'Jones' to the last name field, it will return all people with the age of 34, and all people with Jones.
But what if I want it so it returns only the people with the age 34, and Jones. But leave the other fields blank. The later than add 'Jim' to the first name, so now it will return 34, jones, jim. I tried to use the AND operator, but then it requires that field to be filled. Here is my code.
ALTER PROCEDURE [dbo].[SrchActiveII]
@FName nvarchar(50)=NULL, @LName nvarchar(50)=NULL, @DOB nvarchar(50)=NULL, @Acct nvarchar(50)=NULL, @Login nvarchar(50)=NULL, @Status nvarchar(50)=NULL, @Rmark nvarchar(255)=NULL, @Room nvarchar(50)=NULL, @Age nvarchar(50)=NULL, @Type nvarchar(50)=NULL, @Misc bit = NULL AS BEGIN SELECT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat, Active_Orders.Order_ID, Active_Orders.Login, Active_Orders.Misc FROM Active_Orders INNER JOIN Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN Locations ON Active_Orders.Location_ID = Locations.Location_ID WHERE (Active_Orders.First_Name =@FName) OR (First_Name IS NULL) OR (Active_Orders.Last_Name =@Lname) OR (Last_Name IS NULL) OR (Active_Orders.DOB = @DOB) OR (DOB IS NULL) OR (Active_Orders.Account_Number = @Acct) OR (Account_Number IS NULL) OR (Order_Status.Status = @Status) OR (Status IS NULL) OR (Active_Orders.Remarks = @Rmark) OR (Remarks IS NULL) OR (Active_Orders.Rm_Desc = @Room) OR (Rm_Desc IS NULL) OR (Active_Orders.Age = @Age) OR (Age IS NULL) OR (Active_Orders.Type = @Type) OR ([Type] IS NULL) OR (Active_Orders.Login = @Login) OR ([Login] IS NULL) OR (Active_Orders.Misc = @Misc) OR (Misc IS NULL) END
I am making a search query for jobs site. I have a situation that if user type following string in my search box:
Web Developer Designer ISF New York
It should display all jobs conatins any one of given words. following are columns in my jobs table: (Query will be applied on Location, Title, Description, Company fields)
[OpporID] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL , [OpportunityID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [OpportunityTypeID] [numeric](10, 0) NOT NULL , [SLABased] [int] NOT NULL , [LoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateCreated] [datetime] NOT NULL , [AccountID] [int] NOT NULL , [GeographyID] [int] NOT NULL , [VerticalID] [int] NOT NULL , [BDMID] [int] NOT NULL , [Probability] [int] NOT NULL , [PASStatus] [int] NULL , [InsertedDate] [datetime] NULL , [InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedDate] [datetime] NULL , [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedFlag] [int] NULL
and SKILL [SkillNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [OpportunityID] [numeric](18, 0) NOT NULL , [OrderId] [numeric](18, 0) NOT NULL , [PracticeID] [int] NULL , [SkillID] [int] NOT NULL , [NoOfPeople] [int] NOT NULL , [Clientinterview] [int] NOT NULL , [Location] [int] NOT NULL , [JDAttached] [int] NOT NULL , [JDFilePath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Status] [int] NULL , [Experience] [int] NULL , [InsertedDate] [datetime] NULL , [InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [UpdatedDate] [datetime] NULL , [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedFlag] [int] NULL , [GeoLocation] [int] NULL )
i want to make a stored procedure for custom search on these two tables with the following fields given to the user as an option to make his choice.. from opportunity table - OpportunityTypeID,SLABased,AccountID , GeographyID, VerticalID, BDMID, Probability
and from skill table SkillID, Location, GeoLocation
Hi!In short description I want to make table with Articles and search Query for this table like there is search engine at templatemonster.com with templates and categories. Basicly I have table:- id - cat_name- 1 - Software- 2 - Hardware- 3 - Games- 4 - Internet- 5 - Events etc.And for example Article is in Software, Games and Events category, "1,3,5".Now user select to show articles in Games and Events categories, so "3,4,5"How write this search query? I don't have idea.
Hi, I'm wondering about the following: I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I havereviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're foundinside some DLL. I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere. So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)? Thanks in advance for your advise. Stewart
