มีเพื่อนๆในเพจได้ถามเข้ามาโดยเข้ามีโจทย์อยู่ว่า : ต้องการออกรายงานที่แสดงยอดรวมของ Amount ตามช่วงเวลาที่ต้องการ และแสดงผลรวมของ Amount ทั้งหมดก่อนถึงช่วงเวลาที่ต้องให้อยู่ในรายงานหน้าเดียวกันจะีวิธีการเขียนยังไงได้บ้าง

เราจะเขียน SQL Script ดึงข้อมูลให้แสดงผลตามรูปขวามือ

เขียนคำสั่ง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

TG Facebook Comments