var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Group rows according to criteria
I have a SQL Table named "Processes" with 3 rows:
Item| Process | Date
34 | Paint | 4/12/11
34 | Welding | 1 /12/11
37 | Arrange | 5/3/11
37 | Paint |8 / 3 / 11
37 | Pack | 12 /3/11
I need to group it into another table the values "Item" plus a new record named "Dispersion" that shows the difference between the newest and the farthest date for a given "Item".
By example, for the upper table the result should be:
34 | 3 (=4-1)
37 |7 (=12-5)
I have thousands of Items in my table and each Item can have 2 or plus Processes with its date.
Thank you very much!
Last edited by vautrin; 06-10-11 at 19:34.
Reason: change title
, DATEDIFF(day,MAX(date),MIN(date)) AS Dispersion
Thanx very much!!! Issue solved
thanx very much!!! Issue solved
Originally Posted by r937
Hi r937 , it's me again...
I wonder that if I have only one date value, the query returns "0", the same as I have two equals dates...
I would need the query returns NULL or other value not equal to "0" when there is only 1 date to count.
is this possible?
This should do it:
, NULLIf(DATEDIFF(day, MIN([date]), MAX([date])), 0) AS Dispersion
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Thank you Wim.
it was perfect! Problem solved.
Tags for this Thread