I am trying to create report with 2 years data with self join. The issue I am facing is I need data from one table in 2 different columns for Crystal report. Any help is appreciated. Thanks
Putting data from one table into 2 columns shouldn’t be a problem. But, you need to give more details as to what you are trying to do. What does your data look like? Why do you need a self join to get two years worth of data? What does the output need to look like?
Thanks for the response. I am trying to create a report to compare last years student count vs this years for different terms. all the data I need is in one table that is why I need self join. My report will have 2-3 columns, have different rows like student count, distance learning student count, different locations of campuses student count, New student count, returning students etc. really appreciate you help.
Thanks
You don’t need a self join for that, you’ll just add an unnecessary level of complexity.
Filter your data for the 2 years you need and do a cross tab on year and add in your various measures. Or, you can do a regular table and create two variables for each measure adding in a where year = x condition to the formula.
Two approaches for this.
Either have a smart calendar table in your database/data warehouse and leverage that or use more basic case expressions if you’re writing the SQL code yourself.
Sales TY:
SUM(CASE WHEN FactSales.SalesYear = Year(GETDATE()) THEN FactSales.SalesValue ELSE 0 END)
Sales LY:
SUM(CASE WHEN FactSales.SalesYear = Year(GETDATE())-1 THEN FactSales.SalesValue ELSE 0 END)
The approach for a smart calendar would be to have a year offset flag in your calendar table, where current year = 0 and last year =1.
Then you’d create a similar case expression logic as the above but using the YearOffset = 1, which makes the solution DBMS-agnostic…