September 23, 2014 at 6:49 am
Problem:
(1) am disallowed to create a calendar table in database.
(2)A sproc used at my company to support the reporting tab of our company's website doesn't return property_names if no activity occurred between supplied @start and @end times.
(3) business users have requested property_names be listed even when no activity
EG.
SELECT property_name
FROM property_activity
WHERE activity_date BETWEEN @start_date and @end_date
AND activity_type = 'F'
Is the next best solution to JOIN to a calendar #temp table?
September 23, 2014 at 9:50 am
Depends.
From info you posted it appears you have a denormalized entity with property names and activity => no activity means no record in table, so right join to a calendar table would return null property_name for dates without activity.
September 23, 2014 at 9:58 am
You'll need to use the "property master" table with a left join, something like below:
SELECT property_name, pa.activity_date
FROM properties p
LEFT OUTER JOIN property_activity pa ON
pa.property_name = p.property_name AND
pa.activity_date BETWEEN @start_date and @end_date AND
pa.activity_type = 'F'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 23, 2014 at 10:13 am
Yes, a right join to a calendar table will create nulls for property_name column on days no activity occurred. how about i full outer joined to property activity table on date, won't it create placeholders so I can aggregate counts of activities by property_name and return property_name even if noactivities logged for supplied date? Left outer join is same as left join...I am wondering if better solution than temp table join? Users are supplying start and end date parameters. Looking for common practices.
September 23, 2014 at 10:23 am
You're assuming that every property name will be in the activity table. That may be a valid assumption for your particular data set up, I don't know. You'd definitely have to scan more of the table than just your current time period to get all the property names.
But you should have a property "master" table somewhere with property name in it. That's the best place to get the property name from.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 23, 2014 at 10:24 am
In fact, the "property name" should not really be in the activity table, just the property_id that represents that property name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply