Hi
I want to write a function that can return a sum for a given date
range. The same function should be able to return the sum for the same
period year before.
Let me give an example:
The Table LedgerTrans consist among other of the follwing fields
AccountNum (Varchar)
Transdate
AmountMST (Real)
The sample data could be
1111, 01-01-2005, 100 USD
1111, 18-01-2005, 125 USD
1111, 15-03-2005, 50 USD
1111,27-06-2005, 500 USD
1111,02-01-2006, 250 USD
1111,23-02-2006,12 USD
If the current day is 16. march 2006 I would like to have a function
which called twice could retrive the values.
Previus period (for TransDate >= 01-01-2005 AND TransDate <=
16-03-2005) = 275 USD
Current period (for TransDate >= 01-01-2006 AND TransDate <=
16-03-2006) = 262 USD
The function should be called with the AccountNum and current date
(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.
How can I create a function that dynamically can do this ?
I have tried f.ex. calling the function with
@ThisYear as GetDate()
SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +
'-'+datepart(y,@ThisYear)
But the value for @dateStart is something like 12-07-1905 so this
don't work.
I Would appreciate any help on this.
BR / Jan 3 16488
(ja**********@hotmail.com) writes: Let me give an example: The Table LedgerTrans consist among other of the follwing fields AccountNum (Varchar) Transdate AmountMST (Real)
The sample data could be 1111, 01-01-2005, 100 USD 1111, 18-01-2005, 125 USD 1111, 15-03-2005, 50 USD 1111,27-06-2005, 500 USD 1111,02-01-2006, 250 USD 1111,23-02-2006,12 USD
If the current day is 16. march 2006 I would like to have a function which called twice could retrive the values. Previus period (for TransDate >= 01-01-2005 AND TransDate <= 16-03-2005) = 275 USD Current period (for TransDate >= 01-01-2006 AND TransDate <= 16-03-2006) = 262 USD The function should be called with the AccountNum and current date (GetDate() ?) and f.ex. 0 or 1 for this year / previous year. How can I create a function that dynamically can do this ?
I'm uncertain on want interface you want on your function (and I am
not sure that you should use a function anyway), but here is a
query for the task:
SELECT AccountNum, LastYearTroubles =
SUM(CASE WHEN Transdate BETWEEN
dateadd(YEAR, -1,
convert(char(4), @date, 112) + '0101')) AND
dateadd(YEAR, -1, @date)
THEN AmountMST
ELSE 0
END),
ThisYear =
SUM(CASE WHEN Transdate BETWEEN
convert(char(4), @date, 112) + '0101')) AND
@date)
THEN AmountMST
ELSE 0
END)
FROM Ledger
WHERE TransDate BETWEEN dateadd(YEAR, -1,
convert(char(4), @date, 112) + '0101')) AND
@date
GROUP BY AccountNum
As for the date conversion, format 112 is essentail for playing with
dates. This format is YYYYMMDD, and this is one of the formats that
always converts back to date in the same way.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Hi There
Thank you very much. I modified your query a lillte bit, and it worked
as wanted in the function.
CREATE FUNCTION GuruInvoicedPeriodNew (@AccountNum as
VarChar(10),@Period AS Int, @ThisY as DateTime)
RETURNS Float AS
BEGIN
DECLARE @LedgerTrans AS Float
SET @LedgerTrans = 0
IF @Period = 0 /*this year*/
BEGIN
SELECT @LedgerTrans =
SUM(AmountMST) FROM dbo.LedgerTrans
WHERE (DATAAREAID = dbo.GuruDataArea())
AND (TransDate >= (convert(char(4), @ThisY, 112) + '0101')
AND TransDate <= @ThisY)
AND AccountNum = @AccountNum
END
IF @Period = 1 /*previous year*/
BEGIN
SELECT @LedgerTrans = SUM(AMOUNTMST) FROM dbo.LEDGERTRANS
WHERE (DATAAREAID = dbo.GuruDataArea())
AND Transdate >= (dateadd(YEAR, -1, convert(char(4), @ThisY,
112) + '0101'))
AND TransDate <= dateadd(YEAR, -1, @ThisY)
AND AccountNum = @AccountNum
END
RETURN @LedgerTrans
END
BR/Jan
(ja**********@hotmail.com) writes: Thank you very much. I modified your query a lillte bit, and it worked as wanted in the function.
CREATE FUNCTION GuruInvoicedPeriodNew (@AccountNum as VarChar(10),@Period AS Int, @ThisY as DateTime) RETURNS Float AS BEGIN
Yellow alert! How are you going to use this function? If you are going
to say something like:
SELECT AccountNum, dbo.InvoicedPeriod(AccountNum, 1, getdate()),
dbo.InvoicedPeriod(AccountNum, 0, getdate())
FROM accounts
It's not going to perform well. Scalar UDFs is something you should use
with care, and not the least scalar UDFs that perform table access.
There is quite an overhead for calling a UDF once per row, and when you
do table access, you have essentially created a disguised cursor.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: www |
last post by:
Hi there,
I need to calculate working days for a given period, "Date from", "Date To".
Plus I want to be able to insert Public Holidays to exclude aswell. Your
help will be greatly appreciated....
|
by: Terencetrent |
last post by:
I have created a query that examines qarterly sales for 5 regions in
the country. The query contains data for the past 6 quarters for each
region and calculates the perecentage of total sales for...
|
by: Bernd Hohmann |
last post by:
Dear collegues,
small query problem.
A table (simplified example)...
customer as char(5)
inv_date as date
amount as double
|
by: jiffylube |
last post by:
I'm putting together a retail comp report that compares the current day stats to the same day, previous year. Is there a code I can use to get last year, same day?
For example: This year stats=...
|
by: Johan Mcgillicutty |
last post by:
Could use some help on this one-
I’m new to MS Access, and even newer to SQL. I’m building a database for clients of a treatment center, and need to keep track of when people are due for their...
|
by: craigfr |
last post by:
I want to create a graph (automatically in a report) which compares
the current year's values to the previous years by plotting two lines
on one chart.
I have one table which has the fields: ...
|
by: shilpareddy2787 |
last post by:
Hello,
I have some total values, I want to calculate percenatge of these Total Values.
I want to divide the total with No. Of working Days Excluding Saturdays and Sundays in a given period.
...
|
by: beaudreaux |
last post by:
I am needing to calculate a month to date for the previous year in a report. Example: today is 02/18/10 and I have a month to date for today(current year). I need the report to also show month to...
|
by: popovaa |
last post by:
Hi:
i need to get data for a time frame in a current year and same time frame in previous year. i found the below on this site which is almost what i need but i also need help on how to adjust the...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |