สหายของข้า "ตัวของข้าเกิดมาเป็นลิง" ผสมพันธ์กันบนที่สูง

USE MyFriends_DB
GO

DECLARE @PeriodNo1 VARCHAR(100)= '2560/02'; --- งวดตั้งต้น
DECLARE @PeriodNo2 VARCHAR(100) = '2560/01'; --- งวดก่อนหน้า
DECLARE @DKACode VARCHAR(100) = '01';---
DECLARE @UnitCode VARCHAR(100) = ''; ---
DECLARE @DKATypeCode VARCHAR(100) = '1';

WITH vw1 --งวดปัจจุบัน
     AS
        (
     SELECT IDCardNo, FullName, LevelCode, StepCode, Amount, DeductAmount, 
            InComeCODE, TotalAmount, TaxAmount, KoboKhoAmount, IncKoboKhoAmount
     FROM ModDKAUnitDetail
     WHERE PeriodNo = @PeriodNo1 --- งวดปัจจุบัน
           AND DKACode = @DKACode
           AND UnitCode = @UnitCode
           AND DKATypeCode = @DkaTypeCode
     EXCEPT
     SELECT IDCardNo, FullName, LevelCode, StepCode, Amount, DeductAmount, 
            InComeCODE, TotalAmount, TaxAmount, KoboKhoAmount, IncKoboKhoAmount
     FROM UnitDetail
     WHERE PeriodNo = @PeriodNo2 --- งวดก่อนหน้านั้น
           AND DKACode = @DKACode
           AND UnitCode = @UnitCode
           AND DKATypeCode = @DkaTypeCode
     INTERSECT
     SELECT IDCardNo, FullName, LevelCode, StepCode, Amount, DeductAmount, 
            InComeCODE, TotalAmount, TaxAmount, KoboKhoAmount, IncKoboKhoAmount
     FROM UnitDetail
     WHERE PeriodNo = @PeriodNo2 --- งวดก่อนหน้านั้น
           AND DKACode = @DKACode
           AND UnitCode = @UnitCode
           AND DKATypeCode = @DkaTypeCode
        ),
     vw2 -- งวดก่อนหน้านั้น
     AS
        (
     SELECT IDCardNo, FullName, LevelCode, StepCode, Amount, DeductAmount, 
            InComeCODE, TotalAmount, TaxAmount, KoboKhoAmount, IncKoboKhoAmount
     FROM UnitDetail
     WHERE PeriodNo = @PeriodNo2 --- งวดก่อนหน้านั้น
           AND DKACode = @DKACode
           AND UnitCode = @UnitCode
           AND DKATypeCode = @DkaTypeCode
     EXCEPT
     SELECT IDCardNo, FullName, LevelCode, StepCode, Amount, DeductAmount, 
            InComeCODE, TotalAmount, TaxAmount, KoboKhoAmount, IncKoboKhoAmount
     FROM UnitDetail
     WHERE PeriodNo = @PeriodNo1 --- งวดปัจจุบัน
           AND DKACode = @DKACode
           AND UnitCode = @UnitCode
           AND DKATypeCode = @DkaTypeCode
     INTERSECT
     SELECT IDCardNo, FullName, LevelCode, StepCode, Amount, DeductAmount, 
            InComeCODE, TotalAmount, TaxAmount, KoboKhoAmount, IncKoboKhoAmount
     FROM UnitDetail
     WHERE PeriodNo = @PeriodNo1 --- งวดปัจจุบัน
           AND DKACode = @DKACode
           AND UnitCode = @UnitCode
           AND DKATypeCode = @DkaTypeCode
        )    
     --SELECT vw1.*, vw2.* FROM vw1 FULL JOIN vw2 ON vw1.IDCARDNO=vw2.IDCARDNO AND vw1.InComeCODE = vw2.InComeCODE
     --ORDER BY vw1.IDCARDNO ASC            
     SELECT vw1.*, vw2.*, XXX.REMARK, XXX.FirstName FROM vw1 FULL JOIN vw2 ON vw1.IDCARDNO=vw2.IDCARDNO AND vw1.InComeCODE = vw2.InComeCODE
     LEFT JOIN XXX ON XXX.IDCardNo = COALESCE(vw1.IDCARDNO, vw2.IDCARDNO)
     ORDER BY vw1.LEVELCODE DESC, vw1.STEPCODE DESC, XXX.FirstName ASC