Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Return dafault values when no rows returned Expand / Collapse
Author
Message
Posted Monday, February 15, 2010 2:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 06, 2012 4:33 PM
Points: 20, Visits: 44
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...

Post #865734
Posted Monday, February 15, 2010 2:52 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #865739
Posted Monday, February 15, 2010 2:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 6,932, Visits: 12,655
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...




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
Post #865743
Posted Monday, February 15, 2010 7:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #865816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse