January 14, 2015 at 11:20 am
I am trying to display various activities in the table through a Query . What will be the best way to display an activity with NULL or 0 if its not in the table .Say I want to see Security and Housekeeping ...How can I add that to the Query
Create table #Temp ( Activity Varchar(100) , Hours Int)
Insert into #Temp values ('Administration', 20)
Insert into #Temp values( 'Payroll', 30)
January 14, 2015 at 11:48 am
You need to have one table having all activities and then you outer join your table to it.
January 14, 2015 at 11:49 am
Exactly what I was thinking. Where do those other two values come from? They have to be stored somewhere and then you join to that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2015 at 11:51 am
Yeah but since there are just 2-3 such additional activities . I was wondering we we can tweak the query and see if the table has it if not add it to the result set. Is that possible?
January 14, 2015 at 11:55 am
Another approach would be to hard code your list into a temporary table in the query and then join to that. Not pretty, but it'll work. There just has to be a way to know "this should be there" and then have a mechanism for dealing with it if it's not. The best way to define "this should be here" in a relational storage engine is to store something somewhere.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 14, 2015 at 4:31 pm
Everything said here was spot on. You're probably best doing this in a lookup table of sorts. That said, here is an example of the sort of "hard coding" Grant described:
;with possibleActivities (Activity) as
(
select 'Administration' union all
select 'Payroll' union all
select 'Security' union all
select 'Housekeeping'
), results (Activity, [Hours]) as
(
select 'Administration', 20 union all
select 'Payroll', 30
)
select
Activity = p.Activity,
[Hours] = isnull(r.Hours, 0)
from possibleActivities p
left outer join results r
on p.Activity = r.Activity
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply