SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date should be dynamic		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

phanikumargamini
Starting Member

India
4 Posts
Posted - 02/05/2013 :  11:16:53  Show Profile  Reply with Quote
I want to retrive the Average time on the current date,previous date and 4 weeks Average time


Current Date Previous Date 4 Weeks Avg.

Avg. Time XXXX XXXX XXXX

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 02/05/2013 :  11:18:51  Show Profile  Reply with Quote
tell us rules for calculating avg time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

phanikumargamini
Starting Member

India
4 Posts
Posted - 02/05/2013 :  11:33:49  Show Profile  Reply with Quote
Start Time End Time Min
x 7:00 AM 7:05 AM 05:00
x 7:06 AM 7:10 AM 04:00
x 7:11 AM 7:15 AM 04:00

Average Time 4:20
A person does this task for the last 1 month / year.

I want to know the Average time on the current date,previous date, 4 months Avg.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 02/05/2013 :  11:59:08  Show Profile  Reply with Quote
what are the datatypes of field starttime,endtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

phanikumargamini
Starting Member

India
4 Posts
Posted - 02/05/2013 :  12:37:01  Show Profile  Reply with Quote
DateTime()

example: 2013-01-31 18:26:59
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 02/05/2013 :  13:07:59  Show Profile  Reply with Quote
then what will be date part? will they be same for start and end?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

phanikumargamini
Starting Member

India
4 Posts
Posted - 02/05/2013 :  14:15:58  Show Profile  Reply with Quote
Minutes, yes same for both.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 02/06/2013 :  00:10:42  Show Profile  Reply with Quote 

SELECT DATEADD(mm,DATEDIFF(mm,0,[Start]),0) AS MonthDate,
CONVERT(varchar(8),DATEADD(minute,AVG(DATEDIFF(minute,[Start],[End])),0),108) AS TimeDiff
FROM Table
GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start]),0)


provided date range doesnt overlap over a month boundary

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000