thava (8/15/2013)

SELECT c.CLIName1, c.CLICode, g.AGPName1 AS [GL Account Group], a.COAName1 AS [GL Account], d.DIVName1,

a.COACode2Code AS [GLChart Code 2], a.COACode2Name1 AS [GLChart Code...

I think my handling of negative numbers is correct....

You could use something like this:

GROUP BY datediff(day, StartTime, getdate())/7

OK, So, the corrected code with conversion to money and divide by 100 looks like this:

alter function fixop2(@a varchar(20)) returns table

as

return...

If you want maximum performance you could use something like this:

create function fixop2(@a varchar(20)) returns table

as

return select

case

when right(@a,1) between 'A' and 'I' then +cast(left(@a, len(@a)-1) as int)*10 + (ascii(right(@a,1)) -...

Create Table #Temp

(

Lnno Varchar(15),

Custcode varchar(10),

Flag varchar(10),

Amount Int,

Amount_Flag varchar(1)

)

Insert Into #Temp

Values ('1','A1','Cust',1000,''),

('1','A1','Cust',1000,'')

Select * from #Temp

;with cte as (

Select row_number() over (partition by Lnno order by amount desc, flag desc, custcode) as...

avdhut.k (8/12/2013)

I mean Now rest of the things are correct in my requirement,

Now, In case where for two records, if flag...

Easy enough to change the WHERE on the date to an OR with the two specific date ranges.

If the @StartDate is the start of the current period -- which seemed...

OK, I suppose this could be a...

Nice!

My solution looks like this:

;with cte1 as (

-- get all interesting months

select dateadd(month, N, '20100101') FirstDay

from tsqlc_Tally

where N < 120

)

select RIGHT(CONVERT(char(10), FirstDay, 103), 7), SumAmount

from cte1

cross apply (

select sum(Amount)...

('3','C1','Cust',0,''),

('3','C2','CoAp',1000,''),

('3','C3','CoAp',1000,''),

('3','C4','CoAp',5000,'')

I has to Update Amount_Flag as Y,to maximum Amount for that Lnno.

3)In Case Lnno = 3,Cust has amount 0,but two CoAp has Amount same that is...

Would this work for you:

;with cte as (

Select row_number() over (partition by Lnno order by amount desc, flag desc, custcode) as rn,*

from #Temp

)

update cte

set Amount_Flag = 'Y'

where rn=1

This should work.

Note that the code is untested since you did not provide actual table definitions and test data, but the principle should work.

;with

thisYear as (

-- Get summarized values...

Solutions I tried:

1) Add a computed column with definition: (case when EndDateTime is not null then EndDateTime Else StartDateTime End) and have an index on this. This works perfect but...

A little challenge:

Assume there is a clustered index on the date column.

Can you create a query that exploits the clustered index to achieve even better performance?