SQL server语言 - 需要SQL查询格式的前11个月的数据

Question

我正在运行报告，我像这样传递参数'Jan-13'或' may -13'或' june -12'，它可能是任何东西。

select CardName,CardCode ,DLN1.Quantity 
from ODLN inner join DLN1 on odln.DocEntry = ODLN.DocEntry 
where ODLN.DocDate = 'Jan-13' (this doc date is the datetime format) in report they will chose Jan-13 

我需要过去11个月的数量输出，当前月份为Jan-13

Cardname    CardCode  Jan-13 Dec-12 Nov-12 Oct-12 Sep-12  aug-12  jul-12   jun-12  may-12   Apr -12  mar-12 feb-12   
EFIGLobal     A2       12     10      12      10    8       12      3       2        12       11       4      6

Answer 1

sqlserver-2008+

先创建这个测试表

create table test(CardName varchar(20),CardCode int, Quantity int, DocDate datetime)
insert test values('ab', 1, 2, '2013-01-01 20:30')
insert test values('ab', 1, 2, '2012-05-01 20:30')
insert test values('abc', 3, 4, '2012-03-01 20:30')
go

脚本如下:

-- @a is your input
declare @a char(6) = 'Jan-13'
declare @to datetime = dateadd(mm, 1, convert(datetime, '01-'+@a, 16))
declare @from datetime = dateadd(yy, -1, @to)
declare @col varchar(200)
;with dates as
(
select @to-1 m
union all
select dateadd(mm, -1, m)
from dates
where m > dateadd(mm, 1, @from)
)
select @col = coalesce(@col + ',', '') + '['+right(stuff(convert(varchar(9), m, 6), 7, 1, '-'), 6)+']' from dates
declare @sql nvarchar(max) =
'
;with x as
(
select CardName,CardCode ,Quantity, right(stuff(convert(varchar(9), docdate, 6), 7, 1, ''-''), 6) dd
from 
/*
-- use this in your case remove the comments markers (/* and */)
(
select CardName,CardCode ,DLN1.Quantity 
from ODLN inner join DLN1 on odln.DocEntry = ODLN.DocEntry 
)
*/
test
where DocDate >= @from and DocDate < @to
)
select * from x
pivot (sum(Quantity) FOR [dd] IN ('+@col+')) AS pvt '
EXEC sp_executesql @sql, N'@from datetime, @to datetime', @from, @to

结果:

CardName CardCode   Jan-13 Dec-12 Nov-12 Oct-12 Sep-12 Aug-12 Jul-12 Jun-12 May-12 Apr-12 Mar-12 Feb-12
ab       1          2      NULL   NULL   NULL   NULL   NULL   NULL   NULL   2      NULL   NULL   NULL
abc      3          NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   4      NULL

Answer 2

不要在这里寻找没有bug的查询，因为您还没有提供足够的信息和示例数据。开始指出1 = 1，

Declare @i varchar='Jan-13'
declare @upto int=11
Declare @FromDate date=convert(varchar(12),'1-'+'Jan-13',120)
Declare @Todate date =dateadd(month,@upto,@FromDate)
select convert(char(6),@FromDate,107) ,@ToDate
Declare @StringDate varchar(2000)=''
select @StringDate= stuff((select ','+'['+convert(char(6),ODLN.DocDate ,107)DocDate+']'
from ODLN inner join DLN1 on odln.DocEntry = ODLN.DocEntry 
where ODLN.DocDate between between @FromDate and @Todate
for xml path('')),1,1,'')
--@StringDate will have value like [Jan-13],[Dec-12] and so on
Declare @qry varchar(max)=''

set @qry='select CardName,'+@StringDate+'  from
(select CardName,CardCode ,DLN1.Quantity,convert(char(6),ODLN.DocDate ,107)DocDate
from ODLN inner join DLN1 on odln.DocEntry = ODLN.DocEntry 
where ODLN.DocDate between between '+@FromDate+' and '+@Todate+')tbl
pivot( max(Quantity) for DocDate in('+@StringDate+'))pvt'
exec @qry