Solved

Nested loops in SQL Server

Posted on 2013-06-04
15
331 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now