Question about subquries and logic

20 Oct 2005 7:21 PM

mindjuju

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 bgm.dbo.tbl_customer_profile 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 bgm.dbo.tbl_customer_profile 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.

20 Oct 2005 7:32 PM

Jerry Spivey

Try something like this or similar (as example):

CREATE TABLE #ZERO
(VAL1 INT NOT NULL,
VAL2 INT NOT NULL)

INSERT #ZERO
VALUES(10,2)
INSERT #ZERO
VALUES(10,0)

SELECT CASE VAL2 WHEN 0 THEN 0 ELSE VAL1/VAL2 END AS 'RESULT'
FROM #ZERO

--DROP TABLE #ZERO

HTH

Jerry
Show quote

"mindjuju" <mindj***@discussions.microsoft.com> wrote in message
news:F7F3C313-1849-4F83-9C9F-1C7FA0D14D8A@microsoft.com...
> 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 bgm.dbo.tbl_customer_profile 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 bgm.dbo.tbl_customer_profile 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.

20 Oct 2005 7:34 PM

Jerry Spivey

Or...

SELECT ISNULL(VAL1/NULLIF(VAL2,0),0) AS 'RESULT'
FROM #ZERO

HTH

Jerry
Show quote

"mindjuju" <mindj***@discussions.microsoft.com> wrote in message
news:F7F3C313-1849-4F83-9C9F-1C7FA0D14D8A@microsoft.com...
> 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 bgm.dbo.tbl_customer_profile 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 bgm.dbo.tbl_customer_profile 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.

20 Oct 2005 7:58 PM

mindjuju

It's funny you mention temp tables, i was just starting to research those,
though admittedly i was at a standstill from the math.

I think your answer brings a lot of possibility and i'm excited to morph my
statement into your suggestion.

Thanks for the help Jerry!

Show quote

"Jerry Spivey" wrote:

> Or...
>
> SELECT ISNULL(VAL1/NULLIF(VAL2,0),0) AS 'RESULT'
> FROM #ZERO
>
> HTH
>
> Jerry
> "mindjuju" <mindj***@discussions.microsoft.com> wrote in message
> news:F7F3C313-1849-4F83-9C9F-1C7FA0D14D8A@microsoft.com...
> > 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 bgm.dbo.tbl_customer_profile 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 bgm.dbo.tbl_customer_profile 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.
>
>
>