SQL Server Select statement using dynamic variables

Question

Ok so in the below statement I am trying to take the first pi.planinfoid selected as the first field and make it a dynamic variable in the place of the 1736 you see in the two select statements being used as additional fields.

I also need it to still return the two fields generated by the two select statements even if the value is 0 or null.

I am at my wits end thanks in advance if anyone can figure this one out.

SELECT  pi.planinfoid,
        pi.description,
        count(DISTINCT p.planid) AS total,
        sum(dm.debitamount) AS Num,
        pi.minperiod,
        (
            SELECT count(p.planid)
            FROM plans p,
                    planinfo pi
            WHERE pi.planinfoid = p.planinfoid
                    AND pi.planinfoid = '1736'
                    AND p.closed = 0
        ) AS opened,
        (
            SELECT count(p.planid)
            FROM plans p,
                    planinfo pi
            WHERE pi.planinfoid = p.planinfoid
                    AND pi.planinfoid = '1736'
                    AND p.closed = 1
        ) AS closedd
FROM    planinfo pi, plans p, debitmovements dm
WHERE   pi.price > 0
        AND p.planinfoid = pi.planinfoid
        AND dm.planid = p.planid
        AND p.servicestart BETWEEN '2012-01-01' AND '2013-01-01'
GROUP BY    pi.description,
            pi.minperiod,
            pi.planinfoid
ORDER BY total DESC

That ran, but it doesnt seem that open and closed are tied to the total number in anyway,

planid  plan descrip   min      TOTAL   PRICE   OPEN    CLOSE
1736    Additional IP   1   146 1926    101 545

Techincally you cant have 101 still open and 545 closed if the total in that time period sold was 146, maybe its not pulling number still opened and closed for only the ones opened in that time period. the uniqe field for the 146 open would be p.planid or each one would basically have a unique p.planid, great job so far though.

YOur first problem is that you should not be using correlated subqueries, ever. They are performance kilers. If you swith to using derived tables you might want a left join. — HLGEM, Jan 10 '13 at 15:09
I don;t understnad what you mean about making a dynamic variable, could you explain more or show some data and expected results. — HLGEM, Jan 10 '13 at 15:11

Mahmoud Gamal · Answer 1 · 2013-01-10T15:22:56.097

1

Try this:

SELECT
  pi.planinfoid, 
  pi.description, 
  pi.minperiod,
  count(DISTINCT p.planid) AS total,
  sum(dm.debitamount) AS Num, 
  SUM(CASE WHEN p.closed = 0 THEN 1 ELSE 0 END) AS opened,
  SUM(CASE WHEN p.closed = 1 THEN 1 ELSE 0 END) AS closedd
FROM planinfo pi  
INNER JOIN plans           p ON p.planinfoid = pi.planinfoid
INNER JOIN debitmovements dm ON dm.planid    = p.planid 
WHERE pi.price > 0
  AND p.servicestart BETWEEN '2012-01-01' 
                         AND '2013-01-01'
GROUP BY pi.description,
         pi.minperiod,
         pi.planinfoid 
ORDER BY total DESC;

What I have done here is:

I used the implicit JOIN syntax, the ANSI-92 SQL Standard one, instead of the old one.
Replaced the two correlated COUNT() subqueries, with two CASE expression.
I moved pi.planinfoid = 1736 to the WHERE clause, you can put it in the CASE expression condition.
You will need to pass the planinfoid that your are looking for instead of the @value.

edited Jan 10 '13 at 15:22

answered Jan 10 '13 at 15:09

Mahmoud Gamal

78,257
17
139
164

I got an errorMsg 137, Level 15, State 2, Line 14 Must declare the variable '@value'. – ThatGuy Jan 10 '13 at 15:12
Sorry, basically it is saying I have not declared @value – ThatGuy Jan 10 '13 at 15:12
@ThatGuy Sorry, you should pass that value instead of the `@value` see my edit. – Mahmoud Gamal Jan 10 '13 at 15:13
I am trying to make what you have @value be what is returned for each row from the original planinfoid. the 1736 from the above statement is just a holder, that should change on each loop of the original statement and yeild different numbers of plans now opened or closed based on the planid and date range. – ThatGuy Jan 10 '13 at 15:18
Each line returned that has a unique description would also have its own unique num of plans opened and closed. – ThatGuy Jan 10 '13 at 15:22
That ran, but it doesnt seem that open and closed are tied to the total number in anyway, planid plan descrip min TOTAL PRICE OPEN CLOSE 1736 Additional IP 1 146 1926 101 545 Techincally you cant have 101 still open and 545 closed if the total in that time period sold was 146, maybe its not pulling number still opened and closed for only the ones opened in that time period. the uniqe field for the 146 open would be p.planid or each one would basically have a unique p.planid, great job so far though. – ThatGuy Jan 10 '13 at 15:32
Sorry I added that comment to my orig post so you can see what I was saying better. – ThatGuy Jan 10 '13 at 15:36
@ThatGuy Can you please setup a demo [**in this site**](http://www.sqlfiddle.com/) with some sample data, explaining that issue. That would be helpful. Thanks – Mahmoud Gamal Jan 10 '13 at 15:39

score 0 · Answer 2 · answered Jan 10 '13 at 15:15

It is hard for me to tell if the grouping conditions should apply to the counts. To make the counts explicit, you can use a subquery:

SELECT  pi.planinfoid,
        pi.description,
        count(DISTINCT p.planid) AS total,
        sum(dm.debitamount) AS Num,
        pi.minperiod,
        max(opened) as opened,
        max(closed) as closed
FROM    planinfo pi join
        plans p
        on p.planinfoid = pi.planinfoid join
        debitmovements dm
        on dm.planid = p.planid left outer join
        (select planinfoid, SUM(1-p.closed) as opened, SUM(p.closed) as closed
         from planinfo pi join
              plans p
              on pi.planinfoid = p.planinfoid
         group by planinfoid
        ) psum
        on psum.planinfoid = psum.planinfoid
WHERE   pi.price > 0 AND
        p.servicestart BETWEEN '2012-01-01' AND '2013-01-01'
GROUP BY    pi.description,
            pi.minperiod,
            pi.planinfoid
ORDER BY total DESC

In the context of your question, though, you do what you want by giving the inner subquery different aliases:

    (
        SELECT count(p2.planid)
        FROM plans p2 join
             planinfo pi2
             on pi2.planinfoid = p2.planinfoid
        WHERE
                pi2.planinfoid = pi.planinfoid
                AND p2.closed = 0
    ) AS opened,

I want to group by the description, return the total plans opened in the time frame. also cat the currently open plan p.close=0 and the plans that have been closed since p.closed=1 — ThatGuy, Jan 10 '13 at 15:21
You probably want to do this with the group by suggested by Mahmoud. — Gordon Linoff, Jan 10 '13 at 15:28

SQL Server Select statement using dynamic variables

2 Answers2