Cross tab query in sql-server sample

 

declare

@drdate as varchar(15)

declare

@drweek as varchar(15)

declare

@fdate1 as varchar(15)

declare

@fdate2 as varchar(15)

declare

@fdate3 as varchar(15)

declare

@fdate4 as varchar(15)

declare

@fdate5 as varchar(15)

declare

@fdate6 as varchar(15)

declare

@fdate7 as varchar(15)

declare

@fdate8 as varchar(15)

declare

@fdate9 as varchar(15)

declare

@SQL varchar (max)

set

@drdate='1/3/2010'

select

@drweek=drweek from dbo.DateRef where drdate=@drdate

set

@fdate1=convert(varchar(10), cast(@drweek as datetime),101)

set

@fdate2=convert(varchar(10),dateadd(day,1,@fdate1),101)

set

@fdate3=convert(varchar(10),dateadd(day,1,@fdate2),101)

set

@fdate4=convert(varchar(10),dateadd(day,1,@fdate3),101)

set

@fdate5=convert(varchar(10),dateadd(day,1,@fdate4),101)

set

@fdate6=convert(varchar(10),dateadd(day,1,@fdate5),101)

set

@fdate7=convert(varchar(10),dateadd(day,1,@fdate6),101)

set

@fdate8=convert(varchar(10),dateadd(day,1,@fdate7),101)

set

@fdate9=convert(varchar(10),dateadd(day,1,@fdate8),101)

print

@fdate9

print

@fdate1

select

distinct m.FICLBNO,m.FIUPC,m.prscitemno,m.prsamsitemno,m.OwnerName,m.HubName,

isnull

(m1.FIQTY,0) Q1 ,isnull(m1.FICOST,0) C1,isnull(m1.FISALES,0) T1,

isnull

(m2.FIQTY,0) Q2,isnull(m2.FICOST,0) C2,isnull(m2.FISALES,0)T2,

isnull

(m3.FIQTY,0) Q3,isnull(m3.FICOST,0) C3,isnull(m3.FISALES,0)T3,

isnull

(m4.FIQTY,0) Q4,isnull(m4.FICOST,0) C4,isnull(m4.FISALES,0)T4,

isnull

(m5.FIQTY,0) Q5,isnull(m5.FICOST,0) C5,isnull(m5.FISALES,0)T5,

isnull

(m6.FIQTY,0) Q6,isnull(m6.FICOST,0) C6,isnull(m6.FISALES,0)T6,

isnull

(m7.FIQTY,0) Q7,isnull(m7.FICOST,0) C7,isnull(m7.FISALES,0)T7,

isnull

(m8.FIQTY,0) Q8,isnull(m8.FICOST,0) C8,isnull(m8.FISALES,0)T8,

isnull

(m9.FIQTY,0) Q9,isnull(m9.FICOST,0) C9,isnull(m9.FISALES,0)T9

from

smrsalesrep_m m

left

outer join smrsalesrep m1 on m.FICLBNO=m1.FICLBNO and m.FIUPC=m1.FIUPC and m.prscitemno=m1.prscitemno

and

m.prsamsitemno=m1.prsamsitemno and m.OwnerName=m1.OwnerName and m.HubName=m1.HubName and m1.FISALDAT=@fdate1

left

outer join smrsalesrep m2 on m.FICLBNO=m2.FICLBNO and m.FIUPC=m2.FIUPC and m.prscitemno=m2.prscitemno

and

m.prsamsitemno=m2.prsamsitemno and m.OwnerName=m2.OwnerName and m.HubName=m2.HubName and m2.FISALDAT=@fdate2

left

outer join smrsalesrep m3 on m.FICLBNO=m3.FICLBNO and m.FIUPC=m3.FIUPC and m.prscitemno=m3.prscitemno

and

m.prsamsitemno=m3.prsamsitemno and m.OwnerName=m3.OwnerName and m.HubName=m3.HubName and m3.FISALDAT=@fdate3

left

outer join smrsalesrep m4 on m.FICLBNO=m4.FICLBNO and m.FIUPC=m4.FIUPC and m.prscitemno=m4.prscitemno

and

m.prsamsitemno=m4.prsamsitemno and m.OwnerName=m4.OwnerName and m.HubName=m4.HubName and m4.FISALDAT=@fdate4

left

outer join smrsalesrep m5 on m.FICLBNO=m5.FICLBNO and m.FIUPC=m5.FIUPC and m.prscitemno=m5.prscitemno

and

m.prsamsitemno=m5.prsamsitemno and m.OwnerName=m5.OwnerName and m.HubName=m5.HubName and m5.FISALDAT=@fdate5

left

outer join smrsalesrep m6 on m.FICLBNO=m6.FICLBNO and m.FIUPC=m6.FIUPC and m.prscitemno=m6.prscitemno

and

m.prsamsitemno=m6.prsamsitemno and m.OwnerName=m6.OwnerName and m.HubName=m6.HubName and m6.FISALDAT=@fdate6

left

outer join smrsalesrep m7 on m.FICLBNO=m7.FICLBNO and m.FIUPC=m7.FIUPC and m.prscitemno=m7.prscitemno

and

m.prsamsitemno=m7.prsamsitemno and m.OwnerName=m7.OwnerName and m.HubName=m7.HubName and m7.FISALDAT=@fdate7

left

outer join smrsalesrep m8 on m.FICLBNO=m8.FICLBNO and m.FIUPC=m8.FIUPC and m.prscitemno=m8.prscitemno

and

m.prsamsitemno=m8.prsamsitemno and m.OwnerName=m8.OwnerName and m.HubName=m8.HubName and m8.FISALDAT=@fdate8

left

outer join smrsalesrep m9 on m.FICLBNO=m9.FICLBNO and m.FIUPC=m9.FIUPC and m.prscitemno=m9.prscitemno

and

m.prsamsitemno=m9.prsamsitemno and m.OwnerName=m9.OwnerName and m.HubName=m9.HubName and m9.FISALDAT=@fdate9