I have 3 inpt tables-
day_level
Dim_type Id day_date month year
1 1 2015-01-05 January 2015
1 2 2015-01-06 January 2015
1 3 2015-01-07 January 2015
1 4 2015-01-08 January 2015
1 5 2015-01-09 January 2015
1 6 2015-01-10 January 2015
1 7 2015-01-11 January 2015
1 8 2015-01-12 January 2015
1 9 2015-01-13 January 2015
1 10 2015-01-14 January 2015
1 11 2015-01-15 January 2015
1 12 2015-01-16 January 2015
1 13 2015-01-17 January 2015
1 14 2015-01-18 January 2015
1 15 2015-01-19 January 2015
1 16 2015-01-20 January 2015
This shows the weekly basis data.
week_level
Dim_type Id week_number month year
2 101 week1 January 2015
2 102 week2 January 2015
2 103 week3 January 2015
2 104 week4 January 2015
2 105 week1 February 2015
This shows the monthly basis data.
month_level
Dim_type Id month year
3 1001 January 2015
3 1002 January 2015
3 1003 January 2015
3 1004 January 2015
3 1005 February 2015
I have a 3 tables which have data according to the day level, week level and month level. There is Dim_type column which tells us which data is from which table like
dim_type=1 is for day level
dim_type=2 is for week level
dim_type=3 is for month level
Here I am not able to write a function/procedure which on the basis of input dates given by the user can decide which of the data is to be shown-
Here I give you some example suppose the date input by the user start date- 2015-01-01 and end date- 2015-01-31. Now here data is needed for whole January month so data will come from month table.
Second like start date-2015-01-05 and end date- 2015-01-07. Now we don't have a complete month on either side so here we have to consider week data. So here output will be like-
id value
102 week2 ( January)
103 week3 ( ,, )
104 week4 ( ,, )
105 week1 (Febuaray)
Third is like the start date- 2015-01-05 and end date- 2015-01-20 so it will be like
id value
102 week2 ( January)
103 week3 ( ,, )
14 day level data for 18 January
15 day lvel data for 19 January
12 day lvel data for 20 January
So I am not able make a stored procedure/ functions that will able to tell if there is whole month aur this by week data or it should be output as day level. Can anyone help me? Thanks