August 18, 2011 at 1:16 pm
My problem is as follows. I have this query that works fine as two seperate subquery fields.
--VP
SELECT ATTUID, TITLE, NAME
, (SELECT count(*) FROM dbo.tbl_CDE_Hierarchy_Export
WHERE VP_ATTUID = A.VP_ATTUID
AND TITLE = 'REP'
AND (cast(([Posted Sales Revenue (ABR)]/(nullif([Sales Revenue Target],0) * [Percent_Elapsed_Days]))*100 as decimal (20,2))) > 100
) CRITERIA1
, NULLIF((SELECT count(*) FROM dbo.tbl_CDE_Hierarchy_Export
WHERE VP_ATTUID = A.VP_ATTUID
AND TITLE = 'REP'
),0) CRITERIA2
FROM dbo.tbl_CDE_Hierarchy_Export A
WHERE TITLE = 'VP'
ORDER BY TITLE
But when I try to divide the query above I get all Zeros and Nulls instead of the product values I am expecting.
--VP
SELECT ATTUID, TITLE, NAME
, (SELECT count(*) FROM dbo.tbl_CDE_Hierarchy_Export
WHERE VP_ATTUID = A.VP_ATTUID
AND TITLE = 'REP'
AND (cast(([Posted Sales Revenue (ABR)]/(nullif([Sales Revenue Target],0) * [Percent_Elapsed_Days]))*100 as decimal (20,2))) > 100
)
/
( NULLIF((SELECT count(*) FROM dbo.tbl_CDE_Hierarchy_Export
WHERE VP_ATTUID = A.VP_ATTUID
AND TITLE = 'REP'
),0) )CRITERIA2
FROM dbo.tbl_CDE_Hierarchy_Export A
WHERE TITLE = 'VP'
ORDER BY TITLE
Can someone tell me what I'm doing wrong trying to divide two subqueries?
Thanks in advance for your help,
August 18, 2011 at 1:40 pm
If either query produces a null, the division or multiplication of those fields will result in NULL.
Do either of the subqueries yield a null when you run them individually?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 1:52 pm
Some of them do, I'm ok with the ones that come out null. But the ones that are supposed to have values are coming out with zeros. That's my main problem.
August 18, 2011 at 1:56 pm
Here's how some of the output looks...
Query1
ATTUID TITLE NAME CRITERIA1 CRITERIA2
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
AA2340 COACH ASSEUS, ALENS 2 11
AA2895 COACH ALCALA, ALBERT 0 NULL
AA5144 COACH O'BRIEN, ANNA 0 1
AA5471 COACH HERRERA, ALVEAN 0 12
AA5757 COACH ANDREWS, ADAM 9 12
AA8631 COACH ABDUR-RAHMAN, AMINA 1 2
AB2537 COACH BAILEY, ANITRA 3 13
AB255W COACH BLAKE, AMANDA 2 13
AB4356 COACH BROKSCHMIDT, ANGELIQUE 7 13
AB7578 COACH BARGIE, ALGERNON 6 10
AB8316 COACH BORNEY, ANDRE 0 9
AC0895 COACH CHIRINOS, ANTONIO 3 9
AC2582 COACH COWIN-BERRYMAN, APRIL 5 13
AC2858 COACH CHAN, ALLEN 2 18
AC4768 COACH CARRILLO, ALEJANDRO 4 14
AD0160 COACH DANIEL-DIAZ, ANGELA 3 11
AD5492 COACH DELOSSANTOS, ALICIA 3 13
AD7934 COACH DAVIS, ANN 8 14
AD9491 COACH GARZA, ANGELICA 5 12
AE1836 COACH EHIEMUA, ANTONYO 7 16
AE2438 COACH LEDET, ALONYA 8 11
AE4715 COACH ELSEY, AMON 11 14
AF6319 COACH FURQAN, ALIFAH 7 7
AF7821 COACH FERNANDEZ, ANA 7 12
AG1873 COACH GREEN, ANDREW 0 2
AG7255 COACH GHIMENTI, AUGUSTO 5 8
AH4515 COACH WHITE, ANGETTE 4 5
AH5306 COACH HOGAN-WRIGHT, ANGELA 6 11
AK1276 COACH KING, ANGELA 16 20
AK8485 COACH MCGUIRE, ATASHA 9 12
AL0946 COACH LEWIS, ABBIE 4 13
AL2625 COACH LIEN, ALICE 0 17
AL3715 COACH BRIDGES, ANNETTE 0 16
AM2621 COACH WICKS, AYISHA 2 14
AM3217 COACH SULLIVAN, ANN 3 16
AM4566 COACH MACDONALD, ANGELIA 8 11
AM5187 COACH MORALES, ADOLFO 7 15
AM6685 COACH MCFADDEN, ANDRELIA 0 19
AM695R COACH MCDUFFIE, ARTIS 7 10
AN2971 COACH NELSON, AMY 0 18
AP0598 COACH PEREZ, ANGEL 8 8
AP2372 COACH WHITMORE, ADRIANNA 5 14
AP6952 COACH PAGAN, ARIANA 5 6
AP7383 COACH PAYTON, AUNDREA 9 14
AR1617 COACH RUIZ, AGUSTIN 0 4
AR2795 COACH REYNOLDS, AMANDA 0 13
AR4464 COACH ROJO-READ, ALICIA 2 12
AS2642 COACH SICHINGA, AUSTIN 9 13
AS2957 COACH SALDANA, ANNALISA 3 14
AS3555 COACH SIMMONS, ANDREA 9 10
AS7660 COACH SANJUR, ABRAHAM 6 9
AS8327 COACH SIRACUSA, AURELIO 2 13
AT2697 COACH WILLIAMS, ANITA 3 13
AT9141 COACH TORRES, ANALESLIE 4 13
AW0296 COACH LAUBACH, AMY 0 20
AW6818 COACH WILLIAMSON, ANNA 5 16
AW7350 COACH WILLIAMS, AMINAH 9 13
AW8409 COACH WALES, ASHANTI 0 NULL
AY9049 COACH YANIZ, AMADO 7 14
BA5289 COACH AKINS, BILLIE 5 12
BA7413 COACH AYRES, BROOKE 3 10
BA8459 COACH BRADSHAW, ANDREA 6 15
BB0255 COACH BRIGGS, BRANDI 7 11
BB0530 COACH BURTON, BRANDI 12 13
BB1411 COACH BAKER HAYDEN, BRENDA 7 11
BC5710 COACH CARTER, BLANCHE 2 10
BC8979 COACH CROSSNO, BARBARA 6 12
BD1499 COACH DECKARD, BARBARA 13 15
BF7971 COACH FRIEMEL, BRYAN 6 13
BF9525 COACH FELCIANO, BRET 0 12
BG7732 COACH GREENWELL, BRITTANY 6 10
BH1349 COACH IOANE, BRANDY 5 8
BK2525 COACH KALBFLEISCH, BROOKE 14 17
BK2963 COACH KELLER, BARBARA 1 12
BM1634 COACH MINCEY-DANIELS, BARBARA 0 14
BM2658 COACH MAYNARD, BRADLEY 7 12
BM2857 COACH LEWIS, BENITA 0 16
BM3297 COACH MAGANA, BLANCA 2 12
BR5698 COACH RINCON, BIANCA 3 13
BS029W COACH SIMMONS, BRAD 4 12
BS696Y COACH SMALL, BRANDIE 4 4
BV3524 COACH VILLARREAL, BERTHA 6 13
BW4441 COACH WILLIAMS, BRENDA 7 13
BY6353 COACH YOON, BRIAN 1 10
CA2105 COACH AGUDELO, CYNTHIA 6 6
CA2948 COACH ARELLANO, CYNTHIA 2 14
CB0239 COACH BRENNEMAN, CAROLE 9 12
CB0393 COACH BUCKMAN, CHERIE' 3 15
CB2895 COACH BAKER, CINDY 4 10
CB4382 COACH BAYLESS, CATRINA 1 9
CB5632 COACH BARNES, C LOUISE 5 9
CB6231 COACH HUTCHISON, CANDACE 1 13
CB6782 COACH BREWER, CHRISTINA 5 10
CB7731 COACH MUNOZ, CINDY 5 8
CC2285 COACH COLEMAN, CUREAKA 4 5
CC2515 COACH CRAWFORD, CAROL 5 12
CC8967 COACH COLLINS, CHRISTINA 2 11
CD4671 COACH DADI, CARMEN 3 8
CE2620 COACH ELDER, CLINTON 6 12
CE2715 COACH JONES, CAROL 5 14
CF8249 COACH FILIO, CYNTHIA 0 22
CG0953 COACH GIRON, CARLOS 9 10
CG1284 COACH TRACY, CARMEN 4 7
CH1718 COACH HOPKINS, CLEOFUS 9 13
CH478H COACH HILL, COREY 6 13
CH5454 COACH HERNANDEZ, CARLOS 4 9
CH6748 COACH HAYES, CONNIE 11 12
CH7176 COACH HOWARD, CHEVON 0 16
CH8560 COACH HOMER, CAROLYN 8 10
CH9394 COACH HARRIS, CHARLES 0 20
Query 2 Looks like this,
ATTUID TITLE NAME CRITERIA2
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
AA2340 COACH ASSEUS, ALENS 0
AA2895 COACH ALCALA, ALBERT NULL
AA5144 COACH O'BRIEN, ANNA 0
AA5471 COACH HERRERA, ALVEAN 0
AA5757 COACH ANDREWS, ADAM 0
AA8631 COACH ABDUR-RAHMAN, AMINA 0
AB2537 COACH BAILEY, ANITRA 0
AB255W COACH BLAKE, AMANDA 0
AB4356 COACH BROKSCHMIDT, ANGELIQUE 0
AB7578 COACH BARGIE, ALGERNON 0
AB8316 COACH BORNEY, ANDRE 0
AC0895 COACH CHIRINOS, ANTONIO 0
AC2582 COACH COWIN-BERRYMAN, APRIL 0
AC2858 COACH CHAN, ALLEN 0
AC4768 COACH CARRILLO, ALEJANDRO 0
AD0160 COACH DANIEL-DIAZ, ANGELA 0
AD5492 COACH DELOSSANTOS, ALICIA 0
AD7934 COACH DAVIS, ANN 0
AD9491 COACH GARZA, ANGELICA 0
AE1836 COACH EHIEMUA, ANTONYO 0
AE2438 COACH LEDET, ALONYA 0
AE4715 COACH ELSEY, AMON 0
AF6319 COACH FURQAN, ALIFAH 1
AF7821 COACH FERNANDEZ, ANA 0
AG1873 COACH GREEN, ANDREW 0
AG7255 COACH GHIMENTI, AUGUSTO 0
AH4515 COACH WHITE, ANGETTE 0
AH5306 COACH HOGAN-WRIGHT, ANGELA 0
AK1276 COACH KING, ANGELA 0
AK8485 COACH MCGUIRE, ATASHA 0
AL0946 COACH LEWIS, ABBIE 0
AL2625 COACH LIEN, ALICE 0
AL3715 COACH BRIDGES, ANNETTE 0
AM2621 COACH WICKS, AYISHA 0
AM3217 COACH SULLIVAN, ANN 0
AM4566 COACH MACDONALD, ANGELIA 0
AM5187 COACH MORALES, ADOLFO 0
AM6685 COACH MCFADDEN, ANDRELIA 0
AM695R COACH MCDUFFIE, ARTIS 0
AN2971 COACH NELSON, AMY 0
AP0598 COACH PEREZ, ANGEL 1
AP2372 COACH WHITMORE, ADRIANNA 0
AP6952 COACH PAGAN, ARIANA 0
AP7383 COACH PAYTON, AUNDREA 0
AR1617 COACH RUIZ, AGUSTIN 0
AR2795 COACH REYNOLDS, AMANDA 0
AR4464 COACH ROJO-READ, ALICIA 0
AS2642 COACH SICHINGA, AUSTIN 0
AS2957 COACH SALDANA, ANNALISA 0
AS3555 COACH SIMMONS, ANDREA 0
AS7660 COACH SANJUR, ABRAHAM 0
AS8327 COACH SIRACUSA, AURELIO 0
AT2697 COACH WILLIAMS, ANITA 0
AT9141 COACH TORRES, ANALESLIE 0
AW0296 COACH LAUBACH, AMY 0
AW6818 COACH WILLIAMSON, ANNA 0
AW7350 COACH WILLIAMS, AMINAH 0
AW8409 COACH WALES, ASHANTI NULL
AY9049 COACH YANIZ, AMADO 0
BA5289 COACH AKINS, BILLIE 0
BA7413 COACH AYRES, BROOKE 0
BA8459 COACH BRADSHAW, ANDREA 0
BB0255 COACH BRIGGS, BRANDI 0
BB0530 COACH BURTON, BRANDI 0
BB1411 COACH BAKER HAYDEN, BRENDA 0
BC5710 COACH CARTER, BLANCHE 0
BC8979 COACH CROSSNO, BARBARA 0
BD1499 COACH DECKARD, BARBARA 0
BF7971 COACH FRIEMEL, BRYAN 0
BF9525 COACH FELCIANO, BRET 0
BG7732 COACH GREENWELL, BRITTANY 0
BH1349 COACH IOANE, BRANDY 0
BK2525 COACH KALBFLEISCH, BROOKE 0
BK2963 COACH KELLER, BARBARA 0
BM1634 COACH MINCEY-DANIELS, BARBARA 0
BM2658 COACH MAYNARD, BRADLEY 0
BM2857 COACH LEWIS, BENITA 0
BM3297 COACH MAGANA, BLANCA 0
BR5698 COACH RINCON, BIANCA 0
BS029W COACH SIMMONS, BRAD 0
BS696Y COACH SMALL, BRANDIE 1
BV3524 COACH VILLARREAL, BERTHA 0
BW4441 COACH WILLIAMS, BRENDA 0
BY6353 COACH YOON, BRIAN 0
CA2105 COACH AGUDELO, CYNTHIA 1
CA2948 COACH ARELLANO, CYNTHIA 0
CB0239 COACH BRENNEMAN, CAROLE 0
CB0393 COACH BUCKMAN, CHERIE' 0
CB2895 COACH BAKER, CINDY 0
CB4382 COACH BAYLESS, CATRINA 0
CB5632 COACH BARNES, C LOUISE 0
CB6231 COACH HUTCHISON, CANDACE 0
CB6782 COACH BREWER, CHRISTINA 0
CB7731 COACH MUNOZ, CINDY 0
CC2285 COACH COLEMAN, CUREAKA 0
CC2515 COACH CRAWFORD, CAROL 0
CC8967 COACH COLLINS, CHRISTINA 0
CD4671 COACH DADI, CARMEN 0
CE2620 COACH ELDER, CLINTON 0
CE2715 COACH JONES, CAROL 0
CF8249 COACH FILIO, CYNTHIA 0
CG0953 COACH GIRON, CARLOS 0
CG1284 COACH TRACY, CARMEN 0
CH1718 COACH HOPKINS, CLEOFUS 0
CH478H COACH HILL, COREY 0
CH5454 COACH HERNANDEZ, CARLOS 0
CH6748 COACH HAYES, CONNIE 0
CH7176 COACH HOWARD, CHEVON 0
CH8560 COACH HOMER, CAROLYN 0
CH9394 COACH HARRIS, CHARLES 0
CJ1612 COACH JABLONSKI, CHARLENE 0
CJ2181 COACH JOHNSON, CRYSTAL 0
CK5282 COACH ECHOLS, CYNTHIA 0
CL1295 COACH LASAT, CECILIA 0
CL167E COACH LEATHERS, CHARLES 0
CL2535 COACH LANDRY, CHARLES 0
CL2876 COACH LEE, CAROLYN 0
CL4157 COACH LLERENA, CARMEN 0
CL5935 COACH LASTER, CLARISSA NULL
CM1913 COACH RANDOLPH, CYNTHIA 0
CM5814 COACH MACKEY, CHARISSE 0
CM6567 COACH GONZALEZ, CLAUDIA 0
CM8769 COACH STEVENSON, CAROL 0
August 18, 2011 at 2:19 pm
EDIT ***
I think I see your problem. You're not doing typecasts correctly.
Both your numerator and denominator are integer values. Try it - SELECT 1 / 2 will result in a value of 0.
As a simple solution, you can probably get it to work by changing it to this:
SELECT ATTUID, TITLE, NAME
, CAST((SELECT count(*) FROM dbo.tbl_CDE_Hierarchy_Export
WHERE VP_ATTUID = A.VP_ATTUID
AND TITLE = 'REP'
AND (cast(([Posted Sales Revenue (ABR)]/(nullif([Sales Revenue Target],0) * [Percent_Elapsed_Days]))*100 as decimal (20,2))) > 100
) AS DECIMAL(20, 2)
/
CAST(( NULLIF((SELECT count(*) FROM dbo.tbl_CDE_Hierarchy_Export
WHERE VP_ATTUID = A.VP_ATTUID
AND TITLE = 'REP'
),0) ) AS DECIMAL(20, 2) CRITERIA2
FROM dbo.tbl_CDE_Hierarchy_Export A
WHERE TITLE = 'VP'
ORDER BY TITLE
Note that it won't work to simply cast the output - the individual parts have to be decimals to work.
August 18, 2011 at 2:46 pm
You could also just multiply one of the count(*) * 1.0
That would implicitly change resulting datatype without having to cast either side.
--this will return 0
select
(
select COUNT(*) from sysobjects
)
/
(
select COUNT(*) from syscolumns
)
--whereas this will return a decimal
select
(
select COUNT(*) * 1.0 from sysobjects
)
/
(
select COUNT(*) from syscolumns
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 6:18 am
Thanks so much for your input on this. That helps me a ton!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply