Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2012
    Posts
    24

    Unanswered: Help with same row values from subquery

    I have a subquery that I'm using in my inner select, but the problem is that it is returning the same value for each row. How can I modify the query properly, so that the same value is not returned for each row?

    Here's a sample of my current results:

    Day Hour Hist Volume Hist Proc Time Volume Active Proc Time
    Sun 1:00 AM 1243 0.735566 40 0.66661
    Sun 3:00 AM 1154 0.735566 4 0.5385
    Sun 5:00 AM 1163 0.735566 1 0.67354
    Sun 7:00 AM 1342 0.735566 9 0.563192
    Sun 9:00 AM 1386 0.735566 59 0.68794
    Sun 11:00 AM 1421 0.735566 126 0.50247

    Here is my query:

    Code:
    DECLARE @StartDate Datetime, @EndDate Datetime
    SET @StartDate = '2011-06-19'
    SET @EndDate = '2011-06-25'
    
    SELECT 
    	LEFT([Day],3) AS [Day],
    	[Hour],
    	COUNT([Total HistVolume M_Active]) AS [Hist Volume],
    	AVG([Total HistProcTime M_Active]) AS [Hist Proc Time],
    	AVG([Total Volume M_Active]) AS [Volume],
    	AVG([Total ProcTime M_Active]) AS [Active Proc Time]
    	
    FROM 
    	(
    		SELECT 
    			DATEPART(weekday,[TimeStamp]) AS [DayNo],
    			DATENAME(weekday,[TimeStamp]) AS [Day],
    			DATEPART(hour, [TimeStamp]) AS [HourVal],
    			CASE DATEPART(hour, [TimeStamp])
    				WHEN 1 THEN '1 AM'
    				WHEN 3 THEN '3 AM'
    				WHEN 5 THEN '5 AM'
    				WHEN 7 THEN '7 AM'
    				WHEN 9 THEN '9 AM'
    				WHEN 11 THEN '11 AM'
    				WHEN 13 THEN '1 PM'
    				WHEN 15 THEN '3 PM'
    				WHEN 17 THEN '5 PM'
    				WHEN 19 THEN '7 PM'
    				WHEN 21 THEN '9 PM'
    				WHEN 23 THEN '11 PM'
    			END	AS [Hour],
    			cvh.ClaimCount AS [Total HistVolume M_Active],
    			(SELECT 
    				AVG(ProcessingTime) 
    			 FROM 
    				CLAIMS.M_Active 
    			 WHERE 
    				[TimeStamp] BETWEEN DATEADD(year, -1, @StartDate + '00:00:00.000') 
    				and 
    				DATEADD(year, -1, @EndDate + '23:59:59.999')) AS [Total HistProcTime M_Active],
    			COUNT([TimeStamp]) AS [Total Volume M_Active],
    			AVG(ProcessingTime) AS [Total ProcTime M_Active]
    			
    		FROM 
    			CLAIMS.M_Active a
    			INNER JOIN CLAIMS.ClaimVolumeHistory cvh
    			ON a.Plan_ID = cvh.PlanID
    		WHERE 
    			[TimeStamp] BETWEEN @StartDate + '00:00:00.000' AND @EndDate + '23:59:59.999'
    			AND
    			DATEPART(hour, [TimeStamp]) in (1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23)
    		GROUP BY 
    			DATEPART(weekday,[TimeStamp]), 
    			DATENAME(weekday,[TimeStamp]),
    			DATEPART(hour, [TimeStamp]),
    			cvh.ClaimCount
    	)t
    	
    GROUP BY [DayNo], [Day], [HourVal], [Hour]
    ORDER BY [DayNo], [HourVal]

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Correlate the subquery to an appropriate column.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Help with same row values from subquery

    Are you missing a condition in your subquery?

    Code:
    (SELECT AVG(ProcessingTime) 
     FROM CLAIMS.M_Active
     WHERE [TimeStamp] BETWEEN DATEADD(year, -1, @StartDate + '00:00:00.000') 
     AND DATEADD(year, -1, @EndDate + '23:59:59.999')
     AND Plan_ID = cvh.PlanID
    ) AS [Total HistProcTime M_Active],
    Last edited by homerow; 02-03-12 at 13:56. Reason: query re-write

  4. #4
    Join Date
    Jan 2012
    Posts
    24
    Thanks Pat, but how is that done?

  5. #5
    Join Date
    Jan 2012
    Posts
    24
    Hi Homerow,

    No a join is not needed in the subquery. Just to try though, I did include it and still get the same results as I originally posted.

  6. #6
    Join Date
    Jan 2012
    Posts
    24
    I've been looking at my query, but I don't see how I can correlate the subquery with a column from the outer query, because the only thing common between them are the parameters used in the WHERE clauses. Is there a better way of formulating the query to get the desired results?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm really confused... If you don't correlate the subquery, why would you expect the value (result) to change for every row? I would expect them to all be the same.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jan 2012
    Posts
    24
    Yes and that does make sense, thinking about it. The question is now how can I restructure my query to do this properly?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ummm.... Define properly ????

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jan 2012
    Posts
    24
    Properly meaning, so that the desired results are achieve, (i.e. the same value for the row is not duplicated like it currently is).

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I guess that I'm not asking the correct question.

    My problem is that I don't understand what criteria should be used to differentiate the values. I don't know enough about the real world (business) requirements that you are tyring to meet in order to help you to meet them.

    What shuld this column represent? What does it correlate to that determines the "rightness" of the answer?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jan 2012
    Posts
    24
    This column will contain the historical processing time that is determined by selecting the processing time from the CLAIMS.M_Active table based on the selected date range minus 1 year.

    Does that make sense?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That definition still implies a single value to me, although I'd calculate it by subtracting a year from both the start and the end dates and using those values to define a new range. Unless you change the definition, I think that there should only be one value that is returned for every row in the result set.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Jan 2012
    Posts
    24
    I think that there should only be one value that is returned for every row in the result set.
    This is what I'm trying to get help achieving. In the query I posted I also subtract a year from both the start and end dates. With my query how can it be modified to give only one value per row in the result set?

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As far as I can tell, your code returns one value of 0.735566 for every row in the result set. This is what I would expect based on the definition you've written and the code that you've used to implement that defintion.

    I'm sorry, but I can't see what your problem is. I understand that this is frustrating for you, but I can't understand what the problem is so I can't help you fix it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •