Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I'm trying to create daily, monthly and weekly SQL Query report to our services time we spent int task and total billing time just want to see if I'm on right track

GO
--Daily     

SELECT     SUM(billingsTimes.actualTotalTime) AS TotalTime, Tasks.taskName, billableType.billableTypeName
FROM         billingsTimes INNER JOIN
                      aspnet_Users ON billingsTimes.userID = aspnet_Users.UserId INNER JOIN
                      billableType ON billingsTimes.billableTypeID = billableType.rank INNER JOIN
                      Tasks ON billingsTimes.taskID = Tasks.taskID
WHERE     (billingsTimes.taskID = 10045) AND (aspnet_Users.UserId = '178db2a8-be1d-48e0-9ebc-f64f7b1ff63e') AND (CONVERT(DATE, billingsTimes.dateOfService) 
                      = '01/29/2013')
GROUP BY billingsTimes.billableTypeID, Tasks.taskName, billableType.billableTypeName,DATEPART(D, dateOfService)    


GO
--Weekly 

SELECT     SUM(billingsTimes.actualTotalTime) AS TotalTime, Tasks.taskName, billableType.billableTypeName,DATEPART(WEEK, dateOfService)AS weekNumber      
FROM         billingsTimes INNER JOIN
                      aspnet_Users ON billingsTimes.userID = aspnet_Users.UserId INNER JOIN
                      billableType ON billingsTimes.billableTypeID = billableType.rank INNER JOIN
                      Tasks ON billingsTimes.taskID = Tasks.taskID
WHERE     (billingsTimes.taskID = 10045) AND (aspnet_Users.UserId = '178db2a8-be1d-48e0-9ebc-f64f7b1ff63e') 
GROUP BY billingsTimes.billableTypeID, Tasks.taskName, billableType.billableTypeName,DATEPART(WEEK, dateOfService), DATEPART(WEEK, dateOfService)

GO
--Monthly

SELECT     SUM(billingsTimes.actualTotalTime) AS TotalTime, Tasks.taskName, billableType.billableTypeName,DATEPART(MONTH, dateOfService)AS monthNumber      
FROM         billingsTimes INNER JOIN
                      aspnet_Users ON billingsTimes.userID = aspnet_Users.UserId INNER JOIN
                      billableType ON billingsTimes.billableTypeID = billableType.rank INNER JOIN
                      Tasks ON billingsTimes.taskID = Tasks.taskID
WHERE     (billingsTimes.taskID = 10045) AND (aspnet_Users.UserId = '178db2a8-be1d-48e0-9ebc-f64f7b1ff63e') 
GROUP BY billingsTimes.billableTypeID, Tasks.taskName, billableType.billableTypeName,DATEPART(MONTH, dateOfService)
GO            
share|improve this question

1 Answer 1

up vote 3 down vote accepted

Somewhat hard to judge without any idea of the schema, but here are a few observations:

  1. Join condition on billableType table is questionable: why comparing an ID to a rank in INNER JOIN billableType ON billingsTimes.billableTypeID = billableType.rank?

  2. Try to limit GROUP BY to the columns that will become sort of 'logical key' of your result set. E.g. it seems the query is limited to a single task at a time (billingTimes.taskID = ###). Use max(taskName) as taskName in your select list and exclude taskName from the group by clause.

  3. There's no need for datepart(D, dateOfService) in GROUP BY: the query is already restricted to a single day.

share|improve this answer
    
Hello Pavel, it seems that this is your first answer on Code Review. Thank you! It's an useful answer. I also 1/ edited it a bit to show you how to format code 2/ tried to make your observations clearer. Hope that helps! –  Quentin Pradet Feb 16 '13 at 15:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.