I have a field that contains data like "mary;john;dog;cat;frog" I want to query the field for the values between each of the semi colins. I was going to use Substring with a patIndex but I can only patIndex the first semi colin and not the 2nd 3rd.... I was wondering if someone could tell me the right was of doing this or point me in the right direction.
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
I need to find rows from the above table which has the first occurence of the corresponding ID. This the output will contain the rows that will have first occurence of the following ids.
1067381, 1087668, 109754, 1103377.
How does one handle this. I have not been able to come up with the sql yet.
In the table you can create below, each set of order number records should contain one record with the OrderNoIndicator field set to 1. i.e. OrderNo 7032357. Is there a way of finding(without using the count function) sets of records that don't contain an OrderNoIndicator set to 1 i.e. OrderNo 7088650 all the OrderNoIndicator fields are set to 2...
use LEIS If OBJECT_ID ('dbo.temp') IS NOT NULL DROP TABLE dbo.temp GO CREATE TABLE dbo.temp ( OrderNo bigint not null, OrderNoindicator tinyint not null
Hi,In the datawarehouse DB (under MS commerce server 2002) a table storesthe referer domain name. Table structure is likerefererdomainid <binary>,domainInternalFlag<0/1>,refererDomainName<varchar>e.g.<binary>|0|unknown<binary>|1|google.com<binary>|1|yahoo.com<binary>|1|google.com<binary>|1|google.com<binary>|1|google.com<binary>|1|altavista.commy problem is to build a query (using this table only) whichrefererDomainName has the max occurrence and how many times. As in thetable above it is google.com and 4 times.Can anyone help me.Thanks in advance.
I have the following query: SELECT Notifications.[TimeStamp]) FROM dbo.vwGrantsMaster LEFT OUTER JOIN dbo.CoFundNotifications ON dbo.vwGrantsMaster.GrantFMBId = dbo.CoFundNotifications.GrantsFMBId It is returning multiple records from Notifications table since it allows multiple entries under a single GrantFMBId. For example for a single GrantFMBId there can be multiple TimeStamp. When retrieved all are appearing even distinct key word is used. What I am looking is that to get only the first occurrence of a record for a GrantFMBId from Notifications table. Any help? Thank you in advance.
I have text column .I want to find out first occurance of string based on logic.I defiend Text with examples and also mentioned expected result.I coloured the text in word document,due to some reasons not displaying same here.Attached as image below texts to understand more clear.
TEXT 1: 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'
From the above text1, I want to get “AXNARENDR”.
Based on logic defined below:
First I have to search for string “A” Then next to ‘A’ it should not be “B” or “C” or “D”.It can be anything other thing these three.Combination of “A” otherthan “B” or “C” or “D”
In the example text I defined “A”,”X” defined three times .I want to capture few characters from the first occurrence of the string
I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.
What function(s) can be used to find the mode of data? I have a column that is populated with codes and I'd like to summarize the data by the code that occurs the most frequently. Any help is appreciated!!
Hi,I am trying to join two tables, one has multiple records per ID, theother is unique record per ID.The two tables look like belowAID date var1 var 2001 1/1 10 20001 2/1 12 15001 3/1 17 18002 2/1 13 10002 3/1 12 14............BID001002003004....The join conditions are1. table A's ID = table B's ID2. take the variables associated with the 1st occrrence of the datevariable in table A'sI have the following SQL code but the it didn't work. It says thecolumns are ambiguously defined. Anyone can help me? Greatly appreciateit!PROC SQL;CONNECT TO ORACLE (USER="&user" PASS="&pass" PATH="@POWH17"BUFFSIZE=25000);CREATE TABLE actvy1_1st AS SELECT * FROM CONNECTION TO ORACLE(SELECTactvy1.ID,actvy1.var1,actvy1.var2,actvy1.datefromA actvy1,(select a.ID,min(a.date) mindatefrom A a,B cwhere a.ID =c.IDgroup by ID) bwhere actvy1.ID =b.IDand actvy1.date =b.mindateorder by ID);disconnect from oracle;quit;
I have [income]- (REVENUE) table of accounts, each account can have multiple instances for one month. how can I find the last occurrence of all the accounts in a month (and then summarize all of these latest occurrences)?
I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217' SELECT CONVERT(DATE, CAST(DATEPART(YEAR, IIF( --If we're at the start of a year --we'll need to go back a year DATEPART(MONTH, @Date) IN (1,2,3), DATEADD(YEAR, - 1, @Date), @Date )) AS VARCHAR(4)) + '0401')
I'm trying to find if the Saturdays worked in Bi-Weekly period is first or second, based on the Saturday occurrence the CODEID column value changes from 01 to another #, for example if it's first Saturday then CODEID changes from 01 to 02 and it's second Saturday then the CODEID changes from 01 to 03.
Below is my table, current result and desired results.
My Table:
WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS ( SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014' UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014' UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014' UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014' UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014' UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014' UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014' UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014' UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014' ) SELECT * FROM SampleData
I have a problem creating a SELECT query which involves getting the FIRST occurrence, the SUMS and the LAST occurrence of a selected row. I have two tables A and B which has the following data:
Table A
---------------------------------------------------------------- |Item ID | Item Name | Item Description | Current Quantity | ---------------------------------------------------------------- ----1------------Bolts-------------35"----------------20 ----2-----------Paint----------GOLD Paint------------30 ----3-----------Screws------------30"----------------40 ----------------------------------------------------------------
and Table B
-------------------------------------------------------------- |Log ID | Item ID | Beginning | Add | Less | Ending | Date | -------------------------------------------------------------- ---1---------1----------30-------0-----10------20------04-04 ---2---------2----------40-------10----10------40------04-04 ---3---------3----------50-------5-----10------45------04-04 ---4---------1----------20-------0-----0-------20------04-05 ---5---------2----------40-------0-----10------30------04-04 ---6---------3----------45-------0-----5-------40------04-04 -------------------------------------------------------------
and I have two datetime pickers, one FROM date picker and a TO date picker If a user selects FROM 04-04 TO 04-05...I want a select statement which will select the Item Name and Desc, the BEGINNING QUANTITY (in reference to the FROM DATE), the SUM of Adds and Less and the ENDING QUANTITY (in reference to the To Date) and then group by their Item ID
In this case, it will generate a table like this
---------------------------------------------------------- Item Name | Item Desc | Beginning | Add | Less | Ending | ----------------------------------------------------------- --Bolts---------35"---------30---------0-----10-----20 --Paint-----Gold Paint------40---------10----20-----30 --Screws-------30"---------50---------5-----15-----40
"SELECT A.Item Name, A,Item Desc, B.Beginning=(select B.Beginning from Table B where A.Item ID = B.Item ID AND Date = From Date), SUM(B.ADD), SUM(B.LESS), B.Ending=(select B.Ending from Table B where A.Item ID = B.Item ID and Date = To Date) WHERE A.Item ID = B.Item ID and Date(B.Date)>From Date AND Date(B.Date)< To Date group by B.Item ID order by A.Item Name, Item Desc"
My query returns the right Item Name, Desc, the Sum of Add and Less.My problem is that I can't get the value of my beginning and ending.I think I have a problem in A.Item ID = B.Item ID. As I tried a fixed value condition, (B.Item ID = 2), it will get the right result), I'm guessing the program is not getting A.Item ID.
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name. CREATE TABLE Names (FirstName NVARCHAR (50), LastName NVARCHAR (50)); I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character. I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding. I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View. I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters. Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also? Thanks
int codePage = 950; StringBuilder message = new StringBuilder(); Encoding targetEncoding = Encoding.GetEncoding(codePage); byte[] encodedChars= targetEncoding.GetBytes(str); . message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':"); for (int i = 0; i < encodedChars.Length; i++) { message.Append("Byte " + i + ": " + encodedChars); }
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars)); Console.Writeline(message.ToString());
See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.
DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))
INSERT INTO @t SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL
Hello, I use the full-text search utility in SQL Server 2005 to find word in PDFs document. This is my 'Documents' table:
id (PK), data (VarBinary(max)), extension (nvarchar(4))
My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.
SELECT * FROM Documents WHERE freetext([data], 'Microsoft'); 1 , 0x255044...., .pdf
But I need to know how many times 'Microsoft' word appears in this document. Do you have any idea how can I retrieve this information? Thanks in advance!
Hello, I use the full-text search utility in SQL Server 2005 to find word in PDFs document. This is my 'Documents' table: id (PK), data (VarBinary(max)), extension (nvarchar(4)) My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.
SELECT * FROM Documents WHERE freetext([data], 'Microsoft'); 1 | 0x255044.... | .pdf
But I need to know how many times 'Microsoft' word appears in this document. Do you have any idea how can I retrieve this information? Thanks in advance!
With mstrTable(ItemNo, Sales) as ( Query1 Union All Query2 ) Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales From mstrTable order by ItemNo
The results from Query1 and Query2 overlap sometimes.
I only want the first occurrence of each item. The desired result set is:
1 Item1 10000 1 Item2 20000 1 Item3 30000
I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".How would I obtain a record set that returns just the first occurrence of the ItemNo field?
I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.
SELECT TOP 1000 o.[ID] ,o.[TimeOfOrder] ,x.[StreeLine1] FROM [SC].[dbo].[bvc_Order] o FULL JOIN SC.dbo.xmlAddressRead x ON o.ID= x.id WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48 GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder HAVING COUNT(x.[StreeLine1])>1
Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?
SELECT TOP 1000 o.[ID] ,o.[TimeOfOrder] ,x.[StreeLine1] FROM [SC].[dbo].[bvc_Order] o FULL JOIN SC.dbo.xmlAddressRead x ON o.ID= x.id WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48 AND x.[StreeLine1]='599 Ships Landing Way'