How to combine a loop in a subquery in SQL?

Question

I am trying to make this automated with a loop where I define an beginning and ending time period @t_begin , @t_end starting from 0 to 24 for example. CURRENTLY, I am putting all these codes one by one, and for ending t=60 for example, I need to copy and paste 60 subqueries.

The thing matters below is tMONTHS=0,1,2,...... I dont want to copy them all the way to t=24 for example.

SELECT
    (
     SELECT COUNT(CODE) FROM #TEMP
     WHERE (tMONTHS = 0) AND (KANALKODU = @channel) AND (tSTATUS = 2) AND (TARIH < @datelimit)
      ) as T,
     (
     SELECT COUNT(CODE) FROM #TEMP
     WHERE (tMONTHS = 1) AND (KANALKODU = @channel) AND (tSTATUS = 2) AND (TARIH < @datelimit)
     ) as t1,
     (
     SELECT COUNT(CODE) FROM #TEMP
     WHERE (tMONTHS = 2) AND (KANALKODU = @channel) AND (tSTATUS = 2) AND (TARIH < @datelimit)
     ) as t2,
    (
     SELECT COUNT(CODE) FROM #TEMP
     WHERE (tMONTHS = 3) AND (KANALKODU = @channel) AND (tSTATUS = 2) AND (TARIH < @datelimit)
    ) as t3; 
GO

Writing URGENT in ALL CAPS won't get you help faster. In fact, it will likely delay help. — Eric J., Oct 20 '15 at 23:01
You just need to write one query which places your data in 24 rows then use the `PIVOT` operator to pivot them into columns. You still need to list the columns out but there is a lot less repeated code. There are about a billion examples of pivot on stack overflow as well as the official documentation etc. — Nick.Mc, Oct 20 '15 at 23:07
Here's a short example of the query that puts it in to rows. Just pivot it: `SELECT COUNT(CODE) FROM #TEMP WHERE (tMONTHS = 0) AND (KANALKODU = @channel) AND (tSTATUS IN (0,2,3,4) ) AND (TARIH < @datelimit)` — Nick.Mc, Oct 20 '15 at 23:08
Thanks Nick, really appreciated. But my code above returns side by side 3 columns with one row whereas the PIVOT code you suggested only returning 1x1 output. How to tackle this? I dont think I applied the code wrong, there must be some other sort of thing. — EMRE, Oct 20 '15 at 23:34
It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. — HABO, Oct 21 '15 at 04:53

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

1

First create a numbers table. This numbers will represent months, days, any thing you need.

SELECT TOP 10000 N=IDENTITY(INT, 0, 1)
INTO dbo.Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;

I'll recomend to read this post for extra information https://dba.stackexchange.com/questions/11506/why-are-numbers-tables-invaluable

Then create the list for each month

      SELECT COUNT(tMONTHS ) as code_count, tMONTHS as month FROM #TEMP CROSS JOIN Numbers
        WHERE (tMONTHS = n) AND (KANALKODU = @channel) AND 
           (tSTATUS = 2) AND (TARIH < @datelimit)   
             AND n BETWEEN @t_begin AND @t_end

This resulset brings one row per month with two columns, code_count and month. Now you can PIVOT.

Note: I've to get dinner, see you later

edited Apr 13 '17 at 12:42

Community

1
1

answered Oct 20 '15 at 23:38

Horaciux

6,322
2
22
41

This Numbers solution is genious, checked out the article briefly. – EMRE Oct 21 '15 at 00:00
But when I paste the code directly SELECT function above gives Numbers.n variable not included in an aggregate function or group by error, that should be either a cheesy error that we are forgetting, or my beginner mistake since I am not sure what do you mean by 'now you can PIVOT'. Looking at your code it seemed a final version to me – EMRE Oct 21 '15 at 00:06
Horaciux, on top of your edit I just added a `GROUP BY tMONTHS` statement to the end, works perfectly. My initial intention was to cut down the repeated codes, but it also significantly had cut down the runtime (from 7 minutes to a whopping 3 seconds). Thanks a lot for your continued support, really appreciated. Best regards. – EMRE Oct 21 '15 at 08:14

How to combine a loop in a subquery in SQL?

1 Answers1