CODEWITHSUNDEEP
sqlsql-serversql-server-2008average
Kishor Patil
Kishor Patil

Reputation: 1

How to find the average (in terms of date/day) in sql server?

I want to find the result as such like

  1. I want to take current date from system .
  2. I want to retrieve corresponding day of that date.
  3. and go back 90 days back to the current date and want to try to find out that how many times similar day had occured.
  4. want to find out total patients visisted on those days to clinic. (ex.COUNT(VisitId)) from my PtientVisist table.
  5. and finally want to calaculate the average of patients visited on that day.

like-> if I get todays date 8 jun 2012 and retrieved today day as Friday . so want to find out like since from last 90 days from todays date how many fridays had occured and how many patiens visited on that total fridays and want to count AVG = Total patients visisted/ total friday.

Please give some assistance Thanks in advance.

Upvotes: 0

Views: 287

Answers (1)

Nicole Castle
Nicole Castle

Reputation: 410

Air code to get the # of days of the week in a timespan:

DECLARE @enddate datetime
SET @enddate = GETDATE()

DECLARE @dayofweek nvarchar(50)
SELECT @dayofweek = DATENAME(dw, getdate())

DECLARE @startdate datetime
SELECT @startdate = DATEADD(d,-90,  getdate())

DECLARE @count int
SET @count = 0

WHILE (@startdate < @enddate)
BEGIN
    IF (DATENAME(dw, @startdate) = @dayofweek)
        BEGIN
            SET @count = @count + 1
        END
    SET @startdate = DATEADD(d, 1, @startdate)
END

PRINT @count

You can do something with the actual date in the loop in order to get your final query.

Upvotes: 1

Related Questions