Solved

Nested loops in SQL Server

Posted on 2013-06-04
15
375 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query output (script) from a stored procedure 4 36
Database maintenance 36 100
I need help using SQL case statements in where clauses 3 24
help converting varchar to date 14 25
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

761 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