How To Get Each Number Of Records...
HI EVERYBODY
This is my procedure
"
CREATE PROCEDURE SP_SAMPLE_SEARCH
@Title nvarchar(256)
AS
SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc
GO
"
I exec procedure and it returns 12 results with diffirents ID
and want to get these values 1,2,3....12
How do I get these...
I am a beginner.
Thanks for help..
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Number Of Affected Records
Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005 --i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here? -i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how? cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int)); cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue; Please help me out with this.Thanks
View Replies !
View Related
How Can I Now The RecordSource Number Of Records?
Hi... I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0... cheers.
View Replies !
View Related
Getting Number Of Records Modified
Hi all My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how? please some one guide me how to do that? Your help is highly appreciated
View Replies !
View Related
Total Number Of Records
Hi everbody, I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
View Replies !
View Related
Total Number Of Records
Hi everbody, I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
View Replies !
View Related
How Do I Put Records Number In Union
Hi i have sql statement like this : SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_a UNION SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_b the problem is the rec_num repeat for each statement like this : rec_num empname 1 john 2 maggy 1 lee 2 mary 3 louis How do i make the rec_num continue for the next statement after union.
View Replies !
View Related
Sequence Number For Records
Hi... I have Sql statement more like this SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a UNION SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a rec_num employeeID employeeName employeeDepartment 1 777 Mike HR 2 888 Susy HR 1 111 Smith TECH 2 222 John TECH 3 333 Lenny TECH How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this : rec_num employeeID employeeName employeeDepartment 1 777 Mike HR 2 888 Susy HR 3 111 Smith TECH 4 222 John TECH 5 333 Lenny TECH
View Replies !
View Related
Restricting Number Of Records
I want to restrict the number of records coming from an OLEDB source. I have 500 records in my source table and I want to process one record at a time I have I set the MaxBufferRows parameter to 1and it l sends 8 records from OLEDB source Any help is appreciated.
View Replies !
View Related
Getting The Number Of Records With Like Values
I have a resultset that looks something like this: Anzahl users_statdata_hobbies --------------------- 499 Andere 266 Essen 60 Essen,Andere 127 Essen,Musik 10 Essen,Musik,Party,Andere 30 Essen,Party 4 Essen,Party,Andere 51 Kunst 4 Kunst,Andere 13 Kunst,Essen 4 Kunst,Essen,Andere I get this with this query which might be altered somehow: SELECT COUNT(*) AS Anzahl, users_statdata_hobbies FROM vgetAuswertung2 GROUP BY users_statdata_hobbies ORDER BY users_statdata_hobbies Of course this is not normalized but I can't change this. Nevertheless I need to get the full number of each Hobby and not only the combination of them. So instead or in addition to the existing recordset I need e.g 357 Essen which ist the sum of all records containing 'Essen' in the above example The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly. Any ideas someone?
View Replies !
View Related
DISTINCT And Number Of Records
Wondering if there is a way to do this... I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found.. is there any way to do this with SQL? SELECT DISTINCT user_Name FROM table ORDER BY user_Name is what I have now.... need something that will return: user_Name numRecs bob 3 fred 6 sam 1 linda 2
View Replies !
View Related
Sum Up An Unknown Number Of Records
With this algorithm you can sum up an unkown number of records, so that an aggregation matches a fixed value. If there is not an exakt match available, the algorithm returns the nearest possible value!-- Initialize the search parameter DECLARE@WantedValue INT SET@WantedValue = 349 -- Stage the source data DECLARE@Data TABLE ( RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, MaxItems INT, CurrentItems INT DEFAULT 0, FaceValue INT, BestUnder INT DEFAULT 0, BestOver INT DEFAULT 1 ) -- Aggregate the source data INSERT@Data ( MaxItems, FaceValue ) SELECTCOUNT(*), Qty FROM( SELECT 899 AS Qty UNION ALL SELECT 100 UNION ALL SELECT 95 UNION ALL SELECT 50 UNION ALL SELECT 55 UNION ALL SELECT 40 UNION ALL SELECT 5 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 250 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 90 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 350 UNION ALL SELECT 450 UNION ALL SELECT 450 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 1 UNION ALL SELECT 10 UNION ALL SELECT 1 ) AS d GROUP BYQty ORDER BYQty DESC -- Declare some control variables DECLARE@CurrentSum INT, @BestUnder INT, @BestOver INT, @RecID INT -- If productsum is less than or equal to the wanted sum, select all items! IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) <= @WantedValue BEGIN SELECTMaxItems AS Items, FaceValue FROM@Data RETURN END -- Delete all unworkable FaceValues DELETE FROM@Data WHEREFaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue) -- Update MaxItems to a proper value UPDATE@Data SETMaxItems =CASE WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue ELSE MaxItems END -- Update BestOver to a proper value UPDATE@Data SETBestOver = MaxItems -- Initialize the control mechanism SELECT@RecID = MIN(RecID), @BestUnder = 0, @BestOver = SUM(BestOver * FaceValue) FROM@Data -- Do the loop! WHILE @RecID IS NOT NULL BEGIN -- Reset all "bits" not incremented UPDATE@Data SETCurrentItems = 0 WHERERecID < @RecID -- Increment the current "bit" UPDATE@Data SETCurrentItems = CurrentItems + 1 WHERERecID = @RecID -- Get the current sum SELECT@CurrentSum = SUM(CurrentItems * FaceValue) FROM@Data WHERECurrentItems > 0 -- Stop here if the current sum is equal to the sum we want IF @CurrentSum = @WantedValue BREAK ELSE -- Update the current BestUnder if previous BestUnder is less IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue BEGIN UPDATE@Data SETBestUnder = CurrentItems SET@BestUnder = @CurrentSum END ELSE -- Update the current BestOver if previous BestOver is more IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver BEGIN UPDATE@Data SETBestOver = CurrentItems SET@BestOver = @CurrentSum END -- Find the next proper "bit" to increment SELECT@RecID = MIN(RecID) FROM@Data WHERECurrentItems < MaxItems END -- Now we have to investigate which type of sum to return IF @RecID IS NULL IF @WantedValue - @BestUnder < @BestOver - @WantedValue -- If BestUnder is closer to the sum we want, choose that SELECTBestUnder AS Items, FaceValue FROM@Data WHEREBestUnder > 0 ELSE -- If BestOver is closer to the sum we want, choose that SELECTBestOver AS Items, FaceValue FROM@Data WHEREBestOver > 0 ELSE -- We have an exact match SELECTCurrentItems AS Items, FaceValue FROM@Data WHERECurrentItems > 0With references to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505 Peter Larsson Helsingborg, Sweden
View Replies !
View Related
Selecting A Certain Number Of Records?
Hi, Here is a basic example of the issue I am having: Table 1 columns - name, address, zipcode, favorite food For table 2 I want to find how many zipcodes exists and also take 20% of the count Table 2 columns - based off Table 1 contains zipcode, count(zipcode) as ct, count(zipcode) * .20 as perc_ct For example: zipcode ct perc_ct 83746 10 2 93847 20 4 I want to run a query that will pull any 2 records for 83746 and any 4 records for 93847 from Table 1. Is this possible?
View Replies !
View Related
My Number Of Records Are Different From Views
Why is it my number of records are different from my view. this are the following code that that i used in my code and in my view. my code in my program: this code show all the records i know If iConn.State = ConnectionState.Open Then iConn.Close() iConn.Open() Dim rsBills As New Data.DataSet rsBills = New DataSet Dim daBills As New SqlDataAdapter daBills = New SqlDataAdapter rsBills.Clear() Dim cmBills As New SqlCommand cmBills = iConn.CreateCommand ' "Select U_Code, U_Name, U_Level, P_Word From EPassword ", cmBills.CommandText = "Select OR_no, Billing_mo From dbo.vwBilling Where Month(dbo.vwBilling.Billing_mo)= " & Month(Me.dtto.Value) & " And Year(dbo.vwBilling.Billing_mo) = " & Year(Me.dtto.Value) & " ORDER BY OR_no " daBills.SelectCommand = cmBills rsBills.AcceptChanges() rsBills.Clear() daBills.Fill(rsBills, "Bills") Me.DataGridView1.DataSource = Nothing Me.DataGridView1.DataSource = rsBills.Tables("Bills") my code in views: SELECT OR_no, Billing_mo, Account_no, Name, Address, Cno, Pres_read, Mprev_read, Sub_read, Pres2, Prev2, SRead2, Mtr_cons, Amount, NPC, Dmem, Cmem, Tot_bill, D_Pmnt, Class, Newbill, Prd_fr, Prd_to, Type_Pmnt, Type_Date, Type_Docs, wtax FROM dbo.Billing hope you can help me guys
View Replies !
View Related
Select Specific Number Of Records
Dear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."
View Replies !
View Related
Decreasing Number Of Records.. Is It Important?
hi,If we consider the following scenerio:a school with 500 students.On average student takes [x] courses per year, and [y] tests per course.table of grades:+------------+-----------+---------+-------+| student_id | course_id | term_id | grade |+------------+-----------+---------+-------+| | | | |+------------+-----------+---------+-------+| | | | |+------------+-----------+---------+-------+as average, x = 20, y =6 thennumber of records = 500 * 20 * 6 = 120,000 record per year.is this design correct considering number of records generated per year?Is it important to reduce the "number of records" or no?thank you.
View Replies !
View Related
Batching Large Number Of Records Using ADO
Hi, First, thank you Kurt & Nguyen for replying to my last message. My problem: I need to insert a very large number of rows, from memory (and NOT from file) into a table (in SQL Server 7.0) using ADO. Performance is the big issue and I need to do this as efficiently as possible. I cant use BCP, BULK INSERT etc as I am using ADO to do this. MS documentation appears to suggest using 'AddNew' and 'UpdateBatch' for this purpose, but my attempt at batching it this way is exteremly slow (please look at my code below). Here is my failed attempt at batching large number records using ADO: void myAttemptAtBatchingUsingADO() { try { // Make connection CoInitialize(NULL); _ConnectionPtr pConn = 0; pConn.CreateInstance( __uuidof(Connection)); pConn->Open("dsn=myDB;", "myUser", "batch", adConnectUnspecified); // open recordsert _RecordsetPtr pRS = 0; HRESULT hr = pRS.CreateInstance( __uuidof(Recordset) ); pRS->CursorType = adOpenKeyset; pRS->LockType = adLockBatchOptimistic; pRS->Open(_bstr_t(L"myDB..myTable"), pConn.GetInterfacePtr(), adOpenKeyset, adLockBatchOptimistic, adCmdTable); // say 1000 records. for(short n=0; n<1000; n++) { pRS->AddNew(); pRS->Fields->GetItem("myCOL1")->Value = n; pRS->Fields->GetItem("myCOL2")->Value = n; pRS->Fields->GetItem("myCOL3")->Value = "xyz"; pRS->Update(); } // Batch it. pRS->UpdateBatch(adAffectAll); // Close ... pRS->Close(); pConn->Close(); CoUninitialize(); } catch( _com_error &Ex ) { } catch(...) { } } Can you please help with batching in ADO!! Thanks
View Replies !
View Related
Batching Large Number Of Records Using ADO
Hi, First, thank you Kurt & Nguyen for replying to my last message. My problem: I need to insert a very large number of rows, from memory (and NOT from file) into a table (in SQL Server 7.0) using ADO. Performance is the big issue and I need to do this as efficiently as possible. I cant use BCP, BULK INSERT etc as I am using ADO to do this. MS documentation appears to suggest using 'AddNew' and 'UpdateBatch' for this purpose, but my attempt at batching it this way is exteremly slow (please look at my code below). Here is my failed attempt at batching large number records using ADO: void myAttemptAtBatchingUsingADO() { try { // Make connection CoInitialize(NULL); _ConnectionPtr pConn = 0; pConn.CreateInstance( __uuidof(Connection)); pConn->Open("dsn=myDB;", "myUser", "batch", adConnectUnspecified); // open recordsert _RecordsetPtr pRS = 0; HRESULT hr = pRS.CreateInstance( __uuidof(Recordset) ); pRS->CursorType = adOpenKeyset; pRS->LockType = adLockBatchOptimistic; pRS->Open(_bstr_t(L"myDB..myTable"), pConn.GetInterfacePtr(), adOpenKeyset, adLockBatchOptimistic, adCmdTable); // say 1000 records. for(short n=0; n<1000; n++) { pRS->AddNew(); pRS->Fields->GetItem("myCOL1")->Value = n; pRS->Fields->GetItem("myCOL2")->Value = n; pRS->Fields->GetItem("myCOL3")->Value = "xyz"; pRS->Update(); } // Batch it. pRS->UpdateBatch(adAffectAll); // Close ... pRS->Close(); pConn->Close(); CoUninitialize(); } catch( _com_error &Ex ) { } catch(...) { } } Can you please help with batching in ADO!! Thanks
View Replies !
View Related
Updating Records With A Incremental Number
I'm trying to update every record with a incremental number. I wrote the following query but it updates the records with the same number. Could someone please tell me what I'm doing wrong? Thanks. declare @NextKey int SELECT @NextKey = NEXT_KEY FROM TABLE_KEYS WHERE TABLE_NAME = "tblEmp" set @NextKey = @NextKey + 1 DECLARE tblNewEmp_cursor CURSOR FOR select emp_pk from tblNewEmp open tblNewEmp_cursor FETCH NEXT FROM tblNewEmp_cursor WHILE @@FETCH_STATUS = 0 begin update tblNewEmp set emp_pk = @NextKey set @NextKey = @NextKey + 1 FETCH NEXT FROM tblNewEmp_cursor end close tblNewEmp_cursor
View Replies !
View Related
Display Number Of Records Processed
I've got a stored procedure that processes a TON of records... What I would liek to do is to write a row to a "progress" table which shows how many rows have actually been processed. I have a simple counter defined in the procedure: SET @COUNTER = 0 Each time the procedure loops through, it increments by 1: SET @COUNTER = @COUNTER + 1 What I would like to do is write rows to a PROCESS table which would reads: PROCESSED 1000 rows PROCESSED 2000 rows PROCESSED ...... rows etc. I have a slight idea how to pull this off, but not sure about the whole even number thing by 1000. If anyone has any insight it would be greatly appreciated!! Thanks in advance!
View Replies !
View Related
Inserting Large Number Of Records
Hi! The DB I am working with has about 10 tables and some of the tables have 200,000 to 500,000 records. All tables have a clustered index on the primary key. I performance during INSERT could be better I think - I add thousands of records at a time from many connections. Is there a way to defer the update of indicies? So that, I can update the tables and then let the indicies regenerate ? thanks, Jas.
View Replies !
View Related
Retrieve Specific Number Of Records
Hi, How to display specific number of records? That means I want to display records starting from 3th row to 5th row. Please send your suggestions or links. Table ----- Name Age ----------------- Raja 23 Kumar 26 Suresh 30 Rani 22 Subha 32 Ganesh 25 The result will be Name Age ----------------- Suresh 30 Rani 22 Subha 32 Thanx. GANESAN MURUGESAN
View Replies !
View Related
Select Records Based On Number
I'm trying to select records that occur more than once. I'm trying to base this on the email column. So basically I want the query to look something like this: select * from table where emailaddress count > 1 Can someone provide me with the correct syntax? :)
View Replies !
View Related
How To Select N Number Of Records In Table?
Dear Friends! i have one table namely details empid type qty emp1 bucket 5 emp2 bucket 5 emp4 Book 5 emp5 Lux 5 .. .. .. .. .. .. nenp n n Every end of the day i need select total no of type and qty.i have milions of records in the table ex bucket 100 book 300 How it is possible Regards Umapathy
View Replies !
View Related
Returning Number Of Records Found In Query
I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records. sub findcustomers(sender as object,e as eventargs) dim connection1 as sqlconnection=new sqlconnection(...) dim q1 as string="select * from tblcustomers where store='65'" dim command1 as sqlcommand=new sqlcommand(q1,connection1) dim a as integer command1.connection.open() a=command1.executenonquery() label1.text=a.tostring() command1.connection.close() end sub What am I doing wrong?
View Replies !
View Related
Error While Handling Large Number Of Records
Hi I have created one store procedure which handles global updates I am using cursor to fetch one be one row for updating (It is required for implementing business logic)Now when i execute this store procedure ---it gives me dedlock error , I dont know why i m getting this error(Approx number of rows 1.5lakh)if then i removed unnecessary records from table (Approx -50000) it works fine,Is there any way to handle itI am calling this storeprocedure from my window service.please give me a good solution if possible
View Replies !
View Related
Auto Insert A Variable Number Of Records?
I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam
View Replies !
View Related
Show Number Of Values As % Of Total Records..?
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
View Replies !
View Related
Grouping Records && Assigning Sequential Number
I need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.
View Replies !
View Related
The Best Way To Delete A Huge Number Of Records In Table
Hi Everyone, We have a large test database with million of records for more than company site Code. Sometime we want to refresh the data of that database for one or more site Codes. In order to do that I have to delete all records of the site code we want to refresh on the test database first then copy a new set of data from production database over. Since we refresh data based on the site code therefore I have to use the Delete command instead of Truncate. Since this is a huge database with thousand of tables and million of records per table I have a performance issues with delete command. So what would be the best to delete a large number of records without writing any information to database log file? FYI: The Recovery model of this database is Simple Regards, Jdang
View Replies !
View Related
How To: Retrieve A Limited Number Of Records From A Record Set
I€™m working on a database project that will ultimately contain millions of records for each lot. In addition, each lot will have up to 96 corresponding serial number records. I would like to add a SQL parameter that would tell the database engine to only return X number of records. For Example: If table TBL_LOTS contains one million records I would like to limit the return set to 100 for example. What would I need to add to the SQL command to below to restrict the data set to the first 100 records in the set of one million? SELECT [LOT NUMBER] FROM TBL_LOTS WHERE [STATMENTS]
View Replies !
View Related
Get Count And Average Number Of Records Per Month
Example table structure: Id int, PK Name varchar AddDate smalldatetime Sample data: Id Name AddDate 1 John 01/15/2005 2 Jane 01/18/2005 . . . 101 Jack 01/10/2006 102 Mary 02/20/2006 First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here? select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr from dbo.sampletable group by datename(mm, AddDate), datepart(yy, AddDate) order by count(id) desc Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?
View Replies !
View Related
|
TRACKER
