June 4, 2010 at 8:41 am
I need to be able to write a query that will build a start and end date. The end date already exists but the start date will be the previous end date:
YEAR PERIOD END DATE START DATE
CY1012010-01-31 2009-12-31
CY1022010-02-28 2010-01-31
CY1032010-03-28 2010-02-28
CY1042010-04-25 2010-03-28
June 4, 2010 at 9:01 am
timmynew11
To receive tested code please post table definition(s), sample data and desired results in an easily consumable format.
Refer to the first link in my signature block for explicit examples.
June 4, 2010 at 10:50 am
Thanks, but i figured out my issue with a cartesian join. It appears you have too much time on your hands to post an article on the proper way to post data and code for someone to be able to help. An experienced guru should be able to read anything, not have something properly laid out with comments and everything. Obviously you don't seem to work in the real world where time is an issue.
June 4, 2010 at 11:06 am
timmy,
It's not just him, no one can help you solve your problem without enough information. That's just reality. As the owner of the problem, you may not know what's relevant to a solution, which is why posting as much information as possible is good. Without tables and sample data, all someone can do is guess at what query could work. There may be a syntax error or logical error in the query they post, and they wouldn't know because they can't actually run the query. You might not know what's wrong because you didn't design the query. So this creates a bunch of unnecessary back-and-forth. Mix that with the fact that most of actually do work in the real world, where time is important... And you might realize that the people trying to help you want to get you a solution as quickly as possible so it's not eating up their own time. They don't have time to mess with sample tables and data. They need to be able to cut and paste the tables into their own instances, enabling them to immeadiately start working towards a solution for you.
If it's a big enough problem, you'll post all the information so someone can help you. If you can solve the problem in the time it takes to post all the necessary information, why do you need our help at all?
--J
June 4, 2010 at 11:35 am
timmynew11 (6/4/2010)
Obviously you don't seem to work in the real world where time is an issue.
Obviously you think that we're willing to spend hours solving your problem instead of doing our jobs.
Properly laid out data makes problems quicker and easier to solve, increasing the chance that you've going to get an answer from people who have busy real world jobs with time constraints and deadlines.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2010 at 11:37 am
timmynew11 (6/4/2010)
Thanks, but i figured out my issue with a cartesian join. It appears you have too much time on your hands to post an article on the proper way to post data and code for someone to be able to help. An experienced guru should be able to read anything, not have something properly laid out with comments and everything. Obviously you don't seem to work in the real world where time is an issue.
We are experienced individuals that have full time paid jobs to do and volunteer our time to help others when we can. The more you give us the better answers you will receive in return.
What we aren't are mind readers, we can't see what you are trying to do from here so we rely on you providing as much detail as possible to help us help you.
June 4, 2010 at 12:55 pm
timmynew11 (6/4/2010)
I need to be able to write a query that will build a start and end date. The end date already exists but the start date will be the previous end date:YEAR PERIOD END DATE START DATE
CY1012010-01-31 2009-12-31
CY1022010-02-28 2010-01-31
CY1032010-03-28 2010-02-28
CY1042010-04-25 2010-03-28
So the Periods overlap?
They all look like they share a date.
Glad you found an answer, I too would have wanted just a bit more clarification.
If nothing else, just to verify that you did want the overlap.
And last day of month, last day of month, last day of month, and then something else.
Maybe that is the end date you refer to.....or it could be all end dates.
Back to work.
Greg E
June 4, 2010 at 2:23 pm
Sorry if I affended anybody, time constraints really stress people out. When stressed, some of the simpler problems tend to seem difficult. In case anybody is looking I posted my solution below:
SELECT a.FY_CD, a.PD_NO, upper(dateName(month,dateAdd(month,CAST(a.PD_NO AS int),0) - 1)) AS MONTH_NAME,
dateAdd(day,1,MAX(b.PD_END_DT)) AS PD_START_DT, a.PD_END_DT
FROM ACCTING_PD a JOIN ACCTING_PD b ON a.PD_END_DT > b.PD_END_DT
GROUP BY a.FY_CD, a.PD_NO, a.PD_END_DT
ORDER BY a.FY_CD, a.PD_NO
I did not want the dates to overlap, just an example that I typed out, but the solution above will display the proper start and end dates with no overlap.
June 4, 2010 at 2:39 pm
Just be aware that the above query will perform absolutely terribly on larger row counts (and by larger I mean maybe thousands not tens of millions), as a result of the triangular join (the join on >) If you're going to use that, make sure that the row counts involved are small (very small) and keep an eye on its performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 4, 2010 at 4:01 pm
{edit} Never mind...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2010 at 10:26 am
timmynew11 (6/4/2010)
Sorry if I affended anybody, time constraints really stress people out. When stressed, some of the simpler problems tend to seem difficult. In case anybody is looking I posted my solution below:SELECT a.FY_CD, a.PD_NO, upper(dateName(month,dateAdd(month,CAST(a.PD_NO AS int),0) - 1)) AS MONTH_NAME,
dateAdd(day,1,MAX(b.PD_END_DT)) AS PD_START_DT, a.PD_END_DT
FROM ACCTING_PD a JOIN ACCTING_PD b ON a.PD_END_DT > b.PD_END_DT
GROUP BY a.FY_CD, a.PD_NO, a.PD_END_DT
ORDER BY a.FY_CD, a.PD_NO
I did not want the dates to overlap, just an example that I typed out, but the solution above will display the proper start and end dates with no overlap.
I agree with Gail, and I think if you posted more of what was asked, Jeff would be more than happy to point you towards a much more scalable solution.
To me, simple would have been to make a Fiscal Calendar table. With columns for Calendar Date, FY, FM, names, etc. you would have something I find very flexible.
The last thing I would ever try to do is join 2 copies of what could be very large tables together like this.
I would likely take down production, and still have to fix the problem.
We use our table for other things too - Fiscal Week, keep track of shop days for daily rates, etc.
The reason they ask for this information is simple - it gets both parties up to speed on what the scenario is, and minimizes confusion. Expected results are much clearer.
Time is saved on both ends.
Greg E
Greg E
June 6, 2010 at 11:23 am
Actually the query works just fine on my 10,000 record table. The table won't be much bigger than it already is, so this solution works for me. I can't create a view, stored procedure, or temp tables because of the way the recordset needs to be called, so the only way possible was calling a separate query to return the results. Thanks for all the input, but this post should now be closed.
June 6, 2010 at 1:41 pm
timmynew11 (6/6/2010)
Actually the query works just fine on my 10,000 record table. The table won't be much bigger than it already is, so this solution works for me. I can't create a view, stored procedure, or temp tables because of the way the recordset needs to be called, so the only way possible was calling a separate query to return the results. Thanks for all the input, but this post should now be closed.
Heh... "works just fine on my 10,000 record table". Famous last words. 😉 And, we'll let you know if we want to "close" the post. 😛 If you don't want to be bothered learning about a better way, just unsubscribe from the post.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2010 at 3:20 pm
timmynew11 (6/4/2010)
Sorry if I affended anybody, time constraints really stress people out. When stressed, some of the simpler problems tend to seem difficult. In case anybody is looking I posted my solution below:SELECT a.FY_CD, a.PD_NO, upper(dateName(month,dateAdd(month,CAST(a.PD_NO AS int),0) - 1)) AS MONTH_NAME,
dateAdd(day,1,MAX(b.PD_END_DT)) AS PD_START_DT, a.PD_END_DT
FROM ACCTING_PD a JOIN ACCTING_PD b [font="Arial Black"]ON a.PD_END_DT > b.PD_END_DT [/font]
GROUP BY a.FY_CD, a.PD_NO, a.PD_END_DT
ORDER BY a.FY_CD, a.PD_NO
I did not want the dates to overlap, just an example that I typed out, but the solution above will display the proper start and end dates with no overlap.
Alright... just in case someone else comes along and needs to do the same thing... whatever you do, I recommend that you don't use Timmy's posted code because it has a performance "time bomb" in it. I've highlighted that very bad section of code that Gail and the others are talking about in Timmy's code in the quote above. It creates what is known as a "Triangular Join" and, even with just 10,000 rows, it causes a lot of unnecessary CPU and I/O system usage. Please see the following URL for why that's such a bad thing...
http://www.sqlservercentral.com/articles/T-SQL/61539/
Further, Timmy's code doesn't return the first period. If he had taken the time to actually test against the data he gave us, he'd have seen that. I hope he didn't actually unsubscribe from this thread so that he can find that out long before it'll cause him more "stress". 😉
For such a small number of rows as those in the posted data example, it'll make it look like Timmy's code "wins" the footrace insofar as % of Batch (a very poor measurement method) goes in the actual execution plan. For larger numbers of rows, the internal rowcount for Timmy's code will explode at exponential rates. For 10,000 rows, it'll likely generate 100,000,000 million rows whereas the following code will only touch about 20,000 rows.
--===== Generate the test table and data. This is NOT a part of the solution.
CREATE TABLE #MyHead
(
YEAR CHAR(4) NOT NULL,
PERIOD INT NOT NULL,
[END DATE] DATETIME NOT NULL,
[START DATE] DATETIME
);
INSERT INTO #MyHead
(YEAR,PERIOD,[END DATE])
SELECT 'CY10','1','2010-01-31' UNION ALL
SELECT 'CY10','2','2010-02-28' UNION ALL
SELECT 'CY10','3','2010-03-28' UNION ALL
SELECT 'CY10','4','2010-04-25';
ALTER TABLE #MyHead
ADD PRIMARY KEY CLUSTERED (Year, Period);
--===== This is a possible solution which doesn't use a triangular join
SELECT cur.Year,
cur.Period,
UPPER(DATENAME(mm,DATEADD(mm, cur.Period-1, 0))) AS Month_Name,
ISNULL(prv.[End Date],
DATEADD(yy,DATEDIFF(yy,0,cur.[End Date]),0)-1) AS [Start Date],
cur.[End Date]
FROM #MyHead cur
FULL OUTER JOIN #MyHead prv
ON cur.Year = prv.Year
AND cur.Period - 1 = prv.Period
WHERE cur.Year > ''
ORDER BY cur.Year, cur.Period;
timmynew11 (6/4/2010)
I did not want the dates to overlap, just an example that I typed out,...
Heh... it's no wonder he's under stress. He won't take the time to even define the correct problem when he's asking for help. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2010 at 9:06 am
Yes, Jeff's solution does return all records, but what it does not do is give you data that is correct. Seeing how the Year is a varchar field and not a numeric field, his solution wouldn't work in a fiscal year vs calendar year scenario like below, which is also data is the original table. When a company chooses to switch their accounting period from fiscal to calendar, the dates and periods get thrown off. See data set below for CY08 period 1:
data in table:
200812N2008-03-31 00:00:00
20091C2008-04-27 00:00:00
20092C2008-05-25 00:00:00
20093C2008-06-30 00:00:00
20094C2008-07-27 00:00:00
20095C2008-08-24 00:00:00
20096C2008-09-30 00:00:00
20097C2008-10-26 00:00:00
20098N2008-10-27 00:00:00
CY081C2008-11-23 00:00:00
CY082C2008-12-31 00:00:00
CY091C2009-01-25 00:00:00
my code result set:
200812DECEMBER2008-03-03 00:00:002008-03-31 00:00:00
20091JANUARY 2008-04-01 00:00:002008-04-27 00:00:00
20092FEBRUARY2008-04-28 00:00:002008-05-25 00:00:00
20093MARCH 2008-05-26 00:00:002008-06-30 00:00:00
20094APRIL 2008-07-01 00:00:002008-07-27 00:00:00
20095MAY 2008-07-28 00:00:002008-08-24 00:00:00
20096JUNE 2008-08-25 00:00:002008-09-30 00:00:00
20097JULY 2008-10-01 00:00:002008-10-26 00:00:00
20098AUGUST 2008-10-27 00:00:002008-10-27 00:00:00
CY081JANUARY 2008-10-28 00:00:002008-11-23 00:00:00
CY082FEBRUARY2008-11-24 00:00:002008-12-31 00:00:00
CY091JANUARY 2009-01-01 00:00:002009-01-25 00:00:00
now view the result set from Jeff's code, not quite the results your looking for:
20097JULY 2008-09-30 00:00:002008-10-26 00:00:00
20098AUGUST 2008-10-26 00:00:002008-10-27 00:00:00
*****CY081JANUARY 2007-12-31 00:00:002008-11-23 00:00:00*****
CY082FEBRUARY2008-11-23 00:00:002008-12-31 00:00:00
CY091JANUARY 2008-12-31 00:00:002009-01-25 00:00:00
running my code when compared to Jeff's code on the table size that I specified, returned an accurate result set quicker than Jeff's inaccurate result set. I maybe a forum newbie, but when I bash someone's code, I would make sure my code is right first:w00t:
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply