Sometimes you need to do a report with totals column and with totals that depend on some condition, like
Product, Total Sales this year, total sales this month.
Use SUM(Case…) condition, like here:

SELECT  SUM(CASE WHEN order_date >= '01/01/09' THEN quantity ELSE 0 END) AS items_sold_since_date,
        SUM(quantity) AS items_sold_total,
        product_ID
FROM    Sales
GROUP BY product_ID

Credit goes to Joel Coehoorn

Another way to do Sum:

  COUNT(distinct case when Status.Complete = 1 then Status.ID else NULL end) as [Count Of Complete Status],

Often we need to create temp tables, and drop them every time we re-run the script.

-- if table exists, drop it
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
    drop table #temp 
END

create table #temp (id int, name nvarchar(50))

You can use temp table in the same manner as you use SQL Server cursor

create table #temp (
    value1 int,
    value2 int
)

Declare 
    @SomeValue1 int,
    @SomeValue2 int

insert into #temp (
    value1, 
    value2)
select 
    field1, 
    field2
from realTable
--where some condition

select top 1 
    @SomeValue1 = value1, 
    @SomeValue2 = value2
from #temp

-- while we have rows in the table, do work with them.
while @SomeValue1 is not null 
Begin

    -- Do something with value1 and value2


    -- delete the row we have just worked with
    delete from #temp where value1=@SomeValue1 and value2=@SomeValue2

    -- don't forget this bit!! otherwise you'd be stuck in the infinite loop, 
    -- as variable is not reset to null on empty table
    set @SomeValue1 = null  

    -- get the next row  in the table
    select top 1 @SomeValue1=value1, @SomeValue2 = value2
    from #temp

End

-- here we are done, can drop the temp table
drop table #temp

Probably you would benefit from adding @ID uniqueidentifier to temp table, in case you have repeating data, and delete rows from temp table based by the ID value