see this topic

maybe Paul White post help you

;with t as(

select

H

, D

, (row_number() over (partition by H order by D) -1)/3 [grouping]

, (row_number() over (partition by H order by D) % 3) [clmn]

from

#A

)

select

H, [1], [2], [0]

from...

what trouble?

;with cte as (

select

...

select

H

, max(case when D %3 = 1 then D else 0 end) D1

, max(case when D %3 = 2 then D else 0 end) D2

, max(case when D %3 =...

maybe, it's help you

;with cte as (

select

coalesce(Postal_Address_Line_7, Postal_Address_Line_6, Postal_Address_Line_5, Postal_Address_Line_4,Postal_Address_Line_3, Postal_Address_Line_2, Postal_Address_Line_1) as Postal_Address_Line

, Postal_Address_Postcode

from #address_data

...

it's my solution

first step - we try to update record

secod step - if no record have been updated then we insert record

update Table

set field1 = @field

where field2 = @key

if @@rowcount=0

begin

...

SELECT TOP (1) WITH TIES add to code of Paul White

Because can be more than one record

My reply will help you too

Dave Ballantyne

It's a good article

but we need one more table "Calendar"

and i don't know if it is possible for Sridhar-137443

CREATE TABLE dbo.mytable

( low int, high int, myavg AS (low / high)*100 ) ;

?

The continuous period when the end of one coincides with the beginning of another

first period

'20060905','20070827'

'20070828', '20080304'

'20080305', '20080420'

'20080421', '20080423'

second

'20080425', '20080429'

'20080430', '20080430'

third

'20080424', '20090920'

fourth

'20100101', '20991231'

create table #Example(

Company varchar(10),

Empno int,

Id int,

CourseId int,

Startdate datetime,

Stopdate datetime

)

insert into #Example(Company, Empno, Id, CourseId, Startdate, Stopdate)

select 'TTT', 1, 01, 11, '20060905','20070827'

union all

select 'TTT', 1, 01, 11, '20070828', '20080304'

union all

select 'TTT',...