SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Return dafault values when no rows returned


Return dafault values when no rows returned

Author
Message
mgchris
mgchris
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 45
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 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

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...


GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23351 Visits: 9730
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
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10275 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85352 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search