查找出每一个月的所有物料的最后一次单价(不管函数或存储过程，示图都行)

[BarryW]

有一个表TF_POS 有字段为:PRD_NO(物料编号),OS_DD（日期）,UP（单价；

查找出每一个月的所有物料的最后一次单价（如同一天的同一物料不同的价格就任取一个作为最后价格）；

PRD_NO OS_DD UP

2310031002 2003-1-2 0.053
2310031002 2003-1-4 2.5
2310031002 2003-1-4 0.04
1121000191 2003-1-11 0.055
2310031002 2003-2-1 0.046
1121000191 2003-2-13 0.23
1121000191 2003-2-15 0.197
1121000191 2003-3-11 0.044
1121000191 2003-3-9 0.0066
1121000191 2003-3-15 0.35
2310031002 2003-4-15 1.9
1121000191 2003-4-15 0.04
1121000191 2003-4-15 0.0066
...........

条件：
（1）：如在TF_POS里有如上的数据，但是在一个月内同一天的同一个PRD_NO(物料)
有两笔数据就取其中的任一个单价；
如：2310031002 在一月份四号有两笔资料，并是这一个月的最后一次资料，
所以取这两笔的任一笔资料单价都行；意思就是两笔资料我只要其中任一单价就行了；
2310031002 2003-1-4 1200 2.5 3000
2310031002 2003-1-4 10000 0.04 400

（2）：而在一月份物料1121000191在最后一次是11号，所以就直接取这天的UP单价0.053
2310031002 2003-1-2 100000 0.053 5300

（3）：单一个月在没有这个物料的数据时就给它为0.00（零）;
如上的2310031002 在一，二月份都有它的单价，而在三月份又没有就给它的单价为0.00（零）；

所以根据以上的信息查找出最后的单价表（QTY_TEUP)可以为:

PRD_NO UPM1(一月单价) UPM2(二月单价) UPM3(三月单价）UPM4（四月单价）
2310031002 0.04（或2.5） 0.046 0.00 1.9
1121000191 0.055 00.197 0.35 0.04(或0.0066)


以上表查找从一月份到十二月最后的单价；
谢谢！

[victorycyz]

select prd_no,
sum(case when mon='200301' then [up] else 0 end) [upm1],
sum(case when mon='200302' then [up] else 0 end) [upm2],
...
sum(case when mon='200312' then [up] else 0 end) [upm12]
from
( select a.prd_no,mon,min([up]) as [up]
from tf_pos a join
( select prd_no,
convert(varchar(6),os_dd,112) as mon,
max(os_dd) as maxdate
from tf_pos
group by prd_no,convert(varchar(6),os_dd,112)
) b
on a.prd_no=b.prd_no and a.os_dd=b.maxdate
) c
group by pro_no

[zjcxc]

--不会跨年?
select a.PRD_NO
,UPM1=max(case mm when 1 then UP else 0 end)
,UPM2=max(case mm when 2 then UP else 0 end)
,UPM3=max(case mm when 3 then UP else 0 end)
,UPM4=max(case mm when 4 then UP else 0 end)
,UPM5=max(case mm when 5 then UP else 0 end)
,UPM6=max(case mm when 6 then UP else 0 end)
,UPM7=max(case mm when 7 then UP else 0 end)
,UPM8=max(case mm when 8 then UP else 0 end)
,UPM9=max(case mm when 9 then UP else 0 end)
,UPM10=max(case mm when 10 then UP else 0 end)
,UPM11=max(case mm when 11 then UP else 0 end)
,UPM12=max(case mm when 12 then UP else 0 end)
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO

[zjcxc]

--测试

--测试数据
create table TF_POS(PRD_NO char(10),OS_DD datetime,UP decimal(20,4))
insert TF_POS select '2310031002','2003-1-2',0.053
union all select '2310031002','2003-1-4',2.5
union all select '2310031002','2003-1-4',0.04
union all select '1121000191','2003-1-11',0.055
union all select '2310031002','2003-2-1',0.046
union all select '1121000191','2003-2-13',0.23
union all select '1121000191','2003-2-15',0.197
union all select '1121000191','2003-3-11',0.044
union all select '1121000191','2003-3-9',0.0066
union all select '1121000191','2003-3-15',0.35
union all select '2310031002','2003-4-15',1.9
union all select '1121000191','2003-4-15',0.04
union all select '1121000191','2003-4-15',0.0066
go

--查询
select a.PRD_NO
,UPM1=max(case mm when 1 then UP else 0 end)
,UPM2=max(case mm when 2 then UP else 0 end)
,UPM3=max(case mm when 3 then UP else 0 end)
,UPM4=max(case mm when 4 then UP else 0 end)
,UPM5=max(case mm when 5 then UP else 0 end)
,UPM6=max(case mm when 6 then UP else 0 end)
,UPM7=max(case mm when 7 then UP else 0 end)
,UPM8=max(case mm when 8 then UP else 0 end)
,UPM9=max(case mm when 9 then UP else 0 end)
,UPM10=max(case mm when 10 then UP else 0 end)
,UPM11=max(case mm when 11 then UP else 0 end)
,UPM12=max(case mm when 12 then UP else 0 end)
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO
go

--删除测试环境
drop table TF_POS

/*--测试结果
PRD_NO UPM1 UPM2 UPM3 UPM4 UPM5 UPM6 UPM7 UPM8 UPM9 UPM10 UPM11 UPM12
---------- ---------------------- ---------------------- ---------------------- ---------------------- --------------
1121000191 .0550 .1970 .3500 .0400 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000
2310031002 2.5000 .0460 .0000 1.9000 .0000 .0000 .0000 .0000 .0000 .0000 .0000 .0000

（所影响的行数为 2 行）
--*/

[zjcxc]

--查询的存储过程
create proc p_qry
@OS_DD varchar(7)
as
declare @s varchar(8000),@dt datetime,@dt1 varchar(10)
set nocount on
select @s='',@dt=dateadd(month,1,@os_dd+'-01')-1
,@dt1=convert(varchar(10),@dt,120)
select @s=@s+',UPM'+id+'=max(case mm when '+id+' then UP else 0 end)'
from(
select id='1' union all select '2' union all select '3'
union all select '4' union all select '5' union all select '6'
union all select '7' union all select '8' union all select '9'
union all select '10' union all select '11' union all select '12'
)a where id<=month(@dt)
exec('select a.PRD_NO'+@s+'
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
where OS_DD<='''+@dt1+'''
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO
')

[zjcxc]

--测试

--测试数据
create table TF_POS(PRD_NO char(10),OS_DD datetime,UP decimal(20,4))
insert TF_POS select '2310031002','2003-1-2',0.053
union all select '2310031002','2003-1-4',2.5
union all select '2310031002','2003-1-4',0.04
union all select '1121000191','2003-1-11',0.055
union all select '2310031002','2003-2-1',0.046
union all select '1121000191','2003-2-13',0.23
union all select '1121000191','2003-2-15',0.197
union all select '1121000191','2003-3-11',0.044
union all select '1121000191','2003-3-9',0.0066
union all select '1121000191','2003-3-15',0.35
union all select '2310031002','2003-4-15',1.9
union all select '1121000191','2003-4-15',0.04
union all select '1121000191','2003-4-15',0.0066
go

--查询的存储过程
create proc p_qry
@OS_DD varchar(7)
as
declare @s varchar(8000),@dt datetime,@dt1 varchar(10)
set nocount on
select @s='',@dt=dateadd(month,1,@os_dd+'-01')-1
,@dt1=convert(varchar(10),@dt,120)
select @s=@s+',UPM'+id+'=max(case mm when '+id+' then UP else 0 end)'
from(
select id='1' union all select '2' union all select '3'
union all select '4' union all select '5' union all select '6'
union all select '7' union all select '8' union all select '9'
union all select '10' union all select '11' union all select '12'
)a where id<=month(@dt)
exec('select a.PRD_NO'+@s+'
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
where OS_DD<='''+@dt1+'''
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
group by a.PRD_NO
')
go

--调用
exec p_qry '2003-03'
go

--删除测试环境
drop table TF_POS
drop proc p_qry

/*--测试结果

PRD_NO UPM1 UPM2 UPM3
---------- ---------------------- ---------------------- ----------------------
1121000191 .0550 .1970 .3500
2310031002 2.5000 .0460 .0000

--*/

[LoveSQL]

create table TF_POS(PRD_NO char(10),OS_DD datetime,UP decimal(20,4))
insert TF_POS select '2310031002','2003-1-2',0.053
union all select '2310031002','2003-1-4',2.5
union all select '2310031002','2003-1-4',0.04
union all select '1121000191','2003-1-11',0.055
union all select '2310031002','2003-2-1',0.046
union all select '1121000191','2003-2-13',0.23
union all select '1121000191','2003-2-15',0.197
union all select '1121000191','2003-3-11',0.044
union all select '1121000191','2003-3-9',0.0066
union all select '1121000191','2003-3-15',0.35
union all select '2310031002','2003-4-15',1.9
union all select '1121000191','2003-4-15',0.04
union all select '1121000191','2003-4-15',0.0066
go

--查询
declare @OS_DD datetime ,@strMonth varchar(500),@str varchar(8000)
set @str=''
set @OS_DD=cast('2003-2-1' as datetime)
set @strMonth=case when datepart(month,@OS_DD)=1 then 'PRD_NO,UPM1 '
when datepart(month,@OS_DD)=2 then 'PRD_NO,UPM1,UPM2 '
when datepart(month,@OS_DD)=3 then 'PRD_NO,UPM1,UPM2, UPM3'
when datepart(month,@OS_DD)=4 then 'PRD_NO,UPM1,UPM2, UPM3, UPM4'
when datepart(month,@OS_DD)=5 then 'PRD_NO,UPM1,UPM2, UPM3, UPM4,UPM5 '
when datepart(month,@OS_DD)=6 then 'PRD_NO,UPM1 ,UPM2, UPM3, UPM4,UPM5,UPM6'
when datepart(month,@OS_DD)=7 then 'PRD_NO,UPM1,UPM2, UPM3, UPM4,UPM5,UPM6,UPM7 '
when datepart(month,@OS_DD)=8 then 'PRD_NO,UPM1,UPM2, UPM3, UPM4,UPM5,UPM6,UPM7,UPM8 '
when datepart(month,@OS_DD)=9 then 'PRD_NO,UPM1,UPM2, UPM3, UPM4 ,UPM5,UPM6,UPM7,UPM8,UPM9'
when datepart(month,@OS_DD)=10 then 'PRD_NO,UPM1,UPM2, UPM3, UPM4,UPM5,UPM6,UPM7,UPM8,UPM9,UPM10 '
when datepart(month,@OS_DD)=11 then 'PRD_NO,UPM1,UPM2, UPM3, UPM4,UPM5,UPM6,UPM7,UPM8,UPM9,UPM10,UPM11 '
when datepart(month,@OS_DD)=12 then 'PRD_NO,UPM1,UPM2, UPM3, UPM4 ,UPM5,UPM6,UPM7,UPM8,UPM9,UPM10,UPM11,UPM12'
else 'PRD_NO'
end

select a.PRD_NO
,UPM1=max(case mm when 1 then UP else 0 end)
,UPM2=max(case mm when 2 then UP else 0 end)
,UPM3=max(case mm when 3 then UP else 0 end)
,UPM4=max(case mm when 4 then UP else 0 end)
,UPM5=max(case mm when 5 then UP else 0 end)
,UPM6=max(case mm when 6 then UP else 0 end)
,UPM7=max(case mm when 7 then UP else 0 end)
,UPM8=max(case mm when 8 then UP else 0 end)
,UPM9=max(case mm when 9 then UP else 0 end)
,UPM10=max(case mm when 10 then UP else 0 end)
,UPM11=max(case mm when 11 then UP else 0 end)
,UPM12=max(case mm when 12 then UP else 0 end) into #tmp
from TF_POS a join(
select PRD_NO,mm=month(OS_DD),OS_DD=max(OS_DD)
from TF_POS
where datepart(year,OS_DD)=datepart(year,@OS_DD) and datepart(month,OS_DD)<=datepart(month,@OS_DD)
group by PRD_NO,month(OS_DD)
)b on a.PRD_NO=b.PRD_NO and a.OS_DD=b.OS_DD
where datepart(year,a.OS_DD)=datepart(year,@OS_DD) and datepart(month,a.OS_DD)<=datepart(month,@OS_DD)
group by a.PRD_NO

set @str='select '+@strMonth+ ' from #tmp '

exec (@str)
drop table #tmp
go

--删除测试环境
drop table TF_POS
--结果
PRD_NO UPM1 UPM2
---------- ---------------------- ----------------------
1121000191 .0550 .1970
2310031002 2.5000 .0460

[BarryW]

如上面的资料数据我查找成：
PRD_NO UPM1(一月最后单价)
2310031002 0.04（或2.5）
1121000191 0.055


(2)变量OS_DD如果是查找如是2003-02

我就把2003年1月、2月的所以PRD_NO的最后单价查出来；
如上面的资料数据我查找成：
PRD_NO UPM1(一月单价) UPM2(二月单价)
2310031002 0.04（或2.5） 0.046
1121000191 0.055 00.197


(3)变量OS_DD如果是查找如是2003-03

我就把2003年1月、2月、3月的所以PRD_NO的最后单价查出来；
如上面的资料数据我查找成：
PRD_NO UPM1(一月单价) UPM2(二月单价)
2310031002 0.04（或2.5） 0.046
1121000191 0.055 00.197

.....
这样可以类推到year(OS_DD)从一月份到十二月份的最后单价

谢谢!

[BarryW]

-- zjcxc(: 邹建 :) ：“不会跨年?”

不好意思，这我没有说清楚:
查找的变量是：OS_DD
要求能满足以下就最好了：
(1)变量OS_DD如果是查找如是2003-01

我就只把2003年1月的所以PRD_NO的最后单价查出来；
如上面的资料数据我查找成：
PRD_NO UPM1(一月最后单价)
2310031002 0.04（或2.5）
1121000191 0.055


(2)变量OS_DD如果是查找如是2003-02

我就把2003年1月、2月的所以PRD_NO的最后单价查出来；
如上面的资料数据我查找成：
PRD_NO UPM1(一月单价) UPM2(二月单价)
2310031002 0.04（或2.5） 0.046
1121000191 0.055 00.197


(3)变量OS_DD如果是查找如是2003-03

我就把2003年1月、2月、3月的所以PRD_NO的最后单价查出来；
如上面的资料数据我查找成：
PRD_NO UPM1(一月单价) UPM2(二月单价)
2310031002 0.04（或2.5） 0.046
1121000191 0.055 00.197

.....
这样可以类推到year(OS_DD)从一月份到十二月份的最后单价
谢谢!