昨天问的一个SQL,大家说我写的不清楚,而且也没给分,其实真的是没法给分,分0-0,可能是因为我乱发帖子吧,被惩罚的,现在只好换一个ID,重新问了,拜托帮忙了。要求是这样,
基本的 Table :
Brand Area InvoiceDate DailySalesValue
AA North 2006/06/28 3000
AA North 2006/06/03 2000
AA North 2005/06/03 1000
AA North 2005/06/05 1000
BB East 2006/03/05 2000
BB East 2006/03/08 4000
BB East 2005/03/01 1000想实现的查询是
除显示 Brand Area InvoiceDate SalesValue 之外,还要显示一个MTD05和 MTD06
MTD05是取InvoiceDate中,比如InvocieDate是2006/6/28,那么MTD05就是取2005年的,6/1到/6月28日的 sum(Salesvaue)
MTD06是取 InvoiceDate中,取06年的6/1到/6月28日的 sum(Salesvaue)
所以上面的table要得出的值是: Brand Area InvoiceDate DailySalesValue MTD05 MTD06
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 0
AA North 2005/06/05 1000 2000 0
BB East 2006/03/05 2000 1000 2000
BB East 2006/03/08 4000 1000 6000
BB East 2005/03/01 1000 1000 0
基本的 Table :
Brand Area InvoiceDate DailySalesValue
AA North 2006/06/28 3000
AA North 2006/06/03 2000
AA North 2005/06/03 1000
AA North 2005/06/05 1000
BB East 2006/03/05 2000
BB East 2006/03/08 4000
BB East 2005/03/01 1000想实现的查询是
除显示 Brand Area InvoiceDate SalesValue 之外,还要显示一个MTD05和 MTD06
MTD05是取InvoiceDate中,比如InvocieDate是2006/6/28,那么MTD05就是取2005年的,6/1到/6月28日的 sum(Salesvaue)
MTD06是取 InvoiceDate中,取06年的6/1到/6月28日的 sum(Salesvaue)
所以上面的table要得出的值是: Brand Area InvoiceDate DailySalesValue MTD05 MTD06
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 0
AA North 2005/06/05 1000 2000 0
BB East 2006/03/05 2000 1000 2000
BB East 2006/03/08 4000 1000 6000
BB East 2005/03/01 1000 1000 0
case
when InvoiceDate>=cast('2005-01-01' as datetime) and InvoiceDate<cast('2006-01-01' as datetime) then DailySalesValue
else 0
end
,
MTD06=
case
when InvoiceDate>=cast('2006-01-01' as datetime) and InvoiceDate<cast('2007-01-01' as datetime) then DailySalesValue
else 0
endfrom table
InvoiceDate来的,就是InvoiceDate如果是2006/6/28,那么MTD05就是显示2005/06/01到2005/06/28区间内的DailysalesValue的sum值。
MTD06是06年的显示2006/06/01到2006/06/28区间内的DailysalesValue的sum值。
这个功能主要是SAles部门想要了解今年的这个月这个产品的销售value,和去年这个月区间的比较。
from table
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 0
AA North 2005/06/05 1000 2000 0
======================================================================
这个数据和楼主描述的不对啊
是这样的吧? AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 3000
AA North 2005/06/03 1000 1000 3000
AA North 2005/06/05 1000 2000 4000看起来好混乱
(
Brand varchar(2),
Area varchar(5),
InvoiceDate varchar(10),
DailySalesValue int
)
insert into @t
select 'AA','North','2006/06/28',3000 union all
select 'AA','North','2006/06/03',2000 union all
select 'AA','North','2005/06/03',1000 union all
select 'AA','North','2005/06/05',1000 union all
select 'BB','Ease','2006/03/05',2000 union all
select 'BB','Ease','2006/03/08',4000 union all
select 'BB','Ease','2005/03/01',1000
select
*,
(select sum(DailySalesValue) from @t where InvoiceDate between '2005/01/01' and '2005/'+right(a.InvoiceDate,5) and Brand=a.Brand and Area=a.Area) as [MTD05],
isnull((select sum(DailySalesValue) from @t where InvoiceDate between '2006/01/01' and '2006/'+right(a.InvoiceDate,5) and Brand=a.Brand and Area=a.Area),0) as [MTD06]
from @t a
/*
Brand Area InvoiceDate DailySalesValue MTD05 MTD06
----- ----- ----------- --------------- ----------- -----------
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 2000
AA North 2005/06/05 1000 2000 2000
BB Ease 2006/03/05 2000 1000 2000
BB Ease 2006/03/08 4000 1000 6000
BB Ease 2005/03/01 1000 1000 0
*/
(
Brand varchar(2),
Area varchar(5),
InvoiceDate datetime,
DailySalesValue int
)
insert into @t
select 'AA','North','2006/06/28',3000 union all
select 'AA','North','2006/06/03',2000 union all
select 'AA','North','2005/06/03',1000 union all
select 'AA','North','2005/06/05',1000 union all
select 'BB','Ease','2006/03/05',2000 union all
select 'BB','Ease','2006/03/08',4000 union all
select 'BB','Ease','2005/03/01',1000
select
*,
isnull((select sum(DailySalesValue) from @t where InvoiceDate between '2005/01/01' and '2005/'+right(convert(varchar(10),a.InvoiceDate,111),5) and Brand=a.Brand and Area=a.Area),0) as [MTD05],
(case when InvoiceDate>='2006/1/1' then
isnull((select sum(DailySalesValue) from @t where InvoiceDate between '2006/01/01' and '2006/'+right(convert(varchar(10),a.InvoiceDate,111),5) and Brand=a.Brand and Area=a.Area),0)
else 0 end) as [MTD06]
from @t a/*
Brand Area InvoiceDate DailySalesValue MTD05 MTD06
----- ----- ------------------------------------------------------ --------------- ----------- -----------
AA North 2006-06-28 00:00:00.000 3000 2000 5000
AA North 2006-06-03 00:00:00.000 2000 1000 2000
AA North 2005-06-03 00:00:00.000 1000 1000 0
AA North 2005-06-05 00:00:00.000 1000 2000 0
BB Ease 2006-03-05 00:00:00.000 2000 1000 2000
BB Ease 2006-03-08 00:00:00.000 4000 1000 6000
BB Ease 2005-03-01 00:00:00.000 1000 1000 0*/
(
Brand varchar(2),
Area varchar(5),
InvoiceDate varchar(10),
DailySalesValue int
)
insert into @t
select 'AA','North','2006/06/28',3000 union all
select 'AA','North','2006/06/03',2000 union all
select 'AA','North','2005/06/03',1000 union all
select 'AA','North','2005/06/05',1000 union all
select 'BB','Ease','2006/03/05',2000 union all
select 'BB','Ease','2006/03/08',4000 union all
select 'BB','Ease','2005/03/01',1000select * into #temp from (select Brand,Area,InvoiceDate,DailySalesValue,InvoiceDate as M1,InvoiceDate as M2 from @t ) Tselect C.*,isnull(D.MTD05,0) as MTD05 , isnull(E.MTD06,0) as MTD06 from @t C
left join (select A.InvoiceDate,sum(B.DailySalesValue) as MTD05 from @t A,#temp B where convert(datetime,M1)<=dateadd(yy,-1,convert(datetime,A.InvoiceDate))
and convert(datetime,M1)>=convert(datetime,(convert(char(06),dateadd(yy,-1,convert
(datetime,A.InvoiceDate)),112)+'01')) group by A.InvoiceDate) D
on C.InvoiceDate=D.InvoiceDate
left join (select F.InvoiceDate,sum(G.DailySalesValue) as MTD06 from @t F,#temp G where convert(char(06),convert(datetime,F.InvoiceDate),112)=convert(char(06),convert(datetime,M2),112) and convert(datetime,M2)<=convert(datetime,F.InvoiceDate) group by F.InvoiceDate ) E
on C.InvoiceDate=E.InvoiceDatedrop table #temp/*The result:*/
--MTD06取當年當月小於當日的和
--MTD05取去年當月小於當日的和Brand Area InvoiceDate DailySalesValue MTD05 MTD06
----- ----- ----------- --------------- ----------- -----------
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 0 1000
AA North 2005/06/05 1000 0 2000
BB Ease 2006/03/05 2000 1000 2000
BB Ease 2006/03/08 4000 1000 6000
BB Ease 2005/03/01 1000 0 1000
(Brand Varchar(10),
Area Varchar(10),
InvoiceDate DateTime,
DailySalesValue Int)
Insert TEST Select 'AA', 'North', '2006/06/28', 3000
Union All Select 'AA', 'North', '2006/06/03', 2000
Union All Select 'AA', 'North', '2005/06/03', 1000
Union All Select 'AA', 'North', '2005/06/05', 1000
Union All Select 'BB', 'East', '2006/03/05', 2000
Union All Select 'BB', 'East', '2006/03/08', 4000
Union All Select 'BB', 'East', '2005/03/01', 1000
GO
Select
Brand,
Area,
Convert(Varchar,InvoiceDate,111) As InvoiceDate,
DailySalesValue,
(Select SUM(DailySalesValue) From TEST Where Brand=A.Brand And InvoiceDate Between '2005/'+Rtrim(Month(A.InvoiceDate))+'/01' And '2005/'+Convert(Varchar(5),A.InvoiceDate,101)) As MTD05,
(Case Year(InvoiceDate) When 2006 Then (Select SUM(DailySalesValue) From TEST Where Brand=A.Brand And InvoiceDate Between '2006/'+Rtrim(Month(A.InvoiceDate))+'/01' And '2006/'+Convert(Varchar(5),A.InvoiceDate,101)) Else 0 End) As MTD06
From TEST A
GO
Drop Table TEST
--Result
/*
Brand Area InvoiceDate DailySalesValue MTD05 MTD06
AA North 2006/06/28 3000 2000 5000
AA North 2006/06/03 2000 1000 2000
AA North 2005/06/03 1000 1000 0
AA North 2005/06/05 1000 2000 0
BB East 2006/03/05 2000 1000 2000
BB East 2006/03/08 4000 1000 6000
BB East 2005/03/01 1000 1000 0
*/
樓主要的MTD05不管怎樣都是05年的,MTD06是06年的??
LouisXIV(夜游神) '2005/01/01' and '2005/'+right(convert(varchar(10),a.InvoiceDate,111),5) and Brand=a.Brand and
這麼寫肯定有問題的,他是要統計當月的,你卻是從1月開始統計的。另外得到"03/08"這種格式,可以直接用101,不需要用111然後再用right函數取
SELECT brand,area,invoicedate,dailysalesvalue,
mtd05=SELECT SUM(dailysalevalue) FROM tb
WHERE invoicedate BETWEEN CONVERT(DATETIME,(CONVERT(CHAR(8),DATEADD(YEAR,-1,invoicedate))+'01')) AND DATEADD(YY,-1,invoicedate),
mtd06=SELECT SI,(dailysalevalue) FROM tb
WHERE invoicedate BETWEEN CONVERT(DATETIME,(CONVERT(CHAR(8),DATEADD(YEAR,-1,invoicedate))+'01')) AND invoicedate
FROM tb
SELECT brand,area,invoicedate,dailysalesvalue,
mtd05=SELECT SUM(dailysalevalue) FROM tb
WHERE invoicedate BETWEEN CONVERT(DATETIME,(YEAR(DATEADD(YEAR,-1,invoicedate))*10000+MONTH(DATEADD(YEAR,-1,invoicedate))*100+1)) AND DATEADD(YY,-1,invoicedate),
mtd06=SELECT SUM(dailysalevalue) FROM tb
WHERE invoicedate BETWEEN CONVERT(DATETIME,(YEAR(invoicedate)*10000+MONTH(invoicedate)*100+1)) AND invoicedate
FROM tb
(
brand varchar(2) ,
area varchar(20),
invoice datetime,
dailysalesvalue int
)insert into #
select 'AA', 'North', '2006/06/28' , 3000 union
select 'AA', 'North', '2006/06/03' , 2000 union
select 'AA', 'North', '2005/06/03' , 1000 union
select 'AA', 'North', '2005/06/05' , 1000 union
select 'BB', 'East' , '2006/03/05' , 2000 union
select 'BB', 'East' , '2006/03/08' , 4000 union
select 'BB', 'East' , '2005/03/01' , 1000select * from #select cast('2006-1-1' as datetime)
select cast('2006-01-01' as datetime)
select cast('2006-01-1' as datetime)
select cast('2006-1-01' as datetime)select cast( '2005-'+ cast(month(a.invoice) as varchar(20))+'-01' as datetime)
from # aselect a.* ,
(select sum(isnull(DailySalesValue,0))
from # where
invoice
between cast( '2005-'+ cast(month(a.invoice) as varchar(20))+'-01' as datetime)
and cast( '2005-'+ cast(month(a.invoice) as varchar(20)) +'-' +cast(day(a.invoice) as varchar(20)) as datetime) ) as MTD05 ,
(case year(invoice) when 2005 then 0
when 2006 then
(select sum(isnull(DailySalesValue,0))
from # where
invoice
between cast( '2006-'+ cast(month(a.invoice) as varchar(20))+'-01' as datetime)
and cast( '2006-'+ cast(month(a.invoice) as varchar(20)) + '-'+cast(day(a.invoice) as varchar(20)) as datetime) ) end) as MTD06from # a
(
Brand varchar(2),
Area varchar(5),
InvoiceDate varchar(10),
DailySalesValue int
)
insert into #t
select 'AA','North','2006/06/28',3000 union all
select 'AA','North','2006/06/03',2000 union all
select 'AA','North','2005/06/03',1000 union all
select 'AA','North','2005/06/05',1000 union all
select 'BB','Ease','2006/03/05',2000 union all
select 'BB','Ease','2006/03/08',4000 union all
select 'BB','Ease','2005/03/01',1000
select *,
(select isnull(sum(DailySalesValue),0) from #t where InvoiceDate between cast('2005/'+cast(month(invoicedate) as varchar)+'/01' as datetime)
and cast('2005/'+right(a.InvoiceDate,5) as datetime) and Brand=a.Brand and Area=a.Area ) as [MTD05],
[MTD06]=isnull(case when year(invoicedate)=2006 then isnull((select sum(DailySalesValue) from #t where InvoiceDate between cast('2006/'+cast(month(invoicedate) as varchar)+'/01' as datetime) and
cast('2006/'+right(a.InvoiceDate,5) as datetime) and Brand=a.Brand and Area=a.Area),0) end ,0)
from #t a