SQL Server Compare run date to current date

dw1jzc5e 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(115)

I need help figuring out how to compare the last run date to the current date and then calculate how many days between the two. I only need to compare the first row against the current date because I am sorting in descending order so that should be the last run date.

SELECT DISTINCT
    cast(getdate() AS Date) AS Today, Run_Date, COUNT(Run_Date) AS Daily_Count,
    Days_Since_Last_Run_Date = 
    CASE WHEN ROW_NUMBER() over (order by Run_Date) > 1 AND LAG(Run_Date,0) OVER (ORDER BY Run_Date) <> DATEADD(DAY,+1,Run_Date)
    THEN + CAST((DATEDIFF(DAY,LAG(Run_Date,0) OVER (ORDER BY Run_Date),Run_Date)+1) AS NVARCHAR)
    ELSE ''
    END
FROM tblUnitsOfWork
WHERE Run_Date >= '2023-07-10'
GROUP BY Run_Date
ORDER BY Run_Date DESC;

sql-server

来源：https://stackoverflow.com/questions/76715293/compare-run-date-to-current-date

1条答案

按热度按时间

b1zrtrql1#

It is simpler to use a "nested" subquery (also called "derived table") to supply the row numbers, then in the outer query use a case expression. e.g:

SELECT
    *
    , case when rn = 1 then datediff(day,run_date,getdate()) end as days_diff
FROM (
    SELECT
          cast(getdate() AS DATE) AS Today
        , Run_Date
        , COUNT(Run_Date) AS Daily_Count
        , ROW_NUMBER() OVER (
            ORDER BY Run_Date DESC
            ) AS rn
    FROM tblUnitsOfWork
    WHERE Run_Date >= '2023-07-10'
    GROUP BY Run_Date
    ) AS d
ORDER BY Run_Date DESC

Today	Run_Date	Daily_Count	rn	days_diff
2023-07-19	2023-07-15	1	1	4
2023-07-19	2023-07-14	1	2	null
2023-07-19	2023-07-13	1	3	null
2023-07-19	2023-07-12	1	4	null
2023-07-19	2023-07-11	1	5	null
2023-07-19	2023-07-10	1	6	null

fiddle

note: You do NOT need "select distinct" if you are using GROUP BY - grouping automatically includes making rows "distinct"

To output empty strings in the different column convert that column to a string:

, case when rn = 1 then cast(datediff(day,run_date,getdate()) as varchar(6)) else '' end as

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Compare run date to current date

1条答案

相关问题

热门标签

最新问答