Posts by cgyrob

Thanks for the response.

The cost on using functions on a where clause come when using the function on the column because it causes a table scan even on an indexed field. When the function is used on the opposite side of the column it has no effect on the index seek and limited to no query cost.

Why are you creating constants when you can just use the date functions in the where clause?

SELECT sum(Salesamount)
   FROM Table
   WHERE salesdate BETWEEN   DATEADD(yy,DATEDIFF(yy,0,getdate()),0) --'First Day of Year'
   AND DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))) --'Last Day of Year'

There are alot of ways to do this here are a couple examples.

Using Year() function

Select sum(Salesamount)
From Table
where Year(salesdate) = Year(@userInput)

using Date functions

Select sum(Salesamount)
From Table
where salesdate between DATEADD(yy,DATEDIFF(yy,0,@userInput),0) --'First Day of Year' 
and DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@userInput)+1,0))) --'Last Day of Year'

You don't directly use triggers in a C# app. If you have a trigger on update for the Student table it will be triggered when you send an update statement from your code. If you want the db to do jobs before an update put this code in a stored procedure as this is not what triggers are designed to do.

You can't use the alias in the boolean test plus you should use a having clause to test the count(*) and not the where clause.

Select title, count(*) As Cnt 
From poss_titles 
Group By title
Having count(*) > 1 
Order By count(*) desc

as for only showing fields that don't exist in another table you can use exists condition.

Select title, count(*) As Cnt 
From poss_titles pt
Where not exists (select title from other_table ot where ot.title = pt.title)
Group By title
Having count(*) > 1 
Order By count(*) desc

Did you try using wild cards.

Select * from Table1
Where Name LIKE ('%' + @Name + '%')

Try this

SELECT MAKE.MAKE, MAKE.count, color.Color, color.count 
FROM(
       SELECT MAKE, count(*)count
       FROM cars 
       Group by MAKE
    )MAKE,
   (   SELECT MAKE, color, count(*)count
       From cars
       Group by MAKE,color
   )color
Where make.make = color.make

I don't see where you are getting the alias 'c'

Besides that i think you are making it more difficult then it has to be.

select *
from dbo.tblLevelOneApprover a, dbo.tblLevelTwoApproverToLevelOneApprover b
Where convert(int, b.level_two_emplid) <> convert(int, a.emplid)

When I did my final project instead of trying to think of an aimless program that hundreds of other students have already done and will just be scrapped the second it was graded.

I found a small charitable organization in my city and offered my services. Small organizations always need something but do not have the resources or know who to ask to get them done.

This will not only give you a project with more real world experience but you can benefit your community in a small way.

Select Product From Table
Where Component in ('part1','part3')

What you need to do is build the query for only the selected options within the code block. If you build a static select query with all the options then it will return nothing because no user will select all the options.

In the code you will have to pull all the options the user selected and build the query to include only those options. Also don't use the left join from the original query as that will give null results from the Users_AOI table which you don't want.