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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply