比较计数currentdate与previousDay [英] comparing counts currentdate vs previousDay

问题描述

表A

日期   计数    TableName

05232019    100      TestA

05222019    95        TestA

05232019    134    TestB

05222019    23      TestB

我需要比较TestA，TestB计数最新日期vs latestDate-PreviousRun和IF COUNT> 10000然后发送带TableName的电子邮件更高（主题行）。如果测试A和测试B都较高，则应发送电子邮件以包括主题中的表名

---

sami

解决方案

尝试此但不确定如何修改

声明@Rowcount int 

从[dbo]中选择@ Rowcount = count（*）。[Tbl_AvgCounts];



while（@ Rowcount> 0）

开头



选择Count（*）As AvgCount1，Count（*）As AvgCount2 ，A.Tablename

由... [dbo]提供
。[Tbl_AvgCounts] 内部联接  [dbo]。[Tbl_AvgCounts] B <
¥
A. [TableName] = B. [TableName]



其中A. [LoadDate] = max（A. [LoadDate]）



和B. [LoadDate] = Max（DatePart（dd，B.LoadDATE）） -1）



 端

Table A

Date   Count    TableName

05232019   100      TestA

05222019   95        TestA

05232019    134    TestB

05222019   23      TestB

I need to compare TestA , TestB Counts for latest date vs latestDate-PreviousRun AND IF THE COUNT >10000 then send Email with TableName that is higher (Subject Line). If Both Test A and Test B are higher Email should be sent to include both Table Names in the subject

---

sami

解决方案

Trying this but not sure how to modify

declare @Rowcount int 
select @Rowcount=count(*) from [dbo].[Tbl_AvgCounts];

while( @Rowcount>0)
Begin

Select Count(*) As AvgCount1, Count(*) As AvgCount2, A.Tablename

from [dbo].[Tbl_AvgCounts]  A inner join  [dbo].[Tbl_AvgCounts] B

on A.[TableName]=B.[TableName]

where A.[LoadDate]=max(A.[LoadDate])

and B.[LoadDate]=Max(DatePart(dd,B.LoadDATE)-1)

  End

这篇关于比较计数currentdate与previousDay的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！