T-SQL (SS2K8) :: How To Calculate Total Sum Of Values Of Table

Jul 11, 2014

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?

View 9 Replies


ADVERTISEMENT

Calculate Total Tuition By Accumulating Column Values ( Loop?)

Jun 3, 2004

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

View 3 Replies View Related

SQL Server 2012 :: Calculate Total On-peak And Off-values For A Month

Nov 25, 2014

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)

View 9 Replies View Related

T-SQL (SS2K8) :: How To Calculate Sum Value In Table

Mar 13, 2014

create table cust_details
(
id int ,
city varchar(20),
cust_name varchar(20),
sales int
)

insert into cust_details

values('1','Tamilnadu','101','500'),
values('2','Tamilnadu','102','300'),
values('3','Pondi','103','200'),
values('4','Pondi','104','100')

My expecting o/p:
-----------------

city customer sales
tamilnadu 101 500
102 300
------
total 800
-------
or
customer sales
101 500
102 300
total 800

Like I wanna display separate location?

View 2 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

T-SQL (SS2K8) :: Calculate 90 Days And 3 Years Ago From Effective Date In A Table?

Sep 30, 2014

What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?

as in

SELECT EffectiveDate
from FL.CEFHistory

I need to return the effective date - 90 days and 1 year from that.

[URL]

View 6 Replies View Related

Calculate The Values From A Underlying Table

Jan 17, 2007

I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".

In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)

How to calculate the values from a underlying table?

View 6 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 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]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,

[Code] .....

View 3 Replies View Related

How To Add 7% For The Total And Calculate VAT

Aug 22, 2007

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

View 2 Replies View Related

How To Calculate Only Total One Column

Jul 18, 2014

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
---------
??
----------

View 1 Replies View Related

How Do I Calculate Current Total Bytes Per Row Used

Feb 16, 2001

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.

View 2 Replies View Related

Calculate Total Hours / Minutes?

Jul 5, 2014

need to calculate the total hours mintues in sql server and asp.net

View 11 Replies View Related

Calculate Grand Total Using Pivot

Apr 25, 2014

How to calculate grand total in sql server using pivot?

View 1 Replies View Related

Transact SQL :: Calculate Total Between Two Columns?

Sep 16, 2015

I 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

View 5 Replies View Related

Reporting Services :: How To Calculate TOP N Total

Oct 23, 2015

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.

View 3 Replies View Related

DB Engine :: How To Calculate Total CPU Capacity In Ms/sec

Jun 30, 2015

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....

View 7 Replies View Related

SQL Server 2012 :: Calculate Total Between Two Columns

Sep 14, 2015

I have update schema and I upload image with my desire result.

CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int

[Code] .....

View 7 Replies View Related

Calculate Total Working Hours Between Days

Nov 15, 2013

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.

View 3 Replies View Related

Calculate The Total Of A Coloumn In Reporting Services

Apr 28, 2008



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?

View 6 Replies View Related

T-SQL (SS2K8) :: Return All Values From Table

Oct 1, 2015

I have a small problem with a join clause, because i need to return all values from my table BL:

my code is:

SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora
FROM
(
SELECT noconta,banco, u_area
FROM BL

[code]....

In fact, i need to return 2 accounts (16,35) - x.NOCONTA IN (16,35), but I know that the problem is on the WHERE clause.How can do that, because i need all the condition on WHERE clause regarding my table OL, but also, i need to return my two accounts (16,35).

View 2 Replies View Related

How To Calculate The Total Days Between Open And Close Date

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

Transact SQL :: Calculate Total Working Time Against Attendance ID?

May 16, 2015

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

View 7 Replies View Related

T-SQL (SS2K8) :: Comparing Column Values In Same Table

Jun 16, 2014

How to resolve the below task

create table #temp ( idx int identity(1,1), col1 int, col2 int )

Here i want a flag success or fail on basis of below conditions.

I need to take all the col1 values and then i need to compare to each other.

if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".

if all the col1 values are ok , then we need to check Col2 values same as above.

--case 1

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522

--select * from #temp

--truncate table #temp

Because of difference in col1 values . the value of flag should be fail.

--case 2

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522

Here also the col1 is ok but col2 values have difference so it should be Fail.

Otherwise it should be success.

View 6 Replies View Related

T-SQL (SS2K8) :: Import Values From Excel Into Table?

Nov 18, 2014

I've already created a table and i wanna to insert values in that more than five hundred row ,that values are stored in Excel files, Here I've the doubt is it possible to insert values from excel sheet? I've current data base of ms sql 2000, if it is possible means, how to insert values using query?

ex:

create table test
(
item_code int,
item_name varchar(50),
bill_qty float,
bil_date datetime
)

In that excel file also had the same column name name.

View 4 Replies View Related

Power Pivot :: Sum For Events And Time - Calculate Total Ratio

May 22, 2015

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

View 3 Replies View Related

T-SQL (SS2K8) :: Find Null Values Between 3 Datasets In One Table

Mar 21, 2014

I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.

I have aSQL Fiddle that will allow you to visualize the schema.

The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.

select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)

[Code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Can It Change Columns Of A Table Values To Rows

May 14, 2014

I have a table with this info:

NrCard numberPersonAuto123456789101112
11111111111111111111User1VW Jetta6,46,46,46,45,825,825,825,825,825,825,826,4
22222222222222222222User2Honda CR-V 13,2113,2113,2112,0112,0112,0112,0112,0112,0112,0113,2113,21

How I can get this result:

NrCard numberPersonAutomonthvalue
11111111111111111111User1VW Jetta16,4
11111111111111111111User1VW Jetta26,4
11111111111111111111User1VW Jetta36,4
11111111111111111111User1VW Jetta45,82
11111111111111111111User1VW Jetta55,82
11111111111111111111User1VW Jetta65,82

[code]....

Should I use unpivot or pivot?

View 2 Replies View Related

T-SQL (SS2K8) :: Getting Minimum And Maximum Values In A Large Table

May 23, 2014

Table definition:

Create table code (
id identity(1,1)
code
parentcode
internalreference)

There are other columns but I have omitted them for clarity.

The clustered index is on the ID.

There are indexes on the code, parentcode and internalreference columns.

The problem is the table stores a parentcode with an internalreference and around 2000 codes which are children of the parentcode. I realise the table is very badly designed, but the company love orms!!

Example:
ID| Code| ParentCode| InternalReference|
1 | M111| NULL | 1|
2 | AAA | M111 | 2|
3 | .... | .... | ....|
4 | AAB | M111 | 2000|
5 | M222 | NULL | 2001|
6 | ZZZ | M222 | 2002|
7 | .... | .... | .... |
8 | ZZA | M222 | 4000|

The table currently holds around 300 millions rows.

The application does the following two queries to find the first internalreference of a code and the last internal refernce of a code:

--Find first internalrefernce
SELECT TOP 1 ID, InternalReference
FROM code
WHERE ParentCode = 'M222'
Order By InternalReference

-- Find last ineternalreference
SELECT TOP 1 ID, InternalReference
FROM code
WHERE ParentCode = 'M222'
Order By InternalReference DESC

These queries are running for a very long time, only because of the sort. If I run the query without the sort, then they return the results instantly, but obviously this doesn't find the first and last internalreference for a parentCode.

I realize the best way to fix this is to redesign the table, but I cannot do that at this time.

Is there a better way to do this so that two queries which individually run very slowly, can be combined into one that is more efficient?

View 7 Replies View Related

T-SQL (SS2K8) :: Input Values In Table With A Stored Procedure

Jun 8, 2015

I have the following Query.

SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.PURCHLINE.DEL_INTERCOMPANYRETURNACTIONID' FROM SRC_AX.PURCHLINE WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL UNION
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.SALESLINE.DEL_INTERCOMPANYRETURNACTIONID'

[Code] .....

My tabel is HST_MASTER.Control.

I want to have this query in a stored procedure. What syntax stored procedure i need to make to fill my table.

View 1 Replies View Related

T-SQL (SS2K8) :: Query To Display Different Values From Same Column In Same Table?

Sep 18, 2015

I have a table with a column AttributeNumber and a column AttributeValue. The data is like this:

OrderNo. AttributeNumber AttributeValue
1.-Order_1 2001 A
2.-Order_1 2002 B
3.-Order_1 2003 C
4.-Order_2 2001 A
5.-Order_2 2002 B
6.-Order_2 2003 C

So the logic is as follows:

I need to display in my query the values are coming from Order_1, means AttributreValues coming from AttibuteNumbers: 2001,2002,2003...and Order_2 the same thing.

Not sure how to create my Select here since the values are in the same table

View 2 Replies View Related

T-SQL (SS2K8) :: Load Values From Stored Procedure Into A Temp Table?

May 8, 2014

I would like to know if the following sql can be used to obtain specific columns from calling a stored procedure with parameters:

/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSP @parm1, @parm2

If the above does not work or there is a better way to accomplish this goal, how to change the sql?

View 1 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Characters Based On Table Values

Dec 12, 2014

There is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]

[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000

For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400

The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...

DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';

SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]

SET @Total='select '+@formula

EXECUTE sp_executesql @Total;
PRINT @Total;

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved