SQL for Data science : SUM Total Value and Join with Period Value
มีเพื่อนๆในเพจได้ถามเข้ามาโดยเข้ามีโจทย์อยู่ว่า : ต้องการออกรายงานที่แสดงยอดรวมของ Amount ตามช่วงเวลาที่ต้องการ และแสดงผลรวมของ Amount ทั้งหมดก่อนถึงช่วงเวลาที่ต้องให้อยู่ในรายงานหน้าเดียวกันจะีวิธีการเขียนยังไงได้บ้าง
เขียนคำสั่งSQL ออกมาเป็น 2 ชุดคำสั่งก่อนนำมา left join เพื่อให้ได้ Output ที่ต้องการ
ตัวอย่าง SQL Script เพื่อนๆสามารถนำไปลองรันศึกษาเพิ่มเติมได้เองครับ
–Prepare Data
DROP TABLE #tb_temp
SELECT * into #tb_temp
FROM (
select ‘2011-10-09′ as StartDate ,’A1’ as AccountCode ,300 as [Credit],100 as [Debit] union all
select ‘2011-10-09′ as StartDate ,’B1’ as AccountCode ,310 as [Credit],110 as [Debit] union all
select ‘2011-10-09′ as StartDate ,’C1’ as AccountCode ,30 as [Credit],10 as [Debit] union all
select ‘2011-10-10′ as StartDate ,’A1’ as AccountCode ,300 as [Credit],100 as [Debit] union all
select ‘2011-10-10′ as StartDate ,’B1’ as AccountCode ,310 as [Credit],110 as [Debit] union all
select ‘2011-10-10′ as StartDate ,’C1’ as AccountCode ,30 as [Credit],10 as [Debit] union all
select ‘2011-10-11′ as StartDate ,’A1’ as AccountCode ,320 as [Credit],120 as [Debit] union all
select ‘2011-10-11′ as StartDate ,’D1’ as AccountCode ,340 as [Credit],140 as [Debit] union all
select ‘2011-10-11′ as StartDate ,’E1’ as AccountCode ,380 as [Credit],180 as [Debit] union all
select ‘2011-10-12′ as StartDate ,’B1’ as AccountCode ,390 as [Credit],90 as [Debit] union all
select ‘2011-10-12′ as StartDate ,’F1’ as AccountCode ,390 as [Credit],190 as [Debit] union all
select ‘2011-10-12′ as StartDate ,’G1’ as AccountCode ,320 as [Credit],120 as [Debit] union all
select ‘2012-10-09′ as StartDate ,’A1’ as AccountCode ,200 as [Credit],100 as [Debit] union all
select ‘2012-10-09′ as StartDate ,’B1’ as AccountCode ,210 as [Credit],110 as [Debit] union all
select ‘2012-10-09′ as StartDate ,’C1’ as AccountCode ,20 as [Credit],10 as [Debit] union all
select ‘2012-10-10′ as StartDate ,’A1’ as AccountCode ,200 as [Credit],100 as [Debit] union all
select ‘2012-10-10′ as StartDate ,’B1’ as AccountCode ,210 as [Credit],110 as [Debit] union all
select ‘2012-10-10′ as StartDate ,’C1’ as AccountCode ,20 as [Credit],10 as [Debit] union all
select ‘2012-10-11′ as StartDate ,’A1’ as AccountCode ,220 as [Credit],120 as [Debit] union all
select ‘2012-10-11′ as StartDate ,’D1’ as AccountCode ,240 as [Credit],140 as [Debit] union all
select ‘2012-10-11′ as StartDate ,’E1’ as AccountCode ,280 as [Credit],180 as [Debit] union all
select ‘2012-10-12′ as StartDate ,’B1’ as AccountCode ,190 as [Credit],90 as [Debit] union all
select ‘2012-10-12′ as StartDate ,’F1’ as AccountCode ,290 as [Credit],190 as [Debit] union all
select ‘2012-10-12′ as StartDate ,’G1’ as AccountCode ,320 as [Credit],120 as [Debit]
) x
--Sum MTD by Date Period
Declare @startdate date
Declare @enddate date
set @startdate = '2012-01-01'
set @enddate = '2012-12-12'
SELECT a.AccountCode
,z.[PVCredit]
,z.[PVDebit]
,z.[PVBalance]
,a.[PeriodCredit]
,a.[PeriodDebit]
,a.[PeriodBalance]
FROM(
SELECT x.AccountCode
,sum(x.[Credit]) as [PeriodCredit]
,sum(x.[Debit]) as [PeriodDebit]
,(sum(x.[Credit]) *-1) + sum(x.[Debit]) as [PeriodBalance]
FROM #tb_temp x
WHERE x.Startdate between @startdate and @enddate
--and x.supplier = ''
GROUP BY x.AccountCode
)a
LEFT JOIN (SELECT tmp.AccountCode
,sum(tmp.[Credit]) as [PVCredit]
,sum(tmp.[Debit]) as [PVDebit]
,(sum(tmp.[Credit]) *-1) + sum(tmp.[Debit]) as [PVBalance]
FROM #tb_temp tmp
WHERE tmp.Startdate < @startdate
--and x.supplier = ''
GROUP BY tmp.AccountCode
)z on a.AccountCode = z.AccountCode
