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
|
 |
 |
DeanCovey
Posts: 22
|
| Posted: 02/20/2007, 3:14 AM |
|
Have you tested your data inside MS-Access? I'd try writing this query in Access and prove to yourself the that your data is correct and you are able to get the results you want.
If that's try, you can use this new query as your data source instead of trying to write your own sql statement.
|
|
|
|
sirichai
Posts: 22
|
| Posted: 02/21/2007, 7:22 PM |
|
Hi DeanCovey,
Thanks for your advice. Now It success for virtual SQL. I wrote in ACCESS after that use in Virtual SQL.
Thanks,
Sirichai
|
|
|
|
|
