如何在SQL Server中获取本周的每日数据 [英] how to get day wise data of this week in sql server

问题描述

我有包含用户数据的数据输入表（ServiceData）。列为Userid，BillDate（类型为DateTime）等。
我需要每周（当前周）获取用户数。

I have data entry table(ServiceData) with users data.Columns are Userid,BillDate (type DateTime)and etc. I need to fetch count of users weekly (current week).

查询

select     BillDate
          ,DATENAME(dw,BillDate) as day 
          ,count(BillDate) as total 
from ServiceData 
group by 
BillDate,DATENAME(dw,BillDate) 
order by BillDate desc

这只是获取开票日期，但我想获取星期日入场计数，星期一入场计数....从星期日开始

this is only fetch the day of billdate, but i want to get count of sunday entry , count of monday entry .... from sunday to saturday (current weeek) is this possible?

预期产量

    ID   |   TOTAL  |   DAY
   --------------------------
    1    |    23    |   Sun
    2    |    54    |   Mon
    3    |    17    |   Tues
    4    |    56    |   Thus
    5    |    45    |   Fri
    6    |    78    |   Sat

推荐答案

这应该可以解决问题：

SELECT 
  row_number() over (order by (SELECT 1)) ID,
  count(*) Total,
  LEFT(Datename(weekday, Cast(Billdate as date)), 3) Day
FROM 
  ServiceData
WHERE
  BillDate >= dateadd(week, datediff(d, -1, getdate()-2)/7, -1)
GROUP BY
  Cast(Billdate as date)
ORDER BY
  Cast(Billdate as date)

这篇关于如何在SQL Server中获取本周的每日数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！