Picking Time - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Picking Time

Author

Topic

amitranjan
Starting Member

45 Posts

Posted - 2010-02-23 : 06:16:24

I have column where there is time in standard 24hrs format. I have two blocks of schedule. 1st id Peak Hour and another is off peak hour.
peak hour is from 6:00:00 - 21:59:59 and off peak hour is 22:00:00 to 5:59:59 hrs. Now On selection of a particulare date it will display the columns on the basis of the peak hour and off peak hour.

amit Ranjan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 06:28:24

Can you post table structure, some data and expected result?

Madhivanan

Failing to plan is Planning to fail

amitranjan
Starting Member

45 Posts

Posted - 2010-02-23 : 06:54:46

quote:
Originally posted by madhivanan

Can you post table structure, some data and expected result?

Madhivanan

Failing to plan is Planning to fail

Below id the data

Date StartTime Duration Upload Download TotalData
==========================================================
18-02-2010 00:11:39 200 58.651367 197.8623 256.51367
19-02-2010 13:31:07 1832 595.4092 1432.7861 2028.1953
19-02-2010 00:30:06 8633 6172.6143 155741.31 161913.92
19-02-2010 03:43:15 2309 12535.269 149950.75 162486.02
19-02-2010 21:17:10 1687 990.3047 19214.445 20204.75
19-02-2010 21:32:10 404 193.99805 1194.1709 1388.169
19-02-2010 21:45:39 862 1047.8779 20868.648 21916.525
19-02-2010 22:00:08 1911 1446.7734 23574.074 25020.848
19-02-2010 03:05:20 26 49.256836 556.2324 605.48926

amit Ranjan

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-23 : 07:14:53

To start with:

Declare @TimeToCheck datetime

Set @TimeToCheck = convert(datetime,'06:13:39',108)

Select @TimeToCheck,
Case When convert(datetime,@TimeToCheck,108) between '1900-01-01 06:00:00' and
'1900-01-01 21:59:59'then 'Peak'
Else
'Non peak'
end

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-23 : 07:15:56

Considering your example:

Select Date , StartTime, Case When convert(datetime,StartTime,108)
between '1900-01-01 06:00:00' and
'1900-01-01 21:59:59'then 'Peak'
Else
'Non peak'
end
From yourtable

I have not carried out any testing as i don't have time for creating a dummy table and insert some sample data.

Regards,
Bohra

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:26:16

are you look at aggregated values for peak and off peak hours?

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

amitranjan
Starting Member

45 Posts

Posted - 2010-02-23 : 15:02:56

quote:
Originally posted by visakh16

are you look at aggregated values for peak and off peak hours?

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

Yes u got it right. Actually i am building an application that will track my overall internet usage, download and upload. My ISP is not providing peak hour and off peak hour usage details. It shows commulative.

amit Ranjan

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-23 : 15:09:45

What is your expected for the sample data you have provided?

amitranjan
Starting Member

45 Posts

Posted - 2010-02-24 : 01:00:11

quote:
Originally posted by vijayisonly

What is your expected for the sample data you have provided?

Mine ecpected output will be something like this.
I have two datetime pickers . One for Start Date and another for End even i can choose a single date to see its usage. After the selection of date it will have to display sum of total data transferred with two decimal places in GB. Right now its in KB.

amit Ranjan

amitranjan
Starting Member

45 Posts

Posted - 2010-02-24 : 01:03:39

Mine ecpected output will be something like this.
I have two datetime pickers . One for Start Date and another for End Date. I can also choose a single date to see its usage. After the selection of date it will have to display sum of total data transferred with two decimal places in GB. Right now its in KB.
From above data i have two options either i can choose date range from 18-19 or only 18. Output will have two different results one for peak hour and another for offpeak hour total data transfer.

amit Ranjan

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 09:10:05

It would be better if you can SHOW your expected output...like how you have shown your sample data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 09:49:46

didnt understand the relevance of the date values in sample data. how frequently is data captured in table?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources