January 27, 2014 at 1:24 am
Hi,
I have two tables, one is financial year master and the other one is detail table.
Financial year master table
Financial yearYear_start_dateYear_end_date
FY11-12 01/04/11 31/03/12
FY12-13 01/04/12 31/03/13
FY13-14 01/04/13 31/03/14
FY14-15 01/04/14 31/03/15
Detail table
Cust_noDate
A15/07/11
B21/09/11
A27/02/12
A17/08/12
B20/03/13
C 13/05/13
D14/04/14
E09/06/14
D28/02/15
Now based the above two tables I need a result like the below one
Cust_noDateFinancial year
A15/07/11FY11-12
B21/09/11FY11-12
A27/02/12FY11-12
A17/08/12FY12-13
B20/03/13FY12-13
C 13/05/13FY13-14
D14/04/14FY14-15
E09/06/14FY14-15
D28/02/15FY14-15
Thanks in Advance
Regards,
Sundar
January 27, 2014 at 1:57 am
SELECTCust_No,
Date,
(SELECT Financialyear FROM Financial_year_master_table WHERE Date BETWEEN Year_start_date AND Year_end_date) AS Financial_year
FROMDetail_table
You could do it this way, but you doing query for each row, so performance might not be great.
Another option could be using Date Dimension and joining the tables
Regards
S
(P.S my 1st post helping others so hopefully I'm giving good information :-))
January 27, 2014 at 2:04 am
mea99sdp (1/27/2014)
SELECTCust_No,Date,
(SELECT Financialyear FROM Financial_year_master_table WHERE Date BETWEEN Year_start_date AND Year_end_date) AS Financial_year
FROMDetail_table
You could do it this way, but you doing query for each row, so performance might not be great.
Another option could be using Date Dimension and joining the tables
Regards
S
(P.S my 1st post helping others so hopefully I'm giving good information :-))
Congrats on your first helping post 🙂
Simply joining the two tables together will give you better performance:
SELECT
Cust_No
,Date
,Financialyear
FROM dbo.Detail_table d
JOIN dbo.Financial_year_master_table f ON d.Date >= f.Year_start_date AND d.Date <= f.Year_end_date
@Sundar: please read the first link in my signature about how to ask questions on a forum. It details how you can post DDL statements, sample data and desired output. This will get you better answers much faster, as people can more effectively test the SQL queries they write for you. For example, I couldn't test the query I wrote, because I don't have the tables and the data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 27, 2014 at 6:00 pm
If your fiscal year always starts on April Fool's Day, you don't even need the master table:
WITH Detail_table (Cust_no, [Date]) AS
(
SELECT 'A',CAST('2011-07-15' AS DATETIME) -- 15/07/11
UNION ALL SELECT 'B','2011-09-21' --21/09/11
UNION ALL SELECT 'A','2012-02-27' --27/02/12
UNION ALL SELECT 'A','2012-08-17' --17/08/12
UNION ALL SELECT 'B','2013-03-20' -- 20/03/13
UNION ALL SELECT 'C','2013-05-13' --13/05/13
UNION ALL SELECT 'D','2014-04-14' -- 14/04/14
UNION ALL SELECT 'E','2014-06-09' --'09/06/14
UNION ALL SELECT 'D','2015-02-28' --28/02/15
)
SELECT Cust_no, [Date], Financial_Year=CASE x WHEN -1 THEN fy1 ELSE fy2 END
FROM Detail_table a
CROSS APPLY
(
SELECT x=SIGN(DATEDIFF(day, RIGHT(DATEPART(year, [Date]), 4) + '-04-01', [Date]))
,fy1='FY' + RIGHT(DATEPART(year, [Date])-1, 2) + '-' + RIGHT(DATEPART(year, [Date]), 2)
,fy2='FY' + RIGHT(DATEPART(year, [Date]), 2) + '-' + RIGHT(DATEPART(year, [Date])+1, 2)
) c;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 28, 2014 at 8:10 am
Thanks for all your replies.
@mea99sdp - Glad your first answer worked 🙂
@Koen Verbeeck - i have read "How to post forum questions" 🙂
@dwain.c - Thanks for giving a new technique to achieve this 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply