以前碰到的一个表，存储时间按照周为单位存储，而却需要按照天为单位使用。为了减少程序复杂度，便增加了多层查询，提高Sql查询结果可用性。
select
(week_no-week_start)*7+week_day-week_day_start pp,
dateadd(day,(week_no-week_start)*7+week_day-week_day_start,Month) the_day,
*
from
(
select
case DatePart(w,Month) when 7 then 1 else DatePart(w,Month)+1 end as week_day_start,
1 week_start,
*
from
(
SELECT
[CompanyID]
,[EmployeeNO]
,[Year]
,[Month]
,[WEEK_NO]
,[Order_NO]
,[Sell_NO]
,[WorkCode]
,1 as WEEK_DAY
,[WorkHours_MON]
,[H_MON] as H
,[M_MON] as M
,[GroupCode]
FROM
[CardTable]
union
SELECT
[CompanyID]
,[EmployeeNO]
,[Year]
,[Month]
,[WEEK_NO]
,[Order_NO]
,[Sell_NO]
,[WorkCode]
,2 as WEEK_DAY
,[WorkHours_TUE] as WorkHours
,[H_TUE] as H
,[M_TUE] as M
,[GroupCode]
FROM
[CardTable]
union
SELECT
[CompanyID]
,[EmployeeNO]
,[Year]
,[Month]
,[WEEK_NO]
,[Order_NO]
,[Sell_NO]
,[WorkCode]
,3 as WEEK_DAY
,[WorkHours_WED] as WorkHours
,[H_WED] as H
,[M_WED] as M
,[GroupCode]
FROM
[CardTable]
union
SELECT
[CompanyID]
,[EmployeeNO]
,[Year]
,[Month]
,[WEEK_NO]
,[Order_NO]
,[Sell_NO]
,[WorkCode]
,4 as WEEK_DAY
,[WorkHours_THU] as WorkHours
,[H_THU] as H
,[M_THU] as M
,[GroupCode]
FROM
[CardTable]
union
SELECT
[CompanyID]
,[EmployeeNO]
,[Year]
,[Month]
,[WEEK_NO]
,[Order_NO]
,[Sell_NO]
,[WorkCode]
,5 as WEEK_DAY
,[WorkHours_FRI] as WorkHours
,[H_FRI] as H
,[M_FRI] as M
,[GroupCode]
FROM
[CardTable]
union
SELECT
[CompanyID]
,[EmployeeNO]
,[Year]
,[Month]
,[WEEK_NO]
,[Order_NO]
,[Sell_NO]
,[WorkCode]
,6 as WEEK_DAY
,[WorkHours_SAT] as WorkHours
,[H_SAT] as H
,[M_SAT] as M
,[GroupCode]
FROM
[CardTable]
union
SELECT
[CompanyID]
,[EmployeeNO]
,[Year]
,[Month]
,[WEEK_NO]
,[Order_NO]
,[Sell_NO]
,[WorkCode]
,7 as WEEK_DAY
,[WorkHours_SUN] as WorkHours
,[H_SUN] as H
,[M_SUN] as M
,[GroupCode]
FROM
[CardTable]
) temp1
-- where
-- week_no=6
)temp2
--where not( week_no=week_start and week_day<week_day_start)
order by pp