How to create a view with added rows for missing dates?

Question

I'm not sure how to accurately phrase this question.

Essentially, say I have a bunch of salespeople on different sales teams. Like, the Blue Region, the Orange Region, the Red Region, whatever.

Now, these sales people are each connected to one sales region, but may, on occasion, change sales regions over time.

Say Bob Jones was in the Blue Region for January 2015, February 2015, March 2015, but on April 2015, he went to the Green Region.

So I'm trying to create a report that would aggregate sales revenue based on Region (even though at the technical level, its logged to sales person).

So we would have transaction (table: employee_sales) data as follows:

name date sales
Bob Jones February 18th, 2015 50,000ドル

etc. etc.

So in order to aggregate by region, I would need a helper dimension table as follows (Remember, region can change over time) (table: employee_region)

name region month year
Bob Jones Blue January 2015
Bob Jones Blue February 2015
Bob Jones Blue March 2015
Bob Jones Green April 2015

Then I can simply do a query:

select er.region, er.month, er.year, sum(es.sales)
from employee_sales es
inner join employee_region er
on es.name = er.name and month(es.date) = er.month and year(es.date) = er.year
group by er.region, er.month, er.year

So this would give me the data I need.

HOWEVER, now I have a problem --- say I have 500 employees and the REGION data is updated a month in arrears. So right now, February 2016, we only have region data from January 2016.

Would I would LIKE to do, is ... for the current month (February 2016) ... if the data in the "helper table" for region is missing for the month, take the last month found (which may be January, but sometimes even December or November potentially).

I'm not sure what to do here. Create some kind of view?

Do I restructure the 'helper table' so it's more of an inequality statement?

EDIT: I think this is a slowly changing dimensions problem. Hmm I probably have to reorganize the dimension table.

• Aaron Bertrand: I'm going to state this a little simpler, please tell me if I understand your requirement correctly. You want the query to return data for the current month only; if there is no data for the current month, show data from the last month found?
• BateTech: It sounds to me like the issue is that your sales table is up to date but your region table is not, so you want to use the most recent region in the case that the region is not found. Is this correct?
• user45867: This is correct -- the sales table is updated hourly --- however the region table is (typically) only updated once a month, making the current month nonexistent unfortunately. Since moves are so infrequent, I consider using the previous month instead. I'm using MS SQL Server 2012. But to clarify Aaron's point, I want to see the region data for the current AND previous months. Someone should be able to see the summations of the current month and the previous months.

Answer 1

I wouldn't organize your "helper table" that way. I would keep only {Region, Salesman, StartDate}. To create a view of intervals, join that table to itself to create data ranges where a.StartDate < b.StartDate. Use an outer join, so that NULL represents an EndDate of "now", which you can coalesce with the current date or other, as appropriate. To represent departures (not reassignments) use an artificial region named CiaoBaby or some such.

Now your maintenance chore is much easier; you need only capture staffing changes. The table is oodles smaller (over time, 100 or 1000 oodles). And you automatically have the built-in assumption that, absent contrary information, a salesman remains attached today to the same one as last month.

• user45867: Thank you very much --- funny, you know, I figured the same idea and your post gives me more confidence to implement it. The original table was created by a colleague, hence the unusual nature of its structure.

Answer 2

You do not need to create a view for this. View is generally used to improve the performance of code. For this you have to find out the current month values always using the order by of year and month.

Find below solution of your problem:

select er.region, er.month, er.year, sum(es.sales)
from employee_sales es
inner join employee_region er
on es.name = er.name and month(es.date) = er.month and year(es.date) = er.year
inner join
(select er.name as name, er.year as year , er.month as month
from employee_region er
order by er.year desc, er.month desc) as x
on er.name = x.name and er.year = x.year and er.month = x.month
group by er.region, er.month, er.year

• user45867: I'm confused at this query --- your subquery is returning all data --- the 'order by clause' is meaningless in the data returned in the case of your query. I have thought of a similar solution for now. Left join the region based on employee/ year-month .... then left join a subquery that returns only the maximum year-month found, and finally, return a value using isnull(region found, last region found). It's a bit clumsy but it works. Though there are probably better solutions.
• Govind: yes in this i am finding the maximum month of the employee using the order by so that if there is a mismatch of any month then last month can come as per your requirement. Although if u find my query as your solution thn please upvote the answer.
• user45867: Hmm what I'm saying is I don't think your subquery actually returns a maximum value --- it returns all values, albeit in a sorted order