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)
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?
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
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.
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.
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.
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 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.
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.
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)
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.
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.
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
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.
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:
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,
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 ?
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.
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)
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?
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