--试试 select id,姓名, dateadd(week,datepart(week,收款日期)/2*2 - 1,收款日期) as 结算起始日期, dateadd(day,13,dateadd(week,datepart(week,收款日期)/2*2 - 1,收款日期)) as 结算终止日期, sum(收款额) as 结算金额 from table1 where 收款日期 > '2006-01-08' group by id,姓名,datepart(week,收款日期)/2
datepart(week,收款日期)/2*2——?这里除以2,又乘以2。什么意思呢?请赐教。
(1) SELECT IDENTITY(INT,1,1) AS ID,MIN(姓名) AS 姓名 ,MIN(收款日期) AS 结算起始日期 ,MAX(收款日期) AS 结算终止日期,SUM(收款额) AS 结算金额 INTO #TMP FROM TABLE5 GROUP BY datepart(week,收款日期)/2(2) SELECT * FROM #TMP
更正: SELECT MIN(ID) AS ID,姓名,MIN(收款日期) AS 结算起始日期 ,MAX(收款日期) AS 结算终止日期,SUM(收款额) AS 结算金额 FROM TABLE5 GROUP BY datepart(week,收款日期)/2,姓名 ORDER BY ID
declare @i datetime declare @j numeric(15,4) set @i=getdate()-14 set @j=0 while @i<getdate() begin set @j=@j+(select 收款额 from table1 where 收款日期=@i ) set @i=@i+1 end select ID,姓名,getdate()-14 as'结算起始日期',getdate() as'结算终止日期',@j as'结算金额' from table1
declare @i datetime declare @j numeric(15,4) set @i=getdate()-14 set @j=0 while @i<getdate() begin set @j=@j+(select 收款额 from table1 where 收款日期=@i ) set @i=@i+1 end select ID,姓名,getdate()-14 as'结算起始日期',getdate() as'结算终止日期',@j as'结算金额' from table1 group by ID
SELECT ID, 姓名, MIN(收款日期) AS 结算起始日期, MAX(收款日期) AS 结算终止日期, SUM(收款额) AS 结算金额 FROM [table] WHERE 收款日期>='20060108' GROUP BY ID, 姓名, DATEDIFF(Week, '20060108', 收款日期)/2
select id,姓名,
dateadd(week,datepart(week,收款日期)/2*2 - 1,收款日期) as 结算起始日期,
dateadd(day,13,dateadd(week,datepart(week,收款日期)/2*2 - 1,收款日期)) as 结算终止日期,
sum(收款额) as 结算金额
from table1
where 收款日期 > '2006-01-08'
group by id,姓名,datepart(week,收款日期)/2
(1)
SELECT IDENTITY(INT,1,1) AS ID,MIN(姓名) AS 姓名 ,MIN(收款日期) AS 结算起始日期 ,MAX(收款日期) AS 结算终止日期,SUM(收款额) AS 结算金额
INTO #TMP
FROM TABLE5
GROUP BY datepart(week,收款日期)/2(2)
SELECT * FROM #TMP
SELECT MIN(ID) AS ID,姓名,MIN(收款日期) AS 结算起始日期 ,MAX(收款日期) AS 结算终止日期,SUM(收款额) AS 结算金额
FROM TABLE5
GROUP BY datepart(week,收款日期)/2,姓名
ORDER BY ID
datepart(week,收款日期)/2*2——?这里除以2,又乘以2。什么意思呢?请赐教。
===========================================================
2/2*2 = 2 3/2*2 = 2 4/2* 2 = 4 5/2*2 = 4呵呵,楼主明白了吧?
那~~~没研究过!
declare @j numeric(15,4)
set @i=getdate()-14
set @j=0
while @i<getdate()
begin
set @j=@j+(select 收款额 from table1 where 收款日期=@i )
set @i=@i+1
end
select ID,姓名,getdate()-14 as'结算起始日期',getdate() as'结算终止日期',@j as'结算金额'
from table1
declare @j numeric(15,4)
set @i=getdate()-14
set @j=0
while @i<getdate()
begin
set @j=@j+(select 收款额 from table1 where 收款日期=@i )
set @i=@i+1
end
select ID,姓名,getdate()-14 as'结算起始日期',getdate() as'结算终止日期',@j as'结算金额'
from table1
group by ID
MAX(收款日期) AS 结算终止日期, SUM(收款额) AS 结算金额
FROM [table]
WHERE 收款日期>='20060108'
GROUP BY ID, 姓名, DATEDIFF(Week, '20060108', 收款日期)/2