如何在sql中检索今天，每周和每月的数据 [英] How to retrieve data as today,weekly and monthly in sql

问题描述

我正在写一个查询，给我今天，本周，本月（3个单独的专栏）今天销售的产品数量，一个星期到今天，月初至今的比例意义，例如它将显示自星期一以来销售的产品，从本月的第一天开始，随着时间的推移，每个下一周，每个月和今天都要继续，去年还有其他3个具有相同逻辑的列。我需要的是帮助使用DATEADD或DATEDIFF获取日期查询

I am writing a query to give me number of products sold today,this week, this month (3 separate columns) on today, a week to date, month to date scale meaning today for example it will show products sold since monday, since the first of the month this is to continue with each following week, month and today as time goes, there also are to be 3 other columns with the same logic for last year. What i need is help getting the date query using DATEADD or DATEDIFF

SELECT WeekCount, MonthCount, YearCount
FROM
(
SELECT LearnerId,
    CASE
        WHEN Date >= DATEADD(dd, 1 - DATEPART(dw, GETDATE()), GETDATE())
        THEN 'WeekCount'
        WHEN Date >= DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
        THEN 'MonthCount'
        WHEN Date >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
        THEN 'YearCount'
    END as lbl
FROM admission
) ProductSales
PIVOT
(
COUNT(LearnerId)
FOR lbl IN ([WeekCount], [MonthCount], [YearCount])
) t

但是这不能正常工作

But this not work as I want

推荐答案

1。获取今天的数据

1. to get today's data

SELECT * from table1
WHERE datetimefield >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
AND datetimefield < DATEADD(day, DATEDIFF(day, -1, GETDATE()), 0)

2. to获取当前周的数据：

2. to get current week's data:

select * from table1
WHERE datetimefield >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) / 7 * 7, 0)
AND datetimefield < DATEADD(day, DATEDIFF(day, -1, GETDATE()), 0)

3. to获取当前月份的数据

3. to get current month's data

select * from table1
WHERE datetimefield >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
AND datetimefield < DATEADD(month, DATEDIFF(month, -1, GETDATE()), 0)

这篇关于如何在sql中检索今天，每周和每月的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！