Solved

Nested loops in SQL Server

Posted on 2013-06-04
15
359 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 40

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 40

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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

785 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