 subquries and programming in ms sql2k
i'm trying to create a sql statement that will feed a report. it will pull from 3 tables. to get the exact data i need, 1 of the tables i'm pulling data from is hit 3 times.
Here is the code that I have so far:
SELECT Sec.[user_id], Sec.exp_date, Cus.first_name, Cus.last_name, Cus.type,
(SELECT COUNT (a_id)
FROM db_action.dbo.action
WHERE owner_id = Sec.[user_id]
AND date_created > {ts '2005-01-01 00:00:00'}
AND date_created < {ts '2005-10-19 00:00:00'} ) AS activitycounter,
(SELECT count(chart_id)
FROM db_info.dbo.tbl_charts
WHERE customer_id = Sec.[user_id])AS total_tasks,
(SELECT COUNT(chart_id)
FROM db_info.dbo.tbl_charts
WHERE task_status = 'not applicable'
AND customer_id = Sec.[user_id]) AS not_applicable ,
(SELECT COUNT(chart_id)
FROM db_info.dbo.tbl_charts
WHERE task_status = 'completed'
AND customer_id = Sec.[user_id]) AS total_completed
FROM db_sec.dbo.tbl_security AS Sec
INNER JOIN db_client.dbo.tbl_customerprofile AS Cus
ON Cus.customer_id = Sec.[user_id]
WHERE Sec.area_id = 29
AND Sec.exp_date like '%-Oct-05%'
AND Sec.[permissions] = 'y'
ORDER BY Sec.[user_id]
As is expected, this code works, no problems. Now I just need to do some math on these results.
(total_completed / (total_tasks - not_applicable)) * 100
That is to say, take the result of total_task, subtract not applicable, with what remains, divide that by the result in total_completed and with that multiply it by 100.
Finding percentage
so, i can do that by modifying my query like this
SELECT Sec.[user_id], Sec.exp_date, Cus.first_name, Cus.last_name, Cus.type,
(SELECT COUNT (a_id)
FROM db_action.dbo.action
WHERE owner_id = Sec.[user_id]
AND date_created > {ts '2005-01-01 00:00:00'}
AND date_created < {ts '2005-10-19 00:00:00'} ) AS activitycounter,
(SELECT COUNT(chart_id)
FROM db_info.dbo.tbl_charts
WHERE task_status = 'completed'
AND customer_id = Sec.[user_id])/
((SELECT count(chart_id)
FROM db_info.dbo.tbl_charts
WHERE customer_id = Sec.[user_id]) -
(SELECT COUNT(chart_id)
FROM db_info.dbo.tbl_charts
WHERE task_status = 'not applicable'
AND customer_id = Sec.[user_id])) *100 ,
FROM db_sec.dbo.tbl_security AS Sec
INNER JOIN db_client.dbo.tbl_customerprofile AS Cus
ON Cus.customer_id = Sec.[user_id]
WHERE Sec.area_id = 29
AND Sec.exp_date like '%-Oct-05%'
AND Sec.[permissions] = 'y'
ORDER BY Sec.[user_id]
Now the problem is that occasionally in the subtration, the result of the subtraction is 0. Then when i take it to the division portion, it throws everything off.
What I'd like to write logic to do is that when ever during the subtraction it finds the result to be 0, that it in fact not divide and just declare the result 0
If anybody can't point me in the right direction, it would be greatly appreciaated.
-m Started By mindjuju on Oct 20, 2005 at 12:40:05 PM | 
