0

If I have a SQL statement that contains something like,

ROW_NUMBER() OVER(PARTITION BY school_area ORDER BY school_area)

I get:

1 someschool1 schoolarea1
2 someschool2 schoolarea1
3 someschool3 schoolarea1
1 soomschool4 schoolarea2
2 ...

How can I detect when that parition of data "resets"? In other words, when my result set went from 3 back to 1 above, is it possible to tell that in the same SQL statement?

When I use,

CASE 
    WHEN school_area=1 THEN do something
END

I could not do any logic like run an email routine after the THEN. It doesn't work because that's not what it's for. I thought I would do it in a WHILE, but I didn't know if I was missing something I could do in the SQL statement itself. I was trying to avoid loops and/or cursors, but I think it is the only way.

5
  • 1
    SQL Statement run in batch (not serial as C# code for example) so you can't do something like "send an email" when the value of a column goes from something to somthing else... If you can give more detail about your requirement, it will be easier for us to help you. There is some way to "serialize" execution (like using cursors) but without knowing what you are trying to achieve, it's pretty hard to say it that would be a good option. Maybe the best approch would simply be to get all the data agregated they way you want it and then send 1 email with all different groups... (Check for CTE) Commented Sep 23, 2021 at 15:35
  • @DominiqueBoucher That is what I am trying to do. Send an email when the group changes. Are you saying I should use CTE (recursively)?
    – learntofix
    Commented Sep 23, 2021 at 15:42
  • 1
    let's me try to understand better... in your example the row_number get reset to 1 only once but with real life data, it may happen more then once... are you expecting 1 email per change ? (if so... why ?) This is not happening "sequentially"... SQL will run your query, collect all the data and then send it on your screen... all of those "change" will happen at the same time (or so). Is the reasons behind this is to get an email for schoolarea1 and then another email for schoolarea2 ? Commented Sep 23, 2021 at 15:45
  • @DominiqueBoucher Yes. Many areas with many schools. I need to send an email to each area. There are many schools in an area, so that group of schools will get an email. As the area changes, I will have to send the email for the previous set (the one just before the reset back to 1).
    – learntofix
    Commented Sep 23, 2021 at 15:54
  • I think you need to be more clear in your question with what you are looking for. It sounds like you need something to happen as data changes over time. Put all that in your question. Data looks like A at time 1. Data loos like B at time 2. I want X to happen when A changes to B. Commented Sep 23, 2021 at 16:47

1 Answer 1

0

Here's an example of an SQL Query that would achieve what you are expecting:

create table #school (school_name varchar(20), school_area varchar(20));

insert into #school values ('someschool1', 'schoolarea1'),
('someschool2', 'schoolarea1'),
('someschool3', 'schoolarea1'),
('soomschool4', 'schoolarea2'),
('soomschool5','schoolarea2')

select ROW_NUMBER()  OVER(PARTITION BY school_area ORDER BY school_area), school_name, school_area from #school

-- Get all the school area
select distinct school_area into #school_area from #school;

-- loop into the previously created temp table and collect the required information
declare @area varchar(20);
while (select count(1) from #school_area) >0 
    begin
        select top 1 @area = school_area from #school_area;
        -- do the SQL query you need to extract the data you want for each area (no more "over" clause here)
        select * from #school where school_area = @area;
        --exec msdb..sp_send_dbmail ....(send the result of your previous query by email (for that area)
        delete from #school_area where school_area = @area;
    end


drop table #school;
drop table #school_area;

This is an example that you could run on any of your SQL instance to "see" the behavior. Just make sure you correctly parametrize the sp_send_dbmail if you want to receive the emails

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.