OK, this post contains the following query:

SELECTCOUNT(ResCalc.CONVNO) AS RecordCount,

CASE

WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'

WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'

WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'

WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry...

sturner (2/16/2012)

---

CTE or not, a syntax error is a syntax error. I'm happy the OP resolved his issue but I like...

try this:

--step 1. calc datediff first!

select id, DATEDIFF(day, registered, getdate()) dd

into #aWithDD

-- you may want to try indexing it

--create index ix_#aWithDD on #aWithDD(dd)

-- also try without CTE:

...

Using CTE would still produce neattier looking code...

Classic case for recursive CTE.

Do you want complete answer, or prefer to try googling it out and writing code yourself?

just a hint:

http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/

What query have you tried?

Have you copied CASE statement together with AS [ColumnName]?

If so remove "AS [ColumnName]" from GROUP BY

OOO! I can see you've edited it.

Just add your WHERE clause where CTE is joined to the #a:

;with allusers

as

(

SELECT userid FROM #b

UNION

SELECT userid FROM #c

UNION

SELECT userid...

1. You don't need to SELECT DISTINCT when using UNION, as UNION will dedupe results itself

2. Your "WHERE DAY(registered)<1000" clause is useless, as there is no such date for which...

I like it and have another one:

Exit from unexitable situation is usually located at the same place as...

Why not to follow the forum etiquette when asking this sort of question? Follow the link at the bottom of my signature and I can guarantee you that you will...

And to answer the post header question of "When we should use in a query or a procesure "option(recompile)""...

You should use option(recompile) when you want to ensure that SQLServer...

Have no idea why you did use transaction in your query...

Also, UNION makes distinct output already, so you don't need to SELECT DISTINCT...

The following should execute faster, as it will...

CREATE PROC p_GenYearDays(@year int)

AS

BEGIN

;with d366

as

(

select top 366 CAST(CAST(@year as varchar(4)) + '0101'...

I would suggest ETL-like approach. The following is shown as an example:

-- table setup:

-- tS is a staging table where you load raw data

-- tA is a distination...

Phil Parkin (2/15/2012)

---

Yes - nothing special is required - on an instance of SQL Server, you can access functions in a database from any other database (subject to access rights),...