how to get day wise data of this week in sql server

Question

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).

Query

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?

expected Output

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

You only show us the expected output. how about the sample input ? — Squirrel
Welcome to StackOverflow! Could you please edit your question and add a bit more information. Could you provide us with some sample data of the table ServiceData. Please also let us know what query you've tried already yourself. — MBijen

t-clausen.dk t-clausen.dk · Accepted Answer · 2018-09-18T08:42:16

This should do the trick:

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)

how to get day wise data of this week in sql server

1 Answers