SQL querry help

Question

SQL querry help

mcdt

Valued Member

Points: 62
More actions
April 29, 2008 at 9:27 am

#184932

Hi All,
I am working on reporting page for attendance application and I was wondering if it is possible in to create a querry to get a total # of all requests in a month for a particular date range and if the there are no requests in a month to display 0 for that month. This is what I am currently doing but it only returns values if there are requests in a particular month. I need the following:
Month TotalTime
1 0
2 5
3 6
4 0
etc ...
(
@status int,
@managerUserid uniqueidentifier,
@fromDate smalldatetime,
@toDate smalldatetime,
@hoursInDay int,
@TimeOffType int
)
AS
BEGIN
SET NOCOUNT ON
SELECT (SUM(TimeOffRequests.Hours) / @hoursInDay) AS TotalTime, MONTH(TimeOffRequests.FromDate) AS Month
FROM TimeOffRequests INNER JOIN
Profiles ON TimeOffRequests.UserID = Profiles.UserId
WHERE (Profiles.ReportsTo = @managerUserid)
AND (TimeOffRequests.Status
= @status) AND (TimeOffRequests.TimeOffType = @TimeOffType) AND
(TimeOffRequests.FromDate >= @fromDate) AND (TimeOffRequests.ToDate <= @toDate)
GROUP BY MONTH(TimeOffRequests.FromDate) order by Month
END

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Well, you haven't given us any table DDL to go on, but after a cursory glance I would say that you should use an OUTER JOIN instead of an INNER JOIN.

By the way, when you post code, please put a few carriage returns in so that the code doesn't sprawl off the right hand side of the screen!

John

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

John is right you need to use an outer join, but first you need to have a list of all the months. This solution assumes you have a months table:

[font="Courier New"]SELECT

ISNULL((SUM(TimeOffRequests.Hours) / @hoursInDay), 0) AS TotalTime, -- isnull makes sure you have a 0 instead of null

CM.MONTH

FROM

Months CM LEFT OUTER JOIN -- gets all months

TimeOffRequests ON

CM.month_start_date <= TimeOffRequests.FromDate AND CM.month_end_date > TimeOffRequests.FromDate

-- Or CM.month_no = MONTH(TimeOffRequests.FromDate) INNER JOIN

Profiles ON

TimeOffRequests.UserID = Profiles.UserId

WHERE

(Profiles.ReportsTo = @managerUserid) AND

(TimeOffRequests.Status = @Status) AND

(TimeOffRequests.TimeOffType = @TimeOffType) AND

(TimeOffRequests.FromDate >= @fromDate) AND (TimeOffRequests.ToDate <= @toDate)

GROUP BY

CM.MONTH

ORDER BY

CM.MONTH[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mcdt Valued Member Points: 62 More actions · Answer 3

Hi All,

Thanks for the quick response! Jack I think that is exactly what I need. I will give it a try.

Thanks again.

tom

mcdt Valued Member Points: 62 More actions · Answer 4

Hi John,

I tried your code but It still only returns the months only if there are time off requests for the month. What am I doing wrong?

SELECT ISNULL(SUM(TimeOffRequests.Hours / Profiles.HoursWorkedInDay), 0) AS TotalTime, CM.Month

FROM Months AS CM LEFT OUTER JOIN

TimeOffRequests ON CM.Month = MONTH(TimeOffRequests.FromDate) INNER JOIN

Profiles ON TimeOffRequests.UserID = Profiles.UserId

WHERE (Profiles.ReportsTo = @managerUserid)

AND (TimeOffRequests.Status = @status) AND (TimeOffRequests.TimeOffType = @TimeOffType) AND

(TimeOffRequests.FromDate >= @fromDate)

AND (TimeOffRequests.ToDate <= @toDate)

GROUP BY CM.Month

ORDER BY CM.Month

GSquared SSC Guru Points: 260824 More actions · Answer 5

The problem is the Inner Join to Profiles.

Try this:

;WITH CTE (RequestMonth, HoursRequested, HoursWorked) as

(SELECT datepart(month, TimeOffRequests.FromDate),

TimeOffRequests.Hours, Profiles.HoursWorkedInDay

FROM dbo.TimeOffRequests

INNER JOIN dbo.Profiles

ON TimeOffRequests.UserID = Profiles.UserID

WHERE Profiles.ReportsTo = @managerUserid

AND TimeOffRequests.Status = @status

AND TimeOffRequests.TimeOffType = @TimeOffType

AND TimeOffRequests.FromDate >= @fromDate

AND TimeOffRequests.ToDate <= @toDate)

SELECTISNULL(SUM(HoursRequested / HoursWorked), 0) AS TotalTime, CM.Month

FROMMonths AS CM

LEFT OUTER JOIN CTE ON CM.Month = RequestMonth

GROUP BY CM.Month

ORDER BY CM.Month

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mcdt Valued Member Points: 62 More actions · Answer 6

Hi GSquared,

It looks like that did the trick. Thank you very much for your help.

Regards,

tom