Loop to remove duplicates? 1

[tqeonline]

Hey guys i have this query:

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)

            CREATE TABLE #PROJECTED_CURVE --Prepare Temp Table
            (
              CYCLEDATE Datetime,
              [COUNT] INT,
              DURATION_HH INT,
              DURATION_PCT Decimal(6,3),
              PROJPOINT Decimal(6,3)
            )

            SET @COUNT = 0

            --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(DD, @START_DATE, @END_DATE) + 1

            --Build a row for each hour in Cycle
            WHILE (@COUNT) < @DURATION_HH + 1
            BEGIN
                  --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, [COUNT], DURATION_HH, DURATION_PCT, PROJPOINT)
                  VALUES (DATEADD(DD,@COUNT,@START_DATE), @COUNT, @DURATION_HH, @DURATION_PCT, 0.000)
      
                  Set @COUNT = @COUNT + 1
            CONTINUE
            END

            SELECT CYCLEDATE, POINT FROM #PROJECTED_CURVE, SQA_DASH_PROJECTED_CURVE WITH (NOLOCK)
            WHERE #PROJECTED_CURVE.DURATION_PCT = SQA_DASH_PROJECTED_CURVE.[ORDER]

            DROP TABLE #PROJECTED_CURVE

What it does is creates a temp table with the number of hours between given days... then it goes and finds a % on a table and puts it next to the hour/day.

So your end result is two columns,

CYCLEDATE POINT

Cycle day is the day and individual hour and POINT is the percentage.

Sometimes the percentages are duplicate... So how could I loop through this table after it is create and remove the duplicate rows (based on "POINT") then present that data?

- 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

 

[AndyInNC]

Does your output look like this?

CYCLEDATE               POINT
10/1/2010 1:00          2.1
10/1/2010 2:00          [highlight]2.2[/highlight]
10/1/2010 3:00          [highlight]2.2[/highlight]
10/1/2010 4:00          2.3
10/1/2010 5:00          2.4

...and could it look like this?

CYCLEDATE               POINT
10/1/2010 1:00          2.1
10/1/2010 2:00          [highlight]2.2[/highlight]
10/1/2010 3:00          2.3
10/1/2010 4:00          2.4
10/1/2010 5:00          [highlight]2.2[/highlight]

Please clarify that part.

 

[tqeonline]

It looks like the first THIS and no it wouldn't look like the second this.

- 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

 

[AndyInNC]

Good, that makes it easier.

I avoid looping in SQL whenever possible since the language is geared toward groups of records. Most often, a loop acts on a small subset of records, or even a single record.

This should give you what you are looking for:

SELECT   Min(CYCLEDATE) AS MinOfCYCLEDATE, POINT
FROM     #PROJECTED_CURVE
GROUP BY POINT
ORDER BY MinOfCYCLEDATE

 

[tqeonline]

That did it. Wow that was easy!

Thank you so much

Final code

            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)

            CREATE TABLE #PROJECTED_CURVE --Prepare Temp Table
            (
              CYCLEDATE Datetime,
              [COUNT] INT,
              DURATION_HH INT,
              DURATION_PCT Decimal(6,3),
              PROJPOINT Decimal(6,3)
            )
            SET @COUNT = 0

            --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
                  --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, [COUNT], DURATION_HH, DURATION_PCT, PROJPOINT)
                  VALUES (DATEADD(HH,@COUNT,@START_DATE), @COUNT, @DURATION_HH, @DURATION_PCT, 0.000)
      
                  Set @COUNT = @COUNT + 1
            CONTINUE
            END

            SELECT   Min(CYCLEDATE) AS CYCLEDATE, POINT
            FROM     #PROJECTED_CURVE, SQA_DASH_PROJECTED_CURVE WITH (NOLOCK)
            WHERE #PROJECTED_CURVE.DURATION_PCT = SQA_DASH_PROJECTED_CURVE.[ORDER]
            GROUP BY POINT
            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]

Given this query. How would you get a fourth column that had a running total of the difference between open and closed:

Data appears like this:

Date Opened Closed
10-1-2010 3 2
10-2-2010 1 2
10-3-2010 2 1
10-4-2010 4 0

I want it to do this:

Date Opened Closed Active (aka left over)
10-1-2010 3 2 1
10-1-2010 1 2 0
10-1-2010 2 1 1
10-1-2010 4 0 5

A running total.


- 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

 

[AndyInNC]

Technically speaking, this should be a different thread since it is a different subject. It makes it easier to search for by others in the future. But this is pretty quick so here it is.

You can do certain calculations within the SELECT statement like

[tt]
SELECT CYCLEDATE,
Opened,
Closed,
[Opened] - [Closed] AS [Active]
FROM ...
[/tt]

That can be run on the fly or from a temp table.