Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Nested loops in SQL Server

Posted on 2013-06-04
15
Medium Priority
?
395 Views
Last Modified: 2013-06-05
I have a table which contains among other fields: sites, collection years, treatments, samples, species, and counts of seedlings.

I want to be able to loop through the table and select the 5 most abundant species grouped by:  sites, collection years, treatments,species.

In the t-sql script that I have created, I have been unable to get the looping to work correctly. The outer loop ( cycling thru sites) is not being executed.

My intention is for the inner loop to cycle through treatments, the middle loop cycles through years and the outer loop cycles through sites.

I have attached a zip of the sql script and the results of the script as a csv file.
0
Comment
Question by:dblankman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
15 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 39218596
Hi dblankman,

I cannot see any files attached. Can you try again please?
BTW, you shouldn't us loops in SQL for such tasks. I'm sure you can achieve the same results with SELECT statements
0
 

Author Comment

by:dblankman
ID: 39218605
zipped file of sql script and results
sqlAndcsvFiles.zip
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 39218692
Could you also post your table schema?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:dblankman
ID: 39218705
CREATE TABLE [db_owner].[Seedbank] (
    [Sample_Unique_Key] NVARCHAR (255) NOT NULL,
    [Collection_Year]   SMALLINT       NULL,
    [Site_ID]           NVARCHAR (255) NULL,
    [Germination_Year]  SMALLINT       NULL,
    [Treatment]         NVARCHAR (255) NULL,
    [Plot_ID]           NVARCHAR (255) NULL,
    [Block_ID]          NVARCHAR (255) NULL,
    [Habitat_ID]        NVARCHAR (255) NULL,
    [Sample_Number]     SMALLINT       NULL,
    [Block_net_house]   SMALLINT       NULL,
    [Run_Number]        SMALLINT       NULL,
    [Species]           NVARCHAR (255) NULL,
    [Seedling_Count]    SMALLINT       NULL,
    [table_number]      INT            NULL,
    [SampleKey]         NVARCHAR (50)  NULL
);
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 39218931
Thanks,
what about db_owner.Glowa_Species_List?
0
 

Author Comment

by:dblankman
ID: 39218948
[db_owner].[Glowa_Species_List] (
    [Species_Code]             NVARCHAR (255) NULL,
    [Family]                   NVARCHAR (255) NULL,
    [Genus]                    NVARCHAR (255) NULL,
    [Species_Name]             NVARCHAR (255) NULL,
    [Synonym_1]                NVARCHAR (255) NULL,
    [Synonym_2]                NVARCHAR (255) NULL,
    [Synonym_3]                NVARCHAR (255) NULL,
    [Plant_Functional_Group]   NVARCHAR (255) NULL,
    [Marcelos_Functional_Type] NVARCHAR (255) NULL,
    [Growth_Form]              NVARCHAR (255) NULL,
    [Systematic_Number]        FLOAT (53)     NULL,
    [Fragman_Hebrew_Name]      NVARCHAR (255) NULL,
    [Reshut_Hebrew_Name]       NVARCHAR (255) NULL,
    [Notes]                    NVARCHAR (255) NULL
);
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39220561
Post the sample data from Seedbank and Glowa_Species_List tables with the expected result. I think the looping can be avoided.
0
 

Author Comment

by:dblankman
ID: 39221038
0
 

Author Comment

by:dblankman
ID: 39221041
uploaded sample data  (see previous comment)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39221133
And what is the expected output?
0
 

Author Comment

by:dblankman
ID: 39221142
see file in comment:  zipped file of sql script and result
0
 

Author Comment

by:dblankman
ID: 39221145
sample results, but with all 4 sites:

siteid      sitenumber      Treatment      treatmentnumber      CollectionYear      Species      abundance      countSamples
L      2      I      1      2001      Filago contracta      631      64
L      2      I      1      2001      Trisetaria macrochaeta      307      55
L      2      I      1      2001      Crithopsis delileana      300      59
L      2      I      1      2001      Valantia hispida      111      12
L      2      I      1      2001      Rostraria cristata      85      24
L      2      D      2      2001      Trisetaria macrochaeta      739      76
L      2      D      2      2001      Filago contracta      448      67
L      2      D      2      2001      Crithopsis delileana      231      49
L      2      D      2      2001      Poa bulbosa      227      9
L      2      D      2      2001      Sedum rubens      162      17
L      2      C      3      2001      Trisetaria macrochaeta      684      66
L      2      C      3      2001      Filago contracta      606      58
L      2      C      3      2001      Crithopsis delileana      228      53
L      2      C      3      2001      Sarcopoterium spinosum      91      28
L      2      C      3      2001      Anagallis arvensis      83      37
L      2      I      1      2002      Crithopsis delileana      409      31
L      2      I      1      2002      Trisetaria macrochaeta      211      33
L      2      I      1      2002      Sarcopoterium spinosum      101      25
L      2      I      1      2002      Filago contracta      76      22
L      2      I      1      2002      Allium spp.      63      16
L      2      D      2      2002      Trisetaria macrochaeta      392      41
L      2      D      2      2002      Crithopsis delileana      275      29
L      2      D      2      2002      Sarcopoterium spinosum      82      23
L      2      D      2      2002      Catapodium rigidum      81      13
L      2      D      2      2002      Filago contracta      81      22
L      2      C      3      2002      Trisetaria macrochaeta      393      42
L      2      C      3      2002      Crithopsis delileana      137      30
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 2000 total points
ID: 39221446
Check if this works for you:

DECLARE @seedlingtable TABLE
  (
     siteid          VARCHAR(50),
     sitenumber      INT,
     Treatment       VARCHAR(50),
     treatmentnumber INT,
     CollectionYear  INT,
     samplekey       VARCHAR(50),
     Species         VARCHAR(50),
     seedlingCount   INT
  )

INSERT INTO @seedlingtable
            (siteid,
             sitenumber,
             Treatment,
             treatmentnumber,
             CollectionYear,
             samplekey,
             Species,
             seedlingCount)
SELECT db_owner.Seedbank.site_id,
       CASE
         WHEN site_id = 'B' THEN 5
         WHEN site_id = 'L' THEN 2
         WHEN site_id = 'M' THEN 3
         WHEN site_id = 'Y' THEN 4
       END                                      AS sitenumber,
       db_owner.Seedbank.Treatment,
       CASE
         WHEN treatment = 'I' THEN 1
         WHEN treatment = 'D' THEN 2
         WHEN treatment = 'C' THEN 3
       END                                      AS TreatmentNumber,
       db_owner.Seedbank.Collection_Year,
       db_owner.Seedbank.SampleKey,
       db_owner.Glowa_Species_List.Species_Name AS species,
       Sum(db_owner.Seedbank.Seedling_Count)    AS NumberSeedlings
FROM   db_owner.Seedbank
       INNER JOIN db_owner.Glowa_Species_List
               ON db_owner.Seedbank.Species = db_owner.Glowa_Species_List.Species_Code
WHERE  ( db_owner.Seedbank.Seedling_Count <> 999 )
       AND db_owner.Seedbank.Seedling_Count <> 0
GROUP  BY db_owner.Seedbank.site_id,
          db_owner.Seedbank.Treatment,
          db_owner.Seedbank.Collection_Year,
          db_owner.Seedbank.sampleKey,
          db_owner.Glowa_Species_List.Species_Name
          
;WITH seedling_total AS 
(
	select siteid,sitenumber,CollectionYear,treatment,treatmentnumber,species,sum(seedlingCount) as abundance, count(distinct samplekey) AS countSamples
	from @seedlingtable
	GROUP BY siteid,sitenumber,CollectionYear,treatment,treatmentnumber,Species
),
seedling_total_filtered AS 
(
	SELECT siteid, sitenumber, Treatment, treatmentnumber, CollectionYear, Species, abundance, countSamples, 
		ROW_NUMBER() OVER(PARTITION BY sitenumber,CollectionYear,treatment ORDER BY  abundance desc) AS RowNo
	FROM seedling_total
)
SELECT * FROM seedling_total_filtered WHERE RowNo <=5

Open in new window

0
 

Author Closing Comment

by:dblankman
ID: 39221989
Thanks. I need to learn about the ;with construction
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 39222355
It's called Common Table Expressions (CTE)

You might start here:
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question