GROUP BY Warning. 1

[tqeonline]

I have a query that produce this date:

CYCLEDATE POINT ACTUAL_PCT
2010-09-20 03:00:00.000 0.147 0
2010-09-20 04:00:00.000 0.147 0
2010-09-20 09:00:00.000 0.333 0
2010-09-20 10:00:00.000 0.333 0
2010-09-20 11:00:00.000 0.333 0
2010-09-20 23:00:00.000 0.778 0
2010-09-21 00:00:00.000 0.778 1
2010-09-21 01:00:00.000 0.778 1
2010-09-21 02:00:00.000 1.019 1
2010-09-21 03:00:00.000 1.019 1

In my select I have this:

SELECT   MIN(CYCLEDATE) as 'Date', POINT AS 'Projected %', ACTUAL_PCT as 'Actual %'
FROM     #PROJECTED_CURVE, SQA_DASH_PROJECTED_CURVE WITH (NOLOCK)
WHERE #PROJECTED_CURVE.DURATION_PCT = SQA_DASH_PROJECTED_CURVE.[ORDER]
GROUP BY  POINT, ACTUAL_PCT
ORDER BY CYCLEDATE

It produces an error "CYCLEDATE" is not being used in the group by.

However before this it was producing errors

Warning: Null value is eliminated by an aggregate or other SET operation.

So I put this at the top

SET ANSI_WARNINGS OFF

My question. How do I group by two columns so that the date looks like this:

CYCLEDATE POINT ACTUAL_PCT
2010-09-20 03:00:00.000 0.147 0
2010-09-20 09:00:00.000 0.333 0
2010-09-20 23:00:00.000 0.778 0
2010-09-21 00:00:00.000 0.778 1
2010-09-21 02:00:00.000 1.019 1



- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

The whole query is this: (but this is just for reference)

DECLARE @COUNT DECIMAL (6,1)
DECLARE @START_DATE DATETIME
DECLARE @END_DATE DATETIME
DECLARE @DURATION_HH DECIMAL (6,1)
DECLARE @DURATION_PCT DECIMAL (6,3)
DECLARE @ACTUAL_PCT INT
DECLARE @Cycle INT

SET ANSI_WARNINGS OFF

CREATE TABLE #PROJECTED_CURVE --Prepare Temp Table
(
  CYCLEDATE Datetime,
  DURATION_PCT Decimal(6,3),
  ACTUAL_PCT INT
)

SET @COUNT = 0
SET @Cycle = 1111

--Retrieve and set Start Date
SELECT @START_DATE = RCYC_START_DATE 
FROM RELEASE_CYCLES WITH (NOLOCK)
WHERE RCYC_ID = @Cycle

--Retrieve and set End Date
SELECT @END_DATE = RCYC_END_DATE
FROM RELEASE_CYCLES WITH (NOLOCK)
WHERE RCYC_ID = @Cycle

--Calculate project Duration in hours based on start date and end date
SET @DURATION_HH = DATEDIFF(HH, @START_DATE, @END_DATE) + 24

--Build a row for each hour in Cycle
WHILE (@COUNT) < @DURATION_HH + 1
BEGIN

        --Retrieve the % Passed Value for each day of the Cycle
      SET @ACTUAL_PCT =             
            (CASE WHEN  DATEADD(HH,@COUNT,@START_DATE) <= GetDate() 
             THEN (isnull((SELECT CAST(ROUND(SUM(CAST(PASSED.CNT AS DECIMAL) / CAST(TOTAL.CNT AS DECIMAL)) * 100 ,0) AS INT) AS ACTUAL_PCT
                  FROM
                  (SELECT TC_ASSIGN_RCYC, COUNT(Case When TC_STATUS = 'Passed' Then 1 End) AS CNT
                  FROM TESTCYCL WITH (NOLOCK)          
                  WHERE TESTCYCL.TC_ASSIGN_RCYC = @Cycle
                  AND TC_EXEC_DATE <= DATEADD(HH,@COUNT,@START_DATE)
                  GROUP BY TC_ASSIGN_RCYC) AS PASSED,

                  (SELECT TC_ASSIGN_RCYC, COUNT(*) AS CNT
                  FROM TESTCYCL WITH (NOLOCK)          
                  WHERE TESTCYCL.TC_ASSIGN_RCYC = @Cycle
                  GROUP BY TC_ASSIGN_RCYC) AS TOTAL

                  WHERE TOTAL.TC_ASSIGN_RCYC = PASSED.TC_ASSIGN_RCYC) ,0))
                  
            WHEN  DATEADD(HH,@COUNT,@START_DATE) > GetDate() 
            THEN null
            ELSE 0 End)



      --Calculate the Duration PCT for the hour of the Cycle
      SET @DURATION_PCT = ROUND(((@COUNT / @DURATION_HH) * 100) ,0)

      --Insert the values into the temp table
      INSERT INTO #PROJECTED_CURVE (CYCLEDATE, DURATION_PCT, ACTUAL_PCT)
      VALUES (DATEADD(HH,@COUNT,@START_DATE), @DURATION_PCT, @ACTUAL_PCT)

      Set @COUNT = @COUNT + 1
CONTINUE
END

SELECT   MIN(CYCLEDATE) as 'Date', POINT AS 'Projected %', ACTUAL_PCT as 'Actual %'
FROM     #PROJECTED_CURVE, SQA_DASH_PROJECTED_CURVE WITH (NOLOCK)
WHERE #PROJECTED_CURVE.DURATION_PCT = SQA_DASH_PROJECTED_CURVE.[ORDER]
GROUP BY  CYCLEDATE,POINT, ACTUAL_PCT
ORDER BY CYCLEDATE

DROP TABLE #PROJECTED_CURVE

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

fixed...

SELECT   MIN(CYCLEDATE) as 'Date', POINT AS 'Projected %', ACTUAL_PCT as 'Actual %'
FROM     #PROJECTED_CURVE, SQA_DASH_PROJECTED_CURVE WITH (NOLOCK)
WHERE #PROJECTED_CURVE.DURATION_PCT = SQA_DASH_PROJECTED_CURVE.[ORDER]
GROUP BY  POINT, ACTUAL_PCT
ORDER BY MIN(CYCLEDATE)

Wish I could thank myself

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[markros]

No problem

PluralSight Learning Library