my problem is to build a query (using this table only) which
refererDomainName has the max occurrence and how many times. As in the
table above it is google.com and 4 times.
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.
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?
hi all, I have a table productprice which has the following feildsid price datecreated productname 1 12.00 13/05/2007 a1 2 23.00 14/05/2007 a13 24.00 15/05/2007 a14 56.00 13/05/2007 b15 34.00 18/05/2007 b16 23.00 21/05/2007 b17 11.00 12/02/2007 c1 8 78.00 12/03/2007 c2I need to select the rows that are highlighted here.. ie the row that has the max(datecreated) for all the productname in the table.. plz help thanks in advance..
Hi there, very sorry if this is the wrong forum to post this in.
I want to know what is the BEST practice, the correct Microsoft way of doing this:
basically, lets say I am inserting a new record into SQL. simple customer record:
firstname
lastname
address
city
postcode
password
dateOfRegistration (SQL has this value and the default value is the getdate())
That's all very well. I want to know how I can get the recordID for this and return that back from the caller (returning is easily done) -
You cannot really after this insertion, perform a SELECT statement to get the LAST record entered, as there maybe several records that could all be inserted at the same time by coincidence. It's not the best way of going about this.
I want to know what is the best way of getting the just inserted record's recordID - I was thinking about using date and time, manually inputting them and then using that to retrieve the last record/current inserting record but again its not the best way of going about doing this.
The following select retrieves multiple reoords for each i.number. How can I select just the first record for each i.number?
SELECT i.number, i.desc, i.it_sdate, v.entry_date FROM itemsnum as I INNER JOIN Inventor as V ON SUBSTR(i.number,1,5)=v.catalog WHERE v.entry_date<ctod("04/01/06") AND i.it_sdate < ctod("04/01/06") order by number, it_sdate desc
if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.
I have some VB.NET code to retrieve data from an SQL Server database and display it. The code is as follows: ------------------------------------------------------------------------------------------------------- sw_calendar = calendarAdapter.GetEventByID(cid) If sw_calendar.Rows.Count > 0 Then
lblStartDateText.Text = "*** Not Found ***" lblEndDateText.Text = "*** Not Found ***" lblTitleText.Text = "*** Not Found ***" lblLocationText.Text = "*** Not Found ***" lblDescriptionText.Text = "*** Not Found ***" End If ------------------------------------------------------------------------------------------------------- If all of the fields in the database has values, everything works ok. However, if the title, location or description fields have a null value, I receive the following error message: Unable to cast object of type 'System.DBNull' to type 'System.String'. I've tried a bunch of different things such as:
Adding ".ToString" to the database field, Seeing if the value is null: If sw_calendar(0).description = system.DBnull.value... ...but either I get syntax errors in the code, or if the syntax is ok, I still get the above error message. Can anyone help me with the code required to trap the null within the code example I've provided? I'm sure there are other, and better, ways to code this, but for now I'd really like to get it working as is, and then optimize the code once the application is working (...can you tell I have a tight deadline )
I am having trouble returning the correct record with my stored procedure. my problem is that i don't know how to structure the sql statement to do the following: given a set of records that have the same loankey, i need to 1. find the record that has most recent date (lockExprDt) 2. for all records with that date, find the highest Lock Number (LockNo) 3. for the all the records with that date and that LockNo, find the highest extension number (Ext) currently my sql statement returns a record that has the most recent date. i don't now how to write the sql to further define my query to return the record that has the most recent date with highest lock number, and finally the highest extension number. any suggestions as to what i am doing wrong. below is my slq statement. please note that i need to add the sql that will query for the max LockNo, and max Ext. Any help is greatly appreciated. thx! select a.loankey, a.lockrate, a.investor, a.price, a.ext, a.cost, a.lockno, a.lockstatus , CASE WHEN CONVERT(CHAR(8),a.lockdate,10)='12:00:00 AM' THEN NULL ELSE CONVERT(CHAR(8),a.lockdate,10) END as 'LockDate' , CASE WHEN CONVERT(CHAR(8),b.lockExprDt,10)='12:00:00 AM' THEN NULL ELSE CONVERT(CHAR(8),b.lockExprDt,10) END as 'LockExprDt' , Case WHEN CONVERT(CHAR(8),b.lockExprDt,10)>= CONVERT(CHAR(8),GETDATE(),10) THEN datediff(day, CONVERT(CHAR(8),GETDATE(),10), CONVERT(CHAR(8),b.lockExprDt,10)) ELSE NULL END as 'Days' from cfcdb..locktable ainner join (select loankey, max(lockExprDt) as lockExprDtfrom cfcdb..locktablegroup by loankey) bON a.loankey = b.loankey AND a.lockExprDt = b.lockExprDt where a.loankey = @LoanKey
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.
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.
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
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.
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!
