I have a view that shows payment amount, payment date. I need to add a column to the view that shows what fiscal year the payment belongs to.
How can this be done?
I do have a table that has the fiscal start and end in it, tblGlobal with fields FiscalYearStart and FiscalYearEnd.
Maybe you should clarify your problem a little bit more. What kind of data are FiscalYearStart and FiscalYearEnd? Can you provide us a sample dump?
If you have a series of disjoint datetime intervals representing fiscal years, a simple join would do...
create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)
create table tblData
(DataDate datetime
)
insert into tblGlobal values (2000, '01-Oct-1999','30-Sep-2000')
insert into tblGlobal values (2001, '01-Oct-2000','30-Sep-2001')
insert into tblGlobal values (2002, '01-Oct-2001','30-Sep-2002')
insert into tblGlobal values (2003, '01-Oct-2002','30-Sep-2003')
insert into tblData values ('15-Oct-1998')
insert into tblData values ('15-Oct-1999')
insert into tblData values ('15-Oct-2000')
insert into tblData values ('15-Oct-2001')
insert into tblData values ('15-Oct-2002')
insert into tblData values ('15-Oct-2003')
insert into tblData values ('15-Oct-2004')
select
tblGlobal.FiscalYear
,tblData.DataDate
from tblData
inner join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
select
tblGlobal.FiscalYear
,tblData.DataDate
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
Note that the second query will return data even if it does not find a fiscal year match.
That's useful if you are concerned that you will forget to load tblGlobal on time at the start of a new fiscal year, or if data gets entered with an old or far future date.
It's important to understand that you can join on whatever you want to join on - you are not limited to just foreign key columns!
|||Ok, my fiscalyearstart and end fields are datetime. The date would look like paymentdate, AmountPaid, from table tblPayments. The fiscalyearstart and end are in tblGlobal.
10/1/2007 12:00:00 PM, $1000.00
The fiscal year might be 7/1/yyyy to 6/30/yyyy or 10/1/yyyy to 9/30/yyyy
I was thinking it might be good figure out a way to put the fiscal year in a field with each payment, unless there is a way to do it in the view.
I appreciate any help you give.
|||David this looks great. But I need to tell you one more thing, the reason I was looking to possibly have a field or column that showed the fiscal year for each payment is I will be using the view with a reporting tool. So I need a field to group on so I can show the sum of payments for each fiscal year.
Does it sound like this is something you can help with?
I greatly appreciate your help.
|||
create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)
create table tblData
(DataDate datetime
,DataValue int
)
insert into tblGlobal values (2000, '01-Oct-1999','30-Sep-2000')
insert into tblGlobal values (2001, '01-Oct-2000','30-Sep-2001')
insert into tblGlobal values (2002, '01-Oct-2001','30-Sep-2002')
insert into tblGlobal values (2003, '01-Oct-2002','30-Sep-2003')
insert into tblData values ('15-Oct-1998', 5)
insert into tblData values ('15-Oct-1999', 10)
insert into tblData values ('15-Oct-1999', 11)
insert into tblData values ('15-Oct-1999', 12)
insert into tblData values ('15-Oct-2000', 100)
insert into tblData values ('15-Oct-2000', 101)
insert into tblData values ('15-Oct-2000', 102)
insert into tblData values ('15-Oct-2001', 1000)
insert into tblData values ('15-Oct-2001', 1001)
insert into tblData values ('15-Oct-2001', 1002)
insert into tblData values ('15-Oct-2002', 2000)
insert into tblData values ('15-Oct-2003', 3000)
insert into tblData values ('15-Oct-2004', 4000)
select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
inner join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear
select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear
The group by is pretty standard sql, and allows you to produce summaries, counts, averages, etc for the group.
|||I will give this a try and let you know how it goes. Thank you so much.
|||David, or anyone that would know how to do this. I found that the reporting tool I must use will only accept views and will not let me join on a date range, only single fields.
I think what I need is something like this: (this is an uneducated thought)
I have a view of all payments ever made.
In my view I need an expression column that returns what fiscal year the payment was madebased on the month part of the fiscalyearstart and fiscalyearend fields from tblGlobal (not sure how we would join them in the view)
So if the payment date is 7/2/2007 and the fiscalyearstart month is 7 and the fiscalyearend month is 6 the expression column would return 2008
The actual fiscal year would range 7/1/2007 to 6/30/2008
I have no idea how to do this, this is just my thought.
|||Why not do all the joins you need to do in a view in the database. They your reporting tool is just issuing a simple select statement:
create view some_view_name as
select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear
Your report tool will surely allow you to add a where clause to a query using this view:
select * from some_view_name
where FiscalYear = 2002
ps - When you get the problem you asked for help on solved, you should really mark that thread as answered. If you have a new problem, you should start a new thread. That way, others who have a similar problem can find your thread and its answer.
|||David,
You are very correct, but I had thought about it and if there was a way to not base this on the entire fiscalyearstart and end from the tblGlobal. What I mean is that if they need to base a report on multiple fiscal years then the between join would only allow them to see the fiscal year entered into the tblGlobal table. So I was hoping there was some way to do what you have done here and base it somehow on the months of the fiscal year not the actual fiscal year. Just incase they need to base the report on multiple fiscal years.
For example;
If they enter a payment on 7/2/2007 and the fiscalyearstart is 7/1 and the fiscalyearend is 6/30 then the fiscal year the payment was entered is 2008 or If they enter a payment on 7/2/2007 and the fiscalyearstart is 10/1 and the fiscalyearend is 9/30 then the fiscal year the payment was entered is 2007.
You may have a more educated idea, but this is just what I was thinking about.
I really do appreciate your help.
|||This is the sample tblGlobal I guessed at:
create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)
It would allow you to store as many fiscal years as you want.
Are you telling me that tblGlobal only stores one record, and that FiscalYearStart and FiscalYearEnd are Month/Day, not a Month/Day/Year values?
Because it sure would have saved some time to know that two days ago!
You need to post the create table statements for the tables involved, along with sample data.
|||
Sorry, David,
Yes, the tblGlobal only has one record. The fiscalyearstart and end fields are datetime and do hold the entire date, like 7/1/2007.
Again sorry for missing this key detail. The data samples you have shown look fine to me.
|||If you mean that if we have FiscalYearStart 2007-07-01, all days before 07/01 (1st of July) of year Y would be in fiscal year Y-1 and all days after 07/01 would be in fiscal year Y, you may create a function to get this year like this:
CREATE FUNCTION [dbo].[GetFiscalYear] (@.tDateTime)RETURNSintASBEGIN-- Declare the return variable hereDECLARE @.ResultintDECLARE @.fmint;DECLARE @.fdint;SELECT @.fm =month(FiscalYearStart), @.fd =day(FiscalYearStart)FROM tblGlobal;IF month(@.t) > @.fmORmonth(@.t) = @.fmANDday(@.t) >= @.fdSET @.Result =year(@.t);ELSESET @.Result =year(@.t)-1;RETURN @.Result;END
Then you may use it like in this example:
select orderid, customerid, orderdate, dbo.GetFiscalYear(orderdate)from Orders
I tried the function, I past it a paymentdate of 7/16/2007 and it returned 2007, but it should be 2008.
If the fiscalyearstart is 7/1 and the fiscalyearend is 6/30 (7/1/2007 to 6/30/2008) so it is plus one here
7/16/2007 = a fiscal year of 2008 because the fiscal year ends on 6/30/2008
If the fiscalyearstart is 10/1 and the fiscalyearend is 9/30 (10/1/2006 to 9/30/2007)
7/16/2007 = a fiscal year of 2007 because the fiscal year ends on 6/30/2007
Does this sound right?
|||So flip the greater than/less than signs as needed, and add 1 instead of subtract 1 as needed.
It's your turn to do the programming!
No comments:
Post a Comment