Given the VB.net code, combine multiple queries into 1

Question

Given this code below which returns a first recordset (rs) based on a date range with some values that are then used in the second recordset (rs2) to sum up a cost. Further explanation is below the code:

strSQL = "SELECT job, suffix, isnull(qty_scrapped,0),isnull(qty_released,0), isnull(price,0),co_num FROM vwDashboardsQuality "
strSQL &= "        WHERE trans_date >= '" & dtpStartDate.Value & "' AND trans_date <= '" & dtpEndDate.Value & "' "

rs = conn.Execute(strSQL)

While Not rs.EOF
    strCONUM = Trim("" & rs("co_num").Value)

    strSelectString = "SELECT ISNULL(a_cost,0) FROM jobmatl WHERE job='" & rs("job").Value & "' AND suffix = " & Format(rs("suffix").Value)

     rs2 = conn.Execute(strSelectString)
     While Not rs2.EOF
         dblSumActualMaterialCost = dblSumActualMaterialCost + CDbl(rs2(0).Value)
         rs2.MoveNext()
     End While
     rs2.Close()
     rs2 = Nothing
     rs.MoveNext()
End While
rs.Close()
rs = Nothing

I want to combine the queries into a single query so I am not hitting the database through the second recordset (rs2) just to sum up something that I know can be done in a single query.

Any tips would be helpful. Thank you in advance.

is this Legacy code? is that ADO?..

Brett Caswell
– Brett Caswell

2015-09-29 21:39:48 +00:00
Commented Sep 29, 2015 at 21:39 — Brett Caswell
– Brett Caswell, Commented Sep 29, 2015 at 21:39

InbetweenWeekends · Accepted Answer · 2015-09-29 18:37:17Z

3

It looks like you're just needing to do an inner join on the two queries to get one result set.

See if this works. If so, you can eliminate the second query and second inner loop.

 strSQL = "SELECT d.job, d.suffix, isnull(d.qty_scrapped,0), isnull(d.qty_released,0)," _
 & " isnull(d.price,0), d.co_num, ISNULL(m.a_cost,0)" _
 & " FROM vwDashboardsQuality d" _
 & " INNER JOIN jobmatl m" _
 & " ON d.job = m.job" _
 & " AND d.suffix = m.suffix" _
 & " WHERE trans_date >= '" & dtpStartDate.Value & "'" _
 & " AND trans_date <= '" & dtpEndDate.Value & "'"

You can paste this in Management Studio, replacing dates as applicable to check the results.

SELECT d.job, d.suffix, isnull(d.qty_scrapped,0), isnull(d.qty_released,0), isnull(d.price,0), d.co_num,
    ISNULL(m.a_cost,0)
FROM vwDashboardsQuality d
    INNER JOIN jobmatl m
    ON d.job = m.job
    AND d.suffix = m.suffix
WHERE trans_date >= '2015-09-29' 
    AND trans_date <= '2015-09-30'

answered Sep 29, 2015 at 18:37

InbetweenWeekends

1,4143 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike the Gardener Over a year ago

thanks! worked like a charm ... talk about having a brain freeze

InbetweenWeekends Over a year ago

I won't tell anyone. :) Glad I could help.

Brett Caswell Over a year ago

additionally, you can use the BETWEEN statement trans_data BETWEEN '2015-09-29' AND '2015-09-30' in the Where Clause

Yaroslav Over a year ago

Be careful when using BETWEEN clause What do BETWEEN and the devil have in common?

Yaroslav · Accepted Answer · 2015-09-29 18:56:46Z

-1

From your code I see that you are at the end just running a SUM on all values for jobmatl.a_cost that fulfill a condition set by the where clause. So why not doing everything on the same query? And you will save yourself all the unnecessary iterations on the result set, you are loosing previous CPU time and resources there.

Also, you are not using all other values on the first query, why getting them on the first place? I removed them from the following query.

SELECT  SUM(j.a_cost)
FROM    vwDashboardsQuality vDQ
        INNER JOIN jobmatl j
            ON vDQ.job = j.job
               AND vDQ.suffix = j.suffix
WHERE   vDQ.trans_date >= @startdate
        AND vDQ.trans_date <= @enddate;

answered Sep 29, 2015 at 18:56

Yaroslav

6,55210 gold badges54 silver badges91 bronze badges

3 Comments

Mike the Gardener Over a year ago

actually they are being used, I just didn't include that code.

InbetweenWeekends Over a year ago

My guess is because the OP produced a minimal example, and your solution stripped it down too far.

Yaroslav Over a year ago

Ok, but that doesn't means that the answer is incorrect, is correct for his purpose of getting one query and even with the needed result without iterating on the code. Nevertheless, thanks

Collectives™ on Stack Overflow

Given the VB.net code, combine multiple queries into 1

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related