locked
need to help in sql server RRS feed

  • Question

  • I have two tables. emp:
    
    CREATE TABLE dbo.emp (
        empid INT NULL,
        deptid INT NULL,
        doj DATE NULL,
        loc VARCHAR(50) NULL,
        [status] INT NULL,
        [guid] INT NULL,
        locid INT
    )
    INSERT INTO emp (empid, deptid, doj, loc, [status], [guid], locid)
    VALUES
        (1, 10, '2013-09-25', 'hyd', 5, 10, 4),
        (1, 10, '2014-03-25', 'che', 5, 11, 1),
        (1, 10, '2014-04-09', 'pune', 5, 12, 2),
        (1, 10, '2015-01-22', 'pune', 5, 13, 2),
        (2, 20, '2015-12-13', 'beng', 5, 14, 3),
        (2, 20, '2014-12-12', 'chen', 5, 15, 2),
        (2, 20, '2010-10-15', 'beng', 5, 16, 3),
        (2, 20, '2010-10-15', 'beng', 4, 17, 3),
        (3, 30, '2011-11-15', 'beng', 5, 18, 3),
        (4, 40, '2010-11-15', 'chen', 5, 19, 1),
        (4, 40, '2010-11-15', 'beng', 5, 20, 3),
        (4, 40, '2009-11-15', 'beng', 5, 21, 3),
        (5, 50, '2010-12-14', 'beng', 5, 22, 3)
    empref:
    
    CREATE TABLE dbo.empref (
        empid INT NULL,
        deptid INT NULL,
        startdate DATE NULL,
        [status] INT NULL,
        [guid] INT NULL,
        checkid INT NULL
    )
    INSERT INTO empref (empid, deptid, startdate, [status], [guid], checkid)
    VALUES
        (1, 10, '2013-10-02', 2, 1, 1),
        (1, 10, '2014-04-09', 2, 2, 2),
        (1, 10, '2015-12-09', 1, 3, 3),
        (1, 10, '2015-01-30', 2, 4, 4),
        (2, 20, '2015-12-14', 2, 2, 5),
        (2, 20, '2015-12-15', 2, 3, 6),
        (3, 30, '2011-11-15', 2, 3, 7),
        (3, 30, '2011-11-16', 2, 5, 8),
        (4, 40, '2010-11-17', 2, 6, 9),
        (5, 50, '2010-11-15', 2, 7, 9),
        (5, 50, '2010-11-15', 2, 8, 10)
    Both tables have common columns Empid + deptid We need to consider emp table status=5 related records compare with empref table status=2 related records and emp table doj <= startdate --empref table and days difference between less than or equal to 30 days.Remain status values 4 or 1 ignore in the process time.
    
    if we found multiple records in emp table then we consider min(doj) and min(locid) priority and corresponding guid from empref table and record consider as update in the filter. if we found multiple records in empref table then we consider min(startdate)and min(checkid_) priority and corresponding guid from empref table and record consider as update in the filter.
    
    if above conditions not satisfied with few records in emp table then records need to consider insert in the filter.
    
    based on above tables I want output like below
    
    Empid| Deptid | loc | Status | Filter | Doj |guid
    1 | 10 | hyd | 5 | Update | 2013-09-25|1
    1 | 10 | che | 5 | insert | 2014-03-25|2 ------min(startdate) corresponding record
    1 | 10 | pune| 5 | update | 2014-04-09|11 --------mul
    1 | 10 | Pune| 5 | update | 2015-01-22|4 
    2 | 20 | beng| 5 | insert | 2015-12-13|14 --------------min(doj) record
    2 | 20 | chen| 5 | update | 2014-12-12|2 
    2 | 20 | beng| 5 | insert | 2010-10-15|16 -----this record not fall the above conditions
    3 | 30 | beng| 5 | update | 2011-11-15 |3
    4 | 40 |chen |5 |update |2010-11-15 |6
    4 | 40 |beng |5 |insert |2010-11-15 |20
    4 | 40 |beng |5 |insert |2010-11-15 |21
    5 | 50 |beng |5 |update |2010-12-14 |9
    I tried like below
    
    SELECT e.Empid,
           ER.deptid,
           e.loc,
           e.[status],
           ER.[guid],
           e.[guid],
           CASE
               WHEN DATEDIFF(DAY, e.doj, ER.startdate) <= 0
                   THEN 'INSERT'
               ELSE 'UPDATE'
           END filter,
           e.doj
    FROM emp e
    INNER JOIN empref ER ON e.Empid = ER.empid AND e.deptid = ER.deptid
    WHERE e.[status] = 5
        AND ER.[status] = 2
        AND e.doj <= ER.startdate
        AND DATEDIFF(DAY, e.doj, ER.startdate) <= 30
    and I tried another way
    
    SELECT *
    FROM (
        SELECT e.empid,
               e.deptid,
               e.loc,
               e.status,
               CASE
                   WHEN DATEDIFF(DAY, e.doj, b.startdate) < 30
                       THEN 'Update'
                   ELSE 'Insert'
               END AS filter,
               e.doj,
               CASE
                   WHEN DATEDIFF(DAY, e.doj, b.startdate) < 30
                       THEN b.guid
                   ELSE e.guid
               END AS guid,
               CASE
                   WHEN ROW_NUMBER() OVER (PARTITION BY e.guid ORDER BY e.doj DESC) = 1
                       THEN 'current'
                   ELSE 'initial'
               END AS Fil,
               CASE
                   WHEN ROW_NUMBER() OVER (PARTITION BY b.empid, b.deptid ORDER BY b.startdate DESC) = 1
                       THEN 'current'
                   ELSE 'initial'
               END AS Fil1,
               b.startdate
        FROM emp e
        LEFT JOIN empref b ON b.empid = e.empid
            AND b.deptid = e.deptid
            AND b.startdate >= e.doj
        WHERE e.status = 5
            AND b.status = 2
            AND e.empid = 1
    ) a
    WHERE a.fil = 'current'
    In the above query not given expected result. please tell me how to write query to achieve this task in sql server


    Tuesday, December 29, 2015 4:23 PM

All replies

  • I don't get your logic here, but start with an aggregated statement:

    SELECT  e.empid ,
            e.deptid ,
            MIN(e.doj) AS MinDoj ,
            e.loc ,
            e.guid ,
            MIN(e.locid) AS MinLocID ,
            MIN(ER.startdate) AS MinStartDate ,
            ER.guid ,
            MIN(ER.checkid) AS MinCheckID ,
            CASE WHEN DATEDIFF(DAY, MIN(e.doj), MIN(ER.startdate)) <= 0 THEN 'INSERT'
                 ELSE 'UPDATE'
            END filter
    FROM    emp e
            INNER JOIN empref ER ON e.empid = ER.empid
                                    AND e.deptid = ER.deptid
                                    AND e.[status] = 5
                                    AND ER.[status] = 2
    WHERE   e.doj <= ER.startdate
            AND DATEDIFF(DAY, e.doj, ER.startdate) <= 30
    GROUP BY e.empid ,
            e.deptid ,
            e.loc ,
            e.guid ,
            ER.guid;

    • Proposed as answer by Ciprian Lupu Wednesday, December 30, 2015 8:27 AM
    Tuesday, December 29, 2015 4:33 PM
  • above query not given expected result.can you please provide query to get exact result.
    Tuesday, December 29, 2015 4:47 PM
  • What was incomprehensible with: "I don't get your logic here, but start with an aggregated statement:"?
    Tuesday, December 29, 2015 4:52 PM
  • if we found multiple records foll given conditions then give priority order by doj desc,locid in emp table
    
    similar in empref table  give priority order by startdate desc,checkid desc(it means min(startdate),min(checkid))
    
    we do not need use aggregated functions.example:
    
    emp:
    empid | deptid | doj       | loc   | [status]| [guid]| locid
    4     | 40     | 2010-11-15| chen  | 5        | 19   | 1
    4     | 40     | 2010-11-15| beng  | 5        | 20   | 3
    empref:
    empid| deptid| startdate | [status]| [guid]|checkid
    4    | 40    | 2010-11-17| 2       | 6     | 9
    
    here emp table empid=4 both records foll  given all conditions.
    then we give priority based order by doj desc,locid desc.after that
    we get expected output for empid=4
    
    Empid| Deptid | loc | Status | Filter| Doj       |guid
    4    | 40     |chen |5       |update |2010-11-15 |6
    4    | 40     |beng |5       |insert |2010-11-15 |20
    
    similary to 
    
    emp:
    empid | deptid | doj       | loc   | [status]| [guid]| locid
    5     | 50     | 2010-12-14| beng  | 5        | 22   | 3
    
    empref:
    empid| deptid| startdate | [status]| [guid]|checkid
    5    | 50    | 2010-11-15| 2       | 7     | 9
    5    | 50    | 2010-11-15| 2       | 8     | 10
    expected output for empid=5
    Empid| Deptid | loc | Status | Filter| Doj       |guid
    5    | 50     |beng |5       |update |2010-12-14 |9
    
    please tell me how to wirte query to achive this task in sql server 
    using row_number function .
    
    
    
    
    
    

    Wednesday, December 30, 2015 2:30 AM
  • if we found multiple records foll given conditions

    What given conditions?? Your post is almost unreadable.. Use the embed code button for code only. Code only means: post a concise and complete example.

    In your case post an expected output table based on your sample. This is the result of your JOIN:

    SELECT  e.empid ,
            e.deptid ,
            e.doj ,
            e.loc ,
            e.guid ,
            e.locid ,
            ER.startdate ,
            ER.guid ,
            ER.checkid
    FROM    emp e
            INNER JOIN empref ER ON e.empid = ER.empid
                                    AND e.deptid = ER.deptid
                                    AND e.[status] = 5
                                    AND ER.[status] = 2
    WHERE   e.doj <= ER.startdate
            AND DATEDIFF(DAY, e.doj, ER.startdate) <= 30;

    How is this line created: "2 | 20 | chen| 5 | insert | 2014-12-12|15"?

    Wednesday, December 30, 2015 9:30 AM

  • I am sorry its my mistake for empid=2 I entered wrong output.now I corrected it.

    Above query will work perfect when donot have multiple records foll conditions.if we have multiple records foll then above query not work.

    Emp:

    empid | deptid | doj       | loc   | [status]| [guid]| locid
    2     | 20     | 2015-12-13| beng  | 5        | 14   | 3
    2     | 20     | 2014-12-12| chen  | 5        | 15   | 2
    2     | 20     | 2010-10-15| beng  | 5        | 16   | 3
    2     | 20     | 2010-10-15| beng  | 4        | 17   | 3

    empref:
    empid| deptid| startdate | [status]| [guid]|checkid
    2    | 20    | 2015-12-14| 2       | 2     | 5
    2    | 20    | 2015-12-15| 2       | 3     | 6
    expected result  for empid=2
    Empid| Deptid | loc | Status | Filter | Doj       |guid
    2    | 20     | beng| 5      |insert  | 2015-12-13|14 
    2    | 20     | chen| 5      | update | 2014-12-12|2 
    2    | 20     | beng| 5      | insert | 2010-10-15|16 

    here main logic is 
    case 1: in emp table multiple records satisifed with given conditions
     then give priority order by doj desc,locid desc then first 
    record consider as update and remain records consider insert.

    case 2: in empref table 
    table multiple records satisifed with given conditions
    then give priority order by startdate desc,checkid desc then first 
    record consider as update 

    please tell me how to write query to get exact result and achive this task in sql server


    Wednesday, December 30, 2015 11:16 AM