Solved

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

Posted on 2006-06-15
4
1,536 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Misbah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:folderol
ID: 16911681
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
0
 
LVL 9

Author Comment

by:Misbah
ID: 16912721
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 :)
0
 
LVL 19

Accepted Solution

by:
folderol earned 250 total points
ID: 16913401
Any where I am using the datepart, the time portion will have no effect.  So, to fix the DayIncome, the case statement should be ->

sum(case when cast(floor(cast(dbo.Treatment.DateTime as float)) as datetime) = @requested_day then dbo.Treatment.priceDue else 0 end) as DayIncome

Floor converts the float value of DateTime to integer, and casting the integer back to datetime removes the time portion so you are left with 6/4/2006 00:00:00

There are a bunch of methods for removing the time from a datetime, a lot of them use convert() function, which I tend to avoid.

Tom.
0
 
LVL 9

Author Comment

by:Misbah
ID: 16914437
Thanks Alot Tom :)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question