MS SQL 新增不同日期固定資料

mssql 新增固定資料

twyes 2019-12-05 11:33:03 ‧ 3491 瀏覽

分享至

原始資料 TableA

日期	公司	項目	持有美金
2019/12/13	A	存款	300
2019/12/13	A	應收款項	500
2019/12/13	A	應付帳款	200
2019/12/13	B	存款	200
2019/12/13	B	應付帳款	100
2019/12/13	C	應收款項	600

這是12月13日的資料,共六筆
要新增每月13日至下個月12日的資料進去
資料都一樣,只有日期不一樣

意思是說本月13日到下月12日都要有這6筆

TableB (13日至下月12日 , 每天都要有這6筆,因長度問題我只有列出3天)

日期	公司	項目	持有美金
2019/12/13	A	存款	300
2019/12/13	A	應收款項	500
2019/12/13	A	應付帳款	200
2019/12/13	B	存款	200
2019/12/13	B	應付帳款	100
2019/12/13	C	應收款項	600
2019/12/14	A	存款	300
2019/12/14	A	應收款項	500
2019/12/14	A	應付帳款	200
2019/12/14	B	存款	200
2019/12/14	B	應付帳款	100
2019/12/14	C	應收款項	600
2020/01/12	A	存款	300
2020/01/12	A	應收款項	500
2020/01/12	A	應付帳款	200
2020/01/12	B	存款	200
2020/01/12	B	應付帳款	100
2020/01/12	C	應收款項	600

TableA 要怎麼變成 TableB ? 如何寫 ?

寫好匯入資料,方便大家使用

declare @Tab table(
	日期 date
	,公司 Nvarchar (10)
	,項目 Nvarchar (20)
	,持有美金 decimal(12,0)
)
insert into @Tab
values('2019/12/13','A','存款',300)
,('2019/12/13','A','應收款項',500)
,('2019/12/13','A','應付帳款',200)
,('2019/12/13','B','存款',200)
,('2019/12/13','B','應付帳款',100)
,('2019/12/13','C','應收款項',600)


select * from @Tab

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2019-12-05 13:27:55

最佳解答

CREATE TABLE TEST(
	F1 date
	,F2 Nvarchar (10)
	,F3 Nvarchar (20)
	,F4 decimal(12,0)
)

insert into TEST
values('2019/12/13','A',N'存款',300)
,('2019/12/13','A',N'應收款項',500)
,('2019/12/13','A',N'應付帳款',200)
,('2019/12/13','B',N'存款',200)
,('2019/12/13','B',N'應付帳款',100)
,('2019/12/13','C',N'應收款項',600)

INSERT into TEST(F1,F2,F3,F4)
SELECT 
DATEADD (Day,RowNum,'2019/12/13') NewDate,F2,F3,F4
FROM (
SELECT ROW_NUMBER () OVER (ORDER BY Number ASC) AS RowNum
FROM (
SELECT TOP (DATEDIFF (Day,'2019/12/13',DATEADD(Day,-1,DATEADD(Month,1,'2019/12/13')))) Number
FROM master..spt_values
) AS BaseDataA
) AS BaseDataB
LEFT JOIN (
SELECT F2,F3,F4
FROM TEST
WHERE 1=1
AND F1='2019/12/13'
) AS BaseDataC ON 1=1
WHERE 1=1

Demo

回應 1
分享
檢舉

twyes iT邦新手 4 級 ‧ 2019-12-05 14:28:14 檢舉

感謝,最佳解答

因為不是只有12月13號有這種情況
每個月13號都有新的資料,所以我加了變數

declare @S_DATE date,
        @E_DATE date
SELECT @S_DATE = DATEADD ( DAY ,12 , DATEADD(MONTH,  DATEDIFF(MONTH,0,F1) ,0)  ) FROM TEST
SELECT @E_DATE = DATEADD ( DAY ,11 , DATEADD(MONTH,  DATEDIFF(MONTH,0,F1) +1 ,0) ) FROM TEST

INSERT into TEST(F1,F2,F3,F4)
SELECT 
DATEADD (Day,RowNum,@S_DATE) NewDate,F2,F3,F4
FROM (
SELECT ROW_NUMBER () OVER (ORDER BY Number ASC) AS RowNum
FROM (
SELECT TOP (DATEDIFF (Day,@S_DATE,@E_DATE)) Number
FROM master..spt_values
) AS BaseDataA
) AS BaseDataB
LEFT JOIN (
SELECT F2,F3,F4
FROM TEST
WHERE 1=1
AND F1=@S_DATE
) AS BaseDataC ON 1=1
WHERE 1=1

登入發表回應

ice bear

iT邦新手 4 級 ‧ 2019-12-23 11:38:32

HI 你好

我自己也寫了給你參考~
我在準備資料的部分，就不有日期欄位了

CREATE TABLE TEST(
	 F1 Nvarchar (10)
	,F2 Nvarchar (20)
	,F3 decimal(12,0)
)			
insert into TEST
values('A',N'存款',300)
,('A',N'應收款項',500)
,('A',N'應付帳款',200)
,('B',N'存款',200)
,('B',N'應付帳款',100)
,('C',N'應收款項',600)

運用CTE可產生連續資料

DECLARE @start_date DATETIME,@end_date DATETIME
SELECT @start_date = GETDATE() , @end_date = DATEADD(M,1,GETDATE()) 

;WITH CTETABLE AS (
	SELECT [Date] = @start_date 
	UNION ALL
	SELECT [Date] + 1 FROM CTETABLE 
	WHERE ([Date] < @end_date)
)

SELECT CONVERT(CHAR(10),B.Date,111),A.* 
FROM TEST A
CROSS JOIN CTETABLE B