asked on

Sql query to calculate income of a day, week, month, year.

hi everyone ..

I need a query that will take the a datetime parameter and will output the day income
of this parameter, week, month, year and total income ..

output format :
Staff.Name | day income | week income | month income | year income | total income

--------------------------
you need to use dbo.Treatment.DateTime to compare it with the passed day parameter
--------------------------

SELECT dbo.Staff.Name, SUM(dbo.Treatment.priceDue) AS TotalIncome
FROM dbo.Staff INNER JOIN
dbo.Treatment ON dbo.Staff.StaffID = dbo.Treatment.StaffID
GROUP BY dbo.Staff.Name

folderol

CREATE PROCEDURE Income_summary @requested_day datetime
AS

SELECT dbo.Staff.Name,
sum(case when dbo.Treatment.DateTime = @requested_day then dbo.Treatment.priceDue else 0 end) as DayIncome,
sum(case when datepart(week,dbo.Treatment.DateTime) = datepart(week,@requested_day) and datepart(year,dbo.Treatment.DateTime) = datepart(year,@requested_day) then dbo.Treatment.priceDue else 0 end) as WeekIncome,
sum(case when datepart(month,dbo.Treatment.DateTime) = datepart(month,@requested_day) and datepart(year,dbo.Treatment.DateTime) = datepart(year,@requested_day) then dbo.Treatment.priceDue else 0 end) as MonthIncome,
sum(case when datepart(year,dbo.Treatment.DateTime) = datepart(year,@requested_day) then dbo.Treatment.priceDue else 0 end) as YearIncome,

SUM(dbo.Treatment.priceDue) AS TotalIncome
FROM dbo.Staff INNER JOIN
dbo.Treatment ON dbo.Staff.StaffID = dbo.Treatment.StaffID
GROUP BY dbo.Staff.Name
go

Tom

Misbah

ASKER

Thanks Tom :)

can u please modify it a little more .. the DayIncome is not working now .. I think because the Treatment.DateTime has also the day time .. like this : 6/4/2006 12:14:09 PM .. I am not sure if the rest need to be modified as well !

Thanks again :)

ASKER CERTIFIED SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Misbah

ASKER

Thanks Alot Tom :)