declare @i varchar(25)

set @i='2009-NOV'

select datename(month,cast(@i as datetime))

Hi Arun,

Thank you for the clarification. I can see the difference now. Can the result be achieved by using rollup?

I am still learning.. Can you please let me know where i am going wrong. Please see below the results from Arun and mine.. They look same to me.

Arun's Results:

CCIDSnoItemAmount

XX11AA10

XX12BB30

XX13BB40

XX23CC30

XX24DD40

XX25EE50

XX26EE120

XX36FF50

XX37FF50

Mine:

CCIDAmountSNOitem

XX1101AA

XX1302BB

XX1403BB

XX2303CC

XX2404DD

XX2505EE

XX21206EE

XX3506FF

XX3507FF

From the data provided above. This should work as well:

select CCID,SUM(Amount) as Amount,case when Sno IS Null then MAX(Sno)+1 else Sno end SNO,MAX(item) as item

from #temp1

group by CCID,SNO

with Rollup

having...

As mentioned by Gail above:

DECLARE @Input Varchar(40)

SET @Input='Hi I ''ll be there @6''O clock'

Select @Input

It's Brilliant. It works. Thank you very much.

This is what i wanted to ask:

I have a table as below:

CREATE TABLE #MyTempTable2 (qty INT, startdate datetime, enddate datetime);

INSERT INTO #MyTempTable2 VALUES (1,'2009-01-01 00:00:00','2009-01-25 00:00:00');

INSERT INTO #MyTempTable2...

Thank you so much. It works fine.

Just got another question: Will this logic still work if i wanted to do it for each week or each month instead of each...

i tried this:

; WITH

t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

t2 AS (SELECT 1 N FROM t1 x, t1 y), ...

I haven't got a calendar table. Is it possible to do this without creating the tally table.

Thank you Gail for your article. It's a brilliant artice. It is much clearer now how indexes work.:-)

I have tried with 2, 4 ,8 and then 12.. but the number/pct is not going down for cxpacket.

Thank you guys for the replies. I have temporarily fixed it by doing another join to the eventlog table.It takes 4 seconds now. Thank you grant for pointing me to...

Hi Grant

looking at the execution plan whichs tables do you think needs indexes.

Thank you Grant for your response.

I did run sp_updatestats to update the statistics. Do i have to update the statistics again. As far as missing index is concerned how did...