Need query to retrieve billable information Monthly

Question

Need query to retrieve billable information Monthly

Krtyknm

SSC Eights!

Points: 884
More actions
June 5, 2008 at 1:31 pm

#186523

Hi All,
I have database that holds the productivity information.
We need to create a report for monthly basis. Our company employees will work on the product and charge a billable to the client.
So i need a query it should run monthly and create a report.
Output should be..
EmployeeName Workhours Billable
XXXX 25 9.1%
YYYY 45 10%.
You need not worry about the workhours and billable.
This query should run on monthly without changing anything in the query and also without using any DTS jobs.
Thanks,
Karthik

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

With all due respect, this community is for people looking for assistance, not complete solutions.

I think I speak for eveyone here in saying that we would more than happy to help you improve what you've done and even share our knowledge, but your questions implies that you want "us" to do all of the work for you.

If you have in fact tried to accomplish your goal, then please read the following article and then post the approach that you've tried.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 2

Heh... bad day getting worse ol' friend? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Krtyknm SSC Eights! Points: 884 More actions · Answer 3

Hey Jason,

This is the query i am trying to solve

Create table Billing (Id int, Name varchar(50), Workhours Varchar(20),Description Varchar(50))

Declare @strdate int

Set @strdate =datepart(mm,getdate())-1

Select name,Sum(CAST(ROUND(Workhours, 0) AS int)) as [Workhours],Sum(CAST(ROUND(Workhours, 0) AS int))/40*100 as Billable from

Billing Where Id in(2,4,6,8) and Datepart(mm,workdate)=@strdate

group by name

Datepart(mm,workdate)=@strdate

Here i got the problem

It will take all the years including. I need a current year.

Can you please assist me?

Thanks,

Karthik

Krtyknm SSC Eights! Points: 884 More actions · Answer 4

Hi All,

Thanks for your time.

I have used the date[art for the year and it worked fine.

Thanks and regards,

Karthik