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 code if a have parameters @Start_Date and @End_Date (these are obviously the beginning and the end of the time frame). Also, i would like the date to be entered in MM/DD/YYYY format and not the YYYYMMDD.
This is somewhat urgent request. If someone can address, it would be greatly appreciated. Thank you
Below is the code:
Erland Sommarskog
Guest Posts: n/a
#2: Mar 15 '06
re: Compare given period in current year / previous year
--------------------------------------------------------------------------------
(jannoergaard@h otmail.com) writes:[color=blue]
> 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 ?[/color]
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, LastYearTrouble s =
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, esquel@sommarsk og.se
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 code if a have parameters @Start_Date and @End_Date (these are obviously the beginning and the end of the time frame). Also, i would like the date to be entered in MM/DD/YYYY format and not the YYYYMMDD.
This is somewhat urgent request. If someone can address, it would be greatly appreciated. Thank you
Below is the code:
Erland Sommarskog
Guest Posts: n/a
#2: Mar 15 '06
re: Compare given period in current year / previous year
--------------------------------------------------------------------------------
(jannoergaard@h otmail.com) writes:[color=blue]
> 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 ?[/color]
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, LastYearTrouble s =
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, esquel@sommarsk og.se
Comment