May 5, 2005 at 9:28 am
Morning,
I am working on a stored procedure for an Order Analysis report. The report contains the Period (Fiscal month) the Monday of each week on that period and the date of the order request along with $$ amonts for each of the departments in the report. Here is my problem I can generate the Order Date and the Monday of the week that it belongs in, but when it comes time to add the Period date it is not as easy as matching months becasue the start & End of some months are actually dates in the previous and next months. An example would be a list of all of the Mondays in June (period 6) and the start and end dates for June are 5-30-2005 and 7-4-2005. Because of this I can not go off of the month name or number to match them to a period date. The report ussually project sales for each of these departments out about 6 months so there would be 6 periods with either 4 or 5 week in each period. How can I pick the period dates, week of dates that fall into that period and then pull in all of the sales info with for each week and assign each week to a period?
Thanks
Kurt Kracaw
May 5, 2005 at 10:11 am
Could you post some sample data from your report table ?!
I'm not sure I understand your problem completely - eg: would the start and end dates for period 5 (May) be 5-2-2005 to 6-6-2005 ? For April would it be 4-4-2005 to 4-25-2005 ?
**ASCII stupid question, get a stupid ANSI !!!**
May 6, 2005 at 6:07 am
Good Morning again,
I have to many tables to post them here so I will try and explain this a lot clearer this time. I have a table 'Date' that has the starting sate for all of the fiscal months, I have a result set from a SP that I need to take the results from and assign them to one of the fiscal months. Columns in the Date table are Cyear (year) AMonth (Month) and PeriodBeginDate (first day of that fiscal month. The other table has Customer Name, Cust Number, Order Number, Part Number, Order Entry Date, Request Date, Promise Date, Ship Date, Customer Service Rep and a couple of calculations to determine days past promise and day past request along withAverage Lead days. In this result set I also determine the Monday of the week that that day falls into so I have a Week OF Date.
What I would LIke to do is take the data from the result set and assign it to a Period Begin Date in the first table. So I can take the Week Of date and say it belong s to June. The date format is Year, Month, Day. Hope this helps.
2005 Jan 050103
2005 Feb 050131
2005 Mar 050228
2005 Apr 050404
2005 May 050502
2005 Jun 050530
2005 Jul 050704
2005 Aug 050801
2005 Sep 050829
2005 Oct 051003
2005 Nov 051031
2005 Dec 051128
Kurt Kracaw
May 6, 2005 at 8:01 am
are the "periodbegindate" and "weekofdate" columns smalldatetime datatypes ?!
**ASCII stupid question, get a stupid ANSI !!!**
May 6, 2005 at 9:45 am
No, when I use DTS to make the table they keep the same format as the table of origin, Varchar I convert them to 2005-05-05 date when I call them in a Stored Procedure for the result set. AS for When I query for it it comes from a table where it is stored as decimal.
Kurt Kracaw
May 7, 2005 at 9:27 am
I hope you can set the datatype to datetime for all your date fields - I don't know how many other tables you are querying but it can really affect performance when you have to explicitly convert AND manipulate data at the same time....
Given what you have, this is the best that you can do....put in all the conversions where necessary.
select top 1 Amonth from date_table
where weekofdate >= periodbegindate
order by periodbegindate
Let me know if it works out!
**ASCII stupid question, get a stupid ANSI !!!**
May 10, 2005 at 8:48 am
Thanks for the help, I used what you told me to try and it returned the first month on the date table, not the month = the week of date. So I changed the SELECT TOP 1 To SELECT Distinct MAX (into a Temp table)and this seemed to help grab the right month, I then used a join statement to get the rest of the information that I needed. Thanks for the help it worked great and the report now runs faster than it has in the past.
Kurt Kracaw
May 10, 2005 at 8:54 am
Great! Thx for feedback.
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply