Returning Value When Where Clause is False

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning Value When Where Clause is False

Author

Topic

mcgradyming
Starting Member

1 Post

Posted - 2011-03-07 : 10:35:38

Hello,

I was wondering if it was possible to return a value even when the Where clause is false. For example, SELECT sum(*) from Table where date1 > '10/1/2014' and date2 < '10/1/2015' probably won't return anything, but is there anyway to force it to become zero. I need to it to be zero because in the query below, I am using a join.

Basically, the data begins around 3/1/2010. FY10 starts 10/1/2009. When I join it with FY11, I get

Month FY10 FY11
----- ---- ----
3 9999 9999
4 9999
5 9999
6 9999
7 9999
8 9999
9 9999

It should really look like

Month FY10 FY11
----- ---- ----
10 0 9999
11 0 9999
12 0 9999
1 0 9999
2 0 9999
3 9999 9999
4 9999
5 9999
6 9999
7 9999
8 9999
9 9999

Thanks in Advance.

select top 50 FY10.Month as 'Month'
,FY10.Total as 'FY10 Monthly Total'
,FY10.Cumulative as 'FY10 YTD Total'

,FY11.Total as 'FY11 Monthly Total'
,FY11.Cumulative2 as 'FY11 YTD Total',(proj.proj) as 'Projections'
from (
select a1.Mnth as 'Month'
,a1.FY10 as 'Total'
,case a1.Mnth
when 1 then (select sum(t2.FY10) from (select month(CNSLG_DT) as 'Mnth',

sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t1
where t1.Mnth in (10,11,12,1)
)
when 2 then (select sum(t2.FY10) from (

select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t2
where t2.Mnth in (10,11,12,1,2)

)
when 3 then (select sum(t3.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t3
where t3.Mnth in (10,11,12,1,2,3)
)
when 4 then (select sum(t4.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t4
where t4.Mnth in (10,11,12,1,2,3,4)
)
when 5 then (select sum(t5.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t5
where t5.Mnth in (10,11,12,1,2,3,4,5)
)
when 6 then (select sum(t6.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t6
where t6.Mnth in (10,11,12,1,2,3,4,5,6)
)
when 7 then (select sum(t7.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t7
where t7.Mnth in (10,11,12,1,2,3,4,5,6,7)
)
when 8 then (select sum(t8.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t8
where t8.Mnth in (10,11,12,1,2,3,4,5,6,7,8)
)
when 9 then (select sum(t9.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t9
where t9.Mnth in (10,11,12,1,2,3,4,5,6,7,8,9)
)
when 10 then (select sum(t10.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t10
where t10.Mnth in (10)
)
when 11 then (select sum(t11.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t11
where t11.Mnth in (10,11)
)
when 12 then (select sum(t12.FY10) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)) as t12
where t12.Mnth in (10,11,12)
)
end as 'Cumulative'
from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY10'
from EODS2.COUNSELING
where CNSLG_DT < '2010-10-01'
and CNSLG_DT >= '2009-10-01'
group by month(CNSLG_DT)
) as a1

) as FY10

left join (
select a2.Mnth as 'Month',a2.FY11 as 'Total',case a2.Mnth
when 1 then (select sum(t1.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t1
where t1.Mnth in (10,11,12,1)
)
when 2 then (select sum(t2.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t2
where t2.Mnth in (10,11,12,1,2)
)
when 3 then (select sum(t3.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t3
where t3.Mnth in (10,11,12,1,2,3)
)
when 4 then (select sum(t4.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t4
where t4.Mnth in (10,11,12,1,2,3,4)
)
when 5 then (select sum(t5.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t5
where t5.Mnth in (10,11,12,1,2,3,4,5)
)
when 6 then (select sum(t6.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t6
where t6.Mnth in (10,11,12,1,2,3,4,5,6)
)
when 7 then (select sum(t7.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t7
where t7.Mnth in (10,11,12,1,2,3,4,5,6,7)
)
when 8 then (select sum(t8.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t8
where t8.Mnth in (10,11,12,1,2,3,4,5,6,7,8)
)
when 9 then (select sum(t9.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t9
where t9.Mnth in (10,11,12,1,2,3,4,5,6,7,8,9)
)
when 10 then (select sum(t10.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t10
where t10.Mnth in (10)
)
when 11 then (select sum(t11.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t11
where t11.Mnth in (10,11)
)
when 12 then (select sum(t12.FY11) from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)) as t12
where t12.Mnth in (10,11,12)
)
end as 'Cumulative2'
from (select month(CNSLG_DT) as 'Mnth'
,sum(CNSLG_CT) as 'FY11'
from EODS2.COUNSELING
where CNSLG_DT < '2011-10-01'
and CNSLG_DT >= '2010-10-01'
group by month(CNSLG_DT)
) as a2

) as FY11
on FY10.Month = FY11.Month
left join (select datepart(mm,DATEADD(mm,cast(substring(cast(month as varchar(10)),5,2) as Integer)-1,0)) as 'Mnth',
(orig_dl) as 'Proj'
from cp_fy11_proj_all_mnthly) as Proj on fy10.month = Proj.mnth
order by 3 asc

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-07 : 17:42:00

Can you post the DDL for the table? (column names, their data types). I looked at your query for a few minutes, but could not figure out the details - would be much easier with the DDL.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-07 : 19:11:27

>> I was wondering if it was possible to return a value even WHEN the WHERE clause is false. For example, SELECT SUM(*) FROM Table WHERE date1 > '2014-10-01' AND date2 < '2015-10=01' probably won't return anything, but is there anyway to force
it to become zero. <<

Where did you get SUM(*)? That is just weird. Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

You got the date formats wrong, used reserved words for data elements, put display formatting in a SELECT, used single quotes instead of doubles and missed some basic points about how to program in SQL. You also put commas at the start of lines –and had uppercase data element names. were you a punch card programmer? We used to do that before video terminals. If I know your age, I can explain things better. You handled dates as strings, which screams out “COBOL!!!” to me.

Let's try to clean it up. First, you need a Calendar table with the fiscal data. Then you need to re-name almost every data element you have. “Counseling” is a verb not a noun! Think about it; tables are sets, which means that they are nouns. Is it revenue? Sessions? What? You can have long data element names – this is SQL and not BASIC, so why be cryptic?

CREATE TABLE FiscalCalendar
(fiscal_period CHAR(10) NOT NULL PRIMARY KEY,
fiscal_start_date DATE NOT NULL,
fiscal_end_date DATE NOT NULL,
CHECK (fiscal_start_date < fiscal_end_date),
..);

Let's use the MySQL convention for years and month within year that use double zeros. This makes string patterns easy.

INSERT INTO FiscalCalendar
VALUES ('2010-00-00', '2009-10-01', '2010-10-01'), –- fiscal year 2010
('2010-01-00', '2009-10-01', '2009-10-31'), –- fiscal month in year
('2010-02-00', '2009-11-01', '2009-11-30'), –- fiscal month in year
('2010-02-YD', '2009-01-01', '2009-11-30'), –- YTD in year
etc;

SQL is a declarative database language, not computational or procedural. Here is the basic query; you can then pivot it, put it into a report program, etc. to do that front end display work.

This is called a range or report period calendar table. Given an event date, we use BETWEEN to classify it. We then do a GROUP BY on the classification.

SELECT F.fiscal_period,
COALESCE (SUM (C.session_amt, 0.00)AS period_tot
FROM Fiscal_Calendar AS F
LEFT OUTER JOIN
Counseling_Revenues AS C
WHERE (F.fiscal_period LIKE '2010-__-__'
AND BETWEEN F.fiscal_start_date AND F.fiscal_end_date)
OR (F.fiscal_period LIKE '2011-__-__'
AND BETWEEN F.fiscal_start_date AND F.fiscal_end_date)
GROUP BY F.fiscal_period;

Since I had neither DDL or sample data, this is all I can do. It ought to run 3-4 order of magnitude faster than what you had. And it is easy to maintain.

As a study assignment, look up ROLLUP and see if you can use it.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-08 : 12:26:37

Wow jcelko, that is one nasty response. I mean it was nice to provide a solution, but in my humble opinion you went a little extreme there with your line of questioning:

=======================
Where did you get SUM(*)? That is just weird. Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

You got the date formats wrong, used reserved words for data elements, put display formatting in a SELECT, used single quotes instead of doubles and missed some basic points about how to program in SQL. You also put commas at the start of lines –and had uppercase data element names. were you a punch card programmer? We used to do that before video terminals. If I know your age, I can explain things better. You handled dates as strings, which screams out “COBOL!!!” to me.
=======================

How do you know that mcgradyming wrote this to begin with?

Hey, it compiles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources