T-SQL (SS2K8) :: How To Calculate Cumulative Numbers

Feb 6, 2015

I want to show cumulative numbers, but don't know how to calculate them.

Here is an example of the source and the wanted result:

Source:

[Week] [Count]
1 15
2 5
3 6
4 10
(until 52)

Result:

[Week] [Count]
1 15
2 20
3 26
4 36

Is this possible, and how?

TestData:

USE TestDb /*SqlServer 2005*/
CREATE TABLE Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO Test ([Week], [Count]) VALUES (4, 10)

View 8 Replies


ADVERTISEMENT

How To Calculate Cumulative Of Months

Aug 2, 2014

How to calculate the cumulative of months.

Jan FebMar AprMay JunJul AugSep Oct Nov Dec
321 394571 577 617 692924 944956 1010 1308 1686

if i execute my query in this month(getdate) then it should sum from Jan to Aug Similarly i execute same query it should sum from Jan to Sept So on.

View 1 Replies View Related

Calculate Cumulative Amount From Table

Sep 2, 2014

I need to calculate cum amount from the following table.

CREATE TABLE #TotalRevenue_Investments
( [Month] INT,[Year] INT,TotalRevenue INT,Descr VARCHAR(100),Company VARCHAR(100))
INSERT INTO #TotalRevenue_Investments
( Month ,
Year ,
TotalRevenue ,

[Code] ....

AND so ON ..

I need the OUTPUT AS FOR example

SELECT 1 AS Month,2014 AS Year,12 AS cumAmt,'Late Sales' AS Descr,'US Late Sales' AS Company

View 3 Replies View Related

Updating Table Column With Cumulative Numbers

Nov 13, 2007

Hello,

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.

View 1 Replies View Related

How Do I Calculate The Sum Of Each Number In A String Of Numbers?

Jul 20, 2005

I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks

View 5 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) :: Display Numbers 0-9

Aug 8, 2014

I am having query where i am calulating datediff value, but i want to display resultser as below:

if datediff = 4 then resultset would be
0
1
2
3
4

if datediff = 9 then
0
1
2
3
4
5
6
7
8
9

how can i implement this?

View 2 Replies View Related

T-SQL (SS2K8) :: Calculate Time In Minutes

Oct 8, 2014

I am having below schema:

CREATE TABLE #Attendance(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StudentID] [int] NOT NULL,
[ClassID] [int] NOT NULL,
[DateAdded] [datetime] default getdate() NOT NULL
) ON [PRIMARY]

insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:38:02.900')

[Code] ....

DateAdded column in first table is nothing but in and out time.

Now I want to prepare a query where I want to consider MIN DateAdded and max DateAdded and calculate the duration of student present in the class.

Validations i need to consider are:

If class is starting at 10am then student can come at 9:50am, i.e. Dateadded column should consider as student present in that class if value is less that 10 minutes of StartTime from #ClassAttendance table. Class End time i want to calculate depending upon ClassMinutes from #ClassAttendance

Also DateAdded column should be 10 minutes plus compared to calculated endtime. If its more than that consider lower DateAdded time.

And by using this thingIi want to calculate total number of minutes student present in the class and number of minutes absent.

If there is only one DateAdded for class then consider as a absent student.

View 7 Replies View Related

T-SQL (SS2K8) :: Expression To Calculate Age On Server

Apr 16, 2015

I have been trying to calculate age and the results either round the age up one year or down one year. I have tried CASE, DATEDIFF, FLOOR functions but nothing works.

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate PS1 And PS2 Time From Two Tables

Sep 3, 2015

I have 2 tables as defined below. I want to calculate PS1time and Ps2 time.

Table 1
O_IDP_TYPEP_startdateP_enddate
ABCP8/24/2015 13:148/24/2015 13:41
ABCP8/24/2015 14:038/24/2015 15:31
ABCP8/25/2015 12:098/25/2015 13:25
XYZP8/28/2015 13:108/28/2015 21:44

Table 2
O_IDS_TYPES_startdateS_enddate
ABCS28/24/2015 13:148/24/2015 19:22
ABCS28/24/2015 19:228/30/2015 21:34
XYZS28/27/2015 22:228/28/2015 13:10
XYZS28/28/2015 13:108/28/2015 15:34
XYZS18/28/2015 15:348/28/2015 22:44

OUTPUT
O_IDSP_TYPEPS1_starttimePS1_starttime
ABCPS18/24/2015 13:148/24/2015 19:22
XYZPS18/28/2015 15:348/28/2015 21:44
XYZPS28/28/2015 13:108/28/2015 15:34

For Each O_Id How much time spent for Ps1 and PS2. I tried but not able to reach expecting results as mentioned.

View 3 Replies View Related

T-SQL (SS2K8) :: Update Field Value For Numbers Only

May 13, 2014

Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:

Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223

Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999

There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.

View 8 Replies View Related

T-SQL (SS2K8) :: Generate Combination Of Numbers?

Jun 19, 2015

I want to get "all combinations of the same count" for provided list of numbers.As an example, if I have a string 'NA,T1,T1a'

For this string, I want to generate following:

NA,T1,T1a
NA,T1a,T1
T1,NA,T1a
T1,T1a,NA
T1a,NA,T1
T1a,T1,NA

query to achieve the same or if the solution is already available, provide me with the links.

View 7 Replies View Related

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

T-SQL (SS2K8) :: Calculate On Newly Created Columns

Aug 2, 2014

I have a table that I have created a table and desire to do some basic math by adding a few new columns. The problem is that i cant get this to work without create many new select statements. The new columns that I wish to add refer to other newly created columns. Is there a way I can do this with CTW or subqueries? Unless it is a best practice to chain out the logic for the newly created columns

I have an example from AdventureWorksDW since the data is very accessible. I can safely create EMP_TENURE and PTO_REMAINING is this select statement. I would then need to create a new select statement to define 'BONUS' and then another select statement to define 'NEW_COL1' and so on.

Im still pretty new at SQL and am trying to learn how to complete such a task using subqueries or CTE.

SELECT
NAME = [LastName] + ',' + [FirstName]
,HireDate
,Title
,DepartmentName
,BaseRate
,VacationHours
,SickLeaveHours

[Code] ....

View 1 Replies View Related

T-SQL (SS2K8) :: Calculate And Display Week Between Two Dates

Nov 4, 2015

I want to display week between two dates as below.

requirement is as:

suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016

Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

Say for example the column date is coming as 10/23/2015 then it will fall in week-1

Same way if 11/01/2015 falls in week2.

View 3 Replies View Related

T-SQL (SS2K8) :: Calculate And Return Previous Date At 18:00 Hours

Mar 4, 2014

How can I calculate and return the previous Date at 18:00 Hours?

Here is a miserable attempt:

DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()

SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate

-- Desired Result is the following:

-- 2014-03-03 18:00

View 9 Replies View Related

T-SQL (SS2K8) :: Function To List Distinct Pax Name And Ticket Numbers?

Mar 14, 2014

I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.

The data is:

Branch Folder ID Pax TktNo BookingRef
HQ 123 1 Jim 4444 ABCDE
HQ 123 2 Bob 5555 ABCDE
HQ 123 3 Jim 6666 ABCDE
HQ 123 4 Bob 7777 ABCDE
HQ 124 1 Jenny 8888 FGHIJ
HQ 124 2 Jenny 9999 FGHIJ
HQ 124 3 Jenny 3333 FGHIJ

I somehow need to get a function to pull the data out for each booking ref like so

--BookingRef ABCDE
Jim 4444/
6666
Bob 5555
7777
--BookingRef FGHIJ
Jenny 8888/
9999/
3333

I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.

View 4 Replies View Related

T-SQL (SS2K8) :: Select Query With Records And Sequential Numbers

Apr 5, 2014

I have a problem. In my database I have the following numbers available:

101
104
105
110
111
112
113
114

What I need is to get a select query with records and sequentials numbers after it like:

101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0

How can I do It?

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

T-SQL (SS2K8) :: Stored Procedure - Calculate Closest Date For Manufacturing

May 5, 2015

I'm trying to write a Stored Procedure that have to calculate the closest date for manufacturing.

What I have:

- The BOM (bill of materials) with the needed quantity for production

DECLARE @BOM TABLE
(
ItemIDINT
,neededQuantityfloat
)
INSERT INTO @BOM (ItemID, neededQuantity)

SELECT 1, 10
UNION ALL SELECT 2, 10
UNION ALL SELECT 3, 5

- a calculated table that told me the availability for each component of the BOM, sorted by date. (each row have a plus or minus of the quantity so it can by summarized)

DECLARE @WhareHouseMovement TABLE
(
ItemIDINT
,Quantityfloat
,DateDATETIME
)

INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date)
SELECT 1, 10, '2015-03-01'

[Code] ....

My question is: how do I check when is the closest date to manufacturing? I have to check that the quantity of ALL the components of the BOM is enough to produce the product, but I can't get how to do it.

If I'm not wrong the example should give the result 2015-03-26.

View 9 Replies View Related

T-SQL (SS2K8) :: Distribute Data Into Groups Based On Existing Numbers?

Aug 11, 2014

i've been looking at moving one of our processed from excel (+vba) into t-sql to make life easier but am stuck.

We have lots of tasks that are assigned to work groups which we want to distribute evenly across the work groups. This is a simple task for ntile.. However when these tasks are no longer required they are removed which leaves the groups uneven. When new tasks are added we want to still try to keep these groups balanced.

EG Existing groups :

GroupName - Task Count
Group1 - 1000
Group2 - 999
Group3 - 998

If we were to add 6 new tasks they would have more assigned to Group 2 & 3 as they have less than group 1.

Task 1 - Group3
Task 2 - Group3
Task 3 -Group2
Task 4 - Group1
Task 5 - Group2
Task 6 - Group3
Sample tables
Create table GroupTable
(GroupID int, Name varchar(200) )
Insert into GroupTable values (1,'Group1')
Insert into GroupTable values (2,'Group2')
Insert into GroupTable values (3,'Group3')

Create table Jobs(jobid int identity(1,1), name varchar(100),Groupid int)

--Existing tasks

Insert into Jobs(name,Groupid) values ('Task1',1)
Insert into Jobs(name,Groupid) values ('Task2',1)
Insert into Jobs(name,Groupid) values ('Task3',1)
Insert into Jobs(name,Groupid) values ('Task4',1)
Insert into Jobs(name,Groupid) values ('Task5',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task6',2)
Insert into Jobs(name,Groupid) values ('Task7',3)

-- New tasks

Insert into Jobs(name) values ('TaskA')
Insert into Jobs(name) values ('TaskB')
Insert into Jobs(name) values ('TaskC')
Insert into Jobs(name) values ('TaskD')
Insert into Jobs(name) values ('TaskE')
Insert into Jobs(name) values ('TaskF')

This gives us 6 unassigned tasks and a uneven group assignment

GROUPNAME TASK_COUNT
<none> 6
Group1 4
Group2 3
Group3 2

This means the new tasks will be assigned like this

TaskA - Group3
TaskB - Group3
TaskC - Group2
TaskD - Group1
TaskE - Group2
TaskF - Group3

View 5 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

T-SQL (SS2K8) :: Get Week Numbers From Dates - Saturday Being Start Of Week

Sep 17, 2015

i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday

CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES

[code]....

The output should look like this

weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000

View 4 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Generate List Of All Numbers (numbers Not In Use)

Feb 21, 2007

I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:

select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)


but don't know how. Any clues?

View 1 Replies View Related

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error

Mar 27, 2007

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.


So close.. Thanks for any help or information.

View 1 Replies View Related

Cumulative Log

Sep 7, 2007

Is 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

View 6 Replies View Related

Cumulative Sum(Urgent)

Apr 26, 2000

I'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

View 1 Replies View Related

Cumulative Count

Oct 4, 2005

I 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

View 6 Replies View Related

Cumulative Weeks

Jul 20, 2005

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







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