March 22, 2004 at 2:06 pm
Hi all,
I have this stored proc where I need to display all the subGroups and Indicators, whether or not they have any tasks assigned to them. Basically, I have 3 tables: SubGroup (from where I need all subgroups), Indicator (I need all indicators, too), and Task. If I run the query WITHOUT the date range part, it returns all matching records from the SubGroup & Indicator tables, but when I add the date range filter in the 'AND' clause, it only returns the records that fall between that date range. What am I doing wrong?
Here is the query portion of my sp, where I've been trying things out in SQL Query analyzer:
DECLARE @groupID int
DECLARE @eventID int
DECLARE @filterdate datetime
DECLARE @todate datetime
SET @groupID = 2
SET @eventID = 1
SET DATEFORMAT dmy
SET @filterdate = CAST('03/05/2004' as datetime)
SET @todate = DATEADD(d,1,@filterdate)
SELECT I.subGroupID,
I.indicatorID,
T.taskID,
SG.subGroupName,
I.indicatorDescription,
I.indicatorDescription2,
I.dataOwner,
I.dataSource,
I.dataLocation,
T.dataValue,
T.dataVintage,
T.lastUpdatedDate
FROM Task T
RIGHT OUTER JOIN Indicator I ON T.indicatorID = I.indicatorID
RIGHT OUTER JOIN SubGroup SG ON SG.subGroupID = I.subGroupID
WHERE (SG.groupID = @groupID )
AND (T.eventID = @eventID OR T.eventID is null)
AND ((T.dataVintage BETWEEN @filterdate AND @todate) OR T.dataVintage is null)
ORDER BY I.subGroupID, I.indicatorID
Thanks in advance; I'm really stuck!
March 22, 2004 at 2:50 pm
SELECT
I.subGroupID,
I.indicatorID,
T.taskID,
SG.subGroupName,
I.indicatorDescription,
I.indicatorDescription2,
I.dataOwner,
I.dataSource,
I.dataLocation,
T.dataValue,
T.dataVintage,
T.lastUpdatedDate
FROM
SubGroup SG
LEFT JOIN
Indicator I
ON
SG.subGroupID = I.subGroupID
LEFT JOIN
Task T
ON
T.indicatorID = I.indicatorID AND
((T.dataVintage BETWEEN @filterdate AND @todate) OR T.dataVintage is null) AND
(T.eventID = @eventID OR T.eventID is null)
WHERE
SG.groupID = @groupID
ORDER BY
I.subGroupID,
I.indicatorID
Which if T.eventID T.dataVintage cannot be null and it also looked like bot a SubGroup and Indicator had to return, try this instead.
SELECT
I.subGroupID,
I.indicatorID,
T.taskID,
SG.subGroupName,
I.indicatorDescription,
I.indicatorDescription2,
I.dataOwner,
I.dataSource,
I.dataLocation,
T.dataValue,
T.dataVintage,
T.lastUpdatedDate
FROM
SubGroup SG
INNER JOIN
Indicator I
ON
SG.subGroupID = I.subGroupID
LEFT JOIN
Task T
ON
T.indicatorID = I.indicatorID AND
(T.dataVintage BETWEEN @filterdate AND @todate) AND
T.eventID = @eventID
WHERE
SG.groupID = @groupID
ORDER BY
I.subGroupID,
I.indicatorID
The reason this will work is it filters Task before the join to the other tables, where in the where clause it filters afterward.
March 22, 2004 at 9:10 pm
Thanks for your help, Antares686. Your suggestions worked well.
BTW, do you know any good books on SQL? I think I need to read up some more on this.
March 23, 2004 at 5:18 am
Lot's of good books out there. Personally thou I learn better from testing various ways and running into issues myself. I always found the Exam Cram books good in overall view and of course SQL BOL. But as for a book totally focused on querying techniques I have not found one that hits my fancy yet. Others will have suggestions and there have been many threads on book suggestions.
March 23, 2004 at 10:20 am
Yeah, I haven't been able to find a good SQL book myself. I usually just try different queries until I get what I want, but this time I was on a tight deadline & I'd wasted enough time already!
March 23, 2004 at 10:29 am
That's what this is all about. Hope all works well for you and feel free to post here often to help others or ask when in need.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply