Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Quarterly Dates.. Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 17, 2010 8:10 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, May 1, 2012 7:39 AM Points: 101, Visits: 185
 Hi All,I want to calculate Sales for each quarter for the past 10 Years. How can i write a query for this?
Post #938932
 Posted Thursday, June 17, 2010 8:14 AM
 SSC Eights! Group: General Forum Members Last Login: Tuesday, March 8, 2016 3:07 PM Points: 825, Visits: 1,212
 That is an extremely broad question? have you tried anything as of yet? do you have a specific question about a particular element? For example do you know how to do everything but calculate the Qtr number? DanIf only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Post #938937
 Posted Thursday, June 17, 2010 8:23 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, May 1, 2012 7:39 AM Points: 101, Visits: 185
 I have to insert data into the table at the end of each quarter for the past 10 years. For now i am able to insert data for the past 10 years.but i need it for quarterly.-- Set @EOM to end-of-last-quarter date DECLARE @EOM datetime SET @EOM = CAST(YEAR(GETDATE()) as varchar(4)) + '-' + CAST((MONTH(GETDATE())-1) / 3 * 3 + 1 AS varchar) + '-01' SET @EOM = DATEADD(day,-1,@EOM) --SET @EOM = '03-31-2010' -- For Testing Purpose--PRINT @EOM--Table to Include Last 10 years Worth of Sales Amt data at the End of Each YearDeclare @StartDate datetime Declare @EndDate datetime SET @StartDate = CONVERT(DATETIME, CAST(YEAR(@EOM)-10 as varchar(4)) + '-01-01',120) SET @EndDate = DATEADD(year,1,@StartDate-1) --PRINT @Startdate--PRINT @EndDate--Loop to retrieve past 10 years worth of Data at the end of each year WHILE @EndDate < @EOM BEGIN SET @StartDate = DATEADD(Year,1,@StartDate) SET @EndDate = DATEADD(Year,1,@EndDate) IF @EndDate > @EOM SET @EndDate=@EOM --Insert into Table ---- @EndDate-- PRINT CONVERT(varchar(11),@StartDate,113) + ' - ' + CONVERT(varchar(11),@EndDate,113) End
Post #938944
 Posted Thursday, June 17, 2010 8:36 AM
 SSC Eights! Group: General Forum Members Last Login: Tuesday, March 8, 2016 3:07 PM Points: 825, Visits: 1,212
 I am still not sure where your question is but I am assuming it is in the date portion of your query.the following will return the quarter date. You can use this concept to pull dates that fall in that quarter. `select datepart(qq,getdate())`I also see you use a loop to pull the sales.Take a look at this article from Jeff Moden I is a very usual piece of information and describes the same type of this you are talking about.http://www.sqlservercentral.com/articles/T-SQL/62867/ DanIf only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Post #938957
 Posted Thursday, June 17, 2010 8:39 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:37 PM Points: 5,911, Visits: 10,366
 This will show you how to convert a range of dates into year/quarters. I'm also adding in the first and last date/time for each quarter. You should be able to use this to go forth and adapt to your requirements. `declare @StartDate datetimeset @StartDate = DateAdd(month, -1, DateAdd(year, DATEDIFF(year, 0, GetDate()), 0))-- See Jeff Moden's article -- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.-- NOTE! A permanent tally table will always be MUCH faster -- than this inline one. See the above article to create your own!;WITH Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Tens t1 CROSS JOIN Tens t2),Dates AS (SELECT MyDate = DateAdd(month, N, @StartDate) FROM Tally ),Quarters AS (SELECT MyDate, MyYear = YEAR(MyDate), Qtr = DatePart(quarter, MyDate) FROM Dates)SELECT MyYear, Qtr, QtrStartDate = MIN(MyDate), QtrEndDate = MAX(DateAdd(ms, -3, DateAdd(month, 1, MyDate))) FROM Quarters GROUP BY MyYear, Qtr ORDER BY MyYear, Qtr` WayneMicrosoft Certified Master: SQL Server 2008Author - SQL Server T-SQL RecipesIf you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #938964
 Posted Friday, June 18, 2010 9:02 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, May 1, 2012 7:39 AM Points: 101, Visits: 185
 Hey Wayne,I need Quarterly dates for the past 10 years. i tried to modify your code, but i am able to retrieve only past 9 years of data.Sahasam..
Post #939677

 Permissions