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
ADVERTISEMENT
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
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
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
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
View Related
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
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
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
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
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
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
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
Oct 10, 2001
Hi all!
How can I calculate the combined table size for the following:
The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.
Thanks,
ndba
View 1 Replies
View Related
Jan 29, 2008
this is the table i have created .
CREATE TABLE [dbo].[Table] (
[BCode] [varchar] (3) ,
[RefNo] [varchar] (12) ,
[RType] [varchar] (2) ,
[TheirRef] [varchar] (50) ,
[Amount] [money] NULL ,
[AccountNo] [varchar] (20) NULL ,
[EnteredDate] [datetime] NULL ,
[EnteredBy] [varchar] (6) ,
[IsTrue] [varchar] (1) ,
[RDate] [datetime] NULL ,
[Details] [varchar] (255) ,
[PostBy] [varchar] (6) ,
[SeqNo] [int] IDENTITY (1, 1) ,
[ThisCode] [varchar] (3) ,
[VDate] [datetime] NULL ,
[Id] [varchar] (20) ,
[Flag] [varchar] (1) ,
[TDate] [datetime] NULL ,
[Type] [char] (1) ,
[SerialNo] [int] NULL
) ON [PRIMARY]
GO
if i insert a single row in a table with values :
INSERT INTO [Table]
SELECT '100','1','DD',1000000,'999999000',
'2008-01-29','sa','F','2008-01-29','PARTIAL DATA','11502',
'101','2008-01-29','ABC1111111','T','2008-01-29','S','11204'
EXECUTE sp_spaceused 'Table'
name | rows |reserved | data |index_size | unused
[Table] | 1 |16KB | 8KB | 8KB | 0KB
i couldnot understand
i) how the system calculate this 16 KB and 8 KB ?
ii) if i changed the VARCHAR(255) to VARCHAR(50) ,will there be difference in size of the table ,if so then how ?
iii) how can i see the change in size of table by reducing the size of datatype ?
can anybody help me ?
View 3 Replies
View Related
Mar 10, 2008
I'm trying to grab the Total for each "brand" in my table group (this is not in a matrix).
Period 1 Period 2 Period 3
Sensible Choice 2007 3843 4020 4746
2008 1830 3352 1025
Total: 1830
Maxi 2007 124388 132248 162429
2008 127729 194458 13732
Total: 127729
I want to make the total the same amount as the 2008 total per period. (everything in yellow is the group 1 (outer group and the red is the inner group. When i tried to get the value for the year 2008 and put it in the total field, i put Last(Fields!PeriodTotal.value), but it doesnt work, because sometimes it gives me the 2007 total, and sometimes it gives me the 2008 total. I dont know why. Any suggestions on how i can get the 2008 value everytime.
View 1 Replies
View Related
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
Apr 29, 2015
Now a sample table is included and an expected result list. I also added a piece of the sql to obtain the results.
The remaining question is how to calculate the total break time in minutes. The first two columns of the results are already in the code.
Sample of table clock
employee check_in check_out
----------- ---------------- ----------------
1 08:00:00.0000000 11:00:00.0000000
2 08:30:00.0000000 12:14:00.0000000
2 12:25:00.0000000 16:00:00.0000000
1 11:30:00.0000000 14:00:00.0000000
sample of table breakt
startt endt
---------------- ----------------
09:45:00.0000000 10:00:00.0000000
12:00:00.0000000 12:30:00.0000000
The result I am searching for:
| Employee | Timemin| Breakmin|
+----------+--------+---------+
| 1 | 180 | 15 |
| 2 | 224 | 19 |
| 2 | 215 | 5 |
| 1 | 150 | 30 |
+----------+--------+---------+
DROP TABLE breakt;
CREATE TABLE breakt(
startt TIME
, endt TIME
);
INSERT INTO breakt(startt,endt) VALUES ('09:45:00','10:00:00');
[Code] .....
View 3 Replies
View Related
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
Mar 28, 2008
I have a sum , which is filtered within its table group, and then i have a fields in the footer. How would i aggregate these too together and use the result in another row within the footer.
I tried entering this:
=SUM(Fields!Period_1.Value,"table2_Period") / Max(Fields!Goal_1.Value)
but i get an error:
[rsInvalidAggregateScope] The Value expression for the textbox €˜textbox129€™ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
Build complete -- 1 errors, 0 warnings
what am i doing wrong, please help!
View 1 Replies
View Related
May 9, 2014
I am a student, and I am so confused by SQL code that calculates incomes of my contract in a year or each month of year.
I have 3 tables:
lodgings_Contract:
id_contract indentity primary,
id_person int,
id_room varchar(4),
day_begin datetime,
day_end datetime,
day_register datetime
money_per_month money
electric:
id_electric indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_electric money,
status bit
Water:
id_Water indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_water money,
status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year'
, Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes'
From lodgings_Contract a
, electric b
, Water c
Where a.id_room = b.id_room
And a.id_room = c.id_room
And b.status = 1
And c.status = 1
Group by Year(day_register)
View 4 Replies
View Related
Nov 4, 2015
I tried using the page below but wasn't able to quite properly adapt it to my scenario
[URL]
I have two UNRELATED (can't be related for reasons outside the scope of this, other relationships already exist) tables.
Each table does, however, contain a WorkerID:
WorkerTimesheets:
WorkerID, HoursLogged, Date
3,10,2015-05-05
3,6,2015-05-10
4,8,2015-05-01
Then the unrelated table "ConstructionSites"
SiteID, SiteManagerID, SiteOpen,SiteClosed
A5, 3,2015-01-01,BLANK
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
View 3 Replies
View Related
Sep 4, 2015
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
How can i do that in a query?
View 5 Replies
View Related
Aug 4, 2015
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
View 9 Replies
View Related
Mar 17, 2014
This seems simple enough but for some reason, my brain isn't working.
I have a lookup table:
Table A: basically dates every 30 days
1/1/2014
2/3/2014
3/3/2014
4/3/2014
I have Table b that has records and dates created assocated with each record
I want all records that fall between the 1st 30 days to have an additional column that indicates 30
union
records with additional column indicating 60 days that fall between the 30 and 60 day
union
records with additional column indicating 90days that fall between the 60 and 90 day mark.
Is there an easy way to do this?
View 6 Replies
View Related
Oct 28, 2014
I am trying to move data from one table to the another (staging to real time) in a stored procedure.
There are no indexes or primary keys on the target table and it is still taking ages to execute it (30 minutes approx.). There are no defaults, no constraints as well. There is one identity int column though.
There are some 500000 odd rows in the target table.
I am using the
Insert into..
Select from..
method.
View 2 Replies
View Related
Mar 19, 2015
IF OBJECT_ID('tTable') IS NOT NULL
DROP TABLE tTable
GO
CREATE TABLE tTable
(nRow_IdINTEGER IDENTITY NOT NULL PRIMARY KEY,
nParent_IdINTEGERNULL,
[Code] ....
gives:
nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C
I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows
4 4 Two A
5 4 Two B
6 5 Tow C
View 5 Replies
View Related
Jul 8, 2014
I have to tables say 'employee1' and 'employee2'
employee1 has lastname
employee2 has firstname, lastname
i have to update firstname in employee1 from firstname in employee2 table and the common field for both tables is 'lastname'
View 8 Replies
View Related
Jun 9, 2014
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
View 3 Replies
View Related
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
May 18, 2015
I have a more than 50 departments in my table and trying to get top 10, something I need to show in a report like top9 departments and then rest of them has to show as Others in 10th row. is it possible ?
when I use top 10 from table I am getting top 10 rows but I need the top 9 list and rest as others in 10th row.
I have table like below
Custid, department, location
1 Fin NY
2 Acc NY
3 HR MI
4
5
6
View 3 Replies
View Related
Nov 19, 2014
In SSMS, I used the ALTER TABLE statement to add a field to a table. No errors are generated and I can verify that the field has been added. Since there are only about 20 records in the table, I use the Edit All Records option to populate that new field. Later, when a job runs using that new field, it fails because the field suddenly does not exist. Sure enough, I check the table and the field is gone. To be more specific, I can add and populate the new field at 2pm. At 6pm, when the job runs, the new field is apparently gone.
I have searched for answers but only have found responses related to the "Prevent saving changes that require table re-creation" in the Tools|Options settings in SSMS. I did uncheck that option, but the problem persists. Based on my searches, the only other alternative is to recreate the table with the new field and move the data from the "old" table to the "new". I got the impression that unchecking that option would mean I would not need to do all this.
I know I have done this type of change before with no issues. What may have changed?I am using SQL Server 2008R2 SP2 on Windows Server 2008R2 SP1.
View 9 Replies
View Related
Dec 16, 2014
I've the table structure below. Example table of my original
create table fms
(
fs_locn char(100),
fs_account_no varchar(200),
fs_cost_center_no varchar(100),
fs_tran_type char(50),
fs_post_amt float,
fs_tran_date datetime
[Code] ....
Expecting Output :
Account 200Prod 201PROD ProdcutionCost
E002-SW100-2100 2500 1000 3500
How to do that?
I tried like
select
k.fs_acoounts,
k.200Prod,
(
my query
)k
its showing error '200prod'
View 1 Replies
View Related
Feb 4, 2015
What I want is to divide the row value for 'OB Dial Attempts' by the row value for '# Ready To Work Inventory', both in the same table.The code below will work, however I think there is a flaw in my logic. The actual table only has one row per category (MatrixCat) and will always have only one row per category.
CREATE TABLE #NumVals (MatrixCat VARCHAR(100), MatrixVal VARCHAR(100));
INSERT INTO #NumVals (MatrixCat, MatrixVal) VALUES
('# Ready To Work Inventory','606'),
('OB Dial Attempts','255');
[code]....
View 8 Replies
View Related
