CODEWITHSUNDEEP
sqlsql-serverdatabase
aparna rai
aparna rai

Reputation: 833

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

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

Upvotes: 1

Views: 4040

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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)

Upvotes: 1

Related Questions