January 12, 2016 at 1:10 am
Hi All,
I have a requirement as below. One of my job runs every 9th calendar of every month. I have to extract the data as below.
For example: I am running the job on 9th Jan 2015 - Based on the current date, need to extract the previous month (Dec 2015). If the previous month is December then it should extract data from 1st Jan 2015 to 31 Dec 2015. (Provided sample data for 2 months-Jan and Feb 2015)
If the job is running on 9th March 2015 (previous month - Feb 2015) then it should extract data as below. From 01Jan 2015 to 27Feb2015.
Note: Based on the month_id from below table, Feb ending is 27th Feb 2015. 28th Feb 2015 falls into March 2015
Can any one help me how to write the logic for this?
Below are the sample data.
CREATE TABLE temp_calendar(
[day_id] [int] NOT NULL,
[day_desc] [varchar](60) NOT NULL,
[month_id] [int] NOT NULL,
[month_desc] [varchar](60) NOT NULL,
[year_id] [int] NOT NULL,
[year_desc] [varchar](60) NOT NULL
)
insert into temp_calendar( [day_id],[day_desc],[month_id],[month_desc],[year_id],[year_desc] )
select '10958','Jan 01 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10959','Jan 02 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10960','Jan 03 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10961','Jan 04 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10962','Jan 05 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10963','Jan 06 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10964','Jan 07 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10965','Jan 08 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10966','Jan 09 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10967','Jan 10 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10968','Jan 11 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10969','Jan 12 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10970','Jan 13 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10971','Jan 14 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10972','Jan 15 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10973','Jan 16 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10974','Jan 17 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10975','Jan 18 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10976','Jan 19 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10977','Jan 20 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10978','Jan 21 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10979','Jan 22 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10980','Jan 23 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10981','Jan 24 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10982','Jan 25 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10983','Jan 26 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10984','Jan 27 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10985','Jan 28 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10986','Jan 29 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10987','Jan 30 2015','201501','Jan 2015','2015','2015' UNION ALL
select '10988','Jan 31 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10989','Feb 01 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10990','Feb 02 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10991','Feb 03 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10992','Feb 04 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10993','Feb 05 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10994','Feb 06 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10995','Feb 07 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10996','Feb 08 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10997','Feb 09 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10998','Feb 10 2015','201502','Feb 2015','2015','2015' UNION ALL
select '10999','Feb 11 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11000','Feb 12 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11001','Feb 13 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11002','Feb 14 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11003','Feb 15 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11004','Feb 16 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11005','Feb 17 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11006','Feb 18 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11007','Feb 19 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11008','Feb 20 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11009','Feb 21 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11010','Feb 22 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11011','Feb 23 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11012','Feb 24 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11013','Feb 25 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11014','Feb 26 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11015','Feb 27 2015','201502','Feb 2015','2015','2015' UNION ALL
select '11016','Feb 28 2015','201503','Mar 2015','2015','2015'
January 12, 2016 at 2:22 am
Try this
declare @currentdate datetime = '20150308' -- getdate() --
select tc.*
from (
select y = datepart(YYYY,dateadd(m,-1,@currentdate))
, m = datepart(m,dateadd(m,-1,@currentdate))
) params
join temp_calendar tc
on [month_id] between y*100+1 and y*100+m
;
January 12, 2016 at 5:34 am
Thanks a lot for the reply. Its worked for me. Need your help in one more thing.
From your query, I am extracting min and max day_id's into temp table and then, I would like to pass those values to other query. In that other query, I would like to show "year" and "month" (This query is at the end)
Please look at the below one, which I have added to extract min, max day_id's and month_id
create table #t_temp_data (
min_dayid int not null,
max_dayid int not null,
month_id int not null)
declare @currentdate datetime = '20150308' -- getdate() --
insert into #t_temp_data
select min(tc.day_id)
,max(tc.day_id)
,month_id
--select tc.*
from (
select y = datepart(YYYY,dateadd(m,-1,@currentdate))
, m = datepart(m,dateadd(m,-1,@currentdate))
) params
join temp_calendar tc
on [month_id] between y*100+1 and y*100+m
group by month_id
;
select * from #t_temp_data
Result is as below:
min_dayidmax_dayid month_id
1095810987 201501
1098811015 201502
I am using min and max day_id's in another query as below, where I would like to show "Year" and "Month" values in select condition.
select
@year as [YEAR],
@MONTH as [MONTH],
X.cases as xvolume
from "X"
where X.day_id >= (select min_dayid from #t_temp_data)
and X.day_id <= (select max_dayid from #t_temp_data)
Can you help me to achieve this.
January 12, 2016 at 7:11 am
prameela.vankineni (1/12/2016)
I am using min and max day_id's in another query as below, where I would like to show "Year" and "Month" values in select condition.select
@year as [YEAR],
@MONTH as [MONTH],
X.cases as xvolume
from "X"
where X.day_id >= (select min_dayid from #t_temp_data)
and X.day_id <= (select max_dayid from #t_temp_data)
Can you help me to achieve this.
Not sure i fully understand your requirements at this point. Which year and which month are needed? Probably, you need extract them from #t_temp_data.month_id when joining #t_temp_data to "X".
Try
with X(DayId) as ( -- sample data
select 10986 UNION ALL
select 10987 UNION ALL
select 10996 UNION ALL
select 10997 UNION ALL
select 10998 UNION ALL
select 10999 UNION ALL
select 11000 UNION ALL
select 11001
)
select DayId, y=month_Id/100, m=month_Id%100
from X
join #t_temp_data td
on X.DayId between td.min_dayid and td.max_dayid
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply