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.
| Author |
Topic |
|
rob41
Yak Posting Veteran
67 Posts |
 Posted - 2010-03-26 : 09:39:44
|
| I'm trying to get the difference between two fields by subtracting one field from the other then it should save as a percentage of the difference. I believe I can accomplish this with DATEDIFFI placed the orignal lines back in the code and commented them out. What i'm trying to do is be able to take one field INSERT INTO [TBLUpdate Percent of Day] ([Ship Date], [Week Ending calc], [LoadId], [ShipId], [Route Id], [OrderTIMESTAMP], [LoadingTIMESTAMP])--[Loading TIMESTAMP] - [OrderTIMESTAMP] AS [percent of day calc]) SELECT DATEDIFF(HOUR,(SELECT Max[LoadingTIMESTAMP],Min[OrderTIMESTAMP]) AS [percent of day calc], [Shipments].[Ship Date], [Shipments].[Week Ending calc], [Shipments].[LoadId], [Shipments].[ShipId], [Shipments].[Route Id], [Shipments].[OrderTIMESTAMP] [Shipments].[LoadingTIMESTAMP] --[Shipments].[LoadingTIMESTAMP] - [OrderTIMESTAMP] AS [percent of day calc] FROM [Shipments]; |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-26 : 09:53:45
|
Can you give sample data?Why should a difference in hours be a percentage?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-26 : 10:41:32
|
I hope that someone else can understand but I can't 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-26 : 11:27:51
|
| i cant really make out purpose of your query. you cant use MIN() and MAX() along with individual values unless you apply GROUP BY. are you looking for min and max values within a group? if yes, what all should involve a group?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rob41
Yak Posting Veteran
67 Posts |
Posted - 2010-03-26 : 11:40:20
|
quote:
Originally posted by visakh16
i cant really make out purpose of your query. you cant use MIN() and MAX() along with individual values unless you apply GROUP BY. are you looking for min and max values within a group? if yes, what all should involve a group?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/min and max can come out of query i'm just trying to find out how to get [LoadingTIMESTAMP] which would be random times such as 8-Apr-2009 11:15 am - [OrderTIMESTAMP] which would be a random time like 10-Apr-2009 3:33 pm which would be saved as [percent of day calc] which is a percentage such as 0.325625000004948 of the two fields. I looked around the web and was trying to accomplish this with DATEDIFF. but I'm open to whatever can get this type of result for me
|
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-27 : 02:13:19
|
quote:
Originally posted by rob41
quote:
Originally posted by visakh16
i cant really make out purpose of your query. you cant use MIN() and MAX() along with individual values unless you apply GROUP BY. are you looking for min and max values within a group? if yes, what all should involve a group?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/min and max can come out of query i'm just trying to find out how to get [LoadingTIMESTAMP] which would be random times such as 8-Apr-2009 11:15 am - [OrderTIMESTAMP] which would be a random time like 10-Apr-2009 3:33 pm which would be saved as [percent of day calc] which is a percentage such as 0.325625000004948 of the two fields. I looked around the web and was trying to accomplish this with DATEDIFF. but I'm open to whatever can get this type of result for me
so you mean you've multiple records present in table per [Shipments].[Ship Date],[Shipments].[Week Ending calc],[Shipments].[LoadId],[Shipments].[ShipId],[Shipments].[Route Id], group?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
DATEDIFF