Transact SQL :: Cumulative Sum On Value Column
Apr 28, 2015How would I iterate through this table and do a cumulative sum on the value column :
I'm trying to get the following result:
ADVERTISEMENT
Transact SQL :: How To Get Cumulative Value In Query
Aug 18, 2015I have table like below, its period wise ,here the value get cumulative period wise.
amtname period
10CHR201202
20TNG201202
10CHR201203
20TNG201203
Transact SQL :: Cumulative Totals - Rolling Window
Aug 14, 2015I'm trying to generate a cumulative total with a rolling window of 13 time periods..Previously I was able to do left outer join to the same table 13 times to add the quantity field but it appears with the migration to SQL Server 2014, that many left outer joins is not possible (query that would run in 3 mins is taking well over 15 hours now)..
View 7 Replies View RelatedTransact SQL :: Cumulative Values Quarter And Half Yearly Wise
Nov 23, 2015Having table like below. Here want to cumulative the values quarter and half yearly wise...
declare @table table
(month varchar(10),
value int)
insert into @table values('apr' ,100 )
insert into @table values('may' ,200 )
insert into @table values('jun' ,300 )
[Code] ....
Like wise the data should added...
Updating Table Column With Cumulative Numbers
Nov 13, 2007Hello,
One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?
Thanks a lot,
Andy
SQL version: 2005, Standard edition.
Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?
Aug 3, 2015How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedTransact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables
Oct 14, 2015I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?
May 1, 2015I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
Transact SQL :: Column Length Restriction When Naming CTE Column?
Jun 22, 2015My CTE is failing and I don't know why...Is there a Common Table Expression column name length restriction???
View 2 Replies View RelatedTransact SQL :: Distinct By One Column By Selecting Multiple Column?
Jul 17, 2015I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1
I supposed to get last row as
cc Zz bab
Instead I am getting
cc Zz rrr
which is wrong
Transact SQL :: Returning A Column Value Based Upon The Precedence Value Of Another Column?
Nov 4, 2015#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View RelatedTransact SQL :: Get Table And Column Name In Separate Column Using PIVOT
Jul 16, 2015Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text QueryPlan Plan_handle
Name Value
select id,name,Address from person <showPlznXML... 010101 Table Person
select id,name,Address from person <showPlznXML... 010101 column id
select id,name,Address from person <showPlznXML... 010101 Table Person
[code]....
Transact SQL :: Calculate SUM Of 100 Of EDSUM Column For EDCOST Column
Aug 18, 2015How I can calculate the 'SUM of 100' of EDSUM column for EDCOST column. Every EDCOST should have sum of 100 on the calculation of EDSUM. I just want to know which is the EDCOST which has <>sum of 100.
Create table #sum (ED numeric, EDCOST numeric, EDName char(6), EDSum numeric, EDCode char(2))
Insert into #sum values (121, 2000,'HLMO',98,'DT')
Insert into #sum values (122, 2000,'HLMT',2,'DT')
Insert into #sum values (123, 2001,'HLMO',100,'DT')
Insert into #sum values (124, 2002,'HLMD',97,'DT')
[Code] ...
Expeced Output:
ED EDCOST EDName EDSum EDCode
126 2003 HLMR 98 DT
130 2005 HLMR 98 DT
Transact SQL :: Calculate Third Column Using Second Column And Variable
Nov 23, 2015create table #t
(
id int,
col1 decimal(18,2)
)
go
[Code] ...
-- I want to subtract @X and col1. But my variable @X must be reduced for each value in col1 for each next row until it reaches zero.
-- OUTPUT:
-- id col1 col2
--@X at starting point is 15000
-- 1 5000.00 0 --@X IS 10000 = 15000 - 5000(col1)
-- 2 1000.00 0 --@X IS 9000 = 10000 - 1000
-- 3 10000.00 1000.00 --@X IS 1000 = 9000 - 10000
-- 4 12000.00 12000.00
-- 5 300.00 300.00
-- 6 35000.00 35000.00
--in col2 i just put zero where col1 is substract from @X and continue for every subsequent order.
-- in 3 row value is 1000 becouse @X is that big (1000 left from col1)
Transact SQL :: Select Row With Max (column Value) Group By Another Column
May 19, 2015i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (
[code]....
Transact SQL :: Add Spaces To First Column So That Second Column Will Be Aligned
Sep 14, 2015I want to add spaces (like space - len(col)) to first column so that second column will be aligned when exported to email (text).
DECLARE @ColumnSpaces TABLE (
Col_1 VARCHAR(50),
Col_2 VARCHAR(50)
)
INSERT INTO @ColumnSpaces VALUES ('AAA', '123')
INSERT INTO @ColumnSpaces VALUES ('AAAAAAAAAAAAAAA', '123')
Transact SQL :: XML Column Data Into Separate Column
Nov 18, 2015I have a column with the data as below :-
<Items>
<Item Value="Value1" />
<Item Value="Value2" />
<Items>
How to get this data into seperate columns as
Items
value1
value2
Transact SQL :: Script One Column To Multiple Column?
Sep 29, 2015I have below dataset and i want to convert as per my requirement.
Dataset:
In the above dataset, if i take 9/5/2015 then i should get like below,
Transact SQL :: How To Add Column
Nov 19, 2015Now I have payment mode and amount column in my sql. I want to add 2 columns which is CreditCard and Cash and post the amount column value accordingly. The current result :
PaymentMode Amount
CASH 1400000.000000
CREDITCARD 953000.000000
My expected Result:
CREDITCARD CASH
953000.000000 1400000.000000
My query :
SELECT dbo.CA_Payment.MRN,
dbo.CA_PaymentModeMaster.Description AS PaymentMode,
Sum(dbo.CA_Payment.ReceivedAmountPrincipalCurrency) AS TotalReceivedAmountPrincipalCurrencyGroupbyMRN ,
dbo.CA_Patient.FirstName,
CASE (CA_Registration.PatientTypeID) WHEN 1 THEN dbo.CA_PatientTypeMaster.Description
[code]....
Cumulative Log
Sep 7, 2007Is it possible to create a cumulative log using SSIS? basiclly I have 5 logs which hold failed records. I would like to create a cumulative log and send it via email using SSIS.
thoughts?
thanks
Transact SQL :: Selecting Rows As Column Name And Other Column Values As Rows
Jun 25, 2015I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies View RelatedTransact SQL :: Passing Column Name Into A Value
Jul 30, 2015I would like to know on how to pass a column name into a particular variable.
Example
Table_name Column_name
Tablenm Name
Tablenm Age
Tablenm Phone
The output will be:
ColName Value
Name Alexander Grahambell
Age 32
Phone 123456
Name Sanosuke Sagara
Age 35
Phone 246790
Transact SQL :: How To Update A Column
May 19, 2015I have a table that has two columns. One column has ID from 1 to 1800. The other column is null. I want to update the second column with values from 29 to 43. So for ID 1, value will be 29, ID value will 30 and it goes to 43. Then after it will start from 29 again and goes to 43. It goes all the way to the highest ID, i.e. 1800.I have added a script for better clarity
declare @t table (stuID int, valueID int)
insert into @t (stuID, valueID)
values (1,null), (2,null), (3,null), (4,null)
the null part is what I want to have from 29 to 43 .... all the way untill the ID reachs to 1800
Transact SQL :: How To Get Total Value As A Column
Jul 14, 2015I have a table like below, which is contain area, zone, branch wise value. In that i want to get company total as a column and area total as a another column and zone total in next column
AREAZONEBRANCHVALUE
TNCENTRALAMB100
TNCENTRALTNF100
TNCENTRALSDGD100
TNCENTRALERW100
TNNORTHAMB50
[Code] ....
My expected output is like below
AREAZONEBRANCHVAL comtot areatot zonetot
TNCENTRALAMB1001200560400
TNCENTRALTNF1001200560400
TNCENTRALSDGD1001200560400
[Code] ...
I achieved this by using query but its will affect performance because my real time scenario dealing with laks of records
select a.*,b.company_tot,c.area_tot,d.zone_tot from FESIBILITY_CHECK a cross join (select SUM(value)company_tot from FESIBILITY_CHECK )b
join (select SUM(value)area_tot,area from FESIBILITY_CHECK group by area)c on a.AREA = c.area
join (select SUM(value)zone_tot,area,zone from FESIBILITY_CHECK group by area,zone)d on a.AREA=d.area and a.ZONE = d.ZONE
So I planned to use cube and roll up but i don't get desired result ...
Transact SQL :: Count As Per Column Value
Jul 2, 2015I have table like below
col1 volume
A 22
B 28
C 1
E 3
D 4
Output should be like below
col1 vol
A 22
B 28
Others 8
Transact SQL :: RANK - Using To Take Only Changes For One Column
Nov 8, 2015I have a table that has the definition from the picture. Let's suppose I will sort the list based on last column (DATA.ORA - DESC). That means the last row inserted is the first one in the sorted list.
What I would like to do is to get only the records when the value of a specific column is different from the row below.
For instance I would like to get the rows where the column WHITE has different values. When the status for column WHITE changes from 0 to 1 or from 1 to 2 etc., then I need that row no matter if next 5 rows has the same status (1).
In the picture you see what the result of query should be:
Result: only the lines where the value of column WHITE is different than the row below.
Cumulative Sum(Urgent)
Apr 26, 2000I'm trying to get the final result using an aggregate function.
Table looks like below;
ID ACRE Probability
1 3 0.3
2 1 0.6
3 6 0.2
4 5 0.5
5 2 0.1
First, I want to descend by probability then select all records that cumulative acre <= 8
So, final result will be:
ID ACRE Probability
2 1 0.6
4 5 0.5
Any idea how to write a script?
Thanks in advance,
Mike Jun
hyunhyo@hotmail.com
GIS reserch Group
University of Colorado
303-492-8781
Cumulative Count
Oct 4, 2005I have to group the no. of employees based on month. i.e
Jan 10 employees
Feb 20 employees
Mar 30 employess
The result is to be
Jan 10
Feb 20
Mar 60.
Kindly provide the sql for getting the above result.
Thanks,
Lakshmi
Cumulative Weeks
Jul 20, 2005SQL Server 2000 SP3Hi,How can I get the cumulative weeks from a givedate to the currentdate. I know I can get the weeknumber by using datepart(wk,getdate())but this will giveme the week number for this year. What if I want to know the number ofweeksthat have passed since june 1 2001. If I use datepart(wk,'20010106') Iwillget the week number for 2001 but I would like the number of weeksexpired between then now.Thanks,Reg
View 1 Replies View RelatedHierarchical Cumulative Sum
Aug 23, 2006I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.
The table is setup as follows:
Parent Child Pop
----------------------------------
Europe France 0
France Paris 1
New York New York City 10
North America United States 0
North America Canada 0
United States New York 0
United States Washington 0
Washington Redmond 200
Washington Seattle 100
World Europe 0
World North America 0
This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.
I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):
World Null 311
World Europe 1
Europe France 1
France Paris 1
World North America 310
North America United States 310
North America Canada 0
United States New York 10
United States Washington 300
New York New York City 10
Washington Redmond 200
Washington Seattle 100
Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.
Any help/ideas would be appreciated! Thank you.
Finding Cumulative Sum
Dec 29, 2007In the emp table :
>
> EMPNO ENAME SAL
> ==================
> 7369 SMITH 1000
> 7499 ALLEN 2000
> 7521 WARD 3000
> 7566 JONES 4000
> 7654 MARTIN 5000
>
> there is a requirement to have a calculated col.
> called cummulative sal
>
> EMPNO ENAME SAL CUMMULATIVE_SAL
> 7369 SMITH 1000 1000
> 7499 ALLEN 2000 3000
> 7521 WARD 3000 6000
> 7566 JONES 4000 10000
> 7654 MARTIN 5000 15000
>
>
How to show this calculated col. by using one select statement?
I could get the result using Empno. in my query as --
select ename,sal,(select sum(sal) from emp k where k.empno <= e.empno) as cum from emp e order by ename
but I was asked to get the same result if empno. col is not there?
SUCKS........... the below code is giving the result but I don't know how you could use operator (<) on two strings to compare......????
select ename,sal,(select sum(sal) from emp k where k.ename <= e.ename) as cum from emp e order by ename
Someone tell me if this is the right approach or is there a better way of getting the thing done.
Transact SQL :: Select Column On Condition
Jul 4, 2015I have a table called names (firstname, lastname, number) then i have a DECLARED variable called @displaynum...If @displaynum id true my select query should select the number column. if the variable is false , only the first two columns are selected.
e.g @displaynum = true
@displaynum = false
Firstname | Lastname | number Firstname | Lastnam
blah | blah | 12345
blah | blah
i am not sure if this is possible as a case still selects the column but just changes the values.
Transact SQL :: Order By A Column From Another Table
Sep 2, 2015I have two tables, one is called (questions), the second one (answers).
questions columns are (ID,questionTitle)
answers columns are (ID,questionID,answer, answerDate)
I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.