日期输出

来源：互联网发布：在法国好用淘宝吗编辑：程序博客网时间：2024/04/30 04:53

一表(tb_xmdata)结构如下：

项目起始日期截止日期
--------------------------------------
项目一 2010-02-01 2010-02-05
项目二 2010-02-06 2010-02-08
项目三 2010-02-09 2010-02-20
项目四 2010-02-21 2010-02-28
项目五 2010-03-01 2010-03-05
项目六 2010-03-06 2010-03-15
项目七 2010-03-16 2010-03-19
......

另一表(tb_xm)
项目
------
项目一
项目二
项目三
项目四
项目五
项目六
项目七
项目八
项目九
项目十
......

希望得到下面的查询结果：
当查询某个日期范围内的数据时生成这样的查询结果
序号项目     2010年2月                                         2010年3月
-----------------------------------------------------------------------------------
           1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

1    项目一 ---------
2    项目二           ------
3    项目三                   -----------------------------------
4    项目四                                                           -----------------------
......

--这个厉害

SET NOCOUNT ON
IF OBJECT_ID('tb_xmdata') IS NOT NULL DROP TABLE tb_xmdata
IF OBJECT_ID('tb_xm') IS NOT NULL DROP TABLE tb_xm
IF OBJECT_ID('TEMPDB..#DATE') IS NOT NULL DROP TABLE #DATE
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL DROP TABLE #TEMP
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
GO
CREATE FUNCTION FUN_MU(@BDATE DATETIME,@EDATE DATETIME,@YEAR INT,@MONTH INT)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @BDAY INT,@EDAY INT,@STR VARCHAR(200),@I INT
IF @BDATE<=CAST(@YEAR AS VARCHAR(4))+'-'+CAST(@MONTH AS VARCHAR(2))+'-01'
SELECT @BDAY=1
ELSE
SELECT @BDAY=DAY(@BDATE)
IF @EDATE>=CAST(@YEAR AS VARCHAR(4))+'-'+CAST(@MONTH AS VARCHAR(2))+'-'
+CASE WHEN @MONTH IN (1,3,5,7,8,10,12) THEN '31' WHEN @MONTH=2 AND @YEAR%4=0 THEN '29' WHEN @MONTH=2 AND @YEAR%4<>0 THEN '28' ELSE '30' END
SELECT @EDAY=CASE WHEN @MONTH IN (1,3,5,7,8,10,12) THEN '31' WHEN @MONTH=2 AND @YEAR%4=0 THEN '29' WHEN @MONTH=2 AND @YEAR%4<>0 THEN '28' ELSE '30' END
ELSE
SELECT @EDAY=DAY(@EDATE)

SELECT @I=1,@STR=''
WHILE @I<=CASE WHEN @MONTH IN (1,3,5,7,8,10,12) THEN '31' WHEN @MONTH=2 AND @YEAR%4=0 THEN '29' WHEN @MONTH=2 AND @YEAR%4<>0 THEN '28' ELSE '30' END
BEGIN
SELECT @STR=@STR+CASE WHEN @I>= @BDAY AND @I<=@EDAY THEN ' '+RIGHT('00'+CAST(@I AS VARCHAR(2)),2) ELSE '---' END
SET @I=@I+1
END
RETURN @STR
END
GO
CREATE TABLE tb_xmdata([项目] VARCHAR(10),[起始日期] DATETIME,[截止日期] DATETIME)
INSERT INTO tb_xmdata
SELECT '项目一','2010-02-01','2010-02-05' UNION ALL
SELECT '项目二','2010-02-06','2010-02-08' UNION ALL
SELECT '项目三','2010-02-09','2010-02-20' UNION ALL
SELECT '项目四','2010-02-21','2010-03-04' UNION ALL
SELECT '项目五','2010-03-01','2010-03-05' UNION ALL
SELECT '项目六','2010-03-06','2010-03-15' UNION ALL
SELECT '项目七','2010-03-16','2010-03-19'
CREATE TABLE TB_XM([项目] VARCHAR(10))
INSERT INTO TB_XM
SELECT '项目一' UNION ALL
SELECT '项目二' UNION ALL
SELECT '项目三' UNION ALL
SELECT '项目四' UNION ALL
SELECT '项目五' UNION ALL
SELECT '项目六' UNION ALL
SELECT '项目七' UNION ALL
SELECT '项目八' UNION ALL
SELECT '项目九' UNION ALL
SELECT '项目十'

--SELECT * FROM TB_XMDATA

--SELECT * FROM TB_XM
DECLARE @BDATE DATETIME,@EDATE DATETIME,@I INT
SELECT @BDATE=MIN([起始日期]),@EDATE=MAX([截止日期]),@I=0 FROM tb_xmdata
CREATE TABLE #DATE(ID INT IDENTITY(1,1),[YEAR] INT,[MONTH] INT,MONSTR VARCHAR(45),DATESTR VARCHAR(100))
WHILE YEAR(DATEADD(MONTH,@I,@BDATE))<YEAR(@EDATE) OR (YEAR(DATEADD(MONTH,@I,@BDATE))=YEAR(@EDATE) AND MONTH(DATEADD(MONTH,@I,@BDATE))<=MONTH(@EDATE))
BEGIN
INSERT INTO #DATE([YEAR],[MONTH],[MONSTR],[DATESTR])
SELECT YEAR(DATEADD(MONTH,@I,@BDATE)),MONTH(DATEADD(MONTH,@I,@BDATE))
,SPACE(35)+CAST(YEAR(DATEADD(MONTH,@I,@BDATE)) AS VARCHAR(4))+'年'+CAST(MONTH(DATEADD(MONTH,@I,@BDATE)) AS VARCHAR(2))+'月'
,' 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28'
+CASE WHEN MONTH(DATEADD(MONTH,@I,@BDATE)) IN (1,3,5,7,8,10,12) THEN ' 29 30 31' WHEN MONTH(DATEADD(MONTH,@I,@BDATE))=2 AND YEAR(DATEADD(MONTH,@I,@BDATE))%4=0 THEN ' 29' WHEN MONTH(DATEADD(MONTH,@I,@BDATE))=2 AND YEAR(DATEADD(MONTH,@I,@BDATE))%4<>0 THEN '' ELSE ' 30' END
SET @I=@I+1
END
--SELECT * FROM #DATE

SELECT CLASS=T2.[项目] ,BDATE=T1.[起始日期],EDATE=T1.[截止日期],T3.MONSTR,[YEAR]=T3.[YEAR] ,[MONTH]=T3.[MONTH]
INTO #TEMP
FROM TB_XMDATA T1
RIGHT JOIN TB_XM T2 ON T1.[项目] =T2.[项目]
FULL JOIN #DATE T3 ON
CAST(CAST(T3.[YEAR] AS VARCHAR(4))+'-'+CAST(T3.[MONTH] AS VARCHAR(2))+'-01' AS DATETIME)
BETWEEN CAST(CAST(YEAR(T1.[起始日期]) AS VARCHAR(4))+'-'+CAST(MONTH(T1.[起始日期]) AS VARCHAR(2))+'-01' AS DATETIME)
AND CAST(CAST(YEAR(T1.[截止日期]) AS VARCHAR(4))+'-'+CAST(MONTH(T1.[截止日期]) AS VARCHAR(2))+'-01' AS DATETIME)
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL='SELECT ''项目''='''''
SELECT @SQL=@SQL+'
,'''+MONSTR+'''=CAST(MAX(CASE WHEN ID='+CAST(ID AS VARCHAR(20))+' THEN DATESTR ELSE '''' END) AS VARCHAR(100))'
FROM #DATE
SELECT @SQL=@SQL+'
FROM #DATE
UNION ALL
SELECT CLASS'

SELECT @SQL=@SQL+'
,'''+MONSTR+'''=MAX(CASE WHEN MONSTR='''+MONSTR+''' THEN
CAST(dbo.FUN_MU(BDATE,EDATE,[YEAR],[MONTH]) AS VARCHAR(100))
ELSE '''' END)'
FROM #DATE
SELECT @SQL=@SQL+'
FROM #TEMP
GROUP BY CLASS '

EXEC (@SQL)

/*
项目                                            2010年2月                                                                                              2010年3月
---------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
            01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28                  01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
项目八
项目二        --------------- 06 07 08------------------------------------------------------------
项目九
项目六                                                                                                             --------------- 06 07 08 09 10 11 12 13 14 15------------------------------------------------
项目七                                                                                                             --------------------------------------------- 16 17 18 19------------------------------------
项目三        ------------------------ 09 10 11 12 13 14 15 16 17 18 19 20------------------------
项目十
项目四        ------------------------------------------------------------ 21 22 23 24 25 26 27 28                  01 02 03 04---------------------------------------------------------------------------------
项目五                                                                                                              01 02 03 04 05------------------------------------------------------------------------------
项目一         01 02 03 04 05---------------------------------------------------------------------

*/

--SELECT '项目'=''
--,'                                   2010年2月'=CAST(MAX(CASE WHEN ID=1 THEN DATESTR ELSE '' END) AS VARCHAR(100))
--,'                                   2010年3月'=CAST(MAX(CASE WHEN ID=2 THEN DATESTR ELSE '' END) AS VARCHAR(100))
--FROM #DATE
--UNION ALL
--SELECT CLASS
--,'                                   2010年2月'=MAX(CASE WHEN MONSTR='                                   2010年2月' THEN
--CAST(dbo.FUN_MU(BDATE,EDATE,[YEAR],[MONTH]) AS VARCHAR(100))
--ELSE '' END)
--,'                                   2010年3月'=MAX(CASE WHEN MONSTR='                                   2010年3月' THEN
--CAST(dbo.FUN_MU(BDATE,EDATE,[YEAR],[MONTH]) AS VARCHAR(100))
--ELSE '' END)
--SELECT * FROM #TEMP
--GROUP BY CLASS