Help with Select Statement

Edited by moderator XIII, please don't use [ code ] tags around your code. Instead change your Profile | Site settings to use the Rich editor (with code support) to insert colorized code and line numbers:

I have a select statement that follows:

SELECT Events.legendID AS Events_legendID,
Units.unitID AS Units_unitID,
Units.msbbID, Units.belongsTo,
Units.name AS unitName,
Legend.legendID AS Legend_legendID,
Legend.Color,
Legend.Name legendName,
Legend.Active,
Events.unitID AS Events_UnitID,
Events.userID AS eventUserID,
Events.startDate,
Events.endDate,
Events.eventID,
Events.Title
FROM Events INNER JOIN Units ON Events.unitID = Units.unitID
AND Events.unitID = Units.unitID INNER JOIN Legend ON Events.legendID = Legend.legendID
WHERE startDate BETWEEN convert(datetime, '4/01/2006') AND convert(datetime, '6/30/2006')
ORDER BY unitName ASC;

This code works great with the exception of the events that start and end outside of the quarter dates I am entering. For example if there is an event that start in January and ends in August then I need to show that event in the 1st quarter, 2nd quarter and 3rd quarter.

any help on how to get those events that start and end outside the 'active' quarter BUT pertain to the active quarter?

thanks in advance!

Hello, if i get you right, maybe this would work:

WHERE
startDate <= convert(datetime, '6/30/2006') AND
endDate >= convert(datetime, '4/01/2006')

Anyway, i'd suggest you craft your data structure a bit more, to work on "quarters" rather than generic (unconstrained) dates...

HTH -LV

|||what do you mean by "... work on quarters rather then generic..."?|||

Mmm, what's unclear? So to say, i'd rather not write a treaty...

Also, i'm not such an expert with SqlServer either, so i guess we'll need someone else for a "ground-breaking" implementation sample...

-LV