Calculating The Total Amount Of Drugs Prescribed, Total Amount

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.


View 4 Replies


Total Page Writes/total Amount Of Data Change In A Set Period Of Time

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.


View 5 Replies View Related

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

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 View Related

How To Know When Total Salary Is At Certain Amount

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

Total Amount Of Triggers Per Table

Aug 1, 2006


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

Running Total: Summing On Distinct Amount

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.


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.



View 5 Replies View Related

SQL Server 2012 :: Reset Running Total To X Amount

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

Show Total Amount Of Rows In Specific Table

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

SQL Server 2012 :: Split Total Days And Amount Between Start And End Month

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


View 4 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

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

How To Separate Period Amount From YTD Amount

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:

when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,

UrsinusCollege.dbo.gl7accounts a

ursinuscollege.dbo.gl7transactions t on

(t.gl7fiscalperiodsid >= 97
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.


View 6 Replies View Related

Reporting Services :: Calculating Average Amount Of Working Days In A Month - SSRS 2005 Matrix

Jun 22, 2015

I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?

View 7 Replies View Related

Calculating Whole School Total Tuition

Jun 12, 2004

I am trying to calculate a whole school total tuition. I've got helped with individual tuition total which is

create proc sp_individualtuition
select c.contractNum, (c.tuition-((sum(d.discountPer))* c.tuition)) as totaltuition
from contract c, contractDiscount cd, discount d
where c.contractNum = cd.contractNum
and cd.discountNum =d.discountNum
group by c.contractNum, c.tuition

however how can i calculate the whole school total tuition (adding all the individual total tuition)?

my contract, contractDiscount and discount DDL are down below

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)

create table contractDiscount(
contractNum int not null,
discountNum char(3) not null

alter table contractDiscount
add constraint pk_contractdiscount primary key clustered (contractNum, discountNum)

alter table contractDiscount
add constraint fk_contractdiscount_contractnum
foreign key (contractNum)
references contract(contractNum)

alter table contractDiscount
add constraint fk_contractdiscount_discountnum
foreign key (discountNum)
references discount(discountNum)

create table discount(
discountNum char(3) primary key,
discountDesc varchar(100) not null,
discountPer decimal(3,2) not null


View 1 Replies View Related

Calculating Total Events (query)

Jan 23, 2008

Hi. I'm trying to calculate the total number of cases that more than 4 events. This is my query:

select count(defendanteventpros.vbkey) as countcase from defendanteventpros
where exists (select * from defendanteventpros where eventid=2 and eventdate between '1/1/2007' and '12/31/2007')
having count(defendanteventpros.vbkey)>4

The defendanteventpros table has a vbkey (primarykey) eventid (type of event) and an evetndate (when the event takes place).

What I'm trying to find is how many cases had 4 or more events and were received (eventid=2 from above) between 1/1/2007 and 12/31/2007.

I keep getting a big number that I know is not correct. I would appreciate any help anyone can offer! Thanks!

View 7 Replies View Related

Calculating Grand Total And Percentage

Oct 19, 2007

Hi All,

I have to create a table like this using SSRS.

LoanStatus NoOfLoans Amouunt Percentage


Sent to Partner

Denied by Partner

Accepted by Partner

Loan Apps Pnd Disb

Loan Apps Disbursed

Table is grouped by loan status.
Percentage is calculate as NoOfLoans/TotalNoOfLoans.

Please can anyone tell me how to calculate the grand total and Percentages?

View 5 Replies View Related

T-SQL (SS2K8) :: Calculating Total Outage Time

Jun 17, 2014

I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.


3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.

In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.

View 3 Replies View Related

Reporting Services :: Calculating Percentage Of Column Total In Charts

Jul 22, 2015

Calculating % of Column Total in Charts...

Doing this exercise in Excel is always very simple: 

View 3 Replies View Related

Aggregated Subquery - Sum Total Trips And Total Values As Separate Columns By Day

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]

[Code] .....

View 3 Replies View Related

Query By Year Group By Total Students Sort By Total For Each County

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

Reporting Services :: SSRS Group Total Expression - Add Total Disabled

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 you can see in my Attached Image

I'm using iff condition in my expression.. 

View 15 Replies View Related

SQL Server 2008 :: Pulling Daily Total From Cumulative Total

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.

(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

Total Record Count - Pagination With Total Rows

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

How to get?

View 2 Replies View Related

Adding Subreport Total To Main Report Total

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 Im having major problems trying to get these totals to work.

Can anyone help, thanks

View 7 Replies View Related

Total Spaceused And Total Allocated

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

SQL 2012 :: Log File Data Transfer Amount (MB) Versus Data File Transfer Amount (MB)

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

Please Help =IIf(IsError([total]),,[total])

Feb 29, 2008


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 :


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.



View 1 Replies View Related

How Do I View The Amount Of The Log That Is Used?

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.


View 7 Replies View Related

Highlighting Max Amount

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

Amount Between Parameters Like Or

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

Amount Of Normalization

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

Size Amount

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

Large Amount Of Text?

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

Thank You.


View 2 Replies View Related

Loop Through Table Until Max Amount?

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

Copyrights 2005-15, All rights reserved