Viewing 15 posts - 2,161 through 2,175 (of 2,894 total)
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...
February 16, 2012 at 9:02 am
sturner (2/16/2012)
Eugene Elutin (2/16/2012)
Using CTE would still produce neattier looking code...
CTE or not, a syntax error is a syntax error. I'm happy the OP resolved his issue but I like...
February 16, 2012 at 8:51 am
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:
...
February 16, 2012 at 8:40 am
Using CTE would still produce neattier looking code...
February 16, 2012 at 8:29 am
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/
February 16, 2012 at 8:26 am
What query have you tried?
Have you copied CASE statement together with AS [ColumnName]?
If so remove "AS [ColumnName]" from GROUP BY
February 16, 2012 at 8:02 am
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...
February 16, 2012 at 7:51 am
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...
February 16, 2012 at 7:48 am
sturner (2/16/2012)
...The probability of survival is inversely proportional to the angle of arrival
I like it and have another one:
Exit from unexitable situation is usually located at the same place as...
February 16, 2012 at 7:35 am
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...
February 16, 2012 at 7:05 am
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...
February 16, 2012 at 7:00 am
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...
February 16, 2012 at 6:36 am
CREATE PROC p_GenYearDays(@year int)
AS
BEGIN
;with d366
as
(
select top 366 CAST(CAST(@year as varchar(4)) + '0101'...
February 16, 2012 at 5:52 am
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...
February 16, 2012 at 4:29 am
Phil Parkin (2/15/2012)
February 16, 2012 at 2:59 am
Viewing 15 posts - 2,161 through 2,175 (of 2,894 total)