Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DavidChel
Constraint Violating Yak Guru
474 Posts |
 Posted - 2008-03-14 : 14:45:23
|
| My company is unusual in that our accounting periods are not actual months. We have what we call "Red Fridays." These are spaced 3-5 weeks apart. So, my company doesn't care what happened in the month of April, but they care what happened between April 4 and May 2 because these are the Red Fridays. So, I have created a database with a table called "RedFridays" with the dates for this year. I want to combine this with various tables in our ERP database. I use a Left Outer Join between the Red Friday Dates and the corresponding date in the ERP database. I need to create a custom grouping formula which accomplishes the following: 1. Subtract a certain number of months from today's date to determine which Red Friday would be the correct starting date. 2. Group records by date between that Red Friday and the second one. This would be listed as something like "Month 1". 3. Continue grouping in this way to the present date and that Red Friday range. Can anyone point me in the right direction? Any help would be greatly appreciated. |
|
|
dineshasanka
Yak Posting Veteran
72 Posts |
Posted - 2008-03-15 : 08:21:00
|
| by using case statment you can give the month you needSELECT RedMonth FROM (SELECT CASE WHEN Date >= 'April 05' AND DATE <='May 02' Then 'April'.......AS RedMonthFROM AAAA)X GROUP BY RedMonth---------------------http://dineshasanka.spaces.live.com/ |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-03-15 : 11:14:55
|
Since you already have a RedFridays table, you should be able to use it for your requirement. How does the table looks like ?Does it has a start and end date that determine your company's accounting period ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-15 : 11:34:32
|
quote:
Originally posted by DavidChelSo, I have created a database with a table called "RedFridays" with the dates for this year.
Presume this table as more than just the actual RedFriday date? would make sense to have 3 columns... RF_period,RF_startdate,RF_enddateYou could possibly make use MVJ's F_Table_Date function to make linking easier to any other date in the range (by passing the startdate,enddate to create the table valued results for all dates between the 2 ranges)the function is here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
DavidChel
Constraint Violating Yak Guru
474 Posts |
Posted - 2008-04-18 : 14:41:00
|
| I'm sorry, I dropped this thread. The table with the Red Friday DAtes has many colums, but the two that interest me look like: Table GLRuleColumn fdend fdstart 2/1/2008 1/1/2008 2/29/2008 2/2/2008 4/4/2008 3/1/2008 5/2/2008 4/5/2008And so on. Since today is April 18th, and this particular report requires the past 2 months the first start date would be 2/2/2008 for Feb, March, and then April MTD. Any further help would be greatly appreciated. |
|
|
|
DavidChel
Constraint Violating Yak Guru
474 Posts |
Posted - 2008-04-22 : 11:48:01
|
| Alright, so I've used MVJ's function to lay out all the dates for the until 2020. This is what my table looks like: create table tblRedFridayAllDates([DATE_ID] [int] not null primary key clustered,[DATE] [datetime] not null ,[YEAR] [smallint] not null ,[MONTH] [tinyint] not null ,[DAY_OF_WEEK] [tinyint] not null ,[REDFRIDAY] [BIT] )I have gone in and placed a '1' in every record that is a Red Friday. However, I'm still not sure how I would automatically group records between Red Fridays which is ultimately what I want to do. Can anyone point me in the right direction? |
|
|
|
|
|
|
|
|
Grouping by Date Range