【蝴蝶廢墟】

問題：時間、日期欄位使用TEXT，需要判斷昨天下午3點到今天上午11點的資料計算數量，然後顯示備註

內容如下：

希望顯示的樣子：

CREATE TABLE [表格合併查詢](
[編號] [int] IDENTITY(1,1) NOT NULL,
[日期] [text] NOT NULL,
[時間] [text] NOT NULL,
[品名] [nvarchar](50) NOT NULL,
[備註] [nvarchar](50) NULL,
CONSTRAINT [PK_表格合併查詢] PRIMARY KEY CLUSTERED
(
[編號] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO 表格合併查詢 VALUES ('20140226', '16:00:00', 'XX','XXX')
INSERT INTO 表格合併查詢 VALUES ('20140226', '20:00:00', 'OO','XXXX')
INSERT INTO 表格合併查詢 VALUES ('20140206', '23:00:00', 'oo',NULL)
INSERT INTO 表格合併查詢 VALUES ('20140206', '23:51:51', 'TT',NULL)
INSERT INTO 表格合併查詢 VALUES ('20140207', '06:00:00', 'XX',NULL)
INSERT INTO 表格合併查詢 VALUES ('20140207', '08:00:00', '88','446')
INSERT INTO 表格合併查詢 VALUES ('20140227', '10:55:00', '11','22')

GO

select a.品名,數量,備註 from
(
select 品名,count(品名) as 數量
from [表格合併查詢]
where ((DATEDIFF(day, CONVERT(VARCHAR(10), [日期], 110), GETDATE()) = 1) and CONVERT(VARCHAR(10), 時間, 114) > CONVERT(VARCHAR(10), '15:00:00', 114))
or (((DATEDIFF(day, CONVERT(VARCHAR(10), [日期], 110), GETDATE()) = 0) and CONVERT(VARCHAR(10), 時間, 114) < CONVERT(VARCHAR(10), '11:00:00', 114)))
GROUP BY 品名) as a inner join
(SELECT 品名,
(
STUFF( --刪掉最前面的-
(
SELECT '-' + 備註
FROM [表格合併查詢] T2
WHERE T2.品名 = T1.品名
FOR XML PATH('') --合併表格
)
, 1, 1, ''
)
) AS 備註
FROM [表格合併查詢] T1
GROUP BY 品名) as b ON a.品名=b.品名