Return dafault values when no rows returned

Question

Return dafault values when no rows returned

mgchris

SSChasing Mays

Points: 652
More actions
February 15, 2010 at 2:50 pm

#142482

What I am trying to do is have some values set by default if there are no rows returned in a subquery. I have 4 weeks that may or may not have data. If no data is returned for a period then have a row created with one of the rows entered as 'no action'.
Mea Webbt002216512010-02-15 00:00:00.0000
Carlos Banuelost002198812010-02-15 00:00:00.0000
Vijay Brockt002222012010-02-15 00:00:00.000-1
Steven Castorenat002177312010-02-20 00:00:00.0000
Lashontaya Beavert002214042010-03-14 00:00:00.0000
As you can see week 2 and 3 have no data, so I would like a row like such for those weeks:
'No Action' NULL 2 NULL 0
'No Action' NULL 3 NULL 0
Currently I am using a Case statement to give value to date ranges like such
Case
When isnull(t.dtmovein,ps.dtprefmovein) between dateadd(week,-2,@week1) and dateadd(d,6,dateadd(week,-2,@week1)) THEN '1'
WHEN isnull(t.dtmovein,ps.dtprefmovein) between dateadd(week,-2,@week2) and dateadd(d,6,dateadd(week,-2,@week2)) THEN '2'
WHEN isnull(t.dtmovein,ps.dtprefmovein) between dateadd(week,-2,@week3) and dateadd(d,6,dateadd(week,-2,@week3)) THEN '3'
WHEN isnull(t.dtmovein,ps.dtprefmovein) between dateadd(week,-2,@week4) and dateadd(d,6,dateadd(week,-2,@week4)) THEN '4'
END Week,
I have looked around and have found nothing in regards to this. Any help would be appreciated.
Here is an excerpt of one of the types that I am using. I thought about IF NOT EXISTS but not really sure how to use these inside a select statement or subquery.
Select
'MIFC' iType,
t.sFirstName + ' ' + t.sLastName tName,
t.scode,
Case
When isnull(t.dtmovein,ps.dtprefmovein) between dateadd(week,-2,@week1) and dateadd(d,6,dateadd(week,-2,@week1)) THEN '1'
WHEN isnull(t.dtmovein,ps.dtprefmovein) between dateadd(week,-2,@week2) and dateadd(d,6,dateadd(week,-2,@week2)) THEN '2'
WHEN isnull(t.dtmovein,ps.dtprefmovein) between dateadd(week,-2,@week3) and dateadd(d,6,dateadd(week,-2,@week3)) THEN '3'
WHEN isnull(t.dtmovein,ps.dtprefmovein) between dateadd(week,-2,@week4) and dateadd(d,6,dateadd(week,-2,@week4)) THEN '4'
END Week,
isnull(t.dtmovein,ps.dtprefmovein) LeaseDate,
CASE
WHEN lb.dtMoveIn >= t.dtmovein THEN -1
ELSE 0 END Completed
From
Table1 t
join Table2 ps on ps.hmy = t.hprospect
LEFT JOIN table3 lb on lb.hcode = t.hmyperson
Where
isnull(t.dtMoveIn,ps.dtprefmovein) between dateadd(week,-2,@week1) and dateadd(d,6,dateadd(week,-2,@week4))
and t.hproperty = (select hmy from property where scode ='ap')
--Union All...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

There are a few different ways to do this. The one I usually find works best is to create a table of the weeks you want to query against (called a Calendar table), and then left join from that to your data.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

LutzM SSC Guru Points: 107049 More actions · Answer 2

Use a Numbers/Tally table or a calendar table to get the weeks you'd like to see as the left part of a left join and use ISNULL() function for weeks with no values returned from your query.

Edit: Oops... Sorry, Gus to almost quote you...:-P

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Chris,

Both answers above are technically correct but if you want a coded answer, you'll need to change this...

Mea Webb t0022165 1 2010-02-15 00:00:00.000 0

Carlos Banuelos t0021988 1 2010-02-15 00:00:00.000 0

Vijay Brock t0022220 1 2010-02-15 00:00:00.000 -1

Steven Castorena t0021773 1 2010-02-20 00:00:00.000 0

Lashontaya Beaver t0022140 4 2010-03-14 00:00:00.000 0

... into some readily comsumable data. See the article at the first link in my signature line below for how to easily do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)