none
Need a SP to find average count RRS feed

Answers

  • Hi arc2013,

     

    Under my understanding , you would like to get one row for each tablename and then compare the difference between Latest Date and Previous Day. So, firstly I will show you a script to get the rows which you need.

     

    And then I will use while () to loop each row to send an e-mail. Please try following  script.

    IF OBJECT_ID('StagingTbl_AvgCounts') IS NOT NULL drop table  [StagingTbl_AvgCounts]
    go 
    create table [StagingTbl_AvgCounts]
    ([LoadDate] date,
    Count int,
    TableName varchar(10))
    insert into [StagingTbl_AvgCounts] values 
    ('20190523',100,'TestA'),
    ('20190522',95 ,'TestA'),
    ('20190521',34,'TestA'),
    ('20190523',134,'TestB'),
    ('20190522',23 ,'TestB'),
    ('20190521',45,'TestB')
    go 
    ;with cte as (
    select a.DATE,a.Count,a.TableName,b.DATE as PreviousDATE,b.Count as PreviousCount,
    max(a.DATE)over(partition by a.TableName) max_date 
    from [Table A] a  join [Table A] b 
    on a.DATE=dateadd(dd,1,b.DATE) and a.TableName=b.TableName)
    ,cte1 as (
    select *,row_number()over(order by TableName) as rn 
    from cte where DATE=max_date)
    select * from cte1 
    /*
    DATE       Count       TableName  PreviousDATE PreviousCount max_date   rn
    ---------- ----------- ---------- ------------ ------------- ---------- --------------------
    2019-05-23 100         TestA      2019-05-22   95            2019-05-23 1
    2019-05-23 134         TestB      2019-05-22   23            2019-05-23 2
    */
    
    declare @Rowcount int 
    declare @curLoadDate datetime
    declare @AvgCountDiff int 
    declare @Tablename varchar(10)
    declare @text nvarchar(max)
    declare @i int=1
    
    
    select @Rowcount=count(distinct TableName) from [dbo].[StagingTbl_AvgCounts]
    
    
    while( @Rowcount>=@i)
    Begin
    ;with cte as (
    select a.[LoadDate],a.Count,a.TableName,b.[LoadDate] as PreviousDATE,b.Count as PreviousCount,
    max(a.[LoadDate])over(partition by a.TableName) max_date 
    from [StagingTbl_AvgCounts] a  join [StagingTbl_AvgCounts] b 
    on a.[LoadDate]=dateadd(dd,1,b.[LoadDate]) and a.TableName=b.TableName)
    ,cte1 as (
    select *,row_number()over(order by TableName) as rn 
    from cte where [LoadDate]=max_date)
    select @curLoadDate=[LoadDate],@AvgCountDiff=Count-PreviousCount,@Tablename=TableName
    from cte1 where rn=@i
    
    select @text = N'Avg Counts for '+CONVERT(nvarchar(30),@curLoadDate,101) +N' '+@Tablename + N' is High than previous day '
    
    If @AvgCountDiff>100 
    	Begin
    	EXEC msdb.dbo.sp_send_dbmail
    	@recipients = 'TestEmail@abc.com',
    	@subject = @text,
    	@BODY='';
    	END
    
     set @i=@i+1
    End

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by arc2013 Friday, May 24, 2019 7:34 PM
    Friday, May 24, 2019 6:36 AM

All replies

  • Why did you fail to post DDL? Do expect people to do your job, read your mind or what?? 

    Ignoring your bad Netiquette, { 'mon', ..'fri'} are NOT ATTRIBUTES! They are values on a calendar scale. In the words of Edsgar Dijkstra, "You are doing everything wrong".  

    Follow forum rules and let us fix this mess from your DDL and specs. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, May 23, 2019 5:46 PM
  • If you don't want to answer that's fine but being rude doesn't help any of us. Where did the etiquette come in to picture? Its a online forum. If you don't understand or if you don't feel like I asked the question correctly you can ignore my question.

    Coming to your question of attributes. I did ask this correctly. Our job runs each day and it loads data in to corresponding days data. So Table Names are just samples that I posted there.

    Evil communication corrupts good manners 


    sami

    Thursday, May 23, 2019 6:03 PM
  • I'm afraid that I don't understand much if what you are looking for. Average count of what? What is a "set" in this context? What are the diff sets?

    For a question like this, it often helps to post CREATE TABLE statements for your tables, and INSERT statements with sample data and the expected results of the sample data. This helps us to understand what you are looking for.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 23, 2019 10:05 PM
  • **

    • Edited by arc2013 Friday, May 24, 2019 7:33 PM
    Thursday, May 23, 2019 11:49 PM
  • Good day Sami,

    I saw that you marked CLKO's response as abusive and read your comment, and I understand you. Please don't take CELKO so seriously. I have no idea what is his real-life persona but I can say how rude he is in his online front and how hurtful he is to newbies. There were several discussions regarding his responses to newbies with the forums team and Moderators and several even mentioned the option to ban him.

    It is important to remember that Celko made worse mistakes and for example he used the word field instead of column IN LECTURE which is much worse then a message in forum which you write in several seconds without any preparation: https://sqlblog.org/2008/10/02/pass-conference-stories-tagging-adam-machanic-and-peter-debetta

    * There are two approaches to show that you are better then others: (1) show that you are the best. Be better, give the best answers and solutions, help others and in time (yes this is the long way) people understand that you are better. (2) Show that others are more worse. In his approach for example, a person level 1 (from 10) will try to show that all the others are at level 0, and that mean that he is the best... each person choose his way...

    I loved the sentence "Evil communication corrupts good manners" 😃


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Friday, May 24, 2019 12:45 AM
    Moderator
  • hi,

    Can you please follow Erland's explanation and provide the following information

    1) Queries to CREATE your table(s) including indexes
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).

    Regards,


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, May 24, 2019 12:50 AM
    Moderator
  • How can I undo that ? I don't have grudge against anyone but thats hurtful. I am OK to undo that but I dont see that option. Please let me know how can I do that ? He is very knowledgable looks like and for newbies like me if he can be a little patient that helps.

    sami

    Friday, May 24, 2019 12:51 AM
  • Don't... just leave it as it is, and let's focus on helping you to solve the issue :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, May 24, 2019 12:54 AM
    Moderator
  • Here is the Table Structure. I just gave TestA and TestB as sample Data. We have more than 10 distinct entries for TableNames.

    LoadDate   Count    TableName

    05232019   100      TestA

    05222019   95        TestA

    05212019    34   TestA

    05232019    134    TestB

    05222019   23      TestB

    05212019    45   TestB

    What I want is  Compare

    Test A Counts for Latest Date (0523)- Previous Day Count (0522)

    Test B Counts for Latest Date (0523)- Previous Day Count (0522)

    If TestA  Count difference >100 then Send Email with subject Line: " Test A Count is more than 100" 

    If TestB Count Difference >100 then Send Email with Subject Line : "Test B count is more than 100 "


    sami

    Friday, May 24, 2019 1:11 AM
  • Loved reading the story you shared.  Thanks for stepping in to explain this in more detail.. 

    sami

    Friday, May 24, 2019 1:21 AM
  • Here is the Table Structure. I just gave TestA and TestB as sample Data. We have more than 10 distinct entries for TableNames.

    LoadDate   Count    TableName

    05232019   100      TestA

    05222019   95        TestA

    05212019    34   TestA

    05232019    134    TestB

    05222019   23      TestB

    05212019    45   TestB

    What I want is  Compare

    Test A Counts for Latest Date (0523)- Previous Day Count (0522)

    Test B Counts for Latest Date (0523)- Previous Day Count (0522)

    If TestA  Count difference >100 then Send Email with subject Line: " Test A Count is more than 100" 

    If TestB Count Difference >100 then Send Email with Subject Line : "Test B count is more than 100 "


    sami

    Hi,

    This is not what you was asked to provide and this does not gives information about the table structure. For example what are the types of the columns? What indexes do you have?

    You should provide queries to create the table. Using the query we can execute it and create the same structure of tabkle in our server in order to test solutions. In addition we need sample data. please check what I asked you to provide which include 5 points

    * If you do not know how to get the information which we asked you, then please inform us. In this case, we will help you in this as well. For example if you do not know how to create tables using queries then there is a simple tool in the SSMS which can create the queries for you.

    Anyway, I am going to sleep so I will check if you got answer tomorrow 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, May 24, 2019 1:22 AM
    Moderator
  • This is the Table I created to store  staging Table counts. 

    CREATE TABLE [dbo].[Staging_AvgCounts](
    [LoadDate] [datetime] NULL,
    [TableName] [varchar](40) NULL,
    [AvgCount] [int] NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Staging_AvgCounts] ADD  DEFAULT (getdate()) FOR [LoadDate]
    GO


    sami

    Friday, May 24, 2019 1:27 AM
  • SELECT [LoadDate]
          ,[TableName]
          ,[AvgCount]
          ,([AvgCount] - LEAD([AvgCount], 1, NULL) OVER(PARTITION BY [TableName] ORDER BY [LoadDate] DESC)) AS [AvgCountDifference]
      FROM [dbo].[Staging_AvgCounts];


    A Fan of SSIS, SSRS and SSAS



    • Edited by Guoxiong Friday, May 24, 2019 2:24 AM
    Friday, May 24, 2019 2:23 AM
  • Hi arc2013,

     

    Under my understanding , you would like to get one row for each tablename and then compare the difference between Latest Date and Previous Day. So, firstly I will show you a script to get the rows which you need.

     

    And then I will use while () to loop each row to send an e-mail. Please try following  script.

    IF OBJECT_ID('StagingTbl_AvgCounts') IS NOT NULL drop table  [StagingTbl_AvgCounts]
    go 
    create table [StagingTbl_AvgCounts]
    ([LoadDate] date,
    Count int,
    TableName varchar(10))
    insert into [StagingTbl_AvgCounts] values 
    ('20190523',100,'TestA'),
    ('20190522',95 ,'TestA'),
    ('20190521',34,'TestA'),
    ('20190523',134,'TestB'),
    ('20190522',23 ,'TestB'),
    ('20190521',45,'TestB')
    go 
    ;with cte as (
    select a.DATE,a.Count,a.TableName,b.DATE as PreviousDATE,b.Count as PreviousCount,
    max(a.DATE)over(partition by a.TableName) max_date 
    from [Table A] a  join [Table A] b 
    on a.DATE=dateadd(dd,1,b.DATE) and a.TableName=b.TableName)
    ,cte1 as (
    select *,row_number()over(order by TableName) as rn 
    from cte where DATE=max_date)
    select * from cte1 
    /*
    DATE       Count       TableName  PreviousDATE PreviousCount max_date   rn
    ---------- ----------- ---------- ------------ ------------- ---------- --------------------
    2019-05-23 100         TestA      2019-05-22   95            2019-05-23 1
    2019-05-23 134         TestB      2019-05-22   23            2019-05-23 2
    */
    
    declare @Rowcount int 
    declare @curLoadDate datetime
    declare @AvgCountDiff int 
    declare @Tablename varchar(10)
    declare @text nvarchar(max)
    declare @i int=1
    
    
    select @Rowcount=count(distinct TableName) from [dbo].[StagingTbl_AvgCounts]
    
    
    while( @Rowcount>=@i)
    Begin
    ;with cte as (
    select a.[LoadDate],a.Count,a.TableName,b.[LoadDate] as PreviousDATE,b.Count as PreviousCount,
    max(a.[LoadDate])over(partition by a.TableName) max_date 
    from [StagingTbl_AvgCounts] a  join [StagingTbl_AvgCounts] b 
    on a.[LoadDate]=dateadd(dd,1,b.[LoadDate]) and a.TableName=b.TableName)
    ,cte1 as (
    select *,row_number()over(order by TableName) as rn 
    from cte where [LoadDate]=max_date)
    select @curLoadDate=[LoadDate],@AvgCountDiff=Count-PreviousCount,@Tablename=TableName
    from cte1 where rn=@i
    
    select @text = N'Avg Counts for '+CONVERT(nvarchar(30),@curLoadDate,101) +N' '+@Tablename + N' is High than previous day '
    
    If @AvgCountDiff>100 
    	Begin
    	EXEC msdb.dbo.sp_send_dbmail
    	@recipients = 'TestEmail@abc.com',
    	@subject = @text,
    	@BODY='';
    	END
    
     set @i=@i+1
    End

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by arc2013 Friday, May 24, 2019 7:34 PM
    Friday, May 24, 2019 6:36 AM