November 30, 2009 at 9:01 am
Im writing a stored procedure where I need to calculate the difference between times.
@SHUTDOWN_TIME and @STARTUP_TIME
@STARTUP_TIME and @HIBERNATE_TIME
@STARTUP_TIME and @SLEEP_TIME
I then need to know which of 2 categories these hours/times fall into, for example if they fall between 7:00AM and 23:00PM then they will be considered hours_off_day otherwise they will be considered hours_off_night.
My code is the following
SELECT @SHUTDOWN_TIME, @STARTUP_TIME, @HIBERNATE_TIME, @SLEEP_TIME
FROM TBL_PC_AUDIT
WHERE STATUS = 'CLOSED'
CASE
WHEN DATEPART(hh,@SHUTDOWN_TIME, @STARTUP_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
WHEN DATEPART(hh,@STARTUP_TIME, @HIBERNATE_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
WHEN DATEPART(hh,@STARTUP_TIME, @SLEEP_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
ELSE hours_off_night
END
Here im trying to distinguish the hours_off_day and hours_off_night, At the moment im getting an error "incorrect syntax near Keyword 'CASE'". Would really appreciate any help or advice here as Im a newbie to using stored procedures and cant get my head around on how to do this.
November 30, 2009 at 9:08 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2009 at 9:15 am
Sorry about that, this is my code so far, i've created my temp table. In relation to @SHUTDOWN_TIME, @STARTUP_TIME, @HIBERNATE_TIME, @SLEEP_TIME they are just a time stamp so for example they will have a date and time like 30/11/2009 16:17:57. Basically for the output i just want it to be recorded in a table with 2 fields, hours_off_day and Hours_off_night,
/* Create temp table */
CREATE TABLE #savingstemp
(
pc_profile_id int,
pc_kwh_rate int,
hours_off_total int,
hours_off_day int,
day_rate int,
hours_off_night int,
night_rate int
)
OPEN pc_audit_cursor
FETCH NEXT FROM pc_audit_cursor into @pc_profile_id
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @pc_profile_id
/** Get total hours off*/
SELECT @SHUTDOWN_TIME, @STARTUP_TIME, @HIBERNATE_TIME, @SLEEP_TIME
FROM TBL_PC_AUDIT
WHERE STATUS = 'CLOSED'
SET @total_hrs_off = DATEDIFF(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME)+ DATEDIFF(HOUR, @STARTUP_TIME, @HIBERNATE_TIME)
+ DATEDIFF(HOUR, @STARTUP_TIME, @SLEEP_TIME);
/** Get hours off at peak rate and hours off at reduced rate*/
SELECT @SHUTDOWN_TIME, @STARTUP_TIME, @HIBERNATE_TIME, @SLEEP_TIME
FROM TBL_PC_AUDIT
WHERE STATUS = 'CLOSED'
CASE
WHEN DATEPART(hh,@SHUTDOWN_TIME, @STARTUP_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
WHEN DATEPART(hh,@STARTUP_TIME, @HIBERNATE_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
WHEN DATEPART(hh,@STARTUP_TIME, @SLEEP_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
ELSE hours_off_night
END
November 30, 2009 at 9:21 am
Sample data and expected results please. Also, the definition of the cursor (and whatever table it's based on).
As it is, I can't test on my own machine and get you a tested solution.
Your syntax error is because CASE is not an independent statement. It has to be part of a SELECT, UPDATE, INSERT or DELETE. Though, once that's fixed there will be more errors.
Once you've calculated the on/off hours, what do you want to do with them?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2009 at 9:24 am
I believe DATEPART should have only 2 parameters and it will return an integer value.
For me ...
select datepart(hh,'2009-11-30 10:18:48.063') returns 10
select datepart(hh,'2009-11-30 22:19:44.790') returns 22
November 30, 2009 at 9:49 am
The definition of the cursor is below and its based on my TBL_PC_AUDIT which has a number of fields
AUDIT_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, PC_PROFILE_ID
2 04/11/2009 14:52:17NULL NULL 04/11/2009 16:02:57 CLOSED 1
3 24/11/2009 18:00:00NULL NULL 04/11/2009 15:00:17 CLOSED 1
4 24/11/2009 21:00:00NULL NULL 04/11/2009 15:02:08 CLOSED 2
6 04/11/2009 16:12:10NULL NULL 04/11/2009 16:12:50 CLOSED 2 [/code]
Sample Data is above, basically any row wil contain 2 date/time values and will also have a AUDIT_ID and PC_PROFILE_ID. When the hours_off_day and hours_off_night are calculated i would like them to be inserted into the #temptable fields called hours_off_day and hours_off_night, its not important where they go, once i can just calculate it, they can be inserted into any table. Sorry about the poor posting but im only new to working with Stored Procedures, thanks again
ALTER PROCEDURE [dbo].[SP_AUDIT_REPORT]
/*Start Date and End date parameters */
/* Type of report (D detailed, S summary) */
/* use return parameter as status (error checking) */
/* Also return error message */
@pc_profile_id int,
@startup_time datetime,
@hibernate_time datetime,
@sleep_time datetime,
@shutdown_time datetime,
@pc_power_rating int,
@day_rate int,
@night_rate int,
@total_hrs_off int,
@status varchar(50)
AS
/* SET NOCOUNT ON */
Declare pc_audit_cursor CURSOR
FOR select distinct PC_PROFILE_ID FROM TBL_PC_AUDIT
/* Create temp table */
CREATE TABLE #savingstemp
(
pc_profile_id int,
pc_kwh_rate int,
hours_off_total int,
hours_off_day int,
day_rate int,
hours_off_night int,
night_rate int
)
OPEN pc_audit_cursor
FETCH NEXT FROM pc_audit_cursor into @pc_profile_id
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @pc_profile_id
/** Get total hours off*/
SELECT @SHUTDOWN_TIME, @STARTUP_TIME, @HIBERNATE_TIME, @SLEEP_TIME
FROM TBL_PC_AUDIT
WHERE STATUS = 'CLOSED'
SET @total_hrs_off = DATEDIFF(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME)+ DATEDIFF(HOUR, @STARTUP_TIME, @HIBERNATE_TIME)
+ DATEDIFF(HOUR, @STARTUP_TIME, @SLEEP_TIME);
/** Get hours off at peak rate and hours off at reduced rate*/
SELECT @SHUTDOWN_TIME, @STARTUP_TIME, @HIBERNATE_TIME, @SLEEP_TIME
FROM TBL_PC_AUDIT
WHERE STATUS = 'CLOSED'
CASE
WHEN DATEPART(hh,@SHUTDOWN_TIME, @STARTUP_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
WHEN DATEPART(hh,@STARTUP_TIME, @HIBERNATE_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
WHEN DATEPART(hh,@STARTUP_TIME, @SLEEP_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
ELSE hours_off_night
END
November 30, 2009 at 11:39 pm
Have been trying for 1 and 1/2 hour, and I have to say, I give up for now (I don't want to be late / skip my work tomorrow)
As Gail has mentioned there is no table definition, sample data and output. What is given a script which is currently not useful since the table and data does not exist.
Started working on creating the table. Here is the table script.
CREATE TABLE [dbo].[TBL_PC_AUDIT](
[AUDIT_ID] [int] NULL,
[SHUTDOWN_TIME] [datetime] NULL,
[HIBERNATE_TIME] [datetime] NULL,
[SLEEP_TIME] [datetime] NULL,
[STARTUP_TIME] [datetime] NULL,
[STATUS] [varchar](100) NULL,
[PC_PROFILE_ID] [int] NULL
) ON [PRIMARY]
The best is yet to come, when I tried to insert the date using the values given below.
AUDIT_ID, SHUTDOWN_TIME, HIBERNATE_TIME, SLEEP_TIME, STARTUP_TIME, STATUS, PC_PROFILE_ID
2 04/11/2009 14:52:17NULL NULL 04/11/2009 16:02:57 CLOSED 1
3 24/11/2009 18:00:00NULL NULL 04/11/2009 15:00:17 CLOSED 1
4 24/11/2009 21:00:00NULL NULL 04/11/2009 15:02:08 CLOSED 2
6 04/11/2009 16:12:10NULL NULL 04/11/2009 16:12:50 CLOSED 2 [/code]
The 1st records gets inserted and from record#2, the date values are wrong and SQL throws error inserting data. The shutdown value in record 2 is '24/11/2009 18:00:00' which should have been '11/24/2009 18:00:00'
I used this Script
INSERT INTO TBL_PC_AUDIT values
(2, '11/04/2009 14:52:17', NULL, NULL, '11/04/2009 16:02:57', 'CLOSED', 1)
INSERT INTO TBL_PC_AUDIT values
(3, '11/24/2009 18:00:00', NULL, NULL, '11/04/2009 15:00:17', 'CLOSED', 1)
INSERT INTO TBL_PC_AUDIT values
(4, '11/24/2009 21:00:00', NULL, NULL, '11/04/2009 15:02:08', 'CLOSED', 2)
INSERT INTO TBL_PC_AUDIT values
(6, '11/04/2009 16:12:10', NULL, NULL, '11/04/2009 16:12:50', 'CLOSED', 2)
So I thought to enter some custom values and try,
I was surprised when I observed the 4 records closely.
Let me explain my confusion....:w00t:
From the column names I could identify the table is storing some data pertaining to a Server or PC.
For Audit_ID 2 and 6, shutdown_times are lower values than Startup_time. This is understandable, a PC shutsdown at '14:52:17' and starts at '16:02:57' same day, I am clear 🙂
For Audit_ID 3 PC shutdown at '18:00:00' on '24/11/2009' and starts at '15:00:17' on '04/11/2009'... this is not clear....:hehe:
If I assume this is only test data (no need to bother about valid times) then fine, I thought to start writing the code, but what to achieve? Is there a specific output format given? Nope...:sick:
After reading the OP's replies multiple times, I think I understood some part of it..
So decided to write the code and started to work on it.
After looking at the case statements and the conditions, I got this doubt and it is based on this from the original post.
I then need to know which of 2 categories these hours/times fall into, for example if they fall between 7:00AM and 23:00PM then they will be considered hours_off_day otherwise they will be considered hours_off_night.
What do you mean by which of these hours/times fall into? Will both startuptime and shutdown time be in the same time period from 700 to 2300? What if startup is at 1000 and Shutdowntime is 2330 ? In this case what should be it called? (neither hours_off_day nor hours_off_night)
Why using DATEPART Function?
If you wanted the time difference, DATEDIFF would do it.. right?
No answers more and more questions coming up.
Either I am looking the question wrong or the question is wrong.. Please someone help.....:(
Now I understand why no one tried answering this for more than half a day...
One final word, I am not a SQL Developer who writes thousands of lines of code.. so obviously there is something I am missing.
Logging off at 1:39 AM EST
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 4:15 am
Bru, appreciate the effort you made, thank you. This issue is quite tough to understand and I've been trying to figure it out for a while now. What I have is a TBL_AUDIT_ID, the table definition and sample data is below, I've attached an image of the data in my TBL_AUDIT_ID aswell.
AUDIT_ID [int]
SHUTDOWN_TIME [datetime]
HIBERNATE_TIME [datetime]
SLEEP_TIME [datetime]
STARTUP_TIME [datetime]
STATUS [varchar(50)]
PC_PROFILE_ID [int]
2 04/11/2009 14:52:17 NULL NULL 04/11/2009 16:02:57 CLOSED 1
3 03/11/2009 18:00:00 NULL NULL 04/11/2009 15:00:17 CLOSED 1
4 03/11/2009 21:00:00 NULL NULL 04/11/2009 15:02:08 CLOSED 2
6 04/11/2009 16:12:10 NULL NULL 04/11/2009 16:12:50 CLOSED 2
This table records STARTUP_TIME, SHUTDOWN_TIME, SLEEP_TIME AND HIBERNATE_TIME. Only two of these 4 fields can be populated at once for example a computer cant be hibernated and shutdown at the same time obviously.Timestamps are recorded when the computer is started up again. What I need to do is calculate the time the computer is off and then subsequently apply a rate so I can calculate the savings obtained from shutting down the computer, There will be 2 rates a DAY_RATE which runs form 7:00AM to 23:00PM and a NIGHT_RATE which runs from 23:00PM to 7:00AM, the rate will be taken from a TBL_ELECTRICITY_RATES Table but will be fixed for the moment. This is why I have tried to write a stored procedure to do this, I was told to use a Cursor and a temp table. It does not matter how I do this but I need to calculate the savings, for example if the SHUTDOWN_TIME is 01/12/09 23:00PM (it is in this format because I am from Europe) and the STARTUP_TIME is 02/12/09 7:00AM, in this case the computer is off for 8 hours and I know it is during the night so I can apply the NIGHT_RATE and calculate the savings. In relation to the output, i just need a definite amount of HOURS_OFF_DAY and HOURS_OFF_NIGHT so I can apply the rates appropriately.
This is a very awkward way to do it and your right i don't know how to calculate it if it spans over the DAY_RATE and NIGHT_RATE for example if its SHUTDOWN_TIME is 01/12/09 23:30PM and it STARTUP_TIME is 02/12/09 10:00PM. I think I will look into alternative ways of doing this because at the moment it seems very difficult and I am not used to using stored procedures. Once again thanks for the time everyone has given this thread, appreciate the effort 🙂 This is my code so far in my stored procedure:
/*
Name: SP_AUDIT_REPORT
Descrtiption: Used to generate Savings Report
*/
ALTER PROCEDURE [dbo].[SP_AUDIT_REPORT]
/*Start Date and End date parameters */
/* Type of report (D detailed, S summary) */
/* use return parameter as status (error checking) */
/* Also return error message */
@pc_profile_id int,
@startup_time datetime,
@hibernate_time datetime,
@sleep_time datetime,
@shutdown_time datetime,
@pc_power_rating int,
@day_rate int,
@night_rate int,
@total_hrs_off int,
@status varchar(50)
AS
/* SET NOCOUNT ON */
Declare pc_audit_cursor CURSOR
FOR select distinct PC_PROFILE_ID FROM TBL_PC_AUDIT
/* Create temp table */
CREATE TABLE #savingstemp
(
pc_profile_id int,
pc_kwh_rate int,
hours_off_total int,
hours_off_day int,
day_rate int,
hours_off_night int,
night_rate int
)
OPEN pc_audit_cursor
FETCH NEXT FROM pc_audit_cursor into @pc_profile_id
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @pc_profile_id
/** Get total hours off*/
SELECT @SHUTDOWN_TIME, @STARTUP_TIME, @HIBERNATE_TIME, @SLEEP_TIME
FROM TBL_PC_AUDIT
WHERE STATUS = 'CLOSED'
SET @total_hrs_off = DATEDIFF(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME)+ DATEDIFF(HOUR, @STARTUP_TIME, @HIBERNATE_TIME)
+ DATEDIFF(HOUR, @STARTUP_TIME, @SLEEP_TIME);
/** Get hours off at peak rate and hours off at reduced rate*/
SELECT @SHUTDOWN_TIME, @STARTUP_TIME, @HIBERNATE_TIME, @SLEEP_TIME
FROM TBL_PC_AUDIT
WHERE STATUS = 'CLOSED'
CASE
WHEN DATEPART(hh,@SHUTDOWN_TIME, @STARTUP_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
WHEN DATEPART(hh,@STARTUP_TIME, @HIBERNATE_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
WHEN DATEPART(hh,@STARTUP_TIME, @SLEEP_TIME) BETWEEN 7:00 AND 23:00
THEN hours_off_day
ELSE hours_off_night
END
/** Get row from pc_audit for this pc from start date to start date + 1 */
/** Work out hours on and off */
/** Get Day/Night/PC power Rating and insert into temp table */
INSERT INTO #savingstemp(day_rate)
SELECT @DAY_RATE
From TBL_ELECTRICITY_RATES
INSERT INTO #savingstemp(night_rate)
SELECT @NIGHT_RATE
From TBL_ELECTRICITY_RATES
INSERT INTO #savingstemp(pc_kwh_rate)
SELECT @PC_POWER_RATING
From TBL_PC_DESCRIPTION
/** Insert values into temp table */
INSERT into #savingstemp (pc_profile_id, hours_off_total, hours_off_day, hours_off_night)
VALUES (100, @total_hrs_off, 300, 400)
FETCH NEXT FROM pc_audit_cursor INTO @pc_profile_id
END
/** Depending on report type, select sum(time*rate) from temp, group by pc, or date */
/** Return values */
CLOSE pc_audit_cursor
DEALLOCATE pc_audit_cursor
/**Drop temporary table */
drop table #savingstemp
December 1, 2009 at 7:15 am
nialltuohy (12/1/2009)
What I have is a TBL_AUDIT_ID, the table definition and sample data is below, I've attached an image of the data in my TBL_AUDIT_ID aswell.
I'm guessing that you haven't read the article I referred you to. Please do so.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 7:25 am
Here's what I come up with. You'll need to change the temp table to your real table, of course.
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
AUDIT_ID [int] primary key,
SHUTDOWN_TIME [datetime],
HIBERNATE_TIME [datetime],
SLEEP_TIME [datetime],
STARTUP_TIME [datetime],
STATUS [varchar](50),
PC_PROFILE_ID [int]);
insert into #T (Audit_ID, Shutdown_Time, Hibernate_Time, Sleep_Time, Startup_Time,
Status, PC_Profile_ID)
select 2, '11/04/2009 14:52:17', NULL, NULL, '11/04/2009 16:02:57', 'CLOSED', 1
union all
select 3, NULL, '11/03/2009 18:00:00', NULL, '11/04/2009 15:00:17', 'CLOSED', 1
union all
select 4, '11/03/2009 21:00:00', NULL, NULL, '11/04/2009 15:02:08', 'CLOSED', 2
union all
select 6, '11/04/2009 16:12:10', NULL, NULL, '11/04/2009 16:12:50', 'CLOSED', 2;
;with
BigNumbers (Number) as
(select top 1000000 row_number() over (order by N1.Number)-1
from dbo.Numbers N1
cross join dbo.Numbers N2),
Calc (Audit_ID, Category) as
(select audit_id, case
when datepart(hour, dateadd(second, Number, coalesce(Shutdown_Time, Hibernate_Time, Sleep_Time))) > 7
and datepart(hour, dateadd(second, Number, coalesce(Shutdown_Time, Hibernate_Time, Sleep_Time))) < 23 then 'Day'
else 'Night'
end as Category
from #T
inner join BigNumbers
on datediff(second, coalesce(Shutdown_Time, Hibernate_Time, Sleep_Time), Startup_Time) >= Number),
DayHrs (Audit_ID, DayHrs) as
(select Audit_ID, count(*)/3600.00
from Calc
where Category = 'Day'
group by Audit_ID),
NightHrs (Audit_ID, NightHrs) as
(select Audit_ID, count(*)/3600.00
from Calc
where Category = 'Night'
group by Audit_ID)
select coalesce(DayHrs.Audit_ID, NightHrs.Audit_ID) as Audit_ID, DayHrs, NightHrs
from DayHrs
full outer join NightHrs
on DayHrs.Audit_ID = NightHrs.Audit_ID
order by coalesce(DayHrs.Audit_ID, NightHrs.Audit_ID);
I have a table, dbo.Numbers, that has numbers from 1 to 10-thousand in it. For the BigNumbers CTE, any cross join that will generate enough numbers to handle conceivable amounts of off-time will accomplish what you need. I used 1-million, which would handle up to about 11 days of off-time. 10-million would handle a computer being off/hibernating/whatever, for up to several months, and 100-million is over three years of downtime. Use what you need on that part.
It would probably be more efficient to use temp tables instead of CTEs if you're dealing with any significant number of rows in the audit table. You'll have to test that for performance.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 7:33 am
Will STARTUP_TIME always be greater than SHUTDOWN_TIME, SLEEP_TIME or HIBERNATE_TIME?
December 1, 2009 at 7:46 am
Lynn Pettis (12/1/2009)
Will STARTUP_TIME always be greater than SHUTDOWN_TIME, SLEEP_TIME or HIBERNATE_TIME?
Not necessarily which is one of my problems, for example if a computer shuts down at 01/12/2009 23:30PM it maybe startup again the following morning at 02/12/09 7:00AM
December 1, 2009 at 7:54 am
But surely 02/12/09 7:00AM is bigger than 01/12/2009 23:30PM
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 7:55 am
This is a very awkward way to do it and your right i don't know how to calculate it if it spans over the DAY_RATE and NIGHT_RATE for example if its SHUTDOWN_TIME is 01/12/09 23:30PM and it STARTUP_TIME is 02/12/09 10:00PM.[/qutoe]
You'd have to calculate off time during each period. In the above example, you'd have savings in both periods for roughly 30 days. I know the concept, but am not the one to write the sql.
So having more definition of the structure and good sample data is needed to get more help with an answer.
Just from a quick glance at your sample data - it looks like if the last column identifies a machine, that machine cycled during an off time.
So you'd also probably want to plan for some error correction.
Greg E
December 1, 2009 at 8:27 am
The solution I posted will handle down-time that spans multiple days, crosses the boundaries between Day and Night, etc. It just depends on how big a number you allow in the BigNumbers CTE.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply