January 31, 2017 at 8:15 am
Hi, I'm looking for some help with a query I'm writing (please bare with me, am an absolute beginner!). At the moment I have the below, which returns data based on a specific user (YK in this instance) and also by date. In the query below this is hard coded in as Period 9 (Which is January as it's based on the financial year) and 2016.
select
MAX(Users.Fullname) as 'Name'
,MAX(Targets.FeesBilled) as 'Billing Target'
,sum(Analysis.CostsDelivered) as 'Actual Billed'
,sum(Analysis.CostsReceived) as 'Actual Received'
from Analysis
join Targets
on Analysis.Feeearnerref = Targets.Feeearnerref
and Analysis.Period = Targets.Period
and Analysis.Year =Targets.Year
join Users
on Analysis.Feeearnerref = Users.Code
where Analysis.FeeEarnerRef = 'yk'
and Analysis.Period = '9'
and Analysis.Year = '2016'
group by Analysis.FeeEarnerRef
What I need is to be able to make this query more "automated". I've been asked to provide this info on a weekly basis for 11 different departmentsand I don't want to have to keep changing the date each time on 11 different queries. Also, obviously when the financial year changes, this would mean changing the years again
My thoughts are that I can get something like a CASE statement to return the current month and year and do something like :
select YEAR(GETDATE()) =
case YEAR
when '1' Then @year = YEAR - 1
when '7' Then @year = YEAR
end
I know the format on that CASE statement is all wrong, but I've never used CASE, GETDATE or variables before (I've only been learning SQL for 2 months!)
Any help at all would be massively appreciated! If something doesn't makes sense, please ask.
Thanks in advance!
January 31, 2017 at 8:24 am
First, welcome and hope we can help.
Second, here's what I'd do. I'd make this a procedure, not a query. That way I can easily call it, stick it in parameters, let it be called from reports, etc.
I'd make the user, period, and year parameters like this:
CREATE PROCEDURE GetBillingReport @user VARCHAR(20) = NULL,
@period INT = 1,
@year CHAR(4) = NULL
AS
BEGIN
IF @year IS NULL
SET @year = CAST(YEAR(GETDATE()) AS CHAR(4));
SELECT
Name = MAX(Users.fullname),
[Billing Target] = MAX(targets.FeesBilled),
[Actual Billed] = SUM(analysis.CostsDelivered),
[Actual Received] = SUM(analysis.CostsReceived)
FROM
Analysis
JOIN targets
ON analysis.feeearnerref = targets.feeearnerref
AND analysis.Period = targets.period
AND analysis.year = targets.year
JOIN Users
ON analysis.feeearnerref = Users.Code
WHERE
analysis.FeeEarnerRef = @user
AND analysis.Period = @period
AND analysis.year = @year
GROUP BY analysis.FeeEarnerRef;
END;
When I call this, I do so like:
exec GetBillingReport 'yk', 1, '2016'
Does that help? It can be tricky to code in logic to handle automatically selecting years, but we can help if you want.
Note, I defaulted period to 1, you could change that as well.
January 31, 2017 at 8:31 am
I have some follow up question if that's OK?
- The report is going to be completely automated (we user TaskCentre for this) and will be sent out each week. How do I get the procedure to run that way? There will be no way to manually enter the values for user, moth or year. The user selection i will build in with some WHERE clauses, but that's why I was asking about grabbing the month and setting a "period" variable and adding or takingaway 1 from the Year based on that.
- Are Procedures just in a local session? i.e. how do i store the prioceudre somehwere so that I don't have to run the big block of code everytime? I knwo in POwereshll I'd put as a module or even a fucntion in my profile or soemthing. Do iI do something like that?
Really appreicate the help!
January 31, 2017 at 8:32 am
select
MAX(Users.Fullname) as 'Name'
, MAX(Targets.FeesBilled) as 'Billing Target'
, sum(Analysis.CostsDelivered) as 'Actual Billed'
, sum(Analysis.CostsReceived) as 'Actual Received'
from Analysis a
join Targets t
on a.Feeearnerref = t.Feeearnerref
and a.Period = t.Period
and a.Year =t.Year
join Users u
on a.Feeearnerref = u.Code
where a.FeeEarnerRef = 'yk'
and a.Period = '9'
and a.Year = '2016'
group by a.FeeEarnerRef
Now, the proper terminology is a CASE expression. I wouldn't normally be so pedantic, but the distinction is important here, since the purpose of an expression is solely to evaluate to a value. With that in mind, your example doesn't make sense, because you're trying to assign a value to an expression, which already has a value ( YEAR(GETDATE()) is 2017 if you run it this year). Please will you show us where in your query you see the CASE expression going?
John
January 31, 2017 at 8:47 am
I may be approaching the problem I have in the wrong way. I'm not dead set on using the method I outlined. Basically, I have a working query that returns data. What I want to be able to do is automate it a little. We already use TaskCentre to schedule SQL reports to be sent out to staff members automatically. In this report, I have statically set value for the month and year that I need to ideally make dynamic. The problems I have with this is :
For the date, if it returns as 1 for Jan, 2 for Feb etc, that will need changing. The software that I am querying the DB of lists dates as Period 1 (May 2016), to period 12 (April 2017). I assumed what I'd have to do to get around this would be to query the date and then convert based on the values. I did some research and found the CASE expression which I though would be able to do what this pseudo code does:
query date
"31-01-2017"
if month is between 1-4, minus 1 from year value.
if month is 1, set period to 9,
if month is 2, set period to 10
if month is 5, set period to 1
Is there a better way of doing this If not, is this possible at all?
January 31, 2017 at 9:06 am
danblank000 - Tuesday, January 31, 2017 8:47 AMHi, like I said, I'm an absolute beginner so most of what you said is new to me. Will try and take on board though.I may be approaching the problem I have in the wrong way. I'm not dead set on using the method I outlined. Basically, I have a working query that returns data. What I want to be able to do is automate it a little. We already use TaskCentre to schedule SQL reports to be sent out to staff members automatically. In this report, I have statically set value for the month and year that I need to ideally make dynamic. The problems I have with this is :
For the date, if it returns as 1 for Jan, 2 for Feb etc, that will need changing. The software that I am querying the DB of lists dates as Period 1 (May 2016), to period 12 (April 2017). I assumed what I'd have to do to get around this would be to query the date and then convert based on the values. I did some research and found the CASE expression which I though would be able to do what this pseudo code does:
query date
"31-01-2017"
if month is between 1-4, minus 1 from year value.
if month is 1, set period to 9,
if month is 2, set period to 10
if month is 5, set period to 1Is there a better way of doing this If not, is this possible at all?
No need for a CASE expression here at all - just a bit of date arithmetic.
-- Create table and insert some random dates from 2017 and 2018
CREATE TABLE #John (RandomDate date)
INSERT INTO #John
SELECT DATEADD(day,RAND(CHECKSUM(NEWID()))*729,'20170101')
FROM sys.objects
-- This is the calculation
SELECT
RandomDate
, YEAR(DATEADD(month,-4,RandomDate)) AS AccountingYearStart
, YEAR(DATEADD(month,-4,RandomDate)) + 1 AS AccountingYearEnd
, MONTH(DATEADD(month,-4,RandomDate)) AS AccountingMonth
FROM #John
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply