Hello, I want to add another extra column after Pivot, my current result is like this:
this is my current query:
DEClARE @startdate date
DECLARE @enddate date
SET @startdate = '2019-03-20'
SET @enddate = '2019-03-31'
CREATE table #tempdata(
Name Varchar(100),
date date,
MeterSN Varchar(100),
Reading float,
RecordedBy Varchar(100),
Remark Varchar(100)
)
INSERt INTO #tempdata SELECT c.Name, a.date, b.MeterSN, a.Reading, a.RecordedBy, a.Remark FROM INF_Facility_ElectricalRecord a
INNER JOIN INF_Facility_ElectricalMeter b ON b.id = a.currentMeterSNID
INNER JOIN INF_Facility_Location c ON c.id = b.LocationID
WHERE a.date BETWEEN @startdate AND @enddate
ORDER BY c.Name, a.date, b.MeterSN
DECLARE @col
AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Name)
from #tempdata
group by Name
order by Name
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ROW_NUMBER() OVER( ORDER BY date) AS No, CONVERT(VARCHAR(10),date,120) AS Date,' + @cols + ', recordedby AS [Recorded By] , remark AS Remark from
(
select name,date, Reading, remark, RecordedBy
from #tempdata
) x
pivot
(
MAX(Reading)
for Name in (' + @cols + ')
) p ORDER by date '
execute(@query);
drop table #tempdata
i want to add another column after Area Column, the result should be like this
Usage records are come from Current Date Reading - Current Date -1 Reading
Here i prepared a consumable table:
CREATE TABLE #INF_Facility_ElectricalRecord (
id int primary key identity(1,1),
date date,
CurrentMeterSNID int,
Reading float,
recordedby varchar(100),
remark varchar(100)
)
Create TABLE #INF_Facility_ElectricalMeter
(
id int primary key identity(1,1),
MeterSN varchar(100),
LocationId int,
date date
)
CREATE TABLE #INF_Facility_Location
(
id int primary key identity(1,1),
Name varchar(100),
Description Varchar(100)
)
INSERT INTO #INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-20',1,1234,'',''
)
INSERT INTO #INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-21',2,2345,'',''
)
INSERT INTO #INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-22',3,3456,'',''
)
INSERT INTO #INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-23',4,4567,'',''
)
INSERT INTO #INF_Facility_ElectricalRecord(date, CurrentMeterSNID, Reading,recordedby,remark) VALUES(
'2019-03-24',5,5678,'',''
)
INSERT INTO #INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T1001', 1, GETDATE()
)
INSERT INTO #INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T2001', 2, GETDATE()
)
INSERT INTO #INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T3001', 3, GETDATE()
)
INSERT INTO #INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T4001', 4, GETDATE()
)
INSERT INTO #INF_Facility_ElectricalMeter(MeterSN, LocationId,date) VALUES
(
'T5001', 5, GETDATE()
)
INSERT INTO #INF_Facility_Location(Name, Description) VALUES(
'T1',''
)
INSERT INTO #INF_Facility_Location(Name, Description) VALUES(
'T2',''
)
INSERT INTO #INF_Facility_Location(Name, Description) VALUES(
'T3',''
)
INSERT INTO #INF_Facility_Location(Name, Description) VALUES(
'T4',''
)
INSERT INTO #INF_Facility_Location(Name, Description) VALUES(
'T5',''
)
Please help.
