I have a table like so.
[Job Lot] [Job ID] [Child Job ID] [Time Completed] [Order of Jobs Done]
1 11 100 10 Pos 1
1 11 101 20 Pos 2
1 11 103 30 Pos 3
2 12 101 30 Pos 1
2 12 105 10 Pos 3
Each Child Job ID represents a delivery to a particular site like New York, Washington or LA each week.
So I need to work work out say for Child ID 101 what is the average postion . I see in JOB ID 11 it has Child Job ID 100 in POS 2 and in JOB ID 12 it is in POS 1, so the min is POS1 , the max is POS2, and the average is POS 1.5. So what is the spread of CHILD JOB ID and position is the real question , so out of 4 CHILD JOB IDS of say 101 what is the spread, is it always usuallt at postion 1.
For CHILD JOB ID 10 , I want the lowest rank of Position 0 , the highest rank value of Position 3 and the Average vlaue not considering any null values and only divide by number of non null vlaues. In this case the the average rank would be Postion 2.
So I need to add 3 columns to store these values ? How can I do this in one query ? Do I need to pivot the data first ?
SELECT [JOB LOT],[CHILD JOB ID], MIN([ORDER OF JOBS DONE])AS [MIN],MAX([ORDE OF JOBS DONE])AS [MAX]
FROM TABLE A WHERE [ORDER OF JOBS DONE] IS NOT NULL group by [JOB LOT],[ORDER OF JOBS DONE]