sirichai
Posts: 22
|
| Posted: 02/20/2007, 1:05 AM |
|
Hi,
Anyone can help me about virtual sql statement. My database is MSAccess. My Sql statement is
-------------------------------------------------
SELECT TECHNICIAL_ALLOTED AS SERVICE_PROVIDER,
(SELECT SPNAME FROM SERVICE_PROVIDER WHERE SPCODE=TECHNICIAL_ALLOTED
) AS SP_NAME
,YEAR(DATE_CALLED) AS YEARNO ,MONTH(DATE_CALLED) AS MONTHNO,
SUM(((VISIT_FROM_DT - DATE_CALLED) * 24 + (VISIT_FROM_TIME - TIME_CALLED)
)) AS TOTAL_TIME_VISIT_GART , COUNT(JOB_SHEET.JOB_SHEET_NO) AS NO_OF_JOBS,
SUM(CASE WHEN VISIT_FROM_TIME > TIME_CALLED THEN VISIT_FROM_TIME > TIME_CALLED ELSE
24 + VISIT_FROM_TIME - TIME_CALLED END) AS TOTAL_VISIT_TIME
FROM JOB_SHEET_VISIT_DETAIL INNER JOIN JOB_SHEET ON
JOB_SHEET_VISIT_DETAIL.JOB_SHEET_NO = JOB_SHEET.JOB_SHEET_NO AND JOB_SHEET.JOB_SHEET_NO = JOB_SHEET_VISIT_DETAIL.JOB_SHEET_NO
WHERE TECHNICIAL_ALLOTED='{SPcode}' and YEAR(DATE_CALLED)={Yearno}
GROUP BY TECHNICIAL_ALLOTED,YEAR(DATE_CALLED) ,MONTH(DATE_CALLED)
ORDER BY TECHNICIAL_ALLOTED,YEAR(DATE_CALLED) DESC ,MONTH(DATE_CALLED)
----------------------------------------------
The problem is in field
SUM(CASE WHEN VISIT_FROM_TIME > TIME_CALLED THEN VISIT_FROM_TIME > TIME_CALLED ELSE
24 + VISIT_FROM_TIME - TIME_CALLED END) AS TOTAL_VISIT_TIME
ALL fields in statement data type are DATE/TIME.
ANYONE PLEASE HELP ME.
Regards,
Sirichai
|
 |
 |
|
