August 3, 2015 at 12:34 am
Hi Sir,
I have some my below requirment to loading some last year and currnet year records for some ID's in my table,
we have to load the ID's that are active at the end of the year for the prior year and ID's that are active as of today for the current year.Here is the scenario when the ID is currently terminated but active at the end of the prior year and the record is not in the table.so, we didn’t load the count for the prior year
Here prior year is 2015-2015 and Current year is 2015-2016
CREATE TABLE remp_year
(ID INT,
STATUS NVARCHAR(100) NULL,
START_DATE DATE NULL,
END_DATE DATE NULL,
date_year nvarchar(10) NULL)
INSERT INTO remp_year VALUES (10,'Active','2015-05-26','2015-12-31','2015-2016');
INSERT INTO remp_year VALUES (20,'Active','2014-01-01','2014-12-31','2014-2015');
INSERT INTO remp_year VALUES (20,'Terminated','2015-01-01','2015-12-31','2015-2016');
INSERT INTO remp_year VALUES (30,'Active','2015-01-01','2015-12-31','2015-2016');
INSERT INTO remp_year VALUES (40,'Active','2014-01-01','2014-12-31','2014-2015');
INSERT INTO remp_year VALUES (50,'Active','2014-01-01','2014-12-31','2014-2015');
INSERT INTO remp_year VALUES (50,'Terminated','2015-01-01','2015-12-31','2015-2016');
Here prior year is 2015-2015 and Current year is 2015-2016.
Here ID 20 and 50 for terminated records is the prior year records so it should count for the last year and those are active in this year those will count for this year.
so request you pls help me how to achive this?.
August 3, 2015 at 12:55 am
please share your desired output as well.
August 3, 2015 at 1:06 am
My output would be
for 2015 if I select date_year=2014-2015 prior yearthe count would be 3 (for id 20,40 and 50)
for 2016 if I select date_year=2015-2016 current year the count would be 2 (for id 10,30)
and the result would be in single sql query
August 3, 2015 at 1:55 am
As per you desired result, this isn't that much hard to write the query for this.
-------- for 2014-2015
select ID
, STATUS
, START_DATE
, END_DATE
, date_year
from remp_year
Where date_year = '2014-2015'
and STATUS = 'Active'
order by START_DATE
-------- for 2015-2016
select ID
, STATUS
, START_DATE
, END_DATE
, date_year
from remp_year
Where date_year = '2015-2016'
and STATUS = 'Active'
order by START_DATE
you just need to put filter on your status column.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply