July 8, 2011 at 7:41 pm
Hello,
I received from a contractor the responsibility of creating SQL reports dependant on weekly parameters. Below is the query I was given but only provide me with year to date reporting. I was given instructions about how to narow down the reporting but none of the changes I made produced the expected results. Can anyone help?
Use lcscdr
SELECT
CONVERT(varchar(10), Media.SessionIdTime, 103) [Start Date],
CONVERT(varchar(10), VoipDetails.SessionIdTime, 108) [Start Time],
CONVERT(varchar(10), SessionDetails.SessionEndTime, 103) [End Date],
CONVERT(varchar(10), SessionDetails.SessionEndTime, 108) [End Time],
CONVERT(varchar(10),SessionDetails.SessionEndTime - Media.SessionIdTime,108) [Time],
dbo.MediaList.Media, dbo.Users.UserUri, dbo.Media.SessionIdTime,
dbo.SessionDetails.SessionEndTime, dbo.SessionDetails.TargetUserId,
dbo.SessionDetails.User1Id, dbo.SessionDetails.User2Id, dbo.SessionDetails.DialogId
From dbo.Media
Inner Join dbo.MediaList
On dbo.Media.MediaId = dbo.MediaList.MediaId
Inner Join dbo.SessionDetails
On dbo.Media.SessionIdTime = dbo.SessionDetails.SessionIdTime
And dbo.Media.SessionIdSeq = dbo.SessionDetails.SessionIdSeq
Inner Join dbo.Users
On dbo.SessionDetails.User1Id = dbo.Users.UserId
Or dbo.SessionDetails.User2Id = dbo.Users.UserId
Or dbo.SessionDetails.SessionStartedById = dbo.Users.UserId
Left Join VoipDetails
On dbo.SessionDetails.SessionIdTime = VoipDetails.SessionIdTime
And dbo.SessionDetails.SessionIdSeq = VoipDetails.SessionIdSeq
And dbo.Users.UserId = VoipDetails.DisconnectedByURIId
Where dbo.Media.SessionIdTime >= '2011-01-30'
and dbo.Media.SessionIdTime <= '2011-2-05'
and dbo.MediaList.MediaId = '5'
and dbo.Users.UserUri like 'gneumair@kraft%'
or dbo.Users.UserUri like
Editor: added code formatting.
July 9, 2011 at 1:12 am
I expect the OR at the end of the query is likely to be your problem. Regardless of the date comparisons if the condition following the "or" evaluates to true you will get that data back.
You need to enclose the two sides of the condition in parenthesis:
where (datecol > '20110601' and datecol < '20110630') or name like 'whatever%'
If that does not help please post the full query, plus DDL and some test data.
Mike
July 9, 2011 at 10:00 am
Have to agree I do. Please read the first article I reference below in my signature block below regarding asking for help. Then post the entire query you are working with, the DDL (CREATE TABLE statements) for the tables involved, sample data for the tables (as a series of INSERT INTO statements), and the expected results. Provide us with this information and you will get many people stepping up to help and you will get tested code in return as well.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply