Date Calculations.. Please Help!
Nov 25, 2005
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 :)
View 2 Replies
ADVERTISEMENT
Aug 24, 2005
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"
View 3 Replies
View Related
Jul 20, 2005
I 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 Related
May 15, 2008
I have an sub-period end-date column, in a row set of (let's say) 2 records.
How Can I grab a previous record and use it's SUB_PD_END_DT
to add a day and get the current record's sub period beginning date?
i.e.
row 1 = col1, col2, SUB_PD_END_DT = (2008-05-02)
row 2 = col1, col2, SUB_PD_END_DT = (2008-05-09)
therefore, row 2 = SUB_PD_start_DT = (2008-05-03)
Any help would be great.
Thanks
JDA
View 9 Replies
View Related
Jul 31, 2015
I'm trying to move some logic that I have currently within a program and putting it into SQL instead.
My table has the following 3 fields that are of interest to me: StartDate (DateTime), StopDate(DateTime), Length (int)
Length is calculated based on StopDate - StartDate and is expressed to the nearest minute.
What I want to do is query the Db giving a start date and end date and return all records that fall within that date range. I then want to present that data such that the earliest date is set to the start date criteria, the last to the end date criteria and the length recalculated.
Say for example I query for results between 01/02/2015 07:00:00 and 01/02/2015 19:00:00 and I get the following:
Start Stop Length
01/02/2015 06:30:00 01/02/2015 07:05:00 35
01/02/2015 07:05:00 01/02/2015 10:00:00 175
01/02/2015 10:00:00 01/02/2015 19:15:00 555
I would like the output to show as
Start Stop Length
01/02/2015 07:00:00 01/02/2015 07:05:00 5
01/02/2015 07:05:00 01/02/2015 10:00:00 175
01/02/2015 10:00:00 01/02/2015 19:00:00 540
Is there a way to do this?
View 5 Replies
View Related
Mar 18, 2008
I am having a hard time creating a view with some complex math to chow availability for rental items.
My tables are as follows:
OrderHeader
OrderID int,
Site int,
StartDate datetime,
EndDate datetime
OrderDetail
OrderDetailID int,
Site int,
OrderID int,
ItemName varchar(30),
Qty int
ShipHeader
ShippingID int,
Type int,
ActionDate datetime
ShipDetail
ShipDetailID int ,
ShippingID int,
OrderDetailID int,
Qty int
Transaction
TransactionID int,
Type int
PurchaseHeader
PurchaseHeaderID int,
Site int
PickupDate datetime,
ReturnDate datetime,
PurchaseDetails
PurchaseDetailsID int,
PurchaseHeaderID int,
ItemName varchar(30),
Qty int
I need a view that shows how many items will be available on a particular day for a specified item, date range and a specified €œSite€? (Office location. For example, NY, LA, CA, etc.)
The quantity actually owned is calculated from the Transaction table. Type, Qty are the columns. (type 1 is a purchase or addition and type 2 is a sale or subtraction. The quantity owned is the sum of all type 1s minus the sum of all type 2s.
The ShipHeader table also shows types. Type 1 is ship, Type 2 is return and Type 3 is lost.
Initially, all availability is calculated based on the FromDate and ToDate of our order headers. However, once the order has shipped, the availability should change to our ship and return dates in our ShipDetails table. Additionally, there is a PurchaseDetails table that shows items to be €œSub-Rented€? for a time frame. The PurchaseHeader table contains the FromDate and ToDate for these sub-rented items to be added to availability. If an item is kept beyond the due date set in the OrderHeader, then the item should show as unavailable from the FromDate through all future dates (as there is no way of telling when a late item will be returning. Once, the item is either returned or marked as lost, it should become available again as of that date.
The view should list dates in each row with the number of units available on that date.
Also, a second view needs to be created that shows the details for the dates listed in the first view. this is essentially a list of the orders or puchase orders that were used to calculate the number available.
I think this covers it.
As I said, this is a bit complicated. i understand what needs to be done but need assistance assembling the code to make it happen.
View 21 Replies
View Related
May 31, 2015
I have a table that stores working hrs, such as
RecID,StaffID,StartDate,EndDate
17,969,2015-05-18 00:00:00.000,2015-05-18 06:00:00.000
18,969,2015-05-18 18:00:00.000,2015-05-19 06:00:00.000
19,969,2015-05-19 18:00:00.000,2015-05-20 06:00:00.000
20,969,2015-05-20 18:00:00.000,2015-05-21 06:00:00.000
21,969,2015-05-21 18:00:00.000,2015-05-22 06:00:00.000
22,969,2015-05-22 18:00:00.000,2015-05-23 06:00:00.000
23,969,2015-05-23 14:00:00.000,2015-05-24 08:00:00.000
24,969,2015-05-24 22:00:00.000,2015-05-25 00:00:00.000
So working times can go over midnight, there can be more than one working period in a day etc.
For this staff member the summary of the weeks work will be
18/05/2015 - 12 hrs
19/05/2015 - 12 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 12 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs
Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.
So for example
If this person
had a days holiday on the 22nd, shown in the HOLIDAY table as
StaffID,DateFrom, DateTo
969, 22/05/2015 00:00:00.000,22/05/2015 23:59:59.000
A Leave of Absence on the 20th, shown in the LEAVE table as
StaffID,DateFrom, DateTo
969,20/05/2015 12:00:00.000,20/05/2015 16:00:00.000
And was off sick on the morning of the 19th, shown in the SICKNESS Table as
StaffID,DateFrom, DateTo
969, 19/05/2015 00:00:00.000,19/05/2015 11:59:59.000
Now the Summary table should now show
18/05/2015 - 12 hrs
19/05/2015 - 6 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 0 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs
The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times. how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?
I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.
View 1 Replies
View Related
May 18, 2007
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.
View 2 Replies
View Related
Jul 1, 2007
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
View 2 Replies
View Related
Mar 21, 2008
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
View 5 Replies
View Related
Jun 5, 2008
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%'
View 2 Replies
View Related
Jun 5, 2006
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?
View 1 Replies
View Related
Jul 8, 2002
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
View 2 Replies
View Related
Jan 4, 1999
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
View 1 Replies
View Related
Mar 5, 2007
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
View 2 Replies
View Related
Jul 7, 2004
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.
View 3 Replies
View Related
May 17, 2006
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
View 4 Replies
View Related
Aug 10, 2006
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 :)
View 4 Replies
View Related
Mar 28, 2007
where should be the calculations be made based on performance, in SQL or in front end?
thanks.
-Ron-
View 7 Replies
View Related
Jul 23, 2005
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 Related
Jul 20, 2005
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 Related
Jul 20, 2005
I 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 Related
Jul 20, 2005
I'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 Related
May 28, 2008
Hi -
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
View 1 Replies
View Related
Nov 2, 2007
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
View 8 Replies
View Related
Aug 21, 2007
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
View 10 Replies
View Related
May 4, 2007
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
View 1 Replies
View Related
Nov 9, 2001
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
View 2 Replies
View Related
Jun 21, 2007
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.
View 2 Replies
View Related
Apr 18, 2008
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
View 10 Replies
View Related
Jun 21, 2007
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.
View 4 Replies
View Related
Jul 3, 2006
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 !!
View 11 Replies
View Related
Jan 6, 2007
There has been a number of topics recently regarding calculations of overlapping times. Here is one approach to reach this with a UDF.CREATE FUNCTION dbo.fnTimeOverlap
(
@FromTime DATETIME,
@ToTime DATETIME,
@Login DATETIME,
@Logout DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE@Temp DATETIME,
@Seconds INT
IF @FromTime > @ToTime
SELECT@Temp = @FromTime,
@FromTime = @ToTime,
@ToTime = @Temp
IF @Login > @Logout
SELECT@Temp = @Login,
@Login = @Logout,
@Logout = @Temp
SELECT@Seconds = CASE
WHEN @FromTime <= @Login AND @Login <= @ToTime AND @ToTime <= @Logout THEN DATEDIFF(second, @Login, @ToTime)
WHEN @FromTime <= @Login AND @Logout <= @ToTime THEN DATEDIFF(second, @Login, @Logout)
WHEN @Login <= @FromTime AND @ToTime <= @Logout THEN DATEDIFF(second, @FromTime, @ToTime)
WHEN @Login <= @FromTime AND @FromTime <= @Logout AND @Logout <= @ToTime THEN DATEDIFF(second, @FromTime, @Logout)
END
RETURN@Seconds
END
Peter Larsson
Helsingborg, Sweden
View 6 Replies
View Related
