时间戳

首页 > 解决方案 > 如何计算不同的值和日期小于同一行

sql - 如何计算不同的值和日期小于同一行

问题描述

我需要知道我今天有多少标题和字幕，有多少超过 60 天。我需要帮助计算超过 60 天的标题。想过使用这样的东西：count(distinct [Tracking ID1] case when [Date] < DATEADD(DAY, -60, '1/5/2021') 但这不起作用。

我不能使用 where 语句，因为它会影响 [Titles] 和 [Subtitles] 列。

在此处输入图像描述

select 
    [Line of Business],
    count([Tracking ID]) as [Total # Subtitles],
    count(distinct [Tracking ID1]) as [Total # Titles], 
    count(case when [Date]   < DATEADD(DAY, -60, '1/5/2021') then 1 else 0 end) as [# Subtitles No Action Yet over 60 days], 
    count(distinct [Tracking ID1] case when [Date]   < DATEADD(DAY, -60, '1/5/2021') as [# Titles No Action Yet over 60 days]
    
from table
where 
[Status] like '%active%'
group by [Line of Business]
order by [Line of Business]

标签： sqlsql-server

解决方案

使用 count 而不是 sum ：

select 
    [Line of Business],
    count([Tracking ID]) as [Total # Subtitles],
    count(distinct [Tracking ID1]) as [Total # Titles], 
    COUNT(case when [Date] < DATEADD(DAY, -60, '1/5/2021') then 1 else NULL end) as [# Subtitles No Action Yet over 60 days], 
    COUNT(distinct CASE when [Date] < DATEADD(DAY, -60, '1/5/2021') THEN [Tracking ID1] ELSE NULL END) AS [# Titles No Action Yet over 60 days]    
from table
where 
[Status] like '%active%'
group by [Line of Business]
order by [Line of Business]

推荐阅读