Selecting Based Upon a Variable Answer.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting Based Upon a Variable Answer.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-06 : 10:45:37

Hi,
I'm pretty new with T-SQL. I am using it in the development of an ASP.NET 2.0 program. If anyone could help me figure out how to take this "theoretical/plain english" statement and turn it into a T-SQL statement, I would greatly appreciate it.
1. Select all records in tblLastPlayed that have varUserName.
2. If TodaysDate < 30 Days from tblLastPlayed.Date then don't include this game.
3. Display all Games that do not have an entry in tblLastPlayed or haven't been played in the last 30 days.
Thanks in advance.
Respectfully,
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-06 : 10:59:52

I think something like this would work but am still working out exact syntax, would appreciate any thoughts:
Select * FROM tblGAME where varUserName <> tblLastPlayed.UserName where tblLastPlayed.Date < (Date.Time.Now - 30[Days])

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-06 : 11:51:44

I am getting closer. Continue to appreciate any thoughts:
Dim varDate as Date = Date.Now
varDate.AddDays(-30)
SqlDataSource1.Select "SELECT * FROM tblGAMES WHERE @username <> tblLastPlayed.uID WHERE tblLastPlayed.LastPlayedDate > '" & varDate & "'"
In other words, it selects all entries from the table where the current user does not have a corresponding record for that game in the tblLastPlayed where the game was played within the last 30 days.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-06 : 12:07:24

What about doing this in a stored procedure then just execute that passing the User as a parameter

Something like:


CREATE PROC NameOfSproc @UserID int
AS
SELECT G.GameID, <Column List>
FROM tblGAMES G LEFT OUTER JOIN tblLastPlayed LP
		ON G.GameID = LP.GameID
WHERE (LP.GameID IS NULL) OR ((DATEDIFF(dd,LP.[Date],GETDATE()) > 30) AND (LP.UserID = @UserID))

Andy

Edit:
Added extra )

Beauty is in the eyes of the beerholder

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-06 : 13:58:43

Thanks. I am not ready to do a stored procedure yet, though I will probably convert the statements over to store procedures eventually. I used your code basically however, but it isn't working like I want it too. I think, maybe, that my initial code was messed up. See, all I want to happen is for the SELECT statement to check whether a given game has been played within the last 30 days, and if so exclude it from the results. Here is my code at this point:
SqlDataSource1.SelectCommand = "SELECT TOP " & CInt(Profile.GamesConfig.NumofGames).ToString() & " G.ID, G.Title, G.Popularity, G.Type, [Last Played] FROM GAME G JOIN LastPlayed LP ON G.ID = LP.gID WHERE ('" & varUserName & "' NOT LIKE LP.uID)"
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-06 : 14:14:41

Do you not want to do this anymore?

quote:
Display all Games that do not have an entry in tblLastPlayed or haven't been played in the last 30 days.

quote:
See, all I want to happen is for the SELECT statement to check whether a given game has been played within the last 30 days, and if so exclude it from the results

Just add this to your WHERE clause then

WHERE (DATEDIFF(dd,LP.[Last Played],GETDATE() > 30) AND ........

Beauty is in the eyes of the beerholder

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-06 : 14:21:10

I think those two things are basically the same thing. I either want it to display all the games that don't have an entry or not display the games that do have an entry. Either way, just as long as it displays all the games but the ones that have an entry (and are within the past thirty days), but I would be happy just to get it to exclude those that have an entry while including the rest at this point.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-06 : 15:19:59

Thats what my original query did using a LEFT JOIN and should work for you, you should just be able to hash it to suit.
Did you try the SELECT statement in QA to see if you got the desired results?

quote:
(LP.GameID IS NULL)

Would make these games appear in the results even if they didnt appear in the last played table

Beauty is in the eyes of the beerholder

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-06 : 15:28:11

Thanks Andy, I finally got it to work. I made some mistakes with your original code, that was the problem. But they are now ironed out. Thanks very much.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources