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

Help with Parameterized query building dataset

I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value.

This is the code for the Method to return a datareader

public

SqlDataReader GetOrgID()

{

Singleton s1 =Singleton.Instance();Guid uuid;

uuid =

newGuid(s1.User_id);SqlConnection con =newSqlConnection(conString);string selectString ="Select OrgID From aspnet_OrgNames Where UserID = @.UserID";SqlCommand cmd =newSqlCommand(selectString, con);

cmd.Parameters.Add(

"@.UserID",SqlDbType.UniqueIdentifier, 16).Value = uuid;

con.Open();

SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return dtr;

This is the code trying to accomplish the same thing with a Dataset instead.

publicDataSet organID(DataSet dataset)

{

Singleton s1 =Singleton.Instance();Guid uuid;

uuid =

newGuid(s1.User_id);string queryString ="Select OrgID From aspnet_OrgNames Where UserID = @.UserID";SqlConnection con =newSqlConnection(conString);

SqlCommand cmd =newSqlCommand(queryString, con);

cmd.Parameters.Add(

"@.UserID",SqlDbType.UniqueIdentifier, 16).Value = uuid;

SqlDataAdapter adapter =newSqlDataAdapter();

adapter.SelectCommand = cmd;

adapter.Fill(dataset);

return dataset;

}

Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well.

So assume that the Guid is a valid entry..I should return a valid dataset but its null.

Got my answer on another board

http://forums.asp.net/1597763/ShowThread.aspx#1597763

|||

hi prysson,

i saw that link and its exactly the same question and posted by yourself only, you asked the same question and answered to yourself.