July 20, 2012 at 3:52 am
pls help me how to get desired out put based below table
declare @t1 table(ID VARCHAR(10),ST_DT DATE,EN_DT DATE,BASE_CUR VARCHAR(10))
INSERT INTO @t1 VALUES('716','2012-04-01','2012-07-08','IND')
INSERT INTO @t1(ID,ST_DT,BASE_CUR) VALUES('716','2012-07-09','USA')
INSERT INTO @t1 VALUES('718','2012-06-01','2012-07-09','IND')
INSERT INTO @t1(ID,ST_DT,BASE_CUR) VALUES('718','2012-07-09','IND')
INSERT INTO @t1 VALUES('719','2012-06-01','2012-07-09','USA')
INSERT INTO @t1(ID,ST_DT,BASE_CUR) VALUES('719','2012-07-09','USA')
i want below out put
IDST_DT EN_DT BASE_CUR
7162012-04-012012-07-08IND
7162012-07-09NULL USA
7182012-07-09NULL IND
7192012-07-09NULL USA
July 20, 2012 at 4:08 am
What is the logic behind removing the two rows?
July 20, 2012 at 4:14 am
actual here logic is one month some employee is working same project in
same country what time i will pick up where en_dt is null
else
one month some employee is working same project in
diff country what time i will pick up what ever that employee related records.
July 20, 2012 at 4:23 am
How do you know its the same project as for as that data set is concerned everyone could be a different project or the same project
But based on the sample data
SELECT
T1.*
FROM
@t1 T1
INNER JOIN
@t1 T2
ON
T1.ID = T2.ID
WHERE
T1.BASE_CUR <> T2.BASE_CUR
OR
T1.EN_DT IS NULL
GROUP BY
T1.ID, T1.ST_DT, T1.EN_DT, T1.BASE_CUR
July 20, 2012 at 4:53 am
THANKS IT IS WORKING
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply