i want to show my pivot table column name as Monday 1,Tuesday 2 based on the month from the given date...i.e if it is July the month then Monday 1 Tuesday 2 so on for all days of the month..
this is my coding how to achieve this
set @query='SELECT
P.tenant_id,CI.companyname,BM.brandname,bl.buildingname,T.Unitname,tm.tradecategoryname,l.locationname,T.adsrid,T.carpetarea,T.chargeablearea,
P.[1],P.[2],P.[3],P.[4],P.[5],P.[6],P.[7],P.[8],P.[9],P.[10],P.[11],P.[12],P.[13],P.[14],P.[15],P.[16],P.[17],
P.[18],P.[19],P.[20],P.[21],P.[22],P.[23],P.[24],P.[25],P.[26],P.[27],P.[28],P.[29],P.[30],P.[31]
FROM
(
select tenant_id,Day1=day(RECEIPT_DATE),Net_Amt from Daywiseconsolidate WITH(NOLOCK)
where tenant_id in (' + @filter + ') and month(receipt_date)='+ convert(nvarchar(2),month(@fromdate)) +' and year(receipt_date)='+convert(nvarchar(4),year(@fromdate))+'
) A
PIVOT
(
SUM(NET_AMT)
FOR DAY1 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],
[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS P
inner join tenant T on P.tenant_id=T.adsrid
left outer join locationmaster l on t.locationId=l.locationId
left outer join companyinfo CI on T.companyid=CI.companyid
left outer join brandmaster BM on T.brandid=BM.brandid
left outer join buildingmaster bl on t.buildingid=bl.buildingid
left outer join tradecategorymaster tm on T.tradecategory=tm.tradecategoryid
where bl.active=''Y'' and tm.active=''Y'''
i tried like this for day 1 column as Monday 1 but it didnt woked!!
SELECT
P.tenant_id,CI.companyname,BM.brandname,bl.buildingname,T.Unitname,tm.tradecategoryname,
l.locationname,T.adsrid,T.carpetarea,T.chargeablearea,
**P.[1] as datename(dw,dateadd(dd,-(day(@FromDate)-1),@FromDate))+' '+datename (day,dateadd(dd,-(day(@FromDate)-1),@FromDate))**,
P.[2],P.[3],P.[4],P.[5],
P.[6],P.[7],P.[8],P.[9],P.[10],P.[11],P.[12],P.[13],P.[14],P.[15],P.[16],P.[17],
P.[18],P.[19],P.[20],P.[21],P.[22],P.[23],P.[24],P.[25],P.[26],P.[27],P.[28],P.[29],
P.[30],P.[31]
FROM
(
select tenant_id,Day1=day(RECEIPT_DATE),Net_Amt from Daywiseconsolidate WITH(NOLOCK)
where tenant_id in (select adsrid from tenant where active='Y' ) and
month(receipt_date)=9 and year(receipt_date)=2011
) A
PIVOT
(
SUM(NET_AMT)
FOR DAY1 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],
[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS P
inner join tenant T on P.tenant_id=T.adsrid
left outer join locationmaster l on t.locationId=l.locationId
left outer join companyinfo CI on T.companyid=CI.companyid
left outer join brandmaster BM on T.brandid=BM.brandid
left outer join buildingmaster bl on t.buildingid=bl.buildingid
left outer join tradecategorymaster tm on T.tradecategory=tm.tradecategoryid
where bl.active='Y' and tm.active='Y'
could you please help me to get those desired columns
