Timespan = 30 days
Start Date = January 1st
Last Processed Day = NULL
Next Cycle Day = IF(Last Processed Day IS NULL)
Start Date + TimeSpan
ELSE
Last Processed Day + TimeSpan
Is it possible to setup a column to do this from sql?
Hi,I have a select query that returns three integer fields from a table thevalues range from 0 to 5. On each row I would like to calculate the averagevalue in the three fields however, the difficulty is that only the rowswhere the value is greater than 0 should be included in the calculation.To make this clearer please consider the following example:Col1 Col2 Col2 Average Calculation1 2 3 2 (Col1+Col2+Col3)/32 2 0 2 (Col1+Col2+Col3)/2The first row should be divided by 3 because each of the three columns has avalue greater than 0, however the second row should be divided by 2 becauseonly two of the value are greater than 0.Is this possible?Also is is possible to pass the results of one calculation into anothercalculation.Thanks in advance,Steve
View 3 Replies View RelatedIf i want to split a computed column into two or more columns based onthe the length (its a varchar) of the computed column, how often willsql server determine what the computed column is? roughly along thelines ofselect comp_col,'comp_col 2'=case when datalength(comp_col)<=100 then comp_colelse left(comp_col,99) + '~' end,datalength(comp_col)from aTableAs you can see, in this scenario we use the computed coulumn,comp_col, in a few places, so does SQL server need to calculate thiseach time? I'm playing with this on the basis that it does and thustrying to shift the computed column out to a variable and thenmanipulte and return from their, but that has its own problems whenyou throw in additional parameters (trying to join table udf's) so ifSQL server is smart enough to not calculate the column each time Iwould save a lot of hassle?Cheers Dave
View 9 Replies View RelatedHi,
I want to have a query where in i can use alias column names in the same query.
like eg
select 1 as 'a', 2 as 'b', a+b as 'c'
note that this query is getting big and is using sub queries.
Kindly help.
Thanks
I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
Some DB Field ID
X
2007
2008
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
A
X-A
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
B
X- A -B
1
500
10
20
0
0
0
0
0
0
0
20
50
0
100
400
25
10
10
0
0
5
0
25
15
10
10
20
130
270
2
750
10
10
10
20
20
10
10
20
10
10
10
10
150
600
20
20
20
20
30
30
10
10
10
30
30
30
260
340
3
600
All,
I am trying to achieve something as above. Basically, the Months subtotals are represented by A and B. Then (X-A) and (X-A-B) are also the subtotals at the same group level as A and B but don't simply display the total for respective years 2007 and 2008, instead those are remaning totals from X. In order to calculate the remaining totals however, one need to consider the subtotal in previous group. For example, for 2007 its X-A, but for 2008 its X-A-B. I would like to know if this can be achieved using Matrix control. If so, what would be the steps?
Thanks.
Hello all. I am trying to do a calculation within an SQL script, however it doesnt seem to be working and i'm a little bit lost. If anyone could shed some light on where i'm going wring it would be much appreciated. The code I have is:
select
EMPLOYEE.EMPLOY_REF AS EDIT_REF,
SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT,
SV_EMPLOYEE_CURRENT_HOLIDAY.CARRIED_FWD,
SV_EMPLOYEE_CURRENT_HOLIDAY.TAKEN,
SV_EMPLOYEE_CURRENT_HOLIDAY.REMAINING,
SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD,
SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED,
SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT + SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD - SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED AS TOTAL_ENTITLEMENT
from
EMPLOYEE
left outer join
SV_EMPLOYEE_CURRENT_HOLIDAY
on
EMPLOYEE.EMPLOY_REF = SV_EMPLOYEE_CURRENT_HOLIDAY.EMPLOY_REF
where
EMPLOYEE.EMPLOY_REF = = 027
Incidentaly SV_EMPLOYEE_CURRENT_HOLIDAY is a view which currently exists.
Thanks in advance people.
In order to find out if an event is late or not I need to do some time calculations in SQL as a Stored procedure.
I have a DateTime variable called Due
I also have an Allowance variable which is an integer and is an extra allowance for that day and a third variable Now which is set with GETDATE()
If I compare Now to Due I can decide if the task is late or not - but I need to take itno account the Allowance.
I tried :
IF @Due + (@Allowance /24) < @Now ......
However I find that @Allowance/24 always equates to zero so this doesn't work.
I'd appreciate any advice.
Regards
Clive
Hello,
I ran into a little problem. My problem is: i need to substract 2 variabeles from 2 different tables in the database
TitleTimes left todayTimes left
My first excercise!15
My second excercise!19
The fields times left are a calculation... the number of times that the admin entered minus a count in the table scores.
Has anyone an idea how i can solve this?
An example excercise would be great!
Thanks in advance
I am attempting to construct a SELECT statement which incorporates some variables. The variables begin life as strings (not String objects) looking like :"6/08/2008" and "06/10/2008" for example. The first is a start date which was retrieved using an AJAX calendar object and the second is an end date retrieved in the same manner. My records are all timestamped by MS SQL (2003) including the clock time. I am stumbling on the syntax. "CallStartTime" is the record's timestamp. The "TraversalString" is something else but I am not attacking that yet. Can anyone make a suggestion or two?
SELECT count(*)FROM RealTime WHERE CallStartTime >= '@starttime' AND CallStartTime <= '@endtime' AND TraversalString LIKE '%1.0%'
I have a timesheet table and I am having trouble getting a calculation to work correctly. I'd like to subtract the punch-in times from the punch out times for a specific period, such as 1 week, and then add the time together and get the number of minutes worked altogether during that time.
The table is simply
Employee ID (int)
PunchIn (datetime)
PunchOut (datetime)
Thanks for any help.
EL
I am trying to do a calculation to find rows which have a date which is 2 days older the the getdate().
i.e Select documentdate from table where documentdate < (getdate() - 2)
Any ideas on how I can perform this operation.
Thanks
Vinny
Hi,
I think that I want to want to build a temporary table in a stored procedure that handles multiple calculations.
I'll try and explain and hopefully you can tell me what i want
First i want to populate a list of all dealerstaff.
Code:
Select * From tblDealerstaff
I Then want to calculate the percentage tasks completed. So i find the number of available awards.
Code:
SELECT TOP (100) PERCENT COUNT(NoPerStaff) AS NoAwardsAvailable
FROM dbo.tblIndivAwards
Then i can find the number awards recieved per person.
Code:
SELECT TOP (100) PERCENT COUNT(dbo.tblIndivAwarded.AwardID) AS AwardCount
FROM dbo.tblIndivAwarded INNER JOIN dbo.tblIndivAwards ON dbo.tblIndivAwarded.IndivAwardID = dbo.tblIndivAwards.IndivAwardID
WHERE (dbo.tblIndivAwarded.staffID = @StaffID)
Trying to get my recordset to look like this >>
Fname + Lname + ((iNoAwarded / iNoAvailableAwards) * 100)
I want to know if it's possible to piece all of this calculation into a single stored procedure. I need to do this sort of thing all the time, as i display overviews of the projects i run to the senior managers.
I always do the maths in the ASP, and consequently i can't sort on the calulated totals.
Is this possible, or am i asking too much ?
Thanks in advance, as any help will be greatly recieved.
Regards,
Chris Brown
Hi
I sometimes find myself in the situation where I want to insert a row into a table using the following form:
insert table ( <field list> ) select <field list> from .. etc .. Where <conditions>
My question is to do with where one or more of the fields in the select field list are calculations and where I also want to use some/all of these derived fields as Where conditions. [ Eg: only insert if the calculated value is > 0]
I currently either repeat the calculation in the Where clause or move it to a function and use the function call in both places. (I always get a pang of guilt using either option - repeating the calculation feels like bad practice - & using the function twice seems inefficient (does this get optimised?)).
I could get a life & stop worrying - but is there a better/neater way of doing this?
Many thanks.
Vikram writes "Hi,
I need some help in date manipulations on SQL server data.
I am required to calculate to see
1. If anyone is absent one day within a 30 day period. if they are then upon their next absence in the same rolling 30 day period they need to be move to step I of disciplinary stage.
this information has to be checked for hundreds of employees.
I tried many ways but am not getting the right results. Moreover upon defaulting they will have to be moved to differnt steps.
Any suggestions will be greatly appreciated.
thanks in advance
Vik"
Hello,
I am a student and I have an assignment in for next week and we are having a few problems... below is some code we have created to enter in payment details for a customer....
INSERT into Payments values(Payments_seq.nextval, initcap('&Payment_Method'), '&Amount_Payable',
Date_Due = (select date_of_order from orders where order_no = (date_of_order+7));
I am having trouble with the last line, I want the date due to be calculated from the date the order was made in another table (orders) and I want a week to be added to this so that in the field it will display (date ordered plus 7 days)...
Any help you can give would be great, I've only been using isql plus for about 4 weeks...
Thanks
Pam :)
Users,
My question is about the number of calculation made with my database and my query.
The situation:
Table1: Contains 900 projects
Table2: Contains 13500 timesheets
Table3: Contains 516 Periods
Then there are 3 queries:
Query 1: Calculating the total for the periods
Query 2: Calculating working hours + working hours * total of a period
Query 3: Calculating loancosts of a project.
Query 3 is using 2 and 2 is using 1.
How many calculations are made in total????
Thanks!
Willio
Hello all.
I am looking to perform a calculation and enter the reult into a field within my table. The fields that I need to base the calculation on are all in one table (SALARY). The fields are: SALARY and BASIC_HOURS and the result is to be entered into field HOURLY_RATE. The actualy calculation to be preformed is:
hourly rate = ()salary / 52)/basic hours)
Thanks for reading :)
where should be the calculations be made based on performance, in SQL or in front end?
thanks.
-Ron-
hi,i ran the follow command:select #tempra.ranumber,#tempra.amountdue,#tempquickrec.t otalrec,#temparap.amountpaid,bal=amountdue - totalrec+amountpaidfrom #tempraleft join #tempquickrec on #tempquickrec.hrr = #tempra.ranumberleft join #temparap on #temparap.ranumber = #tempra.ranumberresult:ranumber amountdue totalrec amountpaid bal1222 $1200 $1000Null null2222 $3000 $3000 Null null3333$3000 $5000 $2000 0i know null is not zero thus bal column is null.this is because there is no corresponding record in the #temparap tableso how do i obtain the following result:(ie Ranumber bal is not 0)ranumber amountdue totalrec amountpaid bal1222 $1200 $1000Null 2002222 $3000 $3000 Null 03333 $3000$5000 $2000 0i am using ms sql2000thanks in advancerashid
View 2 Replies View RelatedI am looking to calculate the difference between and event time and a sampletime of Now. This is the query that I thought would do it, however I'mreturning DIFFERENCE values that look the same when the calcuation is beingmade on different EVENT_TIME values....I thought I knew how DateDiff worked, but apparently not.select GetDate()as NOW,event_time,Datediff(s,(Cast(event_time asNumeric)),(Cast(GetDate() as Numeric))) as DIFFERENCEFROM events-----------NOW----------|-----EVENT_TIME------|-DIFFERENCE2004-06-30 11:22:38.560 1999-10-30 23:51:37.000 1472256002004-06-30 11:22:38.560 1999-10-30 23:23:47.000 1472256002004-06-30 11:22:38.560 1999-10-30 06:49:38.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:00.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:41.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:59.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:58.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:53.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:46.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:49:42.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:36.000 1473120002004-06-30 11:22:38.560 1999-10-30 06:50:07.000 1473120002004-06-30 11:22:38.560 1999-10-30 10:54:37.000 1473120002004-06-30 11:22:38.560 1999-10-30 11:40:15.000 1473120002004-06-30 11:22:38.560 1999-10-30 09:52:51.000 1473120002004-06-30 11:22:38.560 1999-10-30 12:12:46.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:45.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:45.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:32:46.000 1472256002004-06-30 11:22:38.560 1999-10-30 12:46:30.000 1472256002004-06-30 11:22:38.560 1999-10-30 15:31:25.000 1472256002004-06-30 11:22:38.560 1999-10-30 23:08:25.000 1472256002004-06-30 11:22:38.560 1999-10-30 16:35:51.000 147225600Can someone help?TIA!Joe..
View 2 Replies View RelatedI have a field that contains date information, and sometimes timeinformation as well. I would like to be able to take that date and do acalculation on it. Here are some examples of what is in the field:01/12/2003 5:04:00 PM24/11/200319/05/2003 6:30:00 AMHow can I take that date, then do a calculation like minus 5 days from thedate. I understand that I am to use the GETDATE() function, but below isthe SQL I have implemented.SELECT Field1, Field2, Field3FROM Table1WHERE (convert(char(10),Field1) like convert(char(8), GETDATE()-5))For some reason this works, and it will return results that occur on thisday, but it disregards the year. Now someone will probably ask "Whyconvert, char(10), etc". To be honest, I do not know and I ended upimplementing it from some other Usenet posts that are out there. I wastrying to figure this out and I ended up with that working until I laterrealized it was only caring about the day and month. Any ideas what I amdoing wrong here? I just want to return results that have the day being 5minus the current day. I am not interested in time information.Thanks if anyone can help, I am by far not experienced in SQL.
View 1 Replies View RelatedI'm running SQL query to caluclate projected food costs. Thecalculation is this:(ReportedFoodSales / PlanFoodSales) * FullPlanFoodSalesSeems simple enough to me.Using the following numbers, SQL comes up with a different answer thanwhat I do with a calulator. The data types are money. I'm sure thereis some reasonable explanation....right?Reported Food Sales: 28096.4500Plan Food Sales: 28608.4167Full Plan Food Sales: 137702.0000SQL Answer: 135237.1342Calculator Answer: 135237.7308Any ideas?Thanks,Jennifer
View 3 Replies View RelatedHi -
I'm working in Visual Studio 2005 and created an Analysis Services Project.
Using the Business Intelligence Wizard, I created a "Year To Date" calculation. The Year to Date calcualtion appears to work fine.
My problem is that I'd like to edit the calcualtion but most of the icons on the Calculations Tool Bar are disabled. How does this happen and how can I enable them?
Thanks, Mark
Hi--
I am relatively new to SQL Reporting Services and have a couple basic questions (I think they are basic).
First, lets say that I have the following fields in the body of my report: Sales, Cost, Profit, and Percent Profit. Percent Profit is a calculated field of profit/sales.
Then, I have a grouping by customer, with a footer with the sum of sales, sum of cost, sum of profit. And I also want percent profit, but I do not want a sum or average of percent profit. Instead I want percent profit calculated as sum of profit/sum of sales. I cannot seem to get this to work with a calculated field. I must have the syntax wrong (as I was just taking sum(fields!profit.value)/sum(fields!sales.value)*100.
Also, I understand the format of #,# can be used to print a blank instead of zero. But this format also suppresses any decimals. So if I have a field that needs two decimals when there is a value, but need it to be blank when 0.00 is returned, what is the appropriate format?
Any help that can be provided would be much appreciated. As books are great, but sometimes do not explain everything. Thanks!
-Christina
Hi All,
I'm a Geomechanics student and newcomer to SQL. I'm currently working on a project and using SQL as my data analysis tool. Below are some of the sample results from instrument reading. I would really like to have your assistance to create a summary from the data (table) provided below. Further info about summary table is also provided.
=========
create table dbo.t1 (
[PK] varchar (20),
[date] datetime,
[Location] varchar(20),
[Weigth] float,
[Readings] float,
[Days_inc] float
)
go
set nocount on
insert into dbo.t1 values( ' DAFN25S ' , ' 1/1/2004 12:00:00 AM ' , ' A-23 ' , ' 22.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DANS37J ' , ' 1/2/2004 12:00:00 AM ' , ' A-23 ' , ' 27.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' FKFH34U ' , ' 1/3/2004 12:00:00 AM ' , ' A-23 ' , ' 29 ' , ' 0.0083 ' , '3' )
insert into dbo.t1 values( ' DRCY55O ' , ' 1/16/2004 12:00:00 AM ' , ' A-23 ' , ' 23.2 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DRKR13T ' , ' 1/17/2004 12:00:00 AM ' , ' A-23 ' , ' 31.9 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TRKR54Y ' , ' 1/18/2004 12:00:00 AM ' , ' A-23 ' , ' 29.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' RTYT24T ' , ' 1/19/2004 12:00:00 AM ' , ' A-23 ' , ' 23.8 ' , ' 0.0244 ' , '4' )
insert into dbo.t1 values( ' RKCB26T ' , ' 1/24/2004 12:00:00 AM ' , ' A-23 ' , ' 18.3 ' , ' -0.0061 ' , '1' )
insert into dbo.t1 values( ' DKRH51R ' , ' 1/28/2004 12:00:00 AM ' , ' A-23 ' , ' 10.8 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DRGT77U ' , ' 1/29/2004 12:00:00 AM ' , ' A-23 ' , ' 25.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' USNT80T ' , ' 1/30/2004 12:00:00 AM ' , ' A-23 ' , ' 27.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TDNF71Q ' , ' 1/31/2004 12:00:00 AM ' , ' A-23 ' , ' 26.9 ' , ' 0.0014 ' , '4' )
insert into dbo.t1 values( ' DKAH23Y ' , ' 1/2/2004 12:00:00 AM ' , ' B-60 ' , ' 15.4 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DAKD44T ' , ' 1/3/2004 12:00:00 AM ' , ' B-60 ' , ' 21.7 ' , ' 0.0229 ' , '2' )
insert into dbo.t1 values( ' PDNR56Y ' , ' 1/4/2004 12:00:00 AM ' , ' B-60 ' , ' 19.2 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DBTG87K ' , ' 1/5/2004 12:00:00 AM ' , ' B-60 ' , ' 17.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KDHR43I ' , ' 1/6/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' QJKD00F ' , ' 1/7/2004 12:00:00 AM ' , ' B-60 ' , ' 20.5 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KKKR83I ' , ' 1/8/2004 12:00:00 AM ' , ' B-60 ' , ' 18.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KDDR90R ' , ' 1/9/2004 12:00:00 AM ' , ' B-60 ' , ' 20.5 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TTRC87R ' , ' 1/10/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' RDTD67E ' , ' 1/11/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' ZDHR44I ' , ' 1/31/2004 12:00:00 AM ' , ' B-60 ' , ' 24.6 ' , ' 0.0428 ' , '9' )
insert into dbo.t1 values( ' SRHT23T ' , ' 2/1/2004 12:00:00 AM ' , ' B-60 ' , ' 29 ' , ' 0.0413 ' , '1' )
set nocount off
go
=============
Summary table:
PK
Location
Sum Weight
reading var.
FKFH34U
A-23
78.9
0.0083
RTYT24T
A-23
108.5
0.0161
RKCB26T
A-23
18.3
-0.0305
TDNF71Q
A-23
90.3
0.0075
DAKD44T
B-60
37.1
0.0229
ZDHR44I
B-60
178.6
0.0199
SRHT23T
B-60
29
-0.0015
Note:
1.'Sum weight' is the accumulation of weight between two readings. The weight of the previous reading is not included in the calculation. Example: the 'sum weight' of location A-23 between the reading on 1/24/2004 to 1/31/2004 is (10.8+25.3+27.3+26.9)= 90.3
2.'Reading var.' is the substraction value of the most recent reading value to the previous date reading values. Example: the 'reading var' for location A-23, between the latest reading on 01/31/2004 and previous reading on 01/24/2004 is 0.0075. If there is no value on previous reading, then the latest date reading is substracted by zero (0). The substraction can only be done on the records of similar reading location (A-23 or B-60, etc).
3. 'Location' is obtained from the most recent date of reading, which is used for the calculation.
4. 'PK' is obtained similarly than procedure at point 3.
Thank you in advance.
WP
So, I've got a problem with using table variable "fields" and a simple variable in calculations. It ain't workin'. See the bolded code below. When I run the SP, it returns 0 for those values. Anyone got any clues? Is this a table variable limitation? ALTER PROCEDURE YearlyTotalsInPercentages(@Year int) ASBEGINDECLARE @TotalSum intDECLARE @Totals TABLE
(
CBDCYearlyTotals int, ProductLine varchar(50))INSERT INTO @Totals (CBDCYearlyTotals, ProductLine)SELECT SUM(dbo.Main.Hours), dbo.Project.ProductLineFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, @TotalSum AS TotalSum, ROUND((t.CBDCYearlyTotals/@TotalSum) * 100, 1) AS Percentage FROM @Totals tEND
GO
Thanks Yall
Hello all,
I can't see any reason for this error, not having a high level understanding of maths I thought I'd post it and hope someone could share some light on it.
I yesterday got called by a client who said that a payment for £15 + VAT was being passed to their payment gateway as 17.62 when it should be 17.63. The VAT calculation is performed in a SQL Server 2000 stored procedure. In the end I tracked it down and it wasn't a propblem with my calculation.
The price was coming out as 17.63 fine. The stored procedure then had to return this price in pence (17.63 * 100 = 17.63). When I put in a print statement with this calculation it was correct but when I output the variable that the result was assigned to it was coming out as 1762.
The variable that the result was being put into was of real datatype.
I then wrote a udf to test this. Here is the function:
CREATE FUNCTION dbo.POUNDS_TO_PENCE
(
@POUNDVALUE real
)
RETURNS INTEGER
AS
BEGIN
RETURN @POUNDVALUE * 100
END
As you can see nothing very special.
If you run this runction and pass in 17.63 it will return 1762!!!
The bit I don't get is if I change the @POUNDSVALUE intput variable to type float it returns the correct amount.
I've also found that the same problem occurs when passing in £30 + VAT (35.25) + 1pence. So, 35.26 comes out as 3525 instead of 3526. This is the case if you keep doubling the number (and adding a few pence here and there).
Does anyone know why this is or is it a bug in the processor?
The SQL books online say the following about the float and real data types:
--------------------------------------------------------
float and real (T-SQL)
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.
Syntax
float[(n)]
Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
n is Precision Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes
The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).
real
Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
--------------------------------------------------------
Apart from the fact that it says 'Approximate number data types' I can't see any difference between the data type apart from the ranges.
Anyone any ideas?
Thanks
Tom Holder
Hi there I have a following table
Month| Debt1 | Debt1 |TotalDebtToDate
1 | 1 | 1 | 2
2 | 1 | 2 | 5
3 | 1 | 1 | 7
4 | 2 | 1 | 10
5 | 10 | 5 | 25
Basically I have Debt1 and Debt2 given and I need to calculate TotalDebtToDate
As you see it contains sum of all debts from previous monthes(TotalDebtToDate from a row above)
+ current debt1 + current debt2
Is it possible to write such query in MS SQL 2005 for calculating TotalDebtToDate?
please help!!!
Thank you very much.
I have a table that stores start and end datetime fields per room, per employee.
I may have 4 employees in a room at the same time but they did not all come in the room at the same time and may not leave at the same time either.
I need to calculate the NET number of minutes/hours spent in the room. There may be holes in the timespans when the room is unoccupied. The range could cover more than one day.
Ex:
Room Emp Start End
1 001 2008-01-01 09:00 2008-01-01 11:00
1 002 2008-01-01 09:00 2008-01-01 10:00
1 003 2008-01-01 08:00 2008-01-01 11:00
1 001 2008-01-01 13:00 2008-01-01 14:00
1 002 2008-01-02 13:00 2008-01-02 14:00
The net usage time of room 1 is 5 hours.
The gross total is 8 hours.
Is there a way to calculate this NET total in MSSQL T-SQL or maybe by using a SPROC?
I need this to to bind as a datasource to a Crystal Report document to calculate room availability statistics.
Thanks
Rombolt
Hi there I have a following table
Month| Debt1 | Debt1 |TotalDebtToDate
1 | 1 | 1 | 2
2 | 1 | 2 | 5
3 | 1 | 1 | 7
4 | 2 | 1 | 10
5 | 10 | 5 | 25
Basically I have Debt1 and Debt2 given and I need to calculate TotalDebtToDate
As you see it contains sum of all debts from previous monthes(TotalDebtToDate from a row above)
+ current debt1 + current debt2
Is it possible to write such query in MS SQL 2005 for calculating TotalDebtToDate?
please help!!!
Thank you very much.
I would welcome ideas and some code to help me with this one !
I am writing a c# application which incorporates some dynamic barcharts, including one to indicate server perfomance, where the server data is extracted from a database.
In the application, I will iterate over an array of 30 'ServerID' integers, and want to pass them one by one as parameters to an SQL stored procedure.
I need the procedure to:
declare variables for serverid, currentmonth, elapsedminutes, serverdowntime, and serveruptime.
identify the current month and assign the value to an integer variable (currentmonth).
calculate the number of minutes elapsed from the start of the month until the current date/time, and assign the value to a variable (elapsedminutes)
Query a 'ServerDown' table to return a 'DownDuration' value (recorded in minutes) where the server id = the server id passed from the application, and the 'DateDown' field matches the 'current month' variable, and assign the value to the 'serverdown' variable.
Subtract the 'DownDuration' minutes from the 'elapsedminutes' variable and calculate the remainder as a percentage of the 'elapsedminutes' variable, then assign the value to the 'serveruptime' variable, which will need to be type 'float'.
Return the serveruptime variable value to the Application.
The application will then take the returned floating point decimal and build a bar to graphically illustrate the current uptime of the given server during the elapsed current month period.
I have been doing the calculation in the Application, but would prefer to do it in the stored procedure as this will increase program efficiency, but stored procedures are not my strong point !!
Thanks in advance, and sorry its a bit of a brain teaser !!