sql for Financial Reporting periods "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year"

Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine  for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.

I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.

IF @ViewRange = 'This Month'

SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;

IF @ViewRange = 'Last Month'

SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC;

 Any ideas?

 

0
emlarsen
10/26/2006 2:10:03 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies
1387 Views

Similar Articles

[PageSpeed] 30

Why not call this stored procedure from other stored procedures which also need to do the same thing?


Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
11/2/2006 7:37:29 AM

Thanks for the reply - That is what I'd like to do. I'd like to create a stored procedure or function that would return the dates for the appropriate reporting periods and hoped there was a simple way to create that or if there were already sql functions that would help.

 Any suggestions?

0
emlarsen
11/2/2006 1:51:24 PM
Hi Emlarsen, here I wrote a simple example for you:


CREATE PROC sp_GetFinacialReportDate @CurrentDate DATETIME=NULL,
@ThisYear INT OUTPUT,@LastYear INT OUTPUT,@ThisMonth INT OUTPUT,
@LastMonth INT OUTPUT, @ThisQuarterStartMonth INT OUTPUT, @LastQuarterStartMonth INT OUTPUT
AS
SET @CurrentDate=ISNULL(@CurrentDate,GETDATE())--default use getdate() to caculate
SELECT @ThisYear=YEAR(@CurrentDate),@ThisMonth=MONTH(@CurrentDate),
@ThisQuarterStartMonth=MONTH(@CurrentDate)-MONTH(@CurrentDate)%3+1
SELECT @LastYear=@ThisYear-1,@LastMonth=@ThisMonth-1,
@LastQuarterStartMonth= @ThisQuarterStartMonth-3
go

--test
DECLARE @ThisYear INT,@LastYear INT,@ThisMonth INT,
@LastMonth INT , @ThisQuarterStartMonth INT , @LastQuarterStartMonth INT

EXEC sp_GetFinacialReportDate null, @ThisYear OUTPUT,@LastYear OUTPUT,@ThisMonth OUTPUT,
@LastMonth OUTPUT, @ThisQuarterStartMonth OUTPUT, @LastQuarterStartMonth OUTPUT

SELECT @ThisYear,@LastYear,@ThisMonth,
@LastMonth, @ThisQuarterStartMonth, @LastQuarterStartMonth




Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
11/15/2006 7:56:40 AM
Sorry there is a bug in the code I post previously when current month is the last month of a quarter, use this new version:

ALTER PROC sp_GetFinacialReportDate @CurrentDate DATETIME=NULL,
@ThisYear INT OUTPUT,@LastYear INT OUTPUT,@ThisMonth INT OUTPUT,
@LastMonth INT OUTPUT, @ThisQuarter INT OUTPUT, @LastQuarter INT OUTPUT
AS
SET @CurrentDate=ISNULL(@CurrentDate,GETDATE())--default use getdate() to caculate
SELECT @ThisYear=YEAR(@CurrentDate),@ThisMonth=MONTH(@CurrentDate),
@ThisQuarter=DATEPART(q,@CurrentDate)
SELECT @LastYear=@ThisYear-1,@LastMonth=@ThisMonth-1,
@LastQuarter= DATEPART(q,DATEADD(q,-1,@CurrentDate))
print @LastQuarter
go

--test
DECLARE @ThisYear INT,@LastYear INT,@ThisMonth INT,
@LastMonth INT , @ThisQuarter INT , @LastQuarter INT

EXEC sp_GetFinacialReportDate '2000-01-01', @ThisYear OUTPUT,@LastYear OUTPUT,@ThisMonth OUTPUT,
@LastMonth OUTPUT, @ThisQuarter OUTPUT, @LastQuarter OUTPUT

SELECT @ThisYear As ThisYear,@LastYear As LastYear,@ThisMonth As ThisMonth,@LastMonth As LastMonth,
ThisQuarter=CONVERT(VARCHAR,(@ThisQuarter-1)*3+1)+'~'+CONVERT(VARCHAR,@ThisQuarter*3),
LastQuarter=CONVERT(VARCHAR,(@LastQuarter-1)*3+1)+'~'+CONVERT(VARCHAR,@LastQuarter*3)







Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
11/17/2006 6:58:06 AM

Have your reports take a start and end date and pass them in, or calculate them yourself.. Like...

DECLARE @StartDate datetime

DECLARE @EndDate datetime 

SELECT @StartDate=CASE @ViewRange

WHEN 'This Month' THEN DATEADD(m,DATEDIFF(m,0,GETUTCDATE()),0)

WHEN 'Last Month' THEN DATEADD(m,DATEDIFF(m,0,GETUTCDATE())-1,0)

WHEN 'This quarter' THEN DATEADD(q,DATEDIFF(q,0,GETUTCDATE()),0)

WHEN 'Last quarter' THEN DATEADD(q,DATEDIFF(q,0,GETUTCDATE())-1,0)

WHEN 'This year' THEN DATEADD(year,DATEDIFF(year,0,GETUTCDATE()),0)

WHEN 'Last year' THEN DATEADD(year,DATEDIFF(year,0,GETUTCDATE())-1,0) END

SELECT @EndDate=CASE @ViewRange

WHEN 'This Month' THEN DATEADD(m,DATEDIFF(m,0,GETUTCDATE())+1,0)

WHEN 'Last Month' THEN DATEADD(m,DATEDIFF(m,0,GETUTCDATE()),0)

WHEN 'This quarter' THEN DATEADD(q,DATEDIFF(q,0,GETUTCDATE())+1,0)

WHEN 'Last quarter' THEN DATEADD(q,DATEDIFF(q,0,GETUTCDATE()),0)

WHEN 'This year' THEN DATEADD(year,DATEDIFF(year,0,GETUTCDATE())+1,0)

WHEN 'Last year' THEN DATEADD(year,DATEDIFF(year,0,GETUTCDATE()),0) END

SELECT TOP 20 ....

WHERE DateField>=@StartDate AND DateField<@EndDate

 

If you really don't like the DECLARE and multiple selects, you CAN combine them into one massive query if you prefer.. Like...

SELECT TOP 20 ...

WHERE DateField>=CASE @ViewRange WHEN .... END

AND DateField<CASE @ViewRange WHEN .... END

Replacing the .... with the contents of the two above case/end statements

 

0
Motley
11/18/2006 5:12:04 AM

The above methods btw, are also probably the fastest methods to achieve your results as well.  As you can see, the first (and not so obvious, but also the second), calculates a start and end date, then uses those in a direct comparison to a datetime field (and can benefit from the field being indexed!)

Your methods requires that EVERY row calculate the year for some column, and the month for the same column, and then compare those values to the current year/month.  Obviously on a table with a few million rows, this results in a few million comparisons, as well as having to actually read each and every one of those million rows.

0
Motley
11/18/2006 5:24:59 AM

Great help thanks so much!!

I created a function as follows.

 

CREATE FUNCTION GetDateRange (

@ViewRange

as varchar(50),
@Period as char(1)
)


RETURNS datetime
AS
BEGIN

-- Declare the return variable here

DECLARE @PeriodDate datetime

 

IF

@Period = 'S'

SELECT @PeriodDate=CASE @ViewRange

 

 

WHEN 'This Month' THEN DATEADD(m,DATEDIFF(m,0,GETUTCDATE()),0)

WHEN 'Last Month' THEN DATEADD(m,DATEDIFF(m,0,GETUTCDATE())-1,0)

WHEN 'This quarter' THEN DATEADD(q,DATEDIFF(q,0,GETUTCDATE()),0)

WHEN 'Last quarter' THEN DATEADD(q,DATEDIFF(q,0,GETUTCDATE())-1,0)

WHEN 'This year' THEN DATEADD(y,DATEDIFF(y,0,GETUTCDATE()),0)

WHEN 'Last year' THEN DATEADD(y,DATEDIFF(y,0,GETUTCDATE())-1,0)

END

ELSE

SELECT @PeriodDate=CASE @ViewRange

WHEN 'This Month' THEN DATEADD(m,DATEDIFF(m,0,GETUTCDATE())+1,0)

WHEN 'Last Month' THEN DATEADD(m,DATEDIFF(m,0,GETUTCDATE()),0)

WHEN 'This quarter' THEN DATEADD(q,DATEDIFF(q,0,GETUTCDATE())+1,0)

WHEN 'Last quarter' THEN DATEADD(q,DATEDIFF(q,0,GETUTCDATE()),0)

WHEN 'This year' THEN DATEADD(y,DATEDIFF(y,0,GETUTCDATE())+1,0)

WHEN 'Last year' THEN DATEADD(y,DATEDIFF(y,0,GETUTCDATE()),0)

END

-- Return the result of the function

RETURN @PeriodDate

END

GO

===========================================

Select

dbo.GetDateRange('This Quarter','S') as StartDate, dbo.GetDateRange('This Quarter','E') as EndDate

 ---------- Thanks again for all your help.

0
emlarsen
11/20/2006 5:50:03 PM
For the dateadd/datediff functions make sure you use the full word "year" and not just "y".  "y" means dayofthemonth or something.
0
Motley
11/21/2006 1:46:23 AM
Reply:

Similar Artilces:

Precedence of "where" ("of", "is", "will")?
Nobody on #perl6 today could answer this one. Is: Str | Int where { $_ } the same as: (Str | Int) where { $_ } or: Str | (Int where { $_ }) ? Followup questions, Mr. President: What kind of operators are "where", "of", "is", and "will"? Is there a reason that S03 doesn't list them? What are their precedence(s)? -- Chip Salzenberg - a.k.a. - <chip@pobox.com> Open Source is not an excuse to write fun code then leave the actual work to others. Chip Salzenberg writes: &...

.ALLCOL("%COLUMN%", " ", ", ", ", ")
Do you know anyway for me to exclude a subset of columns returned by this function. We have two columns (rec_user and rec_datetime) which are in all of our tables, but when generating triggers I want automatically generate a script which does not include those two columns but does include all other columns in that table. Bruce I should add that I am using PD 9.0.0.580. Bruce "Bruce Lamb" <lamb.bruce@mayo.edu> wrote in message news:6HgI315nCHA.155@forums.sybase.com... > Do you know anyway for me to exclude a subset of columns returned by this > function. ...

quotes, quotes, quotes...
I am getting this error and I know what is causing it, but I have no idea how to fix it, any help would be great. The script steps through the /var/log/messages file on a linux server and puts The entries into a mysql database. However when it gets to the 'hlt' line in the messages file it just barfs. The single quotes are freaking it out. I know about quotes but not how to use in this situation. Thanks, Paul Error: May 27 17:53:00 localhost kernel: Checking 'hlt' instruction... OK. <----- doesn't like this in the messages file DBD::mysql::st exec...

"Using" or "With"
Hi all Please can someone enlighten to me as regards the difference with the "Using" and "With" statement when accessing data - which is better, what are the limitations and/or any pointers. Many thanks. Regards DaveDavid WinchesterPlease mark as answer if this is the solution.  using gives you the ability to use the connection and it closes the connection directlly after you finish using it. and there is no need to try- cach - finaly. there is no limitation on using USING keywordMuhanad YOUNISMCSD.NETMy Blog || My Photos || LinkedIn I have a dataobject the re...

"To" and "From" missing
When I print emails, the words "To" and "From" are blank, even though the "To" name and "From name (addresser, addressee) do show up. This is not a problem for other users on my system. Suggestions In mailbox right click, view. On the message window, right click and choose print options. Make sure print header is checked. -- Barry Merchant NSC Volunteer SysOp *** no email unless requested please!! *** > In mailbox right click, view. On the message window, right click and > choose print options. Make sure prin...

Replacing "\\" with "\"
Hi all I'm getting this value from a CheckBoxList control - a location of file, i have to remove "\\" and replace it with "\" and pass it to Query, how to do it, i tried with Replace, but coud'nt suceed. "\\\\Blaze10xp\\BLZ_SFS_07\\Sample Excel Files\\Excel Files\\report2.xls" thank's in advance - Prakash.C you tried Replace like this? string newstring = oldstring.Replace(@"\\",@"\");Plese, do not forget to click "Mark as Answer" on the post that helped you. Thanx!My blog: Scenes From A Developer Memory yes i tr...

"Me" is better than "You"
Yes I know, strings are frozen. But let me talk about it, I really can't get through the idea of a PC talkin to me. I consider my PC as an extension of myself, not a dumb companion who addresses Me as You. Yes there are times when I get angry with Him while I work and get wrong calculations etc.., but it really is my fault, Me using wrong istructions and eventually wanting to find someone else to blame, but it's Me. And yes, I consider Thunderbird my mail program, reading my mail on my PC as Me. So I personally like to have Me in the header bar as a compact address ...

Using "+" or "||"
Using SQLAnywhere 5.5.04, I've gotten into the habit of using "||" in ISQL to indicate a string concatenation. I needed to paste my SQL statement into the PowerBuilder script painter for some embedded SQL, and PB didn't like the "||" very much at all. I changed it to "+" and it seems to be ok. Do these two operators indicate ~exactly~ the same thing? moin, afaik these two's are not the same! if you're using "||" and any term is NULL then in the resultstring the term will be ignored if you use "+" then the resu...

replace the "." with a ","
Oi.... I need to build a small programm in ASP.NET and chose to use C# for it.Now i got everything working but there's one little problem.the first textbox is a double. I need to make it so that when someone enters a "." then it gets replaced by a ","any ideas?Ghan  string blah = "4.2.2.2";blah = blah.Replace(".", ",");Ryan Ryan OlshanASPInsider | Microsoft MVP, ASP.NEThttp://ryanolshan.comHow to ask a question...

double quote
hello there...  i tried everything of think but not working the way i wanted to be... not sure what i'm missing...i'm generating a <span> in code behind and then using in javascript.... here is what i'm doing code behind: int i=0string _keywordID = "keyword";string _name = row["visit_info_nm"].ToString().Trim(); String _getElementByID = String.Format("<span id='{0}' OnClick = \"document.getElementById('{1}').value='{2}';\">{3}</span><br>", i, _keywordID, _name, _name); here is what it generate : <span id='1' OnClick = \"document.getElementById('keyword')...

"-" not "_"
I wrote a SQL statement in the data tab. I wrote a bunch of alaises as example ' word-type ' but when I hit the layout tab it converts the "-" to "_". So now my field name is ' word_type '. Is there any way to prevent this? CardGunner Don' use a hypen ( - ).  It isn't a valid character for column names.   See http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188931,00.html   Here's an excerpt about column names: Letters as defined in the Unicode Standard 2.0 Decimal numbers from either B...

Regular Expression to remove "/", "\", "<", ">" and "="
Can anyone please show me the regular expression to reject a string ("<blue", "right>" etc.) which has the following expression in it: "/", "\", "<", ">" and "="  hi, It may Help u.. it is in Class file u may use this expressin in validation controls also. Regex objReg = new Regex(@"^[^,.?/\~|`;:'<>]*$", RegexOptions.Singleline); Regex objReg = new Regex(@"^[^,][^.][^?][^/][^\][^~|][^`][^;][^:][^'][[^<][^>]$", RegexOptions.IgnoreCase);Thanks &...

"JROC" / "JROK" / "JROCK" / "JROQ"
I just started a new contract and the tech lead told me that he wanted me to become familiar with something called "JROC" (among some other tools). I've been searching the web and I haven't found any dev tool called "JROC." Based on the name of the tool, it sounds like it encapsulates some javascript functionality. I have tried searching for different spellings - "JROK" / "JROCK" / "JROQ" but I haven't found any matches. Have you ever heard of a dev tool by this name?...

pulling out "a","an", "the" from beginning of strings
I need to pull out articles "a", "an", and "the" from the beginning of title strings so that they sort properly in MySQL. What is the best way to accomplish that if I have a single $scalar with the whole title in it? Thanks, Tim -- Tim McGeary tim.mcgeary@lehigh.edu --=-weUphL4mkB1WR5g+DMDc Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable On Tue, 2004-08-24 at 15:04, Tim McGeary wrote: > I need to pull out articles "a", "an", and "the" from the beginning of=20 >...

Web resources about - sql for Financial Reporting periods "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year" - asp.net.sql-datasource

Resources last updated: 1/23/2016 6:27:02 AM