Post reply

SQL QUERY

  • July 20, 2012 at 3:52 am

    #261409

    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

    ID ST_DT EN_DT BASE_CUR

    716 2012-04-01 2012-07-08 IND

    716 2012-07-09 NULL USA

    718 2012-07-09 NULL IND

    719 2012-07-09 NULL USA

  • July 20, 2012 at 4:08 am

    #1515395

    What is the logic behind removing the two rows?

  • July 20, 2012 at 4:14 am

    #1515397

    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

    #1515401

    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

    #1515421

    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