คือผมต้องการให้ข้อมูลออกมาเหมือนในรูปแบบ ตามภาพ ครับแต่ติดปัญหาตรงที่ join sub query แต่ error ครับ
Code ในส่วนการ join pivot ครับ
Code (SQL)
DECLARE @cols AS NVARCHAR(MAX),
@PlanBeam AS NVARCHAR(MAX),
@PlanColumn AS NVARCHAR(MAX),
@BeamColumn AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT ',' + QUOTENAME(c.Process_Name)
FROM tb_process c
order by Process_ID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @BeamColumn = 'SELECT DateBeamColumn INTO #Temp FROM
( SELECT PlanDate PDate, Dwg_ID DW, ' + @cols + ' dd FROM
( SELECT PlanDate,Dwg_ID,Process_Name
FROM tb_report_dwg_center rt
join tb_type on tb_type.Type_ID = rt.Type_ID
where rt.Type_ID = 1
group by Dwg_ID,Type_Name,PlanDate)x
pivot
(
Max(PDate)
for Process_Name in (' + @cols + ')
) p
) f1
join
( SELECT PlanDate DDate, Dwg_ID DG, ' + @cols + ' cc FROM
( SELECT PlanDate,Dwg_ID,Process_Name
FROM tb_report_dwg_center tr
join tb_type on tb_type.Type_ID = tr.Type_ID
where tr.Type_ID = 2
group by Dwg_ID,Type_Name,PlanDate)z
pivot
(
Max(DDate)
for Process_Name in (' + @cols + ')
) q
) f2 on f1.dd = f2.cc '
execute(@BeamColumn)
ตอนนี้ไม่ error แล้วครับแต่ว่า data 0 row แทน T-T ข้อมูลไม่ขึ้น
Code (SQL)
set @BeamColumn = 'SELECT pp.DateBeamColumn INTO #Temp FROM #Temptemp pp join
( SELECT DATE PDate , ' + @cols + ' FROM
(SELECT CONVERT(CHAR(10), q.PlanDate, 120) AS DATE, Process_Name, Dwg_ID AS Beam_PLAN
FROM
( SELECT Dwg_ID,
Process_Name,
PlanDate
FROM tb_report_dwg_center rt
join tb_type on tb_type.Type_ID = rt.Type_ID
join tb_process pc on pc.Process_ID = rt.Process_ID
where rt.Type_ID = 1 ) q
group by Dwg_ID,PlanDate,Process_Name ) sel1
pivot
(
Max(Beam_PLAN)
for Process_Name in (' + @cols + ')
) p
) f1 on pp.DateBeamColumn = f1.PDate
join
( SELECT DATE DDate, ' + @cols + ' FROM
(SELECT CONVERT(CHAR(10), z.PlanDate, 120) AS DATE, Process_Name, Dwg_ID AS Column_PLAN
FROM
( SELECT Dwg_ID,
Process_Name,
PlanDate
FROM tb_report_dwg_center tr
join tb_type on tb_type.Type_ID = tr.Type_ID
join tb_process pd on pd.Process_ID = tr.Process_ID
where tr.Type_ID = 2 )z
group by Dwg_ID,PlanDate,Process_Name )sel2
pivot
(
Max(Column_PLAN)
for Process_Name in (' + @cols + ')
) q
) f2 on pp.DateBeamColumn = f2.DDate '
set @BeamColumn = 'SELECT pp.DateBeamColumn INTO #Temp FROM #Temptemp pp join
( SELECT DATE PDate , ' + @cols + ' FROM
(SELECT CONVERT(CHAR(10), q.PlanDate, 120) AS DATE, Process_Name, Dwg_ID AS Beam_PLAN
FROM
( SELECT Dwg_ID,
Process_Name,
PlanDate
FROM tb_report_dwg_center rt
join tb_type on tb_type.Type_ID = rt.Type_ID
join tb_process pc on pc.Process_ID = rt.Process_ID
where rt.Type_ID = 1 ) q
group by Dwg_ID,PlanDate,Process_Name ) sel1
pivot
(
Max(Beam_PLAN)
for Process_Name in (' + @cols + ')
) p
) f1 on pp.DateBeamColumn = f1.PDate
join
( SELECT DATE DDate, ' + @cols + ' FROM
(SELECT CONVERT(CHAR(10), z.PlanDate, 120) AS DATE, Process_Name, Dwg_ID AS Column_PLAN
FROM
( SELECT Dwg_ID,
Process_Name,
PlanDate
FROM tb_report_dwg_center tr
join tb_type on tb_type.Type_ID = tr.Type_ID
join tb_process pd on pd.Process_ID = tr.Process_ID
where tr.Type_ID = 2 )z
group by Dwg_ID,PlanDate,Process_Name )sel2
pivot
(
Max(Column_PLAN)
for Process_Name in (' + @cols + ')
) q
) f2 on pp.DateBeamColumn = f2.DDate '
DECLARE @Big TABLE (
FullName VARCHAR(255),
Date_Conv DATETIME,
Time_Conv VARCHAR(5)
)
DECLARE @ColsPivot AS NVARCHAR(MAX)
DECLARE @Query AS NVARCHAR(MAX)
INSERT INTO @Big VALUES ('Jim','2013-09-01','08:02'),
('Jim','2013-09-01','16:00'),
('Jim','2013-09-01','16:30'),
('Jim','2013-09-02','08:04'),
('Jim','2013-09-02','16:25'),
('Jim','2013-09-03','08:29'),
('Jim','2013-09-03','16:02')
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable;
SELECT @colsPivot = STUFF((
SELECT DISTINCT ',' + QUOTENAME(CONVERT(VARCHAR(8), Date_Conv, 112))
FROM @Big WHERE Date_Conv BETWEEN '2013-09-01' AND '2013-09-02'
FOR XML PATH(''),
TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @Query = 'SELECT * FROM #TempTable
PIVOT(MIN(Time_ConvX) FOR Date_Conv IN (' + @colsPivot + '))
AS Pvt1';
;WITH DeepAss
AS (
SELECT ROW_NUMBER() OVER (
PARTITION BY FullName,
Date_Conv ORDER BY Time_Conv ASC
) AS Sideline,
*, (SELECT CASE WHEN Time_Conv <= '12:00' THEN Time_Conv + '(I)' ELSE 'Ass (O)' END) As Time_ConvX
FROM @Big
)
SELECT * INTO #TempTable FROM DeepAss
EXECUTE (@query)
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
DROP TABLE #TempTable
END;
Public Function DateCompareBaseOnBirthDate(ByVal fromDate As DateTime, ByVal toDate As DateTime) As Object
Dim months As Integer = 0
Dim days As Integer = 0
Dim GoMonth As DateTime 'Tricks
Dim retValYMD = New With {.Years = 0, .Months = 0, .Days = 0}
months = (toDate.Year - fromDate.Year) * 12 + (toDate.Month - fromDate.Month) - If(toDate.Day < fromDate.Day, 1, 0)
GoMonth = fromDate.AddMonths(months)
days = System.Math.Abs(toDate.Day - GoMonth.Day) + 1
If days = DateTime.DaysInMonth(GoMonth.Year, GoMonth.Month) Then
months += 1
days = 0
End If
retValYMD.Years = months \ 12
retValYMD.Months = months Mod 12
retValYMD.Days = days
Return retValYMD
End Function
Public Function DateCompareBaseOnBirthDate(ByVal fromDate As DateTime, ByVal toDate As DateTime) As Object
Dim months As Integer = 0
Dim days As Integer = 0
Dim GoMonth As DateTime 'Tricks
months = (toDate.Year - fromDate.Year) * 12 + (toDate.Month - fromDate.Month) - If(toDate.Day < fromDate.Day, 1, 0)
GoMonth = fromDate.AddMonths(months)
days = System.Math.Abs(toDate.Day - GoMonth.Day) + 1
If days = DateTime.DaysInMonth(GoMonth.Year, GoMonth.Month) Then
months += 1
days = 0
End If
Return New With {.Years = months \ 12, .Months = months Mod 12, .Days = days}
End Function
Load balance : Server 03
Quote:
