I need to show the total amount of rows in a specific table?
The query is as follows:
As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.
The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
I am wondering if it€™s possible to lock a SQL table for specific amount of time, say 5 min.
There is a particular 'Phone' table on the database that should never get locked. Yet, during the development stages we have noticed that the table gets locked at time. The issue since has been resolved to the best of our ability, but, there is still a vague chance that the table can get locked due to the multiple jobs that query the table, when we go live.
If such a situation occurs, we just want to be able flip the switch that will send the server to the mirror mode and the previously mirrored database to become the principal.
So, I just want to recreate a situation by voluntarily locking the table.
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
Table B: Name Data UserA xxx UserB asdasd UserB ewrsad UserC dsafasc UserA sdf UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count UserA 2 UserB 3 UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200
I have a question regarding the total count of the table rows
Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".
I'm no expert at SQL, I can't figure out how to solve that problem.
The following statement:
SELECT TeamMemberId, max(MonthId) as MonthId FROM Position INNER JOIN [Month] m INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId) ON m.Id = Position.MonthId GROUP BY TeamMemberId
Returns TeamMemberId MonthId 1 14 6 20
which is exactly what I want: The greates MonthId for each TeamMember in a specific year.
But I need more data for this postition, like the PositionName.
But the statement:
SELECT TeamMemberId, max(MonthId) as MonthId, PositionName FROM Position INNER JOIN [Month] m INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2) ON m.Id = Position.MonthId GROUP BY TeamMemberId, PositionName
Returns TeamMemberId MonthId PositionName 1 2 ***. d. Geschäftsführung 6 20 ***. d. Geschäftsführung 1 14 CEO 6 16 CEO
The red rows are the ones I wanna get. How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns? Any help is much appreciated.
I have a text file which I used as an initial load of data, containing a little over a million rows. I would like to produce a daily "update" file, which contains changes to existing rows, and creation of new rows. What is the best method of updating the existing table in power pivot?
I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required
For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.
Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.
As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month
Period No of Void Days Rent Loss 06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57 01/10/2014 - 15/10/2014 15 349/7*15 = 747.85
I have uploaded a screenshot of how the result on this link: [URL] ....
Declare @void Table ( PropCode VARCHAR(10) ,VoidStartDate date ,LetDate date ,Market_Rent Money
hi there i am using .net framework 1.1 with SQL 2000 . i want the data in table to get deleted automatically after 30 days of inserting data. so how do i achieve this?
Hello. I was using the new sys.dm_db_index_operational_stats function which is nice for seeing counts of insert/update/delete actions per table index, bla bla bla... Anyways, question, can I do the same thing with Profiler? meaning, can I trace stored procs and sopmehow see the proc exec WITH each table it does actions against? Not talking about filtering on table names in the text, talking I just want to run an application, which uses all stored procs, and see every table used by that execution of the proc, and also the number of rows inserted,updated,deleted.... If so, which Profiler events/columns must I flick on to gather that? Thanks, Bruce
Table & Columns: ID field is identity. Currency is either CDN or USD. Order: ID, DateOrder, Currency, AmountOrder Invoice: ID, NumOrder, DateInvoice, AmountInvoice Credit: ID, NumOrder, NumInvoice, DateCredit, AmountCredit Rate: YYYY, MM, US
Relationship among tables: Order & Invoice : on Order.ID = Invoice.NumOrder, one to many Order & Credit: on Order.ID = Credit.NumOrder, one to many Invoice & Credit: on Invoice.ID = Credit.NumInvoice, one to many
Other conditions: For orders of currency USD, the rate of the DateOrder is used to convert the Order.AmountOrder to CDN. The rate of DateInvoice is used to convert the Invoice.AmountInvoice to CDN. A credit always uses the rate of DateInvoice of its associated invoice.
Report: Total billings of USD sales in a period of time (startDate, endDate), converted to CDN
Total billing of USD sales converted to CDN = sum [AmountInvoice * rate of DateInvoice – sum(AmountCredit * rate of DateInvoice)] WHERE the DateInvoice and DateCredit must be in the specified period. AND associated Order.Devise = USD
SQL: 1 SELECT SUM (AmountInvoice*dbo.GetRate(DateInvoice) – SUM (AmountCredit*dbo.GetRate(DateInvoice)) as TotalBilling 2 FROM Order IINNER JOIN Invoice ON Order.ID = Invoice.NumOrder 3 LEFT JOIN Credit ON Invoice.ID = Credit.NumInvoice 4 WHERE Order.Devise = ‘USD’ 5 AND DateInvoice >= startDate AND DateInvoice <= endDate 6 AND DateCredit >= startDate AND DateCredit <= endDate
Actual Results: Only orders which have both invoices & credits. The orders which invoices but no credit are not included in the results.
(SQL Server 2008) I want to display a total for each user that is returned from my query result. I was trying the union all route as that is what I am most comfortable with, however, the result set returns the total row at the bottom instead of under each user. This is the desired result set...Here is my query, what do I need to alter to have the output display like so?
Code: Create Table #Test ( id varchar(50), name varchar(500), out1 int, out2 int, total4day int,
I have a categories table and a products table, the products are associated with a category, and my current select statement looks like this..
SELECT COUNT(dbo.tbl_Categories.CatName) AS TotQty, dbo.tbl_Categories.CatName FROM dbo.tbl_Products INNER JOIN dbo.tbl_Categories ON dbo.tbl_Products.CatID = dbo.tbl_Categories.ID GROUP BY dbo.tbl_Categories.CatName
this give me ..
TotQty CatName 1 Books 2 DVD
I wonder how I can change the select statement so I retrieve a result like this..
TotQty CatName Description 1 Books Oliver Twist 2 DVD Dire Straits 2 DVD Elvis
I tried my syntax below, but it said that the field were not in the group by. Â This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:
I have created a matrix with multiple rows in the main data cell and a subtotal at the end of the row. The first row in my matrix main cell is just a count of records, whereas the 2nd row is a % of the value in the 1st row compared to the total of that row. I have 5 columns in the matrix as below (only first row is shown plus the total row. Ignore any rounding issues):
Status A B C D E Total
02/01/2007 No. 9 32 3 13 0 57
% Total 15.00% 56.00% 5.00% 24.00% 0.00% 100.00%
Total No. 77 143 25 72 2 319
% Total 135.00% 350.00% 48.00% 250.00% 2.00% 556.00%
I have a total column at the end. I want to hide the %Total row within the total row as it does not make sense in this context. Anyone know how to achieve this?
