[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

Assigning Unique Variables

I’ve got an SQL statement run again SQL Server 2005 identifying the years with completed work orders:

  "SELECT dbo.Workorder.year_id AS Year FROM dbo.Admin INNER JOIN dbo.Workorder ON dbo.Admin.wo_id = dbo.Workorder.wo_id GROUP BY dbo.Workorder.year_id, dbo.Admin.status HAVING (COUNT(*) >= 1) AND (dbo.Admin.status = 'Completed') ORDER BY COUNT(*) DESC"

How do I take the resulting years and assign them to unique string variables that I can use elsewhere in code?

I know I can create a loop to see the results within a table:

<% Dim sYear
 
While ((Repeat1__numRows <> 0) AND (NOT rsYearCount.EOF))
 
       sYear = rsYearCount.Fields.Item("Year").Value)
      Response.Write "<td ><strong>" & sYear & "</strong></td>"
 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsYearCount.MoveNext()
Wend

%>

But if I need to plug in the different year values elsewhere in code, I’m thinking I’d have to create a loop each time .  There’s got to be a better way….   Be gentle, I’m not good with this….  Thanks
0
TCCIRM
Asked:
TCCIRM
1 Solution
 
Scott Fell, EE MVEDeveloperCommented:
When you are using Group By, the only unique ID to the related record is going to be for that row.  

Your unique ID's will come from your workorder or admin tables.  In each of those tables you should set up a numeric field as an Identity field.  Then your linking to any one row comes from the id field you set as an Identity.
0
 
Scott PletcherSenior DBACommented:
The best method depends on specifically what you are trying to do.

You can use a cursor to loop thru result rows and get the values one at a time, then process them any way you want:



DECLARE @year int
DECLARE @count int

DECLARE cursorYears CURSOR FAST_FORWARD FOR
SELECT w.year_id AS Year, COUNT(*) AS YearCount
FROM dbo.Admin a
INNER JOIN dbo.Workorder w ON
    a.wo_id = w.wo_id
WHERE
    a.status = 'Completed'
ORDER BY
    COUNT(*) DESC

OPEN cursorYears

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cursorYears INTO @year, @count
    IF @@FETCH_STATUS <> 0
        BREAK
    --**-- ADD CODE HERE TO PROCESS THE [@]YEAR --
END --WHILE

DEALLOCATE cursorYears
0
 
ZberteocCommented:
You don't need any loops not in your ASP code nor in the SQL code. You build the result in the SQL code and retrieve in one step in your ASP code. Use this SQL sequence in the ASP code and run it against the server in one shot to retrieve directly the string you need :

declare @years_string varchar(max); SELECT @years_string=ISNULL(@years_string,'')+'<td><strong>'+cast(dbo.Workorder.year_id as varchar)+'</strong></td>' FROM dbo.Admin INNER JOIN dbo.Workorder ON dbo.Admin.wo_id = dbo.Workorder.wo_id GROUP BY dbo.Workorder.year_id, dbo.Admin.status HAVING (COUNT(*) >= 1) AND (dbo.Admin.status = 'Completed') ORDER BY COUNT(*) DESC; SELECT @years_string as years;

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now