SQL Query Help

[ranjith23]

SQL Experts, Need SQL help to achieve below task:

SQL should pull only FIRST EX to BU PAY GROUP change date for each employee. If Employee don't come under this scenario, it should skip him

INPUT:

EMPLOYEE      DATE           PAY GROUP

A186                01-21-2012     EX
A186                01-22-2012     EX
A186                01-23-2012     EX
A186                01-23-2012     BU
A186                01-24-2012     EX
A186                01-24-2012     BU
A186                01-25-2012     EX

A187                01-21-2012     EX
A187                01-21-2012     EX
A187                01-21-2012     NE

A188                01-28-2012     BU
A188                01-29-2012     BU
A188                01-30-2012     BU

A189                03-18-2012     BU
A189                03-19-2012     BU
A189                03-20-2012     EX

A190                04-18-2012     EX
A190                04-18-2012     BU
A190                04-28-2012     BU

OUTPUT:

EMPLOYEE     DATE           PAY GROUP

A186                01-23-2012     BU
A190                04-18-2012     BU

[BostonBullodu]

Select Top 1 * FROM Tablename
where PAY GROUP = 'BU'

[ranjith23]

13 minutes ago, BostonBullodu said:

Select Top 1 * FROM Tablename
where PAY GROUP = 'BU'

Highlight unnav ga. Just graduated a?

[BostonBullodu]

16 minutes ago, ranjith23 said:

Highlight unnav ga. Just graduated a?

ledu baa paina adi chadavaledu input output choosaa  koncham ivaranga chepparade rek... 

[Popkatapetapotapulti]

epudu table create chesi input chesi query rase opika ledu...idea cheptha try cheyi

1. Partition chesi ranks create cheyi over employee and date..

 after this it will look like 

A186                01-21-2012     EX     1
A186                01-22-2012     EX     2
A186                01-23-2012     EX     3
A186                01-23-2012     BU     3
A186                01-24-2012     EX     4
A186                01-24-2012     BU     4
A186                01-25-2012     EX     5

2. get the top  row with pay group 'BU' ..(at this point you will know the rank as well)

3. Now get the rank of a record whose value is 'EX' which is <= rank from step 2. thats  it 

[Uppi]

with data as(select employee,date,paygroup
from table
where paygroup='BU')
select b.employee,b.date,b.paygroup
from table a,data b
where a.employee=b.employee
and a.paygroup='EX';

[ATRI]

 

1 hour ago, Popkatapetapotapulti said:

epudu table create chesi input chesi query rase opika ledu...idea cheptha try cheyi

1. Partition chesi ranks create cheyi over employee and date..

 after this it will look like 

A186                01-21-2012     EX     1
A186                01-22-2012     EX     2
A186                01-23-2012     EX     3
A186                01-23-2012     BU     3
A186                01-24-2012     EX     4
A186                01-24-2012     BU     4
A186                01-25-2012     EX     5

2. get the top  row with pay group 'BU' ..(at this point you will know the rank as well)

3. Now get the rank of a record whose value is 'EX' which is <= rank from step 2. thats  it 

if you take "<=" in third step, it will pull all the records, the rank for A186  BU is "3" and it retrieves 1, 2, 3 records since the value for all three records is 'EX'

 if i am wrong

I tried with this, let me know if it works for you, it might be complex.. working on it to simplify

CREATE TABLE TEST1 AS
SELECT EMPID, HIREDATE, PAY_GROUP,
CASE WHEN PAY_GROUP='EX' AND LEAD(PAY_GROUP,1) OVER(PARTITION BY EMPID ORDER BY HIREDATE)='BU' THEN 'Y' ELSE 'N' END AS SKIP_REC
FROM EMP;

SELECT * FROM TEST1
WHERE SKIP_REC ='Y' AND (EMPID, HIREDATE)=(SELECT EMPID, MIN(HIREDATE) FROM TEST1
WHERE SKIP_REC='Y'GROUP BY EMPID);

[SUbba LIngam]

SELECT EMPLOYEE, MIN(DATE), PAYGROUP FROM (
select a.EMPLOYEE, a.DATE, b.paygroup from table_1 a, table_1 b where a.paygroup = 'EX' and b.paygroup = 'BU'
 and a.EMPLOYEE=b.EMPLOYEE and a.DATE=b.DATE
 ) GROUP BY  EMPLOYEE, PAYGROUP

[ranku_mogudu]

23 minutes ago, SUbba LIngam said:

SELECT EMPLOYEE, MIN(DATE), PAYGROUP FROM (
select a.EMPLOYEE, a.DATE, b.paygroup from table_1 a, table_1 b where a.paygroup = 'EX' and b.paygroup = 'BU'
 and a.EMPLOYEE=b.EMPLOYEE and a.DATE=b.DATE
 ) GROUP BY  EMPLOYEE, PAYGROUP

+1

[kranthi111]

Create table #temp
  (Employee nvarchar(5),
   Date date,
   PayGroup nvarchar(2))

   insert #temp values ('A186','01-21-2012','EX')
   insert #temp values ('A186','01-22-2012','EX')
   insert #temp values ('A186','01-23-2012','EX')
   insert #temp values ('A186','01-23-2012','BU')
   insert #temp values ('A186','01-24-2012','EX')
   insert #temp values ('A186','01-24-2012','BU')
   insert #temp values ('A186','01-25-2012','EX')

   insert #temp values ('A187','01-21-2012','EX')
   insert #temp values ('A187','01-21-2012','EX')
   insert #temp values ('A187','01-21-2012','NE')

   insert #temp values ('A188','01-28-2012','BU')
   insert #temp values ('A188','01-29-2012','BU')
   insert #temp values ('A188','01-30-2012','BU')

   insert #temp values ('A189','03-18-2012','BU')
   insert #temp values ('A189','03-19-2012','BU')
   insert #temp values ('A189','03-28-2012','EX')

   insert #temp values ('A190','04-18-2012','EX')
   insert #temp values ('A190','04-18-2012','BU')
   insert #temp values ('A190','04-28-2012','BU')

   SELECT * FROM #TEMP

   with CTE AS
  ( Select Employee
          ,Date
          ,PayGroup
          ,case when PayGroup in ('BU','EX') then 1 else 0 end as flag
          ,Rank() over( partition by Employee,PayGroup order by date) as n
        
          from #temp
  ),
   CTE1 as(
   select *,LAG(PayGroup) over(Partition by employee order by date,paygroup desc  ) as PRVgroup from CTE
    where n = 1 and flag = 1)

    select * from CTE1
    where paygroup = 'BU' and prvgroup = 'ex'
 

[BostonBullodu]

@kranthi111 @ATRI baa what if he is still using 2008 version where no Lead and Lag functions exist... 

[ranku_mogudu]

14 minutes ago, BostonBullodu said:

@kranthi111 @ATRI baa what if he is still using 2008 version where no Lead and Lag functions exist... 

vallaki telisina answers they told vuuu..

 

equivalent dhi TS vethukkovali.

anni spoon feed chesthey repu ee doubt vochina DB lo post chesthadu..nerchukodu

[Popkatapetapotapulti]

2 hours ago, ATRI said:

 

if you take "<=" in third step, it will pull all the records, the rank for A186  BU is "3" and it retrieves 1, 2, 3 records since the value for all three records is 'EX'

 if i am wrong

I tried with this, let me know if it works for you, it might be complex.. working on it to simplify

CREATE TABLE TEST1 AS
SELECT EMPID, HIREDATE, PAY_GROUP,
CASE WHEN PAY_GROUP='EX' AND LEAD(PAY_GROUP,1) OVER(PARTITION BY EMPID ORDER BY HIREDATE)='BU' THEN 'Y' ELSE 'N' END AS SKIP_REC
FROM EMP;

SELECT * FROM TEST1
WHERE SKIP_REC ='Y' AND (EMPID, HIREDATE)=(SELECT EMPID, MIN(HIREDATE) FROM TEST1
WHERE SKIP_REC='Y'GROUP BY EMPID);

thats correct but its obvious that he should pull lowest rank ani 

[crazymata]

1 hour ago, kranthi111 said:

Create table #temp
  (Employee nvarchar(5),
   Date date,
   PayGroup nvarchar(2))

   insert #temp values ('A186','01-21-2012','EX')
   insert #temp values ('A186','01-22-2012','EX')
   insert #temp values ('A186','01-23-2012','EX')
   insert #temp values ('A186','01-23-2012','BU')
   insert #temp values ('A186','01-24-2012','EX')
   insert #temp values ('A186','01-24-2012','BU')
   insert #temp values ('A186','01-25-2012','EX')

   insert #temp values ('A187','01-21-2012','EX')
   insert #temp values ('A187','01-21-2012','EX')
   insert #temp values ('A187','01-21-2012','NE')

   insert #temp values ('A188','01-28-2012','BU')
   insert #temp values ('A188','01-29-2012','BU')
   insert #temp values ('A188','01-30-2012','BU')

   insert #temp values ('A189','03-18-2012','BU')
   insert #temp values ('A189','03-19-2012','BU')
   insert #temp values ('A189','03-28-2012','EX')

   insert #temp values ('A190','04-18-2012','EX')
   insert #temp values ('A190','04-18-2012','BU')
   insert #temp values ('A190','04-28-2012','BU')

   SELECT * FROM #TEMP

   with CTE AS
  ( Select Employee
          ,Date
          ,PayGroup
          ,case when PayGroup in ('BU','EX') then 1 else 0 end as flag
          ,Rank() over( partition by Employee,PayGroup order by date) as n
        
          from #temp
  ),
   CTE1 as(
   select *,LAG(PayGroup) over(Partition by employee order by date,paygroup desc  ) as PRVgroup from CTE
    where n = 1 and flag = 1)

    select * from CTE1
    where paygroup = 'BU' and prvgroup = 'ex'
 

lol ... solution

[ranku_mogudu]

13 minutes ago, crazymata said:

lol ... solution

uko baa.. he tried his best no..encourage these people rather than who cannot google.