March 26, 2012 at 11:51 pm
Jeff,
You win hands down for elegance and simplicity and even though I knew how unlikely it was, I was still hoping to beat you out on performance. So I took your data setup and modified it taking a page out of your playbook (generating 1,000,000 rows) and then tried to compare the results using the following:
SET STATISTICS TIME ON
DECLARE @times TABLE ([hh:mm]VARCHAR(12))
--===== Create a table with a column of VARCHAR times
;WITH
cteGenHHHMM AS
(
SELECT TOP (1000000)
SomeDateTime = RAND(CHECKSUM(NEWID())) * 10 + CAST(0 AS DATETIME)
FROM sys.all_columns ac1,
sys.all_columns ac2
)
INSERT INTO @times
SELECT CAST(DATEDIFF(hh,0,SomeDateTime) AS VARCHAR(10)) + ':'
+ RIGHT(CONVERT(CHAR(8),SomeDateTime,108),2)
FROM cteGenHHHMM
;WITH
cteToDateTime(DT) AS
(
SELECT DATEADD(mi,SUM(LEFT([hh:mm],CHARINDEX(':',[hh:mm])-1)*60 + RIGHT([hh:mm],2)),0)
FROM @times
)
SELECT CAST(DATEDIFF(hh,0,DT) AS VARCHAR)
+ SUBSTRING(CONVERT(CHAR(8),DT,108),3,3)
FROM cteToDateTime
;
;WITH Times AS (
SELECT SUM(CAST(SUBSTRING([hh:mm], 1, CHARINDEX(':',[hh:mm])-1) AS INT)) as hh
,SUM(CAST(SUBSTRING([hh:mm], CHARINDEX(':',[hh:mm])+1, LEN([hh:mm])) AS INT)) as mm
FROM @times
)
SELECT CAST(hh + mm/60 AS VARCHAR)+ ':' + RIGHT('00'+CAST(mm%60 AS VARCHAR),2)
FROM Times
And I got this rather curious result:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 47 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1000000 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 3588 ms, elapsed time = 3651 ms.
Msg 8115, Level 16, State 2, Line 20
Arithmetic overflow error converting expression to data type int.
SQL Server Execution Times:
CPU time = 327 ms, elapsed time = 328 ms.
(1 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 1435 ms, elapsed time = 1514 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Perhaps I didn't adapt your code properly to my temporary table. Can you tell me what I did wrong?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 27, 2012 at 2:09 am
Paul,
For future posts, please see the article at the first link in my signature line below. It'll help us help you more quickly.
First, we need some test data. I figure 1,000 different times will do. This is just test data and is not a part of the solution.
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('TempDB..#YourTable','U') IS NOT NULL
DROP TABLE #YourTable
;
GO
--===== Create a table with a column of VARCHAR times
WITH
cteGenHHHMM AS
(
SELECT TOP (1000)
SomeDateTime = RAND(CHECKSUM(NEWID())) * 10 + CAST(0 AS DATETIME)
FROM sys.all_columns ac1,
sys.all_columns ac2
)
SELECT WeekHours = CAST(DATEDIFF(hh,0,SomeDateTime) AS VARCHAR(10)) + ':'
+ RIGHT(CONVERT(CHAR(8),SomeDateTime,108),2)
INTO #YourTable
FROM cteGenHHHMM
;
--===== Show what we've created as a test table
SELECT * FROM #YourTable
;
Here's some relatively simple code to solve the problem.
--===== Now show one way to sum all those Varchar times
WITH
cteToDateTime(DT) AS
(
SELECT DATEADD(mi,SUM(LEFT(WeekHours,CHARINDEX(':',WeekHours)-1)*60 + RIGHT(WeekHours,2)),0)
FROM #YourTable
)
SELECT CAST(DATEDIFF(hh,0,DT) AS VARCHAR(10))
+ SUBSTRING(CONVERT(CHAR(8),DT,108),3,3)
FROM cteToDateTime
;
[/quote]
Thanks for your solution, Jeff. However, I am still unable to incorporate this into the stored procedure I have created. It gives an error - 'cteToDateTime' is not a recognized option. This is how I am trying to add this within the SP-
----------------------------------------
ALTER PROCEDURE [dbo].[timelord_year]
@Year [nvarchar](4000)
WITH
cteToDateTime(DT) AS
SET NOCOUNT ON
SET ANSI_WARNINGS Off
Select
MAX([Group]) as [Group],
MAX(Division) as Division,
MAX(Team) as Team,
(
SELECT DATEADD(mi,SUM(LEFT(WeekHours,CHARINDEX(':',WeekHours)-1)*60 + RIGHT(WeekHours,2)),0)
)
SELECT CAST(DATEDIFF(hh,0,DT) AS VARCHAR(10))
+ SUBSTRING(CONVERT(CHAR(8),DT,108),3,3)
FROM cteToDateTime,
max(case
When datepart(mm,s.[Date])='01' Then 'January'
When datepart(mm,s.[Date])='02' Then 'February'
When datepart(mm,s.[Date])='03' Then 'March'
When datepart(mm,s.[Date])='04' Then 'April'
When datepart(mm,s.[Date])='05' Then 'May'
When datepart(mm,s.[Date])='06' Then 'June'
When datepart(mm,s.[Date])='07' Then 'July'
When datepart(mm,s.[Date])='08' Then 'August'
When datepart(mm,s.[Date])='09' Then 'September'
When datepart(mm,s.[Date])='10' Then 'October'
When datepart(mm,s.[Date])='11' Then 'November'
When datepart(mm,s.[Date])='12' Then 'December'
End) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],
CONVERT(varchar(6), (7 * count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))) + ':' + RIGHT('0' + CONVERT(varchar(2),'00' % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), '00' % 60), 2) as [Standard Hours],
CONVERT(varchar(6), (sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin))) / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2),
(sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) +
sum(datepart(second,Timenetin))) % 60), 2) as [Actual Hours],
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end)))/(3600)) As Hrs_Diff,
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))) %(3600)/60) As Min_Diff,
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))) %60) as Sec_Diff
from StaffDay s
inner join Staff st
on s.Staff_id = st.Staff_id
where s.Dayname NOT IN ('Sun','Sat')
and s.Bank_holiday_flg = 'N'
and datepart(mm,s.[Date]) IN ('01','02', '03','04','05','06','07','08','09','10','11','12')
and datepart(yyyy,s.[Date]) = '2012' --IN (@Year)
and st.active = 'Y'
group by datepart(mm,s.[Date])
order by datepart(mm,s.[Date]) asc
----------------------------------------
March 27, 2012 at 2:37 am
Just shooting in the dark here but maybe if you rearranged your statements as follows it might get you closer:
ALTER PROCEDURE [dbo].[timelord_year]
@Year [nvarchar](4000)
SET NOCOUNT ON
SET ANSI_WARNINGS Off
;WITH
cteToDateTime(DT) AS
(
SELECT DATEADD(mi,SUM(LEFT(WeekHours,CHARINDEX(':',WeekHours)-1)*60 + RIGHT(WeekHours,2)),0)
FROM <YOUR TABLE NAME HERE>
)
Select
MAX([Group]) as [Group],
MAX(Division) as Division,
MAX(Team) as Team,
(SELECT CAST(DATEDIFF(hh,0,DT) AS VARCHAR(10))
+ SUBSTRING(CONVERT(CHAR(8),DT,108),3,3)
FROM cteToDateTime) AS SomeFieldName,
etc. (where you use the CASE to establish the month name instead of using DATENAME(month ...) as you should be).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 27, 2012 at 4:39 am
dwain.c (3/27/2012)
Just shooting in the dark here but maybe if you rearranged your statements as follows it might get you closer:ALTER PROCEDURE [dbo].[timelord_year]
@Year [nvarchar](4000)
SET NOCOUNT ON
SET ANSI_WARNINGS Off
;WITH
cteToDateTime(DT) AS
(
SELECT DATEADD(mi,SUM(LEFT(WeekHours,CHARINDEX(':',WeekHours)-1)*60 + RIGHT(WeekHours,2)),0)
FROM <YOUR TABLE NAME HERE>
)
Select
MAX([Group]) as [Group],
MAX(Division) as Division,
MAX(Team) as Team,
(SELECT CAST(DATEDIFF(hh,0,DT) AS VARCHAR(10))
+ SUBSTRING(CONVERT(CHAR(8),DT,108),3,3)
FROM cteToDateTime) AS SomeFieldName,
etc. (where you use the CASE to establish the month name instead of using DATENAME(month ...) as you should be).
Thanks a lot for your suggestion, however, I now get an error -
Incorrect syntax near the keyword 'SET'. dont know what I am doing wrong....this is how I have amended the SP-
------------------------------
ALTER PROCEDURE [dbo].[timelord_year]
@Year [nvarchar](4000)
SET NOCOUNT ON
SET ANSI_WARNINGS Off
;WITH cteToDateTime(DT) AS
(
SELECT DATEADD(mi,SUM(LEFT(WeekHours,CHARINDEX(':',WeekHours)-1)*60 + RIGHT(WeekHours,2)),0) from staff
)
Select
MAX([Group]) as [Group],
MAX(Division) as Division,
MAX(Team) as Team,
(SELECT CAST(DATEDIFF(hh,0,DT) AS VARCHAR(10)) + SUBSTRING(CONVERT(CHAR(8),DT,108),3,3)
FROM cteToDateTime) AS tt,
max(case
When datepart(mm,s.[Date])='01' Then 'January'
When datepart(mm,s.[Date])='02' Then 'February'
When datepart(mm,s.[Date])='03' Then 'March'
When datepart(mm,s.[Date])='04' Then 'April'
When datepart(mm,s.[Date])='05' Then 'May'
When datepart(mm,s.[Date])='06' Then 'June'
When datepart(mm,s.[Date])='07' Then 'July'
When datepart(mm,s.[Date])='08' Then 'August'
When datepart(mm,s.[Date])='09' Then 'September'
When datepart(mm,s.[Date])='10' Then 'October'
When datepart(mm,s.[Date])='11' Then 'November'
When datepart(mm,s.[Date])='12' Then 'December'
End) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],
CONVERT(varchar(6), (7 * count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))) + ':' + RIGHT('0' + CONVERT(varchar(2),'00' % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), '00' % 60), 2) as [Standard Hours],
/*
dbo.f_timeformat(sum(datediff(HH,'00:00:00',MonHours)) + sum(datediff(mm,'00:00:00',TueHours)) + sum(datediff(mm,'00:00:00',WedHours)) +
sum(datediff(mm,'00:00:00',ThuHours)) + sum(datediff(mm,'00:00:00',FriHours))) as tt,
*/
/*
CAST(SUBSTRING(WeekHours,CHARINDEX(':',WeekHours)+1,DATALENGTH(WeekHours))AS INT)+CAST(SUBSTRING(WeekHours,CHARINDEX(':',WeekHours)+1,DATALENGTH(WeekHours))AS INT)
SELECT CAST(CAST(SUBSTRING(WeekHours,1,CHARINDEX(':',WeekHours)-1) AS INT)
+CAST(SUBSTRING(WeekHours,1,CHARINDEX(':',WeekHours)-1)AS INT)
+(WeekHours - (WeekHours%60))/60 AS VARCHAR(10))+':'+ CAST(WeekHours%60 AS VARCHAR(2)) as tt,
*/
CONVERT(varchar(6), (sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin))) / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2),
(sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) +
sum(datepart(second,Timenetin))) % 60), 2) as [Actual Hours],
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end)))/(3600)) As Hrs_Diff,
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))) %(3600)/60) As Min_Diff,
convert(varchar(10),((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin) * 1)) - (25200* count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))) %60) as Sec_Diff
from StaffDay s
inner join Staff st
on s.Staff_id = st.Staff_id
where s.Dayname NOT IN ('Sun','Sat')
and s.Bank_holiday_flg = 'N'
and datepart(mm,s.[Date]) IN ('01','02', '03','04','05','06','07','08','09','10','11','12')
and datepart(yyyy,s.[Date]) = '2012' --IN (@Year)
and st.active = 'Y'
group by datepart(mm,s.[Date])
order by datepart(mm,s.[Date]) asc
------------------------------
March 27, 2012 at 5:07 am
Try this and see if it helps get you past it.
DECLARE @Year [nvarchar](4000)
--SET NOCOUNT ON
--SET ANSI_WARNINGS Off
If that works you can uncomment the two SET statements.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 28, 2012 at 2:49 am
dwain.c (3/27/2012)
Try this and see if it helps get you past it.DECLARE @Year [nvarchar](4000)
--SET NOCOUNT ON
--SET ANSI_WARNINGS Off
If that works you can uncomment the two SET statements.
I am not able to get to work...I was thinking that there is another option to do the same thing.....
Is it possible to add the time datatype fields ?
The following fields in the table have a time datatype-
MonHours
TueHours
WedHours
ThuHours
Frihours
The requirement is to add the values for all the fields above after doing a SUM for each of them.
It should be something like this but I am not sure about the exact code and that is the reason for my query-
SUM(MonHours) + SUM(TueHours) + SUM(WedHours) + SUM(ThuHours) + SUM(Frihours)
Would that be possible ? Please find the table DDL and the sample data attached.
Thanks,
Paul
March 28, 2012 at 8:03 am
Try this. Tested on SQL 2008 (10.0.4000). Might not be optimal, but makes it readable.
create function dbo.fcn_ConvertTimeToMinutes(@Time Time)
returns int
begin
declare @r int;
select @r = substring(CONVERT(varchar(5), @Time), 1,2) * 60 + substring(CONVERT(varchar(5), @Time), 4,2)
return (@r)
end
create function dbo.fcn_ConvertMinutesToHours(@Minutes int)
returns varchar(7)
begin
declare @Hours varchar(7)
select @hours = right('00'+convert(varchar(3),@minutes / 60),2) + ':' + right('00'+convert(varchar(2),@minutes%60),2)
return (@hours)
end
Then the select for the table:
select
dbo.fcn_ConvertMinutesToHours(
dbo.fcn_ConvertTimeToMinutes(MonHours)
+dbo.fcn_ConvertTimeToMinutes(TueHours)
+dbo.fcn_ConvertTimeToMinutes(WedHours)
+dbo.fcn_ConvertTimeToMinutes(ThuHours)
+dbo.fcn_ConvertTimeToMinutes(FriHours)
) as SumTime
from staff_test
March 28, 2012 at 9:47 am
Anders Pedersen (3/28/2012)
Try this. Tested on SQL 2008 (10.0.4000). Might not be optimal, but makes it readable.
create function dbo.fcn_ConvertTimeToMinutes(@Time Time)
returns int
begin
declare @r int;
select @r = substring(CONVERT(varchar(5), @Time), 1,2) * 60 + substring(CONVERT(varchar(5), @Time), 4,2)
return (@r)
end
create function dbo.fcn_ConvertMinutesToHours(@Minutes int)
returns varchar(7)
begin
declare @Hours varchar(7)
select @hours = right('00'+convert(varchar(3),@minutes / 60),2) + ':' + right('00'+convert(varchar(2),@minutes%60),2)
return (@hours)
end
Then the select for the table:
select
dbo.fcn_ConvertMinutesToHours(
dbo.fcn_ConvertTimeToMinutes(MonHours)
+dbo.fcn_ConvertTimeToMinutes(TueHours)
+dbo.fcn_ConvertTimeToMinutes(WedHours)
+dbo.fcn_ConvertTimeToMinutes(ThuHours)
+dbo.fcn_ConvertTimeToMinutes(FriHours)
) as SumTime
from staff_test
Thanks a lot Anders, your code worked fine but it only sums up the values from MonHours to Frihours.
Actually, I have an additional requirement, which is to SUM up the individual rows - MonHours, TuesHours, WedHours, ThuHours,FriHours first and then add all the 5 columns as you have already done in your code.
I have tried to modify your code to get a SUM for the individual rows but get an error every time. Would you know how to do that ?
Thanks,
Paul
March 28, 2012 at 10:06 am
Should have everything you need.
Just sum on the converted to minute columns however you want it summed, then add the values together.
I guess what you really want here is the total number of hours worked for a team during a period of time. Writing it using the functions I provided is no different than you would normally sum anything in SQL, except you need to wrap the columns in the function that converts to minutes first, then take the entire sum and convert it back to hours.
March 29, 2012 at 3:16 am
Anders Pedersen (3/28/2012)
Should have everything you need.Just sum on the converted to minute columns however you want it summed, then add the values together.
I guess what you really want here is the total number of hours worked for a team during a period of time. Writing it using the functions I provided is no different than you would normally sum anything in SQL, except you need to wrap the columns in the function that converts to minutes first, then take the entire sum and convert it back to hours.
Thanks Anders.
I tried to SUM the columns as you mentioned but I get an error-
Operand data type time is invalid for sum operator.
This is how I have modified the code you created-
MAX(dbo.fcn_ConvertMinutesToHours(
dbo.fcn_ConvertTimeToMinutes(SUM(MonHours))
+dbo.fcn_ConvertTimeToMinutes(SUM(TueHours))
+dbo.fcn_ConvertTimeToMinutes(SUM(WedHours))
+dbo.fcn_ConvertTimeToMinutes(SUM(ThuHours))
+dbo.fcn_ConvertTimeToMinutes(SUM(FriHours))
)) as SumTime
I need to use MAX so as to avoid this error-
Column 'Staff.MonHours' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I am really stuck with now and can't find a way around it.
March 29, 2012 at 4:13 am
Just looking into this thread again...
What I can say, crap data structure design quickly leads to crap and stupid coding you need to do to achieve something good!
Calculating VARCHARs - Plain stupid!
Can some someone come up with one good reason why would you want to store time as varchar in HH:MM format? Just one!
And don't tell me it's because of nice formatting as it it's not even that! It's not "HH:MM" it is: "#00:00" !
Verdict: REDESIGN!
March 29, 2012 at 5:58 am
Eugene Elutin (3/29/2012)
Just looking into this thread again...What I can say, crap data structure design quickly leads to crap and stupid coding you need to do to achieve something good!
Calculating VARCHARs - Plain stupid!
Can some someone come up with one good reason why would you want to store time as varchar in HH:MM format? Just one!
And don't tell me it's because of nice formatting as it it's not even that! It's not "HH:MM" it is: "#00:00" !
Verdict: REDESIGN!
If you don't know anything about the query or don't have any suggestions, its better you don't make a fool of yourself and keep everything to youself.
Did you even read my last query ? It does not concern the varchar column anymore as I already know what you have mentiioned. Its related to sum of the other Time columns in the table.
Don't waste my time by blocking my query with your stupid replies.
March 29, 2012 at 6:40 am
f you don't know anything about the query or don't have any suggestions, its better you don't make a fool of yourself and keep everything to youself.
Did you even read my last query ? It does not concern the varchar column anymore as I already know what you have mentiioned. Its related to sum of the other Time columns in the table.
Don't waste my time by blocking my query with your stupid replies
Hardly a good way to elicit further support!
While Eugene's tone may not have set well with you, he was quite right.
When the house stinks it's probably time to take out the garbage.
I have had to often resort to rewrites to fix issues. It is highly unrewarding work - trust me on this. However you will likely find that it is much easier the second time around to get it right.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 29, 2012 at 6:46 am
Eugene Elutin (3/29/2012)
Just looking into this thread again...What I can say, crap data structure design quickly leads to crap and stupid coding you need to do to achieve something good!
Calculating VARCHARs - Plain stupid!
Can some someone come up with one good reason why would you want to store time as varchar in HH:MM format? Just one!
And don't tell me it's because of nice formatting as it it's not even that! It's not "HH:MM" it is: "#00:00" !
Verdict: REDESIGN!
Heh... calm down ol' friend. It doesn't sound good when Celko does that. What makes you think you'll sound better? I think Celko makes a real *** out of himself when he loses it like this. We've all had to deal with "crap data" before and it's usually out of our control. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2012 at 6:49 am
dwain.c (3/26/2012)
Perhaps I didn't adapt your code properly to my temporary table. Can you tell me what I did wrong?
Sorry. Lost track of this post in all the email I get. I'll take a look tonight.
The scope of this thread has certainly grown compared to the original requirements! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply