Date should be dynamic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date should be dynamic

Author

Topic

phanikumargamini
Starting Member

4 Posts

Posted - 2013-02-05 : 11:16:53

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

52326 Posts

Posted - 2013-02-05 : 11:18:51

tell us rules for calculating avg time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

phanikumargamini
Starting Member

4 Posts

Posted - 2013-02-05 : 11:33:49

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 11:59:08

what are the datatypes of field starttime,endtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

phanikumargamini
Starting Member

4 Posts

Posted - 2013-02-05 : 12:37:01

DateTime()

example: 2013-01-31 18:26:59

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 13:07:59

then what will be date part? will they be same for start and end?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

phanikumargamini
Starting Member

4 Posts

Posted - 2013-02-05 : 14:15:58

Minutes, yes same for both.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 00:10:42

[code]
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)
[/code]

provided date range doesnt overlap over a month boundary

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources