各位好,小弟最近在寫報表,報表是要篩選出時段幾月到幾月
額外需求是需要一份報表裡,有當月的累積紀錄和去年一樣的月份的資料
今年的1-9月 跟去年的1-9月
不太清楚要怎麼寫入到同張報表一樣的資料不同的時間
畫面是最下方的需求怕說的不清楚
Str03 = g.Key.ACC_ID, 是會計ID
Str04 =g.Key.ACC_NAME, 會計名稱
Decimal01 =g.Sum(p=>p.DEBIT_AMT), 借方金額
Decimal02 = g.Sum(p => p.CREDIT_AMT), 貸方金額
傳票日期的欄位
public class SearchAccDetailRpt
{
public Guid? SysUnitFK { get; set; }
時間的起
public DateTime? JOUR_DATE_S { get; set; }
時間的迄
public DateTime? JOUR_DATE_E { get; set; }
}
public RptDataSet.RptDataTable GetAccDetailRpt(SearchAccDetailRpt condition)
{
if (condition == null)
throw new ArgumentNullException("condition");
var query = from a in unitOfWork.ACC_JOURNAL_M.AsNoTracking()
where userAuth.Contains(a.SysUnitFK)
select a;
var queryM = from a in unitOfWork.ACC_JOURNAL_D.AsNoTracking()
join b in unitOfWork.ACC_ACCOUNT_M.AsNoTracking()
on a.ACC_ACCOUNT_M_FK equals b.PK
where userAuth.Contains(b.SysUnitFK)
select b;
if (condition.JOUR_DATE_S.HasValue)
query = query.Where(i => i.JOUR_DATE >= condition.JOUR_DATE_S);
if (condition.JOUR_DATE_E.HasValue)
query = query.Where(i => i.JOUR_DATE <= condition.JOUR_DATE_E);
if (condition.JOUR_DATE_S.HasValue)
query = query.Where(i => i.JOUR_DATE >= DbFunctions.AddYears(condition.JOUR_DATE_S, -1));
if (condition.JOUR_DATE_E.HasValue)
query = query.Where(i => i.JOUR_DATE <= DbFunctions.AddYears(condition.JOUR_DATE_E, -1));
var data = from a in unitOfWork.ACC_JOURNAL_M.AsNoTracking()//傳票主檔
join b in unitOfWork.ACC_JOURNAL_D.AsNoTracking() //傳票明細檔
on a.PK equals b.ACC_JOURNAL_M_FK
join c in unitOfWork.ACC_ACCOUNT_M.AsNoTracking() //會計主檔
on b.ACC_ACCOUNT_M_FK equals c.PK
where query.Select(i => i.PK).Contains(a.PK)
&& queryM.Select(i => i.PK).Contains(b.ACC_ACCOUNT_M_FK)
&& a.CONFIRM_DATE.HasValue
& !a.VOID_DATE.HasValue
group b by new {c.ACC_ID ,c.ACC_NAME } into g
select new
{
Str03 = g.Key.ACC_ID,
Str04 =g.Key.ACC_NAME,
Decimal01 =g.Sum(p=>p.DEBIT_AMT),
Decimal02 = g.Sum(p => p.CREDIT_AMT),
};
RptDataSet.RptDataTable dt = new RptDataSet.RptDataTable();
var list = data.ToList();
using (var reader = FastMember.ObjectReader.Create(list))
{
dt.Load(reader);
}
return dt;
}
使用OR, 或取出年月來比較
以下範例供參考(MS SQL),您也可加變數進去
你也許可以
1.先用SQL將資料整理準備好,再寫程式
或 2.將語法寫進程式中
說明:最後結果
accountid=會計科目
AMT=當年當月金額 =2019年8月 金額
AMT2=去年同年同月金額 =2018年8月 金額
ACC=今年累計 =2019年 1~8月 累計金額
ACC2=去年累計 =2018年 1~8月 累計金額
詳細內容
資料準備 (測試用)
CREATE TABLE TEST(
AccountID char(5) ,
Date date ,
AMT int
)
insert into test
Values (N'1121 ', CAST(N'2018-01-02' AS Date), 500)
,(N'1121 ', CAST(N'2018-01-05' AS Date), 300)
,(N'1121 ', CAST(N'2018-02-02' AS Date), 200)
,(N'1121 ', CAST(N'2018-03-05' AS Date), 600)
,(N'1121 ', CAST(N'2018-03-11' AS Date), 300)
,(N'1121 ', CAST(N'2018-04-05' AS Date), 500)
,(N'1121 ', CAST(N'2018-04-09' AS Date), 600)
,(N'1121 ', CAST(N'2018-05-01' AS Date), 700)
,(N'1121 ', CAST(N'2018-06-02' AS Date), 800)
,(N'1121 ', CAST(N'2018-07-01' AS Date), 100)
,(N'1121 ', CAST(N'2018-08-02' AS Date), 200)
,(N'1121 ', CAST(N'2018-08-08' AS Date), 300)
,(N'1201 ', CAST(N'2018-01-06' AS Date), 500)
,(N'1201 ', CAST(N'2018-01-09' AS Date), 300)
,(N'1201 ', CAST(N'2018-02-06' AS Date), 200)
,(N'1201 ', CAST(N'2018-03-05' AS Date), 600)
,(N'1201 ', CAST(N'2018-05-05' AS Date), 300)
,(N'1201 ', CAST(N'2018-05-10' AS Date), 500)
,(N'1201 ', CAST(N'2018-06-11' AS Date), 600)
,(N'1201 ', CAST(N'2018-07-12' AS Date), 700)
,(N'1201 ', CAST(N'2018-07-18' AS Date), 800)
,(N'1201 ', CAST(N'2018-08-01' AS Date), 100)
,(N'1201 ', CAST(N'2018-08-06' AS Date), 200)
,(N'1201 ', CAST(N'2018-08-10' AS Date), 300)
,(N'1121 ', CAST(N'2019-01-06' AS Date), 100)
,(N'1121 ', CAST(N'2019-01-10' AS Date), 200)
,(N'1121 ', CAST(N'2019-02-08' AS Date), 500)
,(N'1121 ', CAST(N'2019-02-10' AS Date), 200)
,(N'1121 ', CAST(N'2019-03-12' AS Date), 300)
,(N'1121 ', CAST(N'2019-04-11' AS Date), 500)
,(N'1121 ', CAST(N'2019-05-10' AS Date), 300)
,(N'1121 ', CAST(N'2019-07-12' AS Date), 600)
,(N'1121 ', CAST(N'2019-08-01' AS Date), 700)
,(N'1121 ', CAST(N'2019-08-06' AS Date), 100)
,(N'1121 ', CAST(N'2019-08-12' AS Date), 200)
,(N'1121 ', CAST(N'2019-08-16' AS Date), 200)
,(N'1201 ', CAST(N'2019-01-05' AS Date), 200)
,(N'1201 ', CAST(N'2019-02-08' AS Date), 100)
,(N'1201 ', CAST(N'2019-03-11' AS Date), 300)
,(N'1201 ', CAST(N'2019-03-16' AS Date), 300)
,(N'1201 ', CAST(N'2019-05-12' AS Date), 500)
,(N'1201 ', CAST(N'2019-05-16' AS Date), 100)
,(N'1201 ', CAST(N'2019-06-12' AS Date), 500)
,(N'1201 ', CAST(N'2019-07-10' AS Date), 300)
,(N'1201 ', CAST(N'2019-07-12' AS Date), 600)
,(N'1201 ', CAST(N'2019-08-02' AS Date), 800)
,(N'1201 ', CAST(N'2019-08-12' AS Date), 600)
,(N'1201 ', CAST(N'2019-08-16' AS Date), 800)
select accountid,YYYY=substring( CONVERT(nchar(8), DATE, 112) ,1,4),MM=substring( CONVERT(nchar(8), DATE, 112) ,5,2),
AMT=sum(AMT)
INTO TEST2
from test
group by accountid,substring( CONVERT(nchar(8), DATE, 112) ,1,4),substring( CONVERT(nchar(8), DATE, 112) ,5,2)
order by 1,2
select T.*,
acc=SUM(AMT) OVER (PARTITION BY T.AccountID ,T.YYYY ORDER BY T.AccountID ,T.YYYY,T.MM ),
acc2=SUM(AMT2) OVER (PARTITION BY T.AccountID ,T.YYYY ORDER BY T.AccountID ,T.YYYY,T.MM )
into TEST3
from(
select a.*,AMT2=isnull(b.AMT,0)
from test2 a left join test2 b
on A.accountid=b.accountid and a.yyyy=b.yyyy+1 and a.mm=b.mm
) T
select * from test3
where YYYY=2019 and mm=08
先對"年"、"月"做group by 作為子查詢,然後在對"月"做group by ,就可以得到不分年度每月資料
西元年
select year(getdate())
select datepart(yyyy, getdate())
select datepart(yy, getdate())
月
select month(getdate())
select datepart(mm, getdate())
select datepart(m, getdate())
比如說
select *
from(
select year(now()) gy, month(now()) gm,* from XXX
) t
group by gy,gm
組成多層的子查詢
前輩是教我先弄年月 再來group by 會計名稱或是ID
不過我都是要用linq的寫法- -