July 25, 2010 at 10:49 am
I have to report month-over-month counts of "Request Source".
I have a table named RequestSource.
RequestSource contains the following:
RequestSourceKey RequestSource
1 Phone
2 Email
3 Internet
4 Mail
I need to return all the records in the join. So let's say I in last month that' finished. It's July now. So the last month right now is June. I am comparing June to May, until July is finished then I will compare July to June, and so on.
In the current scenario, let's say June has had requests in all the categories 1,2,3 and 4.
But, in May I only have requests in 2. I will do a left join and the output will look like this:
RequestSourceKey RequestSource JuneCountRequestSource MayCountRequestSource
1 Phone 1,000 null
2 Email 2,000 500
3 Internet 6,000 null
4 Mail 10 null
How do I handle the join if now it's August and I am comparing July and June has more counts than July. So, let's say in this case the results are this
RequestSourceKey RequestSource JulyCountRequestSource JuneCountRequestSource
1 Phone 50 1,000
2 Email null 2,000
3 Internet 2,000 6,000
4 Mail null 10
A left join in this scenario will return:
RequestSourceKey RequestSource JuneCountRequestSource MayCountRequestSource
1 Phone 50 1,000
3 Internet 2,000 6,000
But, I need it to return:
RequestSourceKey RequestSource JulyCountRequestSource JuneCountRequestSource
1 Phone 50 1,000
2 Email 0 2,000
3 Internet 2,000 6,000
4 Mail 0 10
How do I dynamically handle the join regardless of which month has more records, so it shows all the RequestSources every month?
Thanks in advance.
July 25, 2010 at 10:34 pm
Your Table Structures are not that clear. Please have a look at the link in my signature on How to post questions to get faster answers.
Anyways, I think what you need is a FULL OUTER JOIN. Have a look at the same in BOL.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 25, 2010 at 10:53 pm
Even any datetime/month columns are not in the table.... How anybody can give such kind of report (monthly count), without having any datetime column....
Also the table design is not very clear... whether phone, email... are column names or the values in the table...???
July 26, 2010 at 1:57 am
some sample data required for the fast result.
July 26, 2010 at 12:09 pm
Your problem is that you are using the transactional data as the left side of the query (the anchor).
What you need is to have a constant set of data on the left that will be there whether or not you have any data on the right. Set up a little table that you can use for the left side like:
ID Descr
1 Pone
2 Mail
3 Internet
and so forth.
Then I would highly suggest that you set up a calendar table. Here is a link to an article I published earlier this month on calendar tables:
http://www.sqlservercentral.com/articles/T-SQL/70482/
Then I would use the calendar table to select and group the prior 2 months. The example in the article was comparing a month from the current year to the same month in the prior year. It isn't very hard to modify this to compare the last completed month to the month before.
Todd Fifield
July 26, 2010 at 1:56 pm
You only specified one of your two tables in the join. What is the structure of the table that has monthly data in it? Does every record in that other table have a request source linked to it (and if not does your report have to count those?)
You only gave us half a question.
July 26, 2010 at 2:14 pm
Sorry for the way I wrote my request. I forgot to preview it before I posted it and didn't realize that I had not done the formatting.
The reason I don't have dates in the data that I represented is that I've already figured that part out, and I don't need help with that. I am not showing dates in the report other than the column headings which will tell which month's are being compared. That would have been more obvious if I had not forgotten to do the proper formatting.
@tfifield: I tried what you are suggesting, but it didn't appear to work. Maybe, I did something wrong because I had been working on this problem for so long and was very frustrated that I wasn't getting the results I was expecting. I will review my work to see if I have any errors. At least I have confirmation that I was headed in the right direction. Thank you for your suggestion.
And, to all, thank you for taking time to look at this. I appreciate it. Again, my apologies, I realize now that it was confusing.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply