I think my INNER is out 1

[ItHurtsWhenIThink]

I can't seem to get the "between' to filter the date range needed.

Can you tell where I'm screwing up?

Here is the statement:

select * from(select Distinct(Users.UserID),Users.UnitID, Users.LName,Users.FName, Users.DeptID,Users.Shift,LeaveRequests.Leavedate,SUM(Leave_Points.Points)

over

(partition by Leave_Points.UserID) TotalPoints from Users inner Join Leave_Points JOIN LeaveRequests on Leave_Points.LeaveID = LeaveRequests.LeaveID on Users.UserID = Leave_Points.UserID

Where Leave_Points.DeptID=1 AND UserType='Firefighter' AND LeaveRequests.LeaveDate between '4/1/2008' and '4/30/2009') PointsRepartition order by TotalPoints ASC, LName, FName

RESULTS: (only including date values)

LeaveDate
3/18/2009 12:00:00 AM
2/27/2009 12:00:00 AM
3/5/2009 12:00:00 AM
4/15/2009 12:00:00 AM


Hmmmm.
Thanks

 

[bborissov]

And what these date seems wrong for you?
You asked for date from 1 April 2008 to 30 April 2009

But I rather use so called ISO date format:

AND LeaveRequests.LeaveDate between '20080401' and '20090430'

That way date is NOT ambitious, because what is
'4/1/2008'?
4 Jan 2008 or 1 April 2008?
You know it, but if SQL Server is set to some European settings you are in trouble.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ItHurtsWhenIThink]

That did the trick. Thanks!


How can I get this query to list all users, whether or not they have any points?

 

[markros]

If you need to list all users regardless of points, use LEFT JOIN from Users to Points.

 

[ItHurtsWhenIThink]

I tried that before adding it to this thread.

This statement should return 11 records but I get 4 which have points greater than zero.

select * from
(select Distinct(Users.UserID),Users.UnitID, Users.LName, Users.DeptID,Users.Shift, Users.FName,SUM(Leave_Points.Points)
over
(partition by Leave_Points.UserID) TotalPoints from Users LEFT JOIN Leave_Points JOIN LeaveRequests on Leave_Points.LeaveID = LeaveRequests.LeaveID on Users.UserID = Leave_Points.UserID

Where Leave_Points.DeptID=1 AND UserType='Firefighter' AND LeaveRequests.LeaveDate between '20080401' and '20090430') PointsRepartition order by TotalPoints ASC, LName, FName

Looks right...uh?

 

[markros]

You're making very common mistake here which I myself was made couple of times before finally getting it.

If you need to restrict values from the table which is on the right side of the LEFT JOIN

e.g. From Users LEFT JOIN LeavePoints ON ...

you can not put the restriction condition on LeavePoints into WHERE clause. It would make the query behave as INNER JOIN instead.

You need to put condition into JOIN condtion e.g.

on Users.UserID = Leave_Points.UserID and Leave_Points.DeptID=1

 

[markros]

Oops, so bad we don't have edit functionality here.
The first phrase should read

I made couple

instead of

I myself was made couple

 

[ItHurtsWhenIThink]

I still only get records where their is a match on the Points column (LeavePoints table).

Here is the statement:

select * from
(select Distinct(Users.UserID),Users.UnitID, Users.LName, Users.DeptID,Users.Shift, Users.FName,SUM(Leave_Points.Points)

over (partition by Leave_Points.UserID) TotalPoints

from Users

LEFT JOIN Leave_Points JOIN LeaveRequests on Leave_Points.LeaveID = LeaveRequests.LeaveID on Users.UserID = Leave_Points.UserID

Where Leave_Points.DeptID=1 AND UserType='Firefighter'
AND LeaveRequests.LeaveDate between '20080401' and '20090430')

PointsRepartition order by TotalPoints ASC, LName, FName

 

[markros]

You didn't change your statement at all as I told you - no wonder you get the same result.

 

[markros]

Try (though join with LeaveRequests complicates the matter further)

select * from
(select Distinct(Users.UserID),Users.UnitID, Users.LName, Users.DeptID,Users.Shift, Users.FName,SUM(ISNULL(Leave_Points.Points,0))

over (partition by Leave_Points.UserID) TotalPoints

from Users

LEFT JOIN Leave_Points on Users.UserID = Leave_Points.UserID AND Leave_Points.DeptID=1
JOIN LeaveRequests on Leave_Points.LeaveID = LeaveRequests.LeaveID AND LeaveRequests.LeaveDate between '20080401' and '20090430'
Where UserType='Firefighter')

PointsRepartition order by TotalPoints ASC, LName, FName

From the top of my head - not tested

 

[gmmastros]

I think the second join (to the LeaveRequests table) also need to be a left join.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, may be. BTW, where have you been? I sort of missed you here and there...

 

[gmmastros]

Vacation last week. Pigeon Forge, Tennessee. It was very nice.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I figured. Glad to hear you had a nice vacation.