I need a little help getting the data back that i need from the SQl server Basically i need to add all the values of pages recorded for one user on unique instrument numbers. im getting at error that say incorrect syntax near ')' with the last ) highlighted. Any help would be greatly appreciated SELECT SUM(T1."NO_PAGES_RECORDED") AS Expr1 FROM(SELECT DISTINCT(T1."INSTRUMENT_NUMBER") FROM "Anthem"."dbo"."OPR_PHYS_DOCUMENTS" T1, "Anthem"."dbo"."USERS" T2, "Anthem"."dbo"."OPR_LOGI_DOCUMENTS" T3 LEFT OUTER JOIN "Anthem"."dbo"."AUDIT_HEADER" T4 on T3."GLOBAL_ID" = T4."GLOBAL_ID" where T2."USER_ID" = T4."USER_ID" and T3."INSTRUMENT_NUMBER" = T1."INSTRUMENT_NUMBER" and T1."DATE_RECEIVED" between '2013-04-17 00:00:00.000' and '2013-04-17 23:00:00.000')
Your sum is referencing a table in your embedded statement I think is the problem. Not 100% sure on that, but maybe take the embedded statement, put it in a temp table, then do a sum on that temp table. Not to mention, it looks like you are trying to get the total number of pages, but your embedded select is returning instrument numbers.
Try this, I think it may work. SELECT SUM(T1."NO_PAGES_RECORDED") AS Expr1 FROM "Anthem"."dbo"."OPR_PHYS_DOCUMENTS" T1, "Anthem"."dbo"."USERS" T2, "Anthem"."dbo"."OPR_LOGI_DOCUMENTS" T3 LEFT OUTER JOIN "Anthem"."dbo"."AUDIT_HEADER" T4 on T3."GLOBAL_ID" = T4."GLOBAL_ID" where T2."USER_ID" = T4."USER_ID" and T3."INSTRUMENT_NUMBER" = T1."INSTRUMENT_NUMBER" and T1."DATE_RECEIVED" between '2013-04-17 00:00:00.000' and '2013-04-17 23:00:00.000' group by T1."INSTRUMENT_NUMBER"
Thanks for the prompt reply Unfortunately im not going to be able to alter the database just read records from it so i know that limits me.' DrLudicrous your SQL statement did return the expected number of rows but the count was way off so i need to figure out whats going on there. And i have been pulled in another direction now so i may not respond for a while.
Looking at what you're trying to retrieve you may also need to group by the user ID, although that would probably increase the number of rows returned. SELECT SUM(T1."NO_PAGES_RECORDED") AS Expr1, T1."INSTRUMENT_NUMBER", T2."USER_ID" FROM "Anthem"."dbo"."OPR_PHYS_DOCUMENTS" T1, "Anthem"."dbo"."USERS" T2, "Anthem"."dbo"."OPR_LOGI_DOCUMENTS" T3 LEFT OUTER JOIN "Anthem"."dbo"."AUDIT_HEADER" T4 on T3."GLOBAL_ID" = T4."GLOBAL_ID" where T2."USER_ID" = T4."USER_ID" and T3."INSTRUMENT_NUMBER" = T1."INSTRUMENT_NUMBER" and T1."DATE_RECEIVED" between '2013-04-17 00:00:00.000' and '2013-04-17 23:00:00.000' group by T1."INSTRUMENT_NUMBER", T2."USER_ID"
I think the issue is the system user "admin" may have their results excluded for the records but not sitting at the cpu I couldnt tell ya
