question

Brian-7574 avatar image
0 Votes"
Brian-7574 asked JackJJun-MSFT answered

SQL Stored Procedure written in C# / LINQ

Say I have a basic SQL table (@reg) that stores registrations with the fields: ID (can be duplicates), pre_status and post_status.

I'm looking to tally up a bunch of the IDs for different situations. Some of the queries are very basic, like the number of IDs where pre_status in ('Enrolled', 'Waiting List'). However, other queries get more complicated because they implement sort of a tiered approach. So I may need to count distinct IDs where:


  • post_event = 'Incomplete' and


  • has an additional record with pre_status = 'Waiting List' or 'Enrolled' and


  • does not include the IDs with a previous record of post_status = 'Complete'

  • etc

I can do this using a stored procedure similar to the code below. But I'm wondering if there's a better way. If I were to take the @reg table and use C# / LINQ to tally the scenarios, what would a good data structure and approach be?



Here is the SQL code (the first part is just to fill the @reg table with sample data. the 2nd part is what I'm considering converting to C# / LINQ):

 /***** Populate temporary table with random test data *****/
 declare @count int
 set @count = 1
    
 declare @RandomNum int
 declare @Pre_Status varchar(20)
 declare @Post_Status varchar(20)
 declare @rnd decimal(13, 1)
    
    
 declare @reg table (ID int, Pre_Status varchar(20), Post_Status varchar(20))
    
    
 While @count <= 2000
 Begin 
    
    set @RandomNum = Round(Rand()*1000, 0)
       
    set @rnd = round(rand(), 1)
    set @Pre_Status = (CASE WHEN @rnd < 0.2 THEN 'Event Full'
    WHEN @rnd < 0.4 THEN 'Cancelled'
               WHEN @rnd < 0.7 THEN 'Waiting List'
               WHEN @rnd < 0.9 THEN 'Enrolled'
               ELSE 'Prerequisite Not Met'
    END)
    
    set @rnd = round(rand(), 1)
    print @rnd
    set @Post_Status = (CASE WHEN @rnd < 0.3 THEN 'Complete'
               WHEN @rnd < 0.4 THEN 'Incomplete'
               WHEN @rnd < 0.5 THEN 'Prerequisite Not Met'
    WHEN @rnd < 0.6 THEN 'No Show'
               WHEN @rnd >= 0.7 THEN '(Not Specified)'
               ELSE 'Other'
    END)
    
    
    Insert Into @reg values (@RandomNum, @Pre_Status, @Post_Status)
    Set @count = @count + 1
 End
    
 -- select * from @reg
    
 /******* Done with Test data ***********/
    
    
 -- Create a temp table to store registrations with Event Full or Waiting List pre status
 declare @efwl table (ID int, Pre_Status varchar(20), Post_Status varchar(20))
    
 insert into @efwl 
 select ID, Pre_Status, Post_Status 
 from @reg 
 where Pre_Status in ('Event Full', 'Waiting List')
    
 -- Create another temp table store registration with Enrolled pre status AND also had pre status = Event Full or Waiting List 
 declare @enrolled table (ID int, Pre_Status varchar(20), Post_Status varchar(20))
    
 insert into @enrolled 
 select ID, Pre_Status, Post_Status 
 from @reg
  where Pre_Status = 'Enrolled' and ID in (select ID from @efwl)
    
    
 select 
 -- Total # of registrations either event full or waiting list
 Total = (select count(*) from @efwl), 
 -- Total # of registrations equal to event full 
 EventFull = (select count(*) from @efwl where Pre_Status = 'Event Full'), 
 -- Total # of registrations equal to waiting list
 WaitingList = (select count(*) from @efwl where Pre_Status = 'Waiting List'),
 -- # Distinct IDs in event full waitlist table
 UniqueIndividuals = (select count(distinct(ID)) from @efwl), 
 -- # distinct IDs where pre status was 'Event Full' or 'Waitlist' and post_status was never enrolled
 NotAdmitted = (select count(distinct(ID)) from @efwl where 
  ID not in (select ID from @enrolled)),
 -- # Distinct IDs in enrolled table
 Enrolled = (select count(distinct(ID)) from @enrolled),
 -- # Distinct IDs in enrolled table where Post_Status = 'Complete'
 Complete = (select count(distinct(ID)) from @enrolled where Post_Status = 'Complete'), 
 -- # Distinct IDs in enrolled table where post status = 'Incomplete' and was never Complete
 Incomplete = (select count(distinct(ID)) from @enrolled where Post_Status = 'Incomplete'
  and ID not in (select ID from @enrolled where Post_Status = 'Complete')), 
 -- # distinct IDs in enrolled table where post status = 'No Show' and was never Complete or Incomplete
 NoShow = (select count(distinct(ID)) from @enrolled where Post_Status = 'No Show'
  and ID not in (select ID from @enrolled where Post_Status in ('Complete', 'Incomplete'))),
 -- # distinct IDs in enrolled table where pre status = 'Cancelled' and post status was never Complete, Incomplete or No Show
 Cancelled = (select count(distinct(ID)) from @reg where Pre_Status = 'Cancelled'
   and ID in (select ID from @enrolled))






dotnet-csharpsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

JackJJun-MSFT avatar image
1 Vote"
JackJJun-MSFT answered

@Brian-7574, you could try the following code to convert 2nd part to linq statement.

 static void Main(string[] args)
         {
             SqlConnection connection = new SqlConnection("connstr");
             connection.Open();
             string sqltext = File.ReadAllText("D:\\Data.txt");       //I stored the first part to the txt file
             SqlDataAdapter adapter = new SqlDataAdapter(sqltext, connection);
             DataSet set = new DataSet();
             adapter.Fill(set);
             DataTable table = set.Tables[0];
             var efwl = from data in table.AsEnumerable()
                        where data.Field<string>("Pre_Status") == "Event Full" || data.Field<string>("Pre_Status") == "Waiting List"
                        select new
                        {
                            ID = data.Field<object>("ID"),
                            Pre_Status = data.Field<object>("Pre_Status"),
                            Post_Status = data.Field<object>("Post_Status")
    
                        };
               
             var enrolled = from data in table.AsEnumerable()
                            where data.Field<string>("Pre_Status") == "Enrolled"&& efwl.Select(i=>i.ID).Contains(data.Field<object>("ID"))
                            select new
                            {
                                ID = data.Field<object>("ID"),
                                Pre_Status = data.Field<object>("Pre_Status"),
                                Post_Status = data.Field<object>("Post_Status")
    
                            };
             var Incomplete = enrolled.Where(i => i.Post_Status.ToString() == "Incomplete");
             var complete= enrolled.Where(i => i.Post_Status.ToString() == "Complete");
             var results1 = (from t1 in Incomplete
                           where !(from t2 in complete
                                   select t2.ID).Contains(t1.ID)
                           select t1.ID).Distinct();
             var Noshow = enrolled.Where(i => i.Post_Status.ToString() == "No Show");
             var comandnotcom = enrolled.Where(i => i.Post_Status.ToString() == "Complete" || i.Post_Status.ToString() == "Incomplete");
             var results2 = (from t1 in Noshow
                             where !(from t2 in comandnotcom
                                     select t2.ID).Contains(t1.ID)
                             select t1.ID).Distinct();
    
    
    
             var final = new Result
             {
                             Total = efwl.Count(),
                             EventFull = efwl.Where(i => i.Pre_Status.ToString() == "Event Full").Count(),
                             WaitingList = efwl.Where(i => i.Pre_Status.ToString() == "Waiting List").Count(),
                             UniqueIndividuals = efwl.Select(i => i.ID).Distinct().Count(),
                             NotAdmitted = efwl.Select(i => i.ID).Except(enrolled.Select(i => i.ID)).Count(),
                             Enrolled = enrolled.Select(i => i.ID).Distinct().Count(),
                             Complete = enrolled.Where(i => i.Post_Status.ToString() == "Complete").Select(i => i.ID).Distinct().Count(),
                             Incomplete = results1.Count(),
                             NoShow = results2.Count(),
                             Cancelled = table.AsEnumerable().Where(i => i.Field<string>("Pre_Status") == "Cancelled" && enrolled.Select(m=>m.ID).Contains(i.Field<object>("ID"))).Select(i => i.Field<object>("ID")).Distinct().Count()
              };
    
    
 public class Result
     {
         public int Total { get; set; }
         public int EventFull { get; set; }
         public int WaitingList { get; set; }
         public int UniqueIndividuals { get; set; }
         public int NotAdmitted { get; set; }
         public int Enrolled { get; set; }
         public int Complete { get; set; }
         public int Incomplete { get; set; }
         public int NoShow { get; set; }
         public int Cancelled { get; set; }
    
     }
  }

Result:

140493-image.png






If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (15.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.