請問各位前輩在SQL Server 2000有個Tabel其中的欄位是建檔日期，DataType為DateTime。
請問要如何透過這個欄位統計出某月份或某季或某期間的星期一至星期六的交易筆數及某時段的交易筆數？
報表結果：
星期一：68筆
星期二：162筆
...
...
...
星期六：24筆
08:00~09:59：XX筆
10:00~12:0：XX筆
...
...
...
22：00~24：XX筆

給你一個方向
SELECT
convert(char(13),X_02,20) TIM , SUM(X_01) SUM
FROM MSTAB
GROUP BY convert(char(13),X_02,20)

------
What do we live for if not to make life less difficult for each other?

Try this:
Select Datepart(weekday, date_created) as wday, (Datepart(hour, date_created) /2) as hours_2, count(*) as num_of_trans
from transaction_table
where date_created between @start_date and @end_date
group by wday, hours_2

The problem you might have with this SQL is that you have to find some way to display the wday and hours_2 fields
because Datepart() function returns integer.

===================引 用 wwwbbs 文 章===================
請問各位前輩在SQL Server 2000有個Tabel其中的欄位是建檔日期，DataType為DateTime。
請問要如何透過這個欄位統計出某月份或某季或某期間的星期一至星期六的交易筆數及某時段的交易筆數？
報表結果：
星期一：68筆
星期二：162筆
...
...
...
星期六：24筆
08:00~09:59：XX筆
10:00~12:0：XX筆
...
...
...
22：00~24：XX筆

------
===波士頓夜未眠===
What a wonderful world!!
Jazz up the world with jazz!!
==================

When I am not programming...
you can find me here:
http://www.holy-war.de/EN/World3/bin/?advertiser=63190

謝謝二位大大，你們提供的方法都不行，目前的思路是覺得只能用分批查詢才能解決，不知有無更Smart的方法？

Then, maybe I misunderstood your output format.
If you need to count the total record of each weekday and the total record of each 2-hour period separately, and just want to use one query, then you need "Union"
Try again with this:

Select Datepart(weekday, date_created) as period, count(*) as num_of_trans
from transaction_table
where date_created between @start_date and @end_date
group period
union
Select (Datepart(hour, date_created) /2) as period, count(*) as num_of_trans
from transaction_table
where date_created between @start_date and @end_date
group by period

Hope this is what you want.

===================引 用 wwwbbs 文 章===================
謝謝二位大大，你們提供的方法都不行，目前的思路是覺得只能用分批查詢才能解決，不知有無更Smart的方法？

------
===波士頓夜未眠===
What a wonderful world!!
Jazz up the world with jazz!!
==================

When I am not programming...
you can find me here:
http://www.holy-war.de/EN/World3/bin/?advertiser=63190

您好
您的需求可以使用下列語法處理。
SELECT { fn DAYOFWEEK(DT_CREATE) } AS dayweek
FROM TABLE
WHERE SUBSTRING(CONVERT(CHAR(10), DT_CREATE, 112), 1, 6) = '200801'
其中 { fn DAYOFWEEK(DT_CREATE) } 是用來取星期幾
CONVERT(CHAR(10), DT_CREATE, 112) 是用來將日期型態轉換成字串型態
試試看吧！

------
Pillar Wang

您好
請問大大是否有測試過可行嗎？

------
Pillar Wang

基本上樓上各位前輩都提供了很多SQL中使用各類FUNCTION的技巧, 不過因為我不是用M$ SQL, 這些FUNCTION根本不管用, 所以我都會用很基本的寫法(假設今天我們是用Delphi來寫程式, 而不是直接在SQL ANSLY...下操作)
SELECT * FROM TABLE1 WHERE DATE1>= :DATE1 AND DATE2<= DATE2
然後由程式提供區間的DATE1, DATE2 兩個值送入 SQL 內不就可以判斷?

編輯記錄

用DELPHI把参数 送进存储过程的做...

------
After three days without programming, life becomes meaningless.