create table #month_max
(
id int,
dianliang decimal(18,2),
Dt datetime
)
insert into #month_max
select 1, 36800.2, '2008-04-21 11:38:12.000'
union all
select 1, 36300.2, '2008-04-18 07:32:39.000'
union all
select 1, 36200.2, '2008-04-15 11:27:12.000'
union all
select 1, 36100.2, '2008-04-12 11:21:45.000'
union all
select 1, 36000.2, '2008-04-02 11:16:15.000'
union all
select 1, 35900.2, '2008-03-29 05:10:43.000'
union all
select 1, 35800.2, '2008-03-18 02:05:06.000'
union all
select 1, 35700.2, '2008-03-08 11:38:12.000'
union all
select 1, 35600.2, '2008-03-01 07:32:39.000'
union all
select 1, 35500.2, '2008-02-27 11:27:12.000'
union all
select 1, 35400.2, '2008-02-16 11:21:45.000'
union all
select 1, 35300.2, '2008-02-10 11:16:15.000'
union all
select 1, 35100.2, '2008-02-09 05:10:43.000'
union all
select 1, 35000.2, '2008-02-08 02:05:06.000'
select * from #month_max order by dt
select max(dianliang),datepart(month,dt)
from #month_max
where id=1
group by datepart(month,dt)
order by datepart(month,dt)
select min(dianliang),datepart(month,dt)
from #month_max
where id=1
group by datepart(month,dt)
order by datepart(month,dt)
drop table #month_max
exec SP_MSFOREACHTABLE sp_msforeachtable
create table #a
(
name varchar(20),
peo varchar(50),
pom varchar(50)
)
insert into #a
select 'aa' , '07', 'q'
union all
select 'aa' , '08' , 'w'
union all
select 'aa' , '09' , 'e'
union all
select 'bb' , '07' , 'r'
union all
select 'bb' , '08' , 't'
union all
select 'bb' , '09' ,'y'
select x.name, x.peo+x.pom as peopom, y.id into #b from #a x left join (
select name,row_number() over(order by name) as id
from #a
group by name ) y on x.name=y.name
declare @i int
set @i=1
while @i<=(select max(id) from #b)
begin
end
select rand(1)
SELECT cast(STUFF(RAND(),1,2,'') as varchar(4))
select id = cast(RAND() * 10000 as int)
select cast(RAND() * 10000 as int)
create table #t
(
id int identity(1,1),
startday varchar(20),
zhouqi int,
finishday varchar(20)
)
insert into #t
select '2008-04-23', 1,null
union
select '2008-04-23', 7,null
union
select '2008-04-24', 10,null
union
select '2008-04-22', 9,null
error:
update a set a.finishday=convert(varchar(20),b.et,20)
from #t a
inner join (
select *,case when datepart(dw,startday)-1+zhouqi>=7 then
dateadd(day,zhouqi+cast((datepart(dw,startday)-1+zhouqi)/6 as int),startday)
else dateadd(day,zhouqi,startday)
end as et
from #t ) b on a.id=b.id
true:
update #T set finishday =
dateadd(day,(zhouqi+(zhouqi+(@@datefirst+datepart(weekday,startday)-1)%7-1)/6),startday)
true:
select dateadd(day,(9+(9+(@@datefirst+datepart(weekday,'2008-04-24')-1)%7-1)/6),'2008-04-24')
select dateadd(day,(50+(50+(@@datefirst+datepart(weekday,'2008-04-24')-1)%7-1)/6),'2008-04-24')
true:
select (9+(@@datefirst+datepart(weekday,'2008-04-24')-1)%7-1)/6
select (50+(@@datefirst+datepart(weekday,'2008-04-24')-1)%7-1)/6
select (@@datefirst+datepart(weekday,'2008-04-24')-1)%7-1
select datepart(weekday,'2008-04-24')
error:
select (datepart(dw,'2008-04-24')-1+8)/6
true:
select dateadd(day,50+(datepart(dw,'2008-04-24')-1+50-1)/6,'2008-04-24')
create table #MR
(
PurchaseBillNo VARCHAR(21),
PurchaseNo DECIMAL(10,2),
StockBillNo VARCHAR(21),
Row INT,
StockNo DECIMAL(10,2)
)
INSERT INTO #MR SELECT 'MR19302',37800.00,'HK-0020853',2,111000.0000
insert into #MR SELECT 'MR19305',35290.00,'HK-0020853',2,111000.0000
insert into #MR SELECT 'MR19299',37800.00,'HK-0020853',2,111000.0000
insert into #MR SELECT 'MR19298',800.00,'HK-0020853',2,111000.0000
insert into #MR SELECT 'MR19299',1660.00,'HK-0021572',2,1600.0000
select * from #MR
drop table #
select id =identity(int,1,1),PurchaseBillNo qd,PurchaseNo qsl,StockBillNo cd,StockNo csl into # from #MR
select * from #
select qd 请购单,sum(qsl) 申购量,sum(case when ssl-qsl >0 then qsl else ssl end) 已采购量
from
(
select qd,qsl,csl - isnull((select sum(qsl) from # where id <a.id and cd = a.cd),0) ssl from # a
) b
group by qd
select id = identity(int,1,1), * into #m from #MR
select * from #m
select purchasebillno, sum(purchaseno), sum( case when purchaseno-sum1>0 then sum1 else purchaseno end)
from (
select purchasebillno, purchaseno, stockno-isnull((select sum(purchaseno)
from #m
where stockbillno=a.stockbillno and id<a.id), 0) as sum1
from #m a
) b
group by purchasebillno
create table #s
(
ID int identity(1,1),
mn varchar(20)
)
insert into #s
select ''
union all
select 'a'
union all
select ''
union all
select ''
union all
select 'b'
select * from #s
--update s set s.mn=t.mn
select *
from #s s
inner join (
select *, isnull((select min(id) from #s where mn<>'' and id<a.id), 1) ssid
from
(select * from #s where mn<>'') a
) t
on s.id between t.ssid and t.id
where s.mn=''
