Hi
I am using the below code in sql procedure.How do I add 7% of the price and then VAT for the total.
CAST(CAST(cast(Price * 1.175 as decimal(19, 2)) as INT) + 0.99 AS DECIMAL(19, 2)) as [item-price],'
Advance thanks
I want calculate to my total column sum
ex- col 1 col 2 col 3 col 4
raj 10 20 20
pani 20 88 88
tiger 87 78 89
john 77 77 77
---------
??
----------
We are close to exceeding the Maximum Row size of 8K per row and the CIO asked me "how close are we to reaching the 8K row limit?"
I began by looking at byte requirements for the data types we are using
from the systypes table then I wondered, do the indecie, Foreign Keys and Primary Keys use bytes which must be included in my Calculation for current number of bytes used per Row.
Question: How do I calculate Current total number of bytes used per row.
need to calculate the total hours mintues in sql server and asp.net
View 11 Replies View RelatedHow to calculate grand total in sql server using pivot?
View 1 Replies View RelatedI have table named #table and three columns. SinGroup(it does not matter, can be anything), Column1, and Column2.
CREATE TABLE #table(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
);
And i have some data:
INSERT INTO #table(sinGroup,column1,column2) VALUES
('y1',100,0),
('y2',0,60),
('y3',40,20),
('z1',150,0),
('z2',0,50),
('z3',0,50)
I want to know how to get this result set (c1 - c2 and group by Y and Z and the result shown under the column 1):
SinGroup C1 C2
y1 100 0
y2 0 60
y3 40 20
Y 60 0
z1 150 0
z2 0 50
z3 0 50
Z 50 0
And also result like this (c1-c2 and grop by each row,the result show under the column 1):
SinGroup C1 C2
y1 100 0
y1 100 0
y2 0 60
y2 -60 0
y3 40 20
y3 20 0
z1 150 0
z1 150 0
z2 0 50
z2 -50 0
z3 0 50
z3 -50 0
I have a SSRS report. I am displaying TOP 10 Stores by sales by creating a filter on group Store. It has 3 row groups (Store, brand, product).
There is no column group. In each cell, I am using sum function in expression (Sum(Fields!TotalSales.Value).
I am adding Totals by going on Group properties, but I am getting totals of all the rows not the total of all the groups within my Store level (which is my top Parent group).
Store   Total sales(CW)  TotalSales(LW)
A Â Â Â Â Â Â 2 Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1 Â Â Â -expression used to calculated this value is sum(Fields!TotalSales.Value)
B Â Â Â Â Â Â 3 Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2
C Â Â Â Â Â Â 7 Â Â Â Â Â Â Â Â Â Â Â Â Â Â 3
I want subtotal =12 AND 6 under respective columns . ALSO, my store (A, B, C) can be expanded into Brands and brands into Products. I tried using running value but its not working as I have 3 groups within Store row parent group and I am doing a SUM on each group.
I want to know how it calculates CPU utilization percentage (we see in Task Manager).
Further how can we find total capacity of our CPU in ms/sec....
I've the table like
create table accutn_det
(
fs_locn char(50),
fs_accno varchar(100),
fs_cost_center varchar(100),
fs_tran_type char(50)
[Code] .....
Like all location details stored from all months in these table
here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount
so i made the query to find the amount for may
select
fs_locn,
fs_accno,
amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount
when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1
end
)
from
accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014'
groupby fs_locn,fs_accno
now i need the sum values of all costcenter for the particular account.how to do that?
I have update schema and I upload image with my desire result.
CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
[Code] .....
I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example:
Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs.
I have a table on my form and each row contains data from a query.
I have a 'Total' row at the bottom and want to add up the above rows. Id rather not do this in the query itself.
Is it possible to do it from the layout view?
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
I have a table like below
i need to calculate total working time against 'AttID' (where I - intime, O - OutTime)
ex :Â
AttID TotalTime
1 08:02:00
2 07:45:00
I am trying to create a procedure which will calculate the total tuition
This process involves 3 tables.
Contract table has tuition information which is all $100 (set price).
Discount table has discount type and discount percentage (ex. 0.3) on each discount type.
ContractDiscount table have contract number and discount number to connect both tables
I think I need to create a loop since some contract gets more than one discount.
I have to calculate and get result nee to be like this
total_tuition = (tuition - discountPer * tuition) - this has to be a loop condition
Do you have any suggestion ?
Thanks
SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).
In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.
For instance
                            201501 201502 201503 TOTAL
Number of events           8        10        10    28
Time                             5           5        Â
4Â Â Â Â 14
Ratio                           8/5      10/5     10/4 28/14
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?
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
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] .....
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 RelatedFor 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..Â
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')
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?
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
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 RelatedHi 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
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
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
Thanks.
Hi,
I am trying to execute a query which calculate % on group by. here is the query
SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))
/ (
SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID)) FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))
)*100 AS [%]
FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_IDWHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL')) GROUP BY C.USR_HIGHEST_DEGREE
My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.
Any help would be appreciate.
I have an SQL statement which returns the Top 10 states with the number of visitorsSELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors FROM [Customer] WHERE Customer.year = '2006' GROUP BY Customer.state ORDER BY COUNT(Customer.state) DESCSo far this is what I havestate| visitorsMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9But what i need is to calculate the total for the Visitors column in my SQL so that is like soMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?Thank you
View 8 Replies View Related
Hi all,
I'm having the table in the following structure,
UId - int
Pct - int
Amt - money
Example
UId Pct Amt
12 25 1500
12 30 2500
12 45 2000
i want to calculate to calculate the sum of amount for the UId 12 in the following manner,
sum of ( Amt * (Pct/100)) for UId = 12
how to write query in sql server 2000?
when i calculate if the value (Pct/100) < 0 that is 0.25 then it will take it as 0 so i'm getting all the values as 0.
Thanks!
I have a table that has a providername and totals field.
I want to be able to calculate the MTD and YTD totals for each provider.
EX. Provider MTD YTD
Bob 100.00 300.00
But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks
Ron