sum ตัวเลขจากการ union จาก 2 บรรทัดให้เหลือ 1 บรรทัด (อธิบายไม่ถูก มีภาพประกอบ)

รหัส,
ISNULL(SUM(round(เดบิต.ยอดปัจจุบัน,2)),0.00)เดบิต.ยอดปัจจุบัน,
ISNULL(SUM(round(เครดิต.ยอดปัจจุบัน,2)),0.00)เครดิต.ยอดปัจจุบัน,
	0 ยอดยกมา

from ......................
where ประเภท=101 
group by รหัส

union 

รหัส,
	0 เดบิต.ยอดปัจจุบัน,
	0 เครดิต.ยอดปัจจุบัน,
	SUM(ISNULL(round(DrAmnt,2),0.00) - ISNULL(round(CrAmnt,2),0.00))ยอดยกมา

from ......................
where ประเภท=105
group by รหัส

DECLARE @ASSINASSOUT TABLE (ID int, PurchaseCost money, PurchaseDate Date, Period int, Savage decimal(6,2))
INSERT INTO @ASSINASSOUT SELECT 1, 25000, '2014-12-29', 5*12, 20.0/100 
;WITH cte
AS (
	SELECT     -- anchor member
		CAST(0 AS int) AS m, PurchaseCost AS PurchaseCost, PurchaseDate As PurchaseDate, 
		Period AS Period, PurchaseDate AS StartdayOfMonth,
		CAST(DATEADD(mm, DATEDIFF(mm, 0, PurchaseDate) + 1, 0) -1 AS DATE) AS LastdayOfMonth,
		var00 AS DayDiff, CAST(((((PurchaseCost-1) * (20*1.0/100))/var02) * var00) AS money) AS DepreAmount,
		PurchaseCost AS BookValue, CAST(0 AS money) AS AccumDepre, CAST(DATEPART(dy, CAST(YEAR(var01) AS CHAR(4)) + '-12-31') AS int) AS DaysOfYear
    FROM @ASSINASSOUT CROSS APPLY (SELECT var00 = CAST(DATEDIFF(Day, PurchaseDate, DATEADD(mm, DATEDIFF(mm, 0, PurchaseDate) + 1, 0) -1) + 1 AS int),
                                     var01 = CAST(DATEADD(mm, DATEDIFF(mm, 0, PurchaseDate) + 1, 0) -1 AS DATE),
                                     var02 = CAST(DATEPART(dy, CAST(YEAR(CAST(DATEADD(mm, 
                                                  DATEDIFF(mm, 0, DATEADD(mm, 0, PurchaseDate)), 0) AS Date)) AS CHAR(4)) + '-12-31') AS int)) a	
    UNION ALL	
	SELECT     -- recursive member
		m + 1, PurchaseCost, PurchaseDate, Period, 
		var11 AS StartdayOfMonth,
		CAST(DATEADD(mm, DATEDIFF(mm, -1, DATEADD(mm, m + 1, PurchaseDate)), 0) - 1 AS Date) AS LastdayOfMonth, var10 AS DaysDiff,
        CASE m + 1 WHEN Period THEN DepreAmount ELSE CAST((PurchaseCost-1) * (20*1.0/100)/var12 * var10 AS money) END AS DepreAmount,
		CASE m + 1 WHEN Period THEN CAST(0 AS money) ELSE BookValue - DepreAmount END AS BookValue,
		CASE m + 1 WHEN Period THEN AccumDepre + BookValue ELSE AccumDepre + DepreAmount END AS AccumDepre,
		DATEPART(dy, CAST(YEAR(var11) AS CHAR(4)) + '-12-31') AS DaysOfYear
	FROM cte CROSS APPLY (SELECT var10 = DATEDIFF(Day,DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, m + 1, PurchaseDate)), 0),
	                                     DATEADD(mm, DATEDIFF(mm, -1, DATEADD(mm, m + 1, PurchaseDate)), 0) - 1 ) + 1,
	                             var11 = CAST(DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, m + 1, PurchaseDate)), 0) AS Date),
	                             var12 = CAST(DATEPART(dy, CAST(YEAR(CAST(DATEADD(mm, 
	                                          DATEDIFF(mm, 0, DATEADD(mm, m + 1, PurchaseDate)), 0) AS Date)) AS CHAR(4)) + '-12-31') AS int)) b 
	WHERE m < Period -- terminator 
	) 
	SELECT * FROM cte

ISNULL(SUM(round(เดบิต.ยอดปัจจุบัน,2)),0.00)เดบิต.ยอดปัจจุบัน,

ISNULL(SUM(round(เดบิต.ยอดปัจจุบัน,2)),0.00) as เดบิต.ยอดปัจจุบัน,

round( SUM( ISNULL(เดบิต.ยอดปัจจุบัน, 0)),2) as เดบิต.ยอดปัจจุบัน,

SELECT
	EM.AccCode,
	EM.AccName,
	ROUND( SUM ( ISNULL(GL.DrAmnt, 0) ), 2) as Dr,
	ROUND( SUM ( ISNULL(GL.CrAmnt, 0) ), 2) as Cr,

FROM EMAcc as EM
LEFT OUTER JOIN GLDT as GL
	ON GL.AccID = EM.AccID
		AND GL.DocuStatus = 'N'
		AND GL.DocuType IN (501)
		AND CONVERT(VARCHAR(8),GL.DocuDate,112) between '20140801' AND '20140801'
		AND GL.BrchID = 1
GROUP BY EM.AccCode, EM.AccName

;WITH cte1
AS 

(
SELECT	EMAcc.AccID,
	EMAcc.AccCode,
	EMAcc.AccName,
	EMAcc.AccNameEng,
	round( SUM( ISNULL(GLDT.DrAmnt, 0)),2) as DrAmntCurr,
	round( SUM( ISNULL(GLDT.CrAmnt, 0)),2) as CrAmntCurr,
	0 Beginn, 'หอยอ้า' AS Sex

FROM EMAcc LEFT OUTER JOIN GLDT ON ((GLDT.AccID = EMAcc.AccID)
		AND (GLDT.DocuStatus = 'N')
		AND (GLDT.DocuType IN (501))
		AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) between '20140801' AND '20140801')
		AND ((GLDT.BrchID = 1) OR (1 = 0)))
	LEFT OUTER JOIN EMJOb ON (GLDT.JobID = EMJOb.JobID)
	LEFT OUTER JOIN EMDept ON (GLDT.DeptID = EMDept.DeptID)
	LEFT OUTER JOIN EMBrch ON (GLDT.BrchID = EMBrch.BrchID)
WHERE   EMAcc.AccControlFlag = '1'
      
AND   ( emacc.acccode between '111216.000' and '111220.000' )  
      
AND ( GLDT.AccID is not null )  
GROUP BY EMAcc.AccID,
	EMAcc.AccCode,
	EMAcc.AccName,
	EMAcc.AccNameEng
UNION	
SELECT	EMAcc.AccID,
	EMAcc.AccCode,
	EMAcc.AccName,
	EMAcc.AccNameEng,
	0 DrAmnt,
	0 CrAmnt,
	SUM(ISNULL(round(DrAmnt,2),0.00) - ISNULL(round(CrAmnt,2),0.00))as Beginn, 'หอยอ้า' AS Sex

FROM EMAcc LEFT OUTER JOIN GLDT ON ((GLDT.AccID = EMAcc.AccID)
		AND (GLDT.DocuStatus = 'N')
		AND ( ((GLDT.DocuType = 503) AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) = '20131231')))
		AND ((GLDT.BrchID = 1) OR (1 = 0)))
	LEFT OUTER JOIN EMDept ON (GLDT.DeptID = EMDept.DeptID)
	LEFT OUTER JOIN EMJob ON (GLDT.JobID= EMJob.JobID)
	LEFT OUTER JOIN EMBrch ON (GLDT.BrchID = EMBrch.BrchID)
WHERE   EMAcc.AccControlFlag = '1'
      
AND (   emacc.acccode between '111216.000' and '111220.000' )  
      
AND ( GLDT.AccID is not null )  
GROUP BY EMAcc.AccID,
	EMACC.AccCode,
	EMAcc.AccName,
	EMAcc.AccNameEng

)
SELECT cte1.AccID, SUM(cte1.DrAmntCurr) AS DrAmntCurr, SUM(cte1.CrAmntCurr) AS CrAmntCurr FROM cte1  
LEFT JOIN cte1 หอยสด ON cte1.Sex = หอยสด.Sex
GROUP BY cte1.Sex, cte1.AccID, cte1.AccCode, cte1.AccName, cte1.AccNameEng

GO
GO
GO 3 WITH ME

ALTER procedure [dbo].[spu_icstockcost_FIFO]
	@StockCostID 	u_id ,
	@docutype 	int , 
	@docutypecode 	varchar(1),
	@docuid 	u_id , 
	@listno 	u_listno , 
	@goodid 	u_id , 
	@brchid 	u_id ,
	@costamnt 	u_amnt output as
begin
	--	เพื่อใช้ในการรับข้อมูลที่ได้จากการทำงานของ Query
	declare @outamnt 	u_amnt 
	--	ดึงข้อมูลจากรายการโดยไม่รวมต้นทุนที่เกิดจาก Trigger ซึ่งคือคัวมันเองหากส่งเป็น 0 แปลว่าเอารายการต้นทุนทั้งเอกสาร
	select 	@outamnt = Convert(decimal(19,4),isnull(Sum
		      (Case icstockcost.StockFlag When 1 
		      /*การรับเข้า*/
		      Then (Case When (isnull(icstockcost.InQty,0) = 0) and (isnull(icstockcost.InAmnt,0) = 0)and
				      (isnull(icstockcost.OutQty,0)<> 0)and (isnull(icstockcost.OutAmnt,0) = 0)
				      Then 0
				 When (isnull(icstockcost.OutQty,0) > 0)and(isnull(icstockcost.OutAmnt,0) > 0)
				      Then Convert(decimal(19,4),isnull(icstockcost.ReceAmnt,0))
				 When (isnull(icstockcost.InQty,0) = 0)and (isnull(icstockcost.InAmnt,0) = 0)and
				      (isnull(icstockcost.OutQty,0) = 0)and (isnull(icstockcost.OutAmnt,0) = 0)		
				      Then 0
			    Else 0 End)
		      /*การจ่ายออก*/
		      Else (Case When (isnull(icstockcost.InQty,0) > 0)and (isnull(icstockcost.InAmnt,0) > 0)and
				      (isnull(icstockcost.OutQty,0) = 0)and (isnull(icstockcost.OutAmnt,0) = 0)	
				      Then (Case  When isnull(icstockcost.Inqty,0) = 0 
							  Then 0 
							  Else (Convert(decimal(19,4),isnull(icstockcost.InAmnt,0))*
								Convert(decimal(19,4),isnull(icstockcost.payqty,0)))/
								Convert(decimal(19,4),isnull(icstockcost.Inqty,0))
					   End )
				 When (isnull(icstockcost.InQty,0) = 0)and (isnull(icstockcost.InAmnt,0) = 0)and
				      (isnull(icstockcost.OutQty,0) = 0)and (isnull(icstockcost.OutAmnt,0) = 0)	
				      Then Convert(decimal(19,4),isnull(icstockcost.payamnt,0))
				When (isnull(icstockcost.InQty,0) = 0)and (isnull(icstockcost.InAmnt,0) = 0)and
				      (isnull(icstockcost.OutQty,0) < 0)and (isnull(icstockcost.OutAmnt,0) = 0)	
				      Then 0
				 Else (Case  When isnull(icstockcost.Inqty,0) = 0 
							  Then 0 
							  Else (Convert(decimal(19,4),isnull(icstockcost.InAmnt,0))*
								Convert(decimal(19,4),isnull(icstockcost.payqty,0)))/
								Convert(decimal(19,4),isnull(icstockcost.Inqty,0))
				             End )
				 End)
		      End),0))
	from	icstockcost
	where 	(icstockcost.GoodID = @goodid) and 
	   	(icstockcost.DocuType = @docutype) and 
	   	(icstockcost.DocuTypeCode = @docutypecode) and 
		(icstockcost.BrchID = @brchid) and 
	   	(icstockcost.DocuID = @docuid) and
	   	(icstockcost.ListNo = @listno) and
	   	(icstockcost.StockCostID <> @StockCostID)
	-- select ค่าข้อมูลเพื่อ Return ค่า่ข้อมูล
	select @costamnt= isnull(@outamnt,0)
end

;WITH cte1
AS 

(
SELECT	EMAcc.AccID,
	EMAcc.AccCode,
	EMAcc.AccName,
	EMAcc.AccNameEng,
	round( SUM( ISNULL(GLDT.DrAmnt, 0)),2) as DrAmntCurr,
	round( SUM( ISNULL(GLDT.CrAmnt, 0)),2) as CrAmntCurr,
	0 Beginn, 'หอยอ้า' AS Sex

FROM EMAcc LEFT OUTER JOIN GLDT ON ((GLDT.AccID = EMAcc.AccID)
		AND (GLDT.DocuStatus = 'N')
		AND (GLDT.DocuType IN (501))
		AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) between '20140801' AND '20140801')
		AND ((GLDT.BrchID = 1) OR (1 = 0)))
	LEFT OUTER JOIN EMJOb ON (GLDT.JobID = EMJOb.JobID)
	LEFT OUTER JOIN EMDept ON (GLDT.DeptID = EMDept.DeptID)
	LEFT OUTER JOIN EMBrch ON (GLDT.BrchID = EMBrch.BrchID)
WHERE   EMAcc.AccControlFlag = '1'
      
AND   ( emacc.acccode between '111216.000' and '111220.000' )  
      
AND ( GLDT.AccID is not null )  
GROUP BY EMAcc.AccID,
	EMAcc.AccCode,
	EMAcc.AccName,
	EMAcc.AccNameEng
UNION	
SELECT	EMAcc.AccID,
	EMAcc.AccCode,
	EMAcc.AccName,
	EMAcc.AccNameEng,
	0 DrAmnt,
	0 CrAmnt,
	SUM(ISNULL(round(DrAmnt,2),0.00) - ISNULL(round(CrAmnt,2),0.00))as Beginn, 'หอยอ้า' AS Sex

FROM EMAcc LEFT OUTER JOIN GLDT ON ((GLDT.AccID = EMAcc.AccID)
		AND (GLDT.DocuStatus = 'N')
		AND ( ((GLDT.DocuType = 503) AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) = '20131231')))
		AND ((GLDT.BrchID = 1) OR (1 = 0)))
	LEFT OUTER JOIN EMDept ON (GLDT.DeptID = EMDept.DeptID)
	LEFT OUTER JOIN EMJob ON (GLDT.JobID= EMJob.JobID)
	LEFT OUTER JOIN EMBrch ON (GLDT.BrchID = EMBrch.BrchID)
WHERE   EMAcc.AccControlFlag = '1'
      
AND (   emacc.acccode between '111216.000' and '111220.000' )  
      
AND ( GLDT.AccID is not null )  
GROUP BY EMAcc.AccID,
	EMACC.AccCode,
	EMAcc.AccName,
	EMAcc.AccNameEng

)
SELECT cte1.AccID, cte1.AccCode, cte1.AccName, cte1.AccNameEng, 
SUM(cte1.DrAmntCurr) AS DrAmntCurr, SUM(cte1.CrAmntCurr) AS CrAmntCurr, cte1.Sex
FROM cte1  
LEFT JOIN cte1 AS หอยสด ON cte1.Sex = หอยสด.Sex
GROUP BY cte1.Sex, cte1.AccID, cte1.AccCode, cte1.AccName, cte1.AccNameEng
ORDER BY cte1.AccCode

SELECT	EMAcc.AccID,
	EMAcc.AccCode,
	GLDT.DocuType,
	EMAcc.AccName,
	EMAcc.AccNameEng,
	ISNULL(SUM(round(GLDT.DrAmnt,2)),0.00)DrAmnt,
	ISNULL(SUM(round(GLDT.CrAmnt,2)),0.00)CrAmnt,
	0 Beginn

FROM EMAcc LEFT OUTER JOIN GLDT ON ((GLDT.AccID = EMAcc.AccID)
		AND (GLDT.DocuStatus = 'N')
		AND (GLDT.DocuType IN (501))
		AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) between '20140801' AND '20140801')
		AND ((GLDT.BrchID = 1) OR (1 = 0)))
	LEFT OUTER JOIN EMJOb ON (GLDT.JobID = EMJOb.JobID)
	LEFT OUTER JOIN EMDept ON (GLDT.DeptID = EMDept.DeptID)
	LEFT OUTER JOIN EMBrch ON (GLDT.BrchID = EMBrch.BrchID)
WHERE   EMAcc.AccControlFlag = '1'
      
AND (  ( emacc.acccode between '111000.010' and '111501.000' )  )  
      
AND ( GLDT.AccID is not null )  
GROUP BY EMAcc.AccID,
	EMAcc.AccCode,
	GLDT.DocuType,
	EMAcc.AccName,
	EMAcc.AccNameEng



UNION

SELECT	EMAcc.AccID,
	EMAcc.AccCode,
	GLDT.DocuType,
	EMAcc.AccName,
	EMAcc.AccNameEng,
	0 DrAmnt,
	0 CrAmnt,
	SUM(ISNULL(round(DrAmnt,2),0.00) - ISNULL(round(CrAmnt,2),0.00))Beginn

FROM EMAcc LEFT OUTER JOIN GLDT ON ((GLDT.AccID = EMAcc.AccID)
		AND (GLDT.DocuStatus = 'N')
		AND (((GLDT.DocuType = 501) AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) BETWEEN '20140801' AND '20140801'))
				OR ((GLDT.DocuType = 503) AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) = '20131231')))
		AND ((GLDT.BrchID = 1) OR (1 = 0)))
	LEFT OUTER JOIN EMDept ON (GLDT.DeptID = EMDept.DeptID)
	LEFT OUTER JOIN EMJob ON (GLDT.JobID= EMJob.JobID)
	LEFT OUTER JOIN EMBrch ON (GLDT.BrchID = EMBrch.BrchID)
WHERE   EMAcc.AccControlFlag = '1'
      
AND (  ( emacc.acccode between '111000.010' and '111501.000' )  )  
      
AND ( GLDT.AccID is not null )  
GROUP BY EMAcc.AccID,
	EMACC.AccCode,
	GLDT.DocuType,
	EMAcc.AccName,
	EMAcc.AccNameEng
order by EMAcc.AccCode

select LEFT(AccCode,6) as AccCode,sum(DrAmntCurr) as DrAmntCurr ,sum(CrAmntCurr) as CrAmntCurr,sum(Beginn)as Beginn
from (

	select LEFT(AccCode,6) as AccCode,sum(DrAmntCurr) as DrAmntCurr ,sum(CrAmntCurr) as CrAmntCurr,sum(Beginn)as Beginn
	from (
	SELECT 
		EMAcc.AccCode,
		round( SUM( ISNULL(GLDT.DrAmnt, 0)),2) as DrAmntCurr,
		round( SUM( ISNULL(GLDT.CrAmnt, 0)),2) as CrAmntCurr,
		0 Beginn

	FROM EMAcc LEFT OUTER JOIN GLDT ON ((GLDT.AccID = EMAcc.AccID)
			AND (GLDT.DocuStatus = 'N')
			AND (GLDT.DocuType IN (501))
			AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) between '20140801' AND '20140801')
			AND ((GLDT.BrchID = 1) ))
		LEFT OUTER JOIN EMBrch ON (GLDT.BrchID = EMBrch.BrchID)
	WHERE   EMAcc.AccControlFlag = '1'
	      
	AND (  ( emacc.acccode between '111216.000' and '111220.000' )  )  
	      
	AND ( GLDT.AccID is not null )  
	GROUP BY 
		EMAcc.AccCode

	UNION 

	SELECT
		EMAcc.AccCode,
		0 DrAmnt,
		0 CrAmnt,
		SUM(ISNULL(round(DrAmnt,2),0.00) - ISNULL(round(CrAmnt,2),0.00))as Beginn

	FROM EMAcc LEFT OUTER JOIN GLDT ON ((GLDT.AccID = EMAcc.AccID)
			AND (GLDT.DocuStatus = 'N')
			AND ( ((GLDT.DocuType = 503) AND (CONVERT(VARCHAR(8),GLDT.DocuDate,112) = '20131231')))
			AND ((GLDT.BrchID = 1) OR (1 = 0)))
		LEFT OUTER JOIN EMBrch ON (GLDT.BrchID = EMBrch.BrchID)
	WHERE   EMAcc.AccControlFlag = '1'
	      
	AND (  ( emacc.acccode between '11100.010' and '111501.000' )  ) 
	      
	AND ( GLDT.AccID is not null )  
	GROUP BY 
		EMACC.AccCode

	) as x
	group by AccCode
 ) as x
where (DrAmntCurr <> '0.00' or CrAmntCurr <> '0.00' or Beginn <> '0.00')
/*where Beginn <> '0.00'*/
group by AccCode


order by LEFT(AccCode,6)

CREATE TABLE [dbo].[tablexx](
	[type] [varchar](10) NULL,
	[id] [int] NULL,
	[list] [varchar](10) NULL,
	[bfdb] [int] NULL,
	[bfcr] [int] NULL,
	[baldb] [int] NULL,
	[balcr] [int] NULL,
	[accdb] [int] NULL,
	[acccr] [int] NULL
) ON [PRIMARY]
GO


insert into [dbo].[tablexx]
values (101,111000,'aaa',0,0,1000,500,500,0),
	   (105,111000,'aaa',5000,0,0,0,5000,0),
	   (101,111200,'bbbb',0,0,500,0,500,0),
	   (105,111200,'bbbb',10000,0,0,0,1000,0),
	   (101,222100,'ccc',0,0,800,500,0,300),
	   (105,222100,'ccc',0,1000,0,0,0,1000)


select * from tablexx

select typea,
		 id, list,
sum(bfdb),sum(bfcr),sum(baldb),sum(balcr),sum(accdb),sum(acccr)
from (
	select LAST_VALUE(a.type) over ( partition by id order by id )  as typeA,
		 id, list,
	bfdb,bfcr,baldb,balcr,accdb,acccr
	from tablexx a
	) aa
group by typea,id,list
order by 2,3