Get Total Amount By Date
Apr 10, 2008
this is my sample records in my table
Sdate Amount
January 2007 250000.00
March 2007 300000.00
January 2008 350000.00
how can i get the amount from February to
May 2007 and sum it up like this
Details
February 2007 250000.00
March 2007 300000.00
April 2007 300000.00
May 2007 300000.00
this is all that i want to get
Total Amount: 1,150,000.00
i also posted this one in new to sql server section, but im hoping to get others opinion here. thnx
View 4 Replies
ADVERTISEMENT
Aug 10, 2006
Hi all,
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 kindly request for help.
Thanx in advance.
Ronnie
View 4 Replies
View Related
Jun 9, 2004
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.
TIA
View 5 Replies
View Related
Jul 24, 2013
I would like to know how to use a criterion on this example. I want to know only when the total salary is at a certain amount
SELECT SUM (salary) as Total Salary
FROM employees
WHERE Total Salary > 25000; ---this is where i am having issue
View 4 Replies
View Related
Aug 1, 2006
Hi
Just a quick question taht I hope someone can answer, What it the total amount of trigger per table?
I understand that it is better to have as least as possible for performance
Thanks Rich
View 5 Replies
View Related
May 9, 2007
I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.
Example:
Month Year Goal Other Value
March 2007 500 5568
March 2007 500 5568
March 2007 500 5569
April 2007 600 5568
April 2007 700 5569
Total (I am receive)
March-April 2007 1600 5568
March-April 2007 1200 5569
Total (I excpect)
March-April 2007 1100 5568
March-April 2007 1200 5569
I haven't found anything online to help.
Thanks,
Sam
View 5 Replies
View Related
Dec 22, 2014
I need to calculate running total which resets when reached to 16, and also needs to calculate remaining amount as paid.
I have attached sample data, so we have two columns Earned, and Used. And we need to calculate Balance, and Paid columns.
View 4 Replies
View Related
May 20, 2012
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).
View 4 Replies
View Related
Mar 21, 2015
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
[Code].....
View 4 Replies
View Related
Apr 10, 2014
I have a query that calculate the total amount of order details based on a particular order:
Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID
My question is what if I wanted to create a formula to something like:
UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount
Do I need to create a function for that? Also is it possible to have m y query as a table variable?
View 7 Replies
View Related
Oct 3, 2015
I have a table that have customer name and their bill amount
a)Â tblBilling
which shows records like below (Billing Dates are shown in Year-Month-Day format)
Invoice  Customer       BillingDate    Amount       Tax
--------------------------------------------------------------------------
1 Â Â Â Â Â Â ABC Â Â Â Â Â Â Â Â Â Â Â 20015-10-2 Â Â Â Â Â 1000.00 Â Â Â Â Â Â 500.00
2 Â Â Â Â Â Â DEF Â Â Â Â Â Â Â Â Â Â Â Â 20015-10-2Â Â Â Â Â Â 2000.00 Â Â Â Â 1000.00
3 Â Â Â Â Â Â GHI Â Â Â Â Â Â Â Â Â Â Â Â 20015-10-2Â Â Â Â Â Â 1000.00 Â Â Â Â Â Â 500.00
4 Â Â Â Â Â Â JKL Â Â Â Â Â Â Â Â Â Â Â Â Â 20015-10-3 Â Â Â Â Â 5000.00 Â Â Â Â Â 2500.00
5 Â Â Â Â Â Â MNO Â Â Â Â Â Â Â Â Â Â Â 20015-10-3 Â Â Â Â Â 1500.00 Â Â Â Â Â Â 750.00
6 Â Â Â Â Â Â PQR Â Â Â Â Â Â Â Â Â Â Â Â 20015-10-4 Â Â Â Â Â Â 500.00 Â Â Â Â Â Â 250.00
7 Â Â Â Â Â Â STU Â Â Â Â Â Â Â Â Â Â Â Â 20015-10-4Â Â Â Â Â Â 1000.00 Â Â Â Â Â 500.00
8 Â Â Â Â Â Â VWX Â Â Â Â Â Â Â Â Â Â Â 20015-10-4 Â Â Â Â Â Â 2500.00 Â Â Â Â 1250.00
I want to perform a query that should SUM Amount and Tax Colums by date basis, so we could get the following result
 BillingDate    Amount       Tax
--------------------------------------------------------------------------
20015-10-2 Â Â Â Â Â 4000.00 Â Â Â Â Â Â 2000.00
20015-10-3Â Â Â Â Â Â 6500.00 Â Â Â Â Â Â 3250.00
20015-10-4Â Â Â Â Â Â 4000.00 Â Â Â Â Â Â 2000.00
View 3 Replies
View Related
Jan 17, 2007
I need to convert a date like 08/1/2009 to 0809
I also need to show currency as 100.00 and not 100.0000
How can I do these in a select statement?
SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment
I appreciate any help!
View 5 Replies
View Related
Mar 18, 2008
I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:
SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid
FROM
UrsinusCollege.dbo.gl7accounts a
join
ursinuscollege.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid
where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')
order by a.accountnumber
Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.
Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.
Thanks very much.
Sue
View 6 Replies
View Related
Apr 6, 2006
Hi All,
I have a table call case and case_status have two fields, date and status as below:
date status
04/01/2006 open
04/05/2006 closed
04/10/2006 open
04/15/2006 closed
Whenever i open and closed the case, one record is insert into the case_status table.
Now I would need to calculate the total days of the case in storeprocedure.
Anyone can help me please.
Aung
View 5 Replies
View Related
Sep 5, 2015
My report looks like this :
In that above report maximum Total quantity is 125929 and its respective date is 7/1/2013.
How can i insert a row(Top or bottom) that gives respective date of maximum quantity?Â
View 7 Replies
View Related
Jul 8, 2015
In the typical Job Cost Reporting world you can easily create SQL Views / Stored Procedures (or create a report) that can return both Job To Date values along with period values for another date range. Something like this:
Job,
Phase of Work,
Cost Type,
UM,
Budgeted Cost,
Budgeted Quantity,
Period Cost,
Period Quantity,
Period Unit Cost,
JTD Cost,
JTD Quanity,
JTD Unit Cost,
Variance between JTD and Period Cost,
Variance between JTD and Period Quantity,
Variance between JTD and Period Unit Cost
But in the tabular BI world I am not certain how to have these values show up in a pivot table or BI visualization. My current Fact table is essentially every job cost transaction so it is at the most granular level holding Company, Job, Phase of Work, UM, Cost Type, Date, Budget Cost, Budget Quantity, Actual Cost, Actual Quantity. The Budget values only have a value if the entry was updating the budget and the Actual values hold actual cost for each transaction. If you don't restrict by date you have your Budgeted Quantity / Cost and Job To date Cost / Quantity.
I can generate a Pivot table or chart and not restrict by date and I can get all the Budget Totals and the Job To Date Totals when I have the Company, Job, Phase Of Work, Cost Type.
But what if I want to look at a range like last quarter or yesterday or last week. I can indeed filter that data in Pivot Table and get the period values, but now I don't know how to have the Job To Date values show up for comparison purposes.
I am guessing maybe a DAX formula (if using Excel Powerpivot or SQL Tabular Model), but I am at a loss.
So in the BI world how do you get Job To Date totals while also viewing the data for a period of time?
Here are some example graphs we would want to show for a single Job. Let's say we want to show the 10 worst performing cost distributions.Â
I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Cost, Period Cost, Job To Date Cost.I would want a graph to show the cost distribution and for each cost distribution it would show Budgeted Unit / Cost, Period Unit / Cost, Job To Date Unit / Cost.I would want a graph to show the cost distribution and the variance between JTD and Budget and Period and Budget.
As you can see it all stems around seeing JTD and Budget values vs Period Values. Here is a visualization that may work. I am using PivotTable filtered on one Company, Job, Phase of Work. I can see the sum of actual cost which is the totals for the date range I provided. But I still want to see the JTD totals for that Phase of Work and Cost Types and actually the Budget Totals as that is sort of a JTD, but for budget... especially if I want to compare Budget vs JTD vs Actual.
View 8 Replies
View Related
Feb 26, 2014
Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,
[Code] .....
View 3 Replies
View Related
Jul 20, 2005
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
View 4 Replies
View Related
Oct 26, 2015
For some reason my Add Total is grey out, when i tried to add grand total using some expression.
I have two row & two column groups?
Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image
I'm using iff condition in my expression..Â
View 15 Replies
View Related
Jun 28, 2015
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')
View 9 Replies
View Related
Jul 26, 2013
My table contains 1000 records,
I need to know the total record count with the below paging query
SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
How to get?
View 2 Replies
View Related
Apr 28, 2006
Hi, can anyone help?
I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I
have named this text box €™SubRepM1€™
The name of the subreport is €˜subreport1'.
On my Main Report, again I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value)*-1, and I
have named this text box 'MainRepM1'
The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values
but I now need to total these values together for each month in order to
produce a grand total.
I have tried using the following to add the totals for Month 1 together,
=subreport1.Report.SubRepM1 + MainRepM1
but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
Can anyone help, thanks
View 7 Replies
View Related
Jul 20, 2005
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD
View 1 Replies
View Related
Mar 19, 2014
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?
View 6 Replies
View Related
Feb 29, 2008
Hi,
I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.
My report looks something like this :
ID Name Oct Nov Dec Jan Feb March Total
001 alex 87.6% 104.1% 65.1% 50.2% 85.6%
002 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
003 Jon 87.6% 104.1%
004 alex 87.6% 104.1% 65.1% 50.2% 85.6%
005 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
For the 002 ID, though he has worked for Nov and Dec the total % is blank.
The formula that I used for all of these entries is :
=IIf(IsError([total]),"",[total])
and for the month it is : =IIf(IsError([Oct]),"",[oct]), nov and so on.
It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..
Please help, how can I get the total for all.
Thanks,
Farn
View 1 Replies
View Related
Apr 3, 2008
How do I view the amount of the log that is used?
How can I tell how often a checkpoint is being executed?
I have a lot of data to insert into a table (via a SQL insert from another database on the same server), and I do not want to fill the log ... so I will do a SET ROWCOUNT 100000, insert records, wait for the checkpoint to run which will clear the log, ... and repeat this process untill all the records are inserted.
Thanks
View 7 Replies
View Related
Jun 6, 2008
I have a report which lists sales figures by salesperson, and I need the report to highlight the maximum amount (ie. the person who got the highest sales figures). How can I do this?
View 2 Replies
View Related
Apr 7, 2014
I am having table Employee with Employee Salaries details, from that i need to get the values based on parameters(@Param1,@Param2)
select * from Employee
where Salaries >@Param1 and Salaries < @Param2
Conditions :
1) @Param1 >10000 and @Param2 <25000
2) @Param1 >10000 and @Param2 =''
3) @Param1 <15000 and @Param2 =''
(may or may not pass the values for @Param2)
View 3 Replies
View Related
Jun 8, 2007
Hello all,
My question concerns the amount of normalization i require for my specific needs. I realize this is a difficult question without knowing alot more about my database. I am hoping with some information I could get advice from those more experienced than I.
- My database consists of 9 tables with the maximim number of columns being 11 which is the contacts table.
- the largest data type is nvarchar 125.
- number of rows in the largest table will eventually grow to hundreds of thousands.
- users access the database online
This is large to me but I expect that to some of you this not.
My application would be easier to setup if the contacts table were to include address info.
So my question is, for a database of this size could I create a contacts table similar to the customer table in the Microsoft Northwind sample data base with the address included, or should I model something more like the contact table in the Microsoft Adventureworks db with the address and State/province split to separate tables.
Any help you could provide with this scetchy info would be greatly appreciated.
View 6 Replies
View Related
Nov 24, 2005
Can anyone tell me how much space the SQL Server 2005 actually has. And if I can access the server from pretty much any computer?
View 1 Replies
View Related
Feb 22, 1999
We have created a databse in SQL Server 7 for support issues.
We are having trouble with only one aspect of the databse and that is the body of the supported problem.
The databse is basically a Question/Answer Support database whch house Frequently Asked Questions.
When the user does a search on a specific problem they have a list of matching questions shows on the screen (links).
When the question is clicked on it shows the Questions with the Answer of possible fixes.
Our problem lies in the fact that SQL server is truncating the Answer portion. I have tried different Data Types with maximum lengths with no success it keeps truncating it.
Right now I am on Data Type nvarchar with a length of 4000.
If anyone has any pointers on how to solve this problem all input is appreciated. You can post here to the forum or e-mail me directly at jason@flnet.com
Thank You.
-JR
View 2 Replies
View Related
Sep 10, 2012
I have a table with AmountSold and AmountLeftWith. I have to buy from the customers until the amount bought =250,000.
The max that user can buy is 250,000 so customers 1-3 get left with 0 (AmountLeftWith ) and customer 4 with 577 (AmountLeftWith ) after the update as user couldn't buy the entire amount as it would have exceeded 250,000. Preferably the query should stop afterwards and not proceed to check the other customers.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL
DROP TABLE #tmpCustomerAmount
CREATE TABLE #tmpCustomerAmount (
[id] [int] IDENTITY(1,1) NOT NULL,
[Code] ......
View 3 Replies
View Related
Oct 15, 2013
I have a table with product_name and introduction_date(when the product was first introduced)as columns. now i wana calculate average as below
if item is sold in previous business year(suppose 2011-12) then avg should be avg price in businessyear(2010-11), if it is newly introduced(suppose 2013-14)then avg should be of current year(2013-14).
Note:- business year Apr-march
View 1 Replies
View Related
