October 27, 2011 at 8:55 am
Hello, I'm trying to set something up and can't for the life of me figure out what to do. I have a table (see diagram below) that has alot of information in it. What i need to do is setup some type of query that will show the current month and the last entry for the previous month and put it into a table format by m10_class_code.
<Sample data to look at the structure of the data>
m10_total_count, m10_class_code, m10_current_date
277 ,AFCR ,3/16/2009 0:00
4116 ,SM ,6/5/2009 0:00
484 ,AFCR ,4/8/2008 0:00
4295 ,C ,12/9/2010 0:00
260 ,AFCR ,11/19/2009 0:00
45 ,SMRT ,7/8/2011 0:00
3865 ,SM ,2/18/2010 0:00
31 ,SMRT ,2/10/2010 0:00
I have a sp that will put a record in the table daily now so it will be easier to find the end of the month for this month going forward however all of the historical documentation does not have complete month information in it.
I need something like this
<current month would need today's count in the current month>
<last month would need the last total count from the last day of the last month that was in the database table>
Class Code Current Month Last Month
Class A 12345 <this would be the m10_total_count from the last day of last month>
Is there any hope for doing this or would I need to create a record for the last day of each month if there was not one in there.
Thank you in advance for your help.
October 27, 2011 at 10:37 am
Could you post the DDL for your table(s), sample data for the table(s), and your expected results based on the sample data?
For help in doing this, please read the first article I reference below in my signature block regarding asking for help. If you provide all the requested information, you will get much better answers plus tested code.
October 27, 2011 at 11:27 am
I have added the appropriate code here in a txt file
Trying to accomplish
1. Getting the current date (that part is easy) and post that in the current date column
2. Get the last value from last month and put that in the last month column
Desired result should like like this (without the ___ and .... that is so I could try to produce a table
Class code_______CurrentDay________LastMonth
A.........................123........................125
AA.......................12..........................12
(etc).
Should see 22 class codes and there is no duplicate data in the text file provided with code to create the table to look at.
October 28, 2011 at 8:47 am
First, the idea behind including a SAMPLE is that it is large enough to provide a realistic representation of the data, BUT NO LARGER. Since you are grouping on the class code, you need to include two or three class codes to make it realistic, but there is no reason to include the 22 different class codes that you actually did.
Also, since you are interested in values for THIS MONTH and LAST MONTH, there is absolutely no reason to include data going back to 2008-01-02. Even if you need to do a running total, you shouldn't need to include more than one or two more months worth of data.
The purpose of providing a DESIRED RESULT is that it should reflect the results obtained FROM YOUR SAMPLE, so that people can run their code on your sample and match your desired results. I looked at the last value for the month, the total for the month, the count for the month, and I could find no way to even come close to your desired result. I can't tell if my approach is correct, because I can't figure out how you got from your sample to the desired result.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 28, 2011 at 8:56 am
Drew, first thank you for the comments, Although it was not appreciated being chastised the way you did it. I wanted to provide all of the information I had and really wanted to present what I am currently facing, saying that, if you could not find a solution it would have been simpler and more appreciated for you to say there is not a possible solution to this problem in code, that would have been taken better. But honestly, quit making people feel bad that are asking for help out here, I have been a member of these forms for quite a while and find people are usually very helpful not sarcastic and badgering when it comes to the responses.
October 28, 2011 at 9:27 am
It wasn't chastisement, it's realism. You have to understand that everyone here is a volunteer. The more hurdles you place in front of people, the fewer will even attempt a solution. 20,000+ lines of code is a HUGE hurdle. Not clearly defining your goal is a HUGE hurdle.
I never said that there wasn't a solution. In fact, I firmly believe that there is a solution. What I'm saying is that you still haven't clearly defined your goal, so I can't determine which solution you are looking for.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 28, 2011 at 10:05 am
Here is a shot in the dark at a solution.
;
WITH Totals_Ranked AS (
SELECT *, Row_Number() OVER (PARTITION BY m10_Class_Code ORDER BY Month_Start DESC) AS Month_Rank
, Row_Number() OVER ( PARTITION BY m10_Class_Code, Month_Start ORDER BY m10_Current_Date DESC ) AS Day_Rank
FROM #MyTableSC
CROSS APPLY ( SELECT DateAdd(Month, DateDiff(Month, 0, m10_Current_Date), 0) ) AS m(Month_Start)
WHERE m10_Current_Date >= DateAdd(Month, DateDiff(Month, 0, GetDate()) - 2, 0)
AND m10_Current_Date < GetDate()
)
SELECT m10_Class_Code
, Max( CASE WHEN Month_Rank = 1 THEN m10_Total_Count END ) AS Current_Day
, Max( CASE WHEN Month_Rank = 2 THEN m10_Total_Count END ) AS Last_Month
FROM Totals_Ranked
WHERE Month_Rank < 3
AND Day_Rank = 1
GROUP BY m10_Class_Code
But this produces the results:
m10_Class_CodeCurrent_DayLast_Month
A 5580 5599
AA 1022 1018
As I said before, this is nowhere close to your specified desired results, so I can't be sure that this is what you are looking for.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply