Wow! As of 8AM EST, only one person got this question right. As far as I can tell, out of all the answers only two dates are excluded,...

This will get the number of days between today and the date in the DtContactDate column:

DATEDIFF(dd, DtContactDate, GetDate()) AS NumberOfDays

Greg

Norman,

Try this:

SELECT DISTINCT S.Computername

FROM dbo.tblSoftware S

WHERE S.softwareName = 'App1' AND NOT EXISTS

(SELECT * FROM dbo.tblRegistry R

WHERE R.Regkey IN('RegKey1', 'Regkey2') AND R.Computername = S.Computername)

Greg

I don't think you would want to go with if statements. There are 12 different potential variables in this case, which equals a whole lot of possibilities! I've seen...

I just realized that INT or BIGINT are not large enough to hold a 24 digit number. Maybe DECIMAL(24, 0)?

Couldn't you use SUBSTRING to isolate each individual day, then convert it to an integer. If it's not zero, then the day has availability?

Greg

You could also pass in an XML string and parse it into a temp table or table variable.

Greg

You have ([Severity] = @Severity OR [Severity] <> '')

It should be ([Severity] = @Severity OR @Severity = '')

If the value that is passed in is an empty string, it will...

Hmm... This looks an awful lot like homework. Why don't you post what you think the design should look like and maybe someone will critique it?

Greg

I always avoid using dynamic SQL. Your WHERE clause could look like this instead:

WHERE (Username = @Username OR @Username = '')

Basically it will use the username if it's supplied...

I would do something like this:

Here I'm setting variables and adding comma before and after the input strings

DECLARE @State VARCHAR(100), @Region VARCHAR(100)

SELECT @State = '1, 2, 3, 4, 5, 6,...

You shouldn't need to use dynamic SQL at all. If @Region and @State are some sort of comma delimited string, I would parse them into table variables and join...

Try this:

SELECT SUBSTRING(ColumnName, CHARINDEX('\', ColumnName) + 1, 100)

FROM TableName

Greg

It's probably the * in Count(*). Instead, select a column from the table that has the records that you are counting.

Greg

I would use a LEFT OUTER JOIN so that all results are returned regardless if they match or not. And then use ISNULL(COUNT(*), 0) AS 'Count' so that zeros...