I would like to know the best method or the simplest method to do the following. Take a datetime value and subtract a given number of hours. I am taking a UTC time and subtracting a given number or hours in a query. I seem to be getting mixed results.
Thanks
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) Â Â col2 (In Hours : Mins)
3days 4:5Â
In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
View 3 Replies View RelatedI have a float variable that holds a decimal number of hours.
So 1.5 equals 1 hour 30 minutes.
I need to change this to the format 1:30
Any idea how to do this?
Hi,I am currently updating a column in my database table with the value typed into a text box. But it is updating with the new value, rather than taking away the value typed in from the present value of the column. 1. How would i subtract the value typed in away from the current column value?2. I guess I need to check the column value is above 0 before updating, then rebind the control to show new values after the update has happened, incase two people are using it at once. How would this be achived? Thanks for any help!!Jon P.S.My code is:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable = @qty WHERE productID=@productID"; command.Parameters.Add("@qty", TextBox1.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); con.Close(); return true;} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@User)"+ "INSERT INTO Transactions (Itemid)VALUES (@productID)"+ "INSERT INTO Transactions (itemname)VALUES (@Itemsname)"+ "INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+ "INSERT INTO Transactions (Qty)VALUES (@qty)"+ command.Parameters.Add("@User", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.Parameters.Add("@qty", TextBox1.Text); command.ExecuteNonQuery(); con.Close(); return true; }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) );}protected void Button2_Command(object sender, CommandEventArgs e) { if (e.CommandName == "Update") { SqlDataSource1.Insert(); } }}
View 7 Replies View RelatedI'm in trouble. I have a table with some fields, one of them is an amount of money.
One field indicates whether the amount is negetive or positive. This fields has only two possible values, 'N' and 'R'. 'N' means the amount is negative and 'R' means the amount is positive.
The problem I have is that I have to sum these amounts to give a total for each customer and report them as
customer1 total amount1
customer2 total amount2
I don't know how to make a query to bring these results. I know that sum would bring me the total amount for each customer but I don't know how to specify which fields are supposed to be negative so that it subtracts them instead of adding them.
By the way I'm not allowed to change the structure of the table.
Could you help me try to create this query?
hi all
I want to find the set of rows that are present in result returned by first select statement & that are not there in the result of second select statement.
Both select statement operate on tables having similar columns.
Is there any way by which i can find such difference in results?
Is this possible using EXCEPT function?
plz help me.
I'd like to take two DateTimes, subtract them and insert the result as HH:MM:SS,
I think you use datediff but I'm not clear on the datediff formatting to get back HH:MM:SS...
Thanks for your help!
--PhB
One of the following selected fields is a sum. I need to subtract ISNULL(R.CMS_AMOUNT,0) where R.Waived = '1
from that amount.
Pretty much just subtract those amounts where the waived field is 1. I am having trouble with the syntax. Can anyone help? Thanks.'
SELECT P.Efs_Network_ID
, F.DBA_Name
, F.ACCOUNT_STATUS
, SUM(ISNULL(R.CMS_Amount,0)) - isnull(RA.Amount,0) AS Amount -- ?? isNULL(R.CMS_AMOUNT,0) WHERE
R.Waived = '1' ??????
, isnull(SUM(RA.Pending),0) AS Pending
, F.Salesman_no
FROM tblPimsFDSLookup P
INNER JOIN ACH_MerDemo F
ON P.Efs_Network_ID = F.Efs_Network_ID
LEFT OUTER JOIN tblrecovery R
ON P.IntMid = R.IntMid
LEFT OUTER JOIN(
SELECT sum(isnull(Amount,0)) as amount
, intmid
, SUM(CASE WHEN RA.Status = 'P' THEN 1 ELSE 0 END) AS Pending
FROM tblACHRecoveryAmounts RA
--WHERE IntMid = 641046
WHERE RA.Status IN ('A','P','M','S')
Group by intmid
) AS RA
ON P.IntMid = RA.IntMid
WHERE P.INTMID = @INTMID
AND R.Waived = 0
AND Upgraded IS NULL
GROUP BY
P.Efs_Network_ID
, F.DBA_Name
, F.ACCOUNT_STATUS
, RA.Amount
,F.Salesman_no
hi,
I need to write a SQL Query in which I have time1 and time2.
Now I need to find the difference between the time1 and time2 and if the difference is less then 15 minutes then I have to raise an Exception in SQL SERVER 2000.
Anyone know how I can do this.
If I have the total amount of 673000 that is passed as a parameter in my stored proc then, I need to do this:
Declare @TotalAmount money
@TotalAmount = 673000
Col1 Col2 Col3
Test1 45 672955 --(I want to subtract 673000 from Col2 data) 673000-45
Test2 30 672925 --(I want to subtract 30 from the remaining amount of col3)
Test3 100 672825 --(I want to subtract 100 from the remaining amount of col3)
Hello forum.
How do I go about subtracting adjacent Rows? Let's say each day I entered one row entry into a table (tblFlowerHeight)showing the new height of a flower in one column(Height)...say if I had a second second column (ChangeFromLastHeight), how would I go about showing the difference between current height and the last recorded height?
Please assist.
I have the following fields in my report
Year
2008 2007 Net Change
Val1 10 6
Val2 7 5
Val3 15 7
The Matrix Column is grouped by Year.
I need to calculate Net Change as Year 1 - Year 2 for each row.
How do I access the contents of the first and second column of the matrix ?
I have a table for which I need a €œspecial€? running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
Credit
Debit
Balance credit
Balance debit
6
0
6
0
5
0
11
0
0
4
7
0
0
9
0
2
3
0
1
0
0
5
0
4
0
2
0
6
I have to point out that this is done in a grouped way where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
changes
SET @PrevGrpBalP = 0
update Temp
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + Credit,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = Bcredit = CASE
WHEN CustomerID = @PrevAcctID
THEN CASE
WHEN CREDIt > DEBIT or CREDIT = 0
THEN (@PrevGrpBal+CREDIT)-(DEBIT)
WHEN DEBIT>CREDIT or DEBIT =0
THEN (@PrevGrpBal+DEBIT)-DEBIt
END
ELSE CASE
WHEN CREDIT >DEBIT
THEN (CREDIT)
WHEN DEBIT > CREDIT
THEN DEBIT -- restarts from 0 if only 1 rec.
END
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN CustomerID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID
I have a table CombinedSales script below (CreateCombSalesTable.sql) which contains the details of a sale.
A row in the table has two flags online/offline
SalesAmount is the financial amount,
online = 1 (online sales)
online = 0 (combined sales off and online).
The table also contains the product and the client etc.,
What I am trying to do is subtract onlinesales from combinedsales within the query or outside I don't mind, to return the result as (below)
HERE is HOW I want the results to appear
skuid(No column name) onlineClientName
200437215183.350Asda
20043716816.651Asda
I query the table using:
select skuid,SUM(SalesAmount),online,ClientName from t_CombinedSalesDetails
where ClientName ='Asda'
and SkuId = 200437
group by Skuid,Online,ClientName
order by skuid
THIS is what I get below the online = 0 is the total sales of this product.
skuid(No column name) onlineClientName
200437232000.000Asda
20043716816.651Asda
How I could achieve the required results
/****** Object: Table [dbo].[t_CombinedSalesDetails] Script Date: 27/08/2014 13:23:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
[Code] ....
I have a Column in my data that gives a financial period value in the YYYYMM format. i.e. an asset was re-valued in a particular period for example 201301. I need to find out the number(count) of periods(months) between another given period for example current period (201403) and the period provided in the table i.e. 201301.
Is this possible in the SQL Database?
Hi,
I have a matrix which looks as follows:
----- Actuals | Budget
ItemA 20 | 50
ItemB 45 | 30
ItemC 20 | 15
I would like to add a column with the difference between actuals and budget.
Actuals/budget is 1 group in the matrix being the dimensionname.
How can I calculate the difference (subtract actuals and budget), because if I just use the subtotal
function it adds both totals together and I need to have the difference
between them?
Please help...
Hi all,I encountered this small problemI have two tables A and B with two columns 1 and 2 each, I would likethe first column of each table when match the first in the second tableis to subtract the second columnso the result would look as followsColumn 1 | Columnn 2where A1=B1 | A2-B2Now this is no problem so far ..But if there was no corresponding value in column 1 in either tables ..i.e. field A1 doesnt exist in Table B column 1, IT SHALL DO A2 - 0; or0-B2 ..NOW How can that be achieved ?Thanks all for your help
View 3 Replies View RelatedI have two date fields, start_date and end_date.I'd like to subtract the two dates, and come up with a number (thenumber of difference between the two dates).What function is there to do this? I haven't been able to find anythingin BOL.Start_date = 6/1/03End_date = 6/8/03End_date - start_date = 7*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI am trying to get a query that subtracts a month and a day from current date in SQL Server.
Currently I have SELECT Â DATEADD("MM", -1,GETDATE()) which subtracts one month from the date but I want to subtract a month and a day from date.
I have a table that has two smalldatetime columns. like say a shift schedule.
Fname Lname Timein TimeOut Dept
What I need to find out is how to construct a SQL Query in BIDS so I can get a report containing names of people who have not worked in last 24 hours.
This query returns values but it seems it is not correct.
Select * FROM Table
WHERE (Timeout < { fn NOW() } - '23:59:59')
Hi there,I am trying to write a stored procedure in which I will retrieve SessionStartDate, SessionEndDate, and Duration (where Duration is calculdated by subtracting SessionEndDate from SessionStartDate).I was duration in the format of hours:minutes:seconds.The stored procedure is pasted below. I am getting the following error. Syntax error converting datetime from character string. Any ideas? ============================== CREATE PROCEDURE sp_ActiveSessions_UsersBrowsingDurationByDate_List ( @websiteID AS int = 0, @SelectedDateFrom AS dateTime, @SelectedDateTo AS dateTime) ASIF DateDiff(d,@SelectedDateTo,@SelectedDateFrom)=0begin set @SelectedDateFrom=null endIF ISNULL(@SelectedDateTo, '') = ''begin SET @SelectedDateTo = @SelectedDateFromendSET @SelectedDateTo = DATEADD(d, 1, @SelectedDateTo)SELECT UserID As 'User ID', SessionStartDate As 'Session Start Date', SessionEndDate AS 'Session End Date', ExitPageTitle As 'Exit Page Title', NumberOfPagesVisited As 'Number of Pages Visited', Convert(datetime, (CONVERT(DATETIME, SessionEndDate, 24) - CONVERT(DATETIME, SessionStartDate, 24)), 101) As 'Duration' FROM ActiveSessions WHERE UserID != 'Anonymous'GROUP BY SessionID, UserID, SessionStartDate, SessionEndDate, NumberOfPagesVisited, ExitPageTitleHAVING (min(SessionStartDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo AND min(SessionEndDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo)GO============================== <Columns> <asp:BoundColumn DataField="User ID" HeaderText="User ID"></asp:BoundColumn> <asp:BoundColumn DataField="Session Start Date" HeaderText="Session Start Date"></asp:BoundColumn> <asp:BoundColumn DataField="Session End Date" HeaderText="Session End Date"></asp:BoundColumn> <asp:BoundColumn DataField="Duration" HeaderText="Duration"></asp:BoundColumn> <asp:BoundColumn DataField="Number Of Pages Visited" HeaderText="Number Of Pages Visited"></asp:BoundColumn> <asp:BoundColumn DataField="Exit Page Title" HeaderText="Exit Page Title"></asp:BoundColumn> </Columns> ============================
View 1 Replies View RelatedHello. How do I increment or decrement a date by adding/subtracting days? For example I want to add 4 days to the date today. I should get 07/31/2005 since today is 07/27/2005. And if I add 5 days then I should get 08/01/2005. Thank you.
View 5 Replies View Related=Sum(Fields!balance_due.Value)
Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total $137.84
It should be like this:
Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total 112.5
How can I get SSRS to add and subtracted numbers.
We have a few Servers that have space issues that need full DB Backups every day. As a result, we have to carefully watch how many DB Backup files we keep on the Server. On the Maintenance Plan, if I set the 'Delete files older than:' to '1' 'Day', will this delete anything over 24 hours old? What I have noticed, is that it tends to delete on Tuesday anything that was created on Sunday, but not Monday. Therefore, I am assuming that instead of '1 Day' being 24 hours, it is anything less than 47 hours and 59 minutes...
Is my assumption correct or do I need to look at something else. Thanks!
Billy
SELECT sysjobs.name, sysjobservers.last_run_outcome, CONVERT(varchar(8), sysjobservers.last_run_date, 112) AS LastRunDate, CONVERT(varchar(8),
sysjobservers.last_run_time, 8) AS LastRunTime, GETDATE() AS CurrentDateTime
FROM sysjobs LEFT OUTER JOIN
sysjobservers ON sysjobs.job_id = sysjobservers.job_id
WHERE (sysjobservers.last_run_outcome = 0) AND (CONVERT(varchar(8), sysjobservers.last_run_date, 112) >= DATEADD(day, - 1, GETDATE()))
I am wanting a similar solution, I need to convert an "int" to a time format, when I use this where ">= DATEADD(minute, - 720, GETDATE())" for the last_run_time I get this error:
Syntax error converting datetime from character string.
I ran the following:
ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0
On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.
Server configuration:
SQL Server 2000 Enterprise Edition with SP3a
2 GB RAM
3.0 GHz P4 with hyperthreading
SCSI RAID
Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?
My database is current 135GB. I have been running the following:
DBCC SHRINKFILE([database_data],2)
DBCC SHRINKDATABASE ( [Database],20,TRUNCATEONLY)
It's been running for 28 hours. Should it be taking this long? If I stop the shrink, will it need to rollback?
Thanks!
I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
View 6 Replies View RelatedI use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column.
So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?
I am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.
HoursBill_rate_1 Hours_2Bill_rate_2
34.2610.26 0.000.00
5.7410.26 5.689.67
I want to report to the business:
40hrs 10.26
5.68 9.67
The query I have written is below:
Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3
From Valid_Timesheets
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and
((Bill_Rate_1 = £10.26 or
Bill_Rate_1 = £9.67 or
Bill_Rate_1 = £8.27 or
[Code] ....
Hi,I have the following query:SELECT p.employee_code, p.employee_name,CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate,CONVERT(VARCHAR(12),t.post_date,101) AS PostDate, SUM(tobill_hrs) ASTotalHoursFROM tat_time t, hbm_persnl pWHERE t.tk_empl_uno = p.empl_uno AND t.tran_date BETWEEN '2005-03-01'AND '2005-03-31' AND p.employee_code IN ('0340')GROUP BY p.employee_code, p.employee_name, t.tran_date, t.post_dateORDER BY p.employee_codeI would like to sum the tobill_hrs by week (Monday-Sunday) instead ofby day. For instance the user will put in a date range (a from dateand to date).Is this possible to do?Thanks!
View 9 Replies View RelatedI am creating a report that has call duration and if i run it over amulti-day span the duration is limited to 24 hours and starts over atzero. is there a way to not limit hours?I am usign the following in my select statement:convert(varchar(12), dateadd(s,SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS[Outgoing Call Duration]
View 3 Replies View Related