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.
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...
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.
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'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:
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.
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
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.
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.
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%'
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?
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)
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)
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)
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.
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?
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.
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:
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
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
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..
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
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?
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
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. =========
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.
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
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).
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.
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 !!
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
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
