รหัส,
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
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
ถ้าเห็นตารางดิบ ด้วยจะทำให้เข้าใจมากกว่านี้
ประวัติการแก้ไข 2015-02-20 10:26:56
Date :
2015-02-20 10:25:45
By :
Chaidhanan
No. 13
Guest
Code (SQL)
;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
Date :
2015-02-20 12:56:15
By :
หน้าฮี
No. 14
Guest
SQL Query Joke
Code (SQL)
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
Date :
2015-02-20 13:04:36
By :
หน้าฮี
No. 15
Guest
จาก #NO13 เดี๋ยวคุณงง Order By หายไปไหนหว่า
Code (SQL)
;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 2 ชั้น เพราะ ทำการ sum 2 รอบค่ะ
Code (PHP)
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)
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
Date :
2019-11-22 15:08:44
By :
WWWWIN
No. 26
Guest
ตามนนี้จ้า 555
Code (SQL)
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
Load balance : Server 00
