Need help to a query in sql srver

balukumar435 · October 21, 2015, 5:20pm

I have a question about SQL Server.

Table : emp

id | name | sdate | edate
1 | abc | 2003-11-13 | 2003-11-26
1 | def | 2000-04-08 | 2000-04-11
1 | har | 2003-08-01 | 2003-08-31
1 | ka | 2003-10-01 | 2003-10-31
Table: emp1

id | locname | date
1 | a | 2003-10-01
1 | b | 2003-08-01
1 | c | 2000-04-08
1 | d | 2000-04-10
Here emp1 table related date column data fall between emp table sdate and edate.

If we got multiple then we need to consider min(date) related as old record and max(date) records as new records.

If we got single records follow condition then we need to consider same records as old and new record for that date.

If we don't have any records, then we need to consider emp1 table related columns data

Default values and based on this table I want output like below

id | name | sdate | edate | Filter | locname | date
1 | abc | 2003-11-13 |2003-11-26 | new | NA | 1800-01-01(defaultdate)
1 | def | 2000-04-08 |2000-04-11 | new | d | 2000-04-10
1 | har | 2003-08-01 |2003-08-31 | new | b | 2003-08-01
1 | ka | 2003-10-01 |2003-10-31 | new | a | 2003-10-01
1 | abc | 2003-11-13 |2003-11-26 | old | NA | 1800-01-01(defaultdate)
1 | def | 2000-04-08 |2000-04-11 | old | c | 2000-04-08
1 | har | 2003-08-01 |2003-08-31 | old | b | 2003-08-01
1 | ka | 2003-10-01 |2003-10-31 | old | a | 2003-10-01

I tried with this query:

select
max(date) as date, id, name,
sdate, edate, 'New' as Filter, locname
from
(select
a.id, a.name, a.sdate, a.edate, 'New'as Filter,
b.locname, b.date
from
emp a
join
emp1 b on aid = b.id
and CONVERT(VARCHAR(10), CAST(b.date AS DATE), 120) between CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.sdate)) AS DATE), 120)
and CASE WHEN coalesce(ltrim(rtrim(a.edate)),'') = '' THEN CONVERT(VARCHAR(10), CAST(getdate() AS DATE), 120)
else CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.edate)) AS DATE), 120) end
) a
group by
date, id, name, sdate, edate, 'New' as Filter, locname

union all

select min(date)as date ,id , name , sdate ,edate,'old'as Filter ,locname from
(select a.id , a.name , a.sdate ,a.edate,'old'as Filter ,b.locname ,b.date from
emp a join emp1 b on aid=b.id
and CONVERT(VARCHAR(10), CAST(b.date AS DATE), 120) between CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.sdate)) AS DATE), 120)
and CASE WHEN coalesce(ltrim(rtrim(a.edate)),'') = '' THEN CONVERT(VARCHAR(10), CAST(getdate() AS DATE), 120)
else CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.edate)) AS DATE), 120) end
)a
group by
date ,id , name , sdate ,edate,'New'as Filter ,locname

above query not qiven expected result .please tell me how to write query to achive this task in sql server

gbritton · October 21, 2015, 6:12pm

First, when you post sample tables, don't post them as bar-delimited columns. Use 'consumable' SQL like this:

CREATE TABLE emp (id INT, NAME VARCHAR(20), sdate DATE, edate DATE)

INSERT INTO emp (id, NAME, sdate, edate)
VALUES (1, 'abc', '2003-11-13', '2003-11-26'), 
       (1, 'def', '2000-04-08', '2000-04-11'), 
       (1, 'har', '2003-08-01', '2003-08-31'), 
       (1, 'ka', '2003-10-01', '2003-10-31')

CREATE TABLE emp1 (id INT, locname CHAR(1), [date] DATE)

INSERT INTO emp1 (id, locname, [date])
VALUES (1, 'a', '2003-10-01'), 
       (1, 'b', '2003-08-01'), 
       (1, 'c', '2000-04-08'), 
       (1, 'd', '2000-04-10')

Second, when you post queries, ensure the syntax is correct (yours is not) and format using poorsql.com or some other tool. Reformatted and with syntax corrected, your query looks like this:

SELECT max(DATE) AS DATE
      , id
      , NAME
      , sdate
      , edate
      , 'New' AS Filter
      , locname
FROM (
      SELECT a.id
            , a.NAME
            , a.sdate
            , a.edate
            , 'New' AS Filter
            , b.locname
            , b.DATE
      FROM emp a
      INNER JOIN emp1 b
            ON a.id = b.id
                  AND CONVERT(VARCHAR(10), CAST(b.DATE AS DATE), 120) BETWEEN CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.sdate)) AS DATE), 120)
                        AND CASE 
                                    WHEN coalesce(ltrim(rtrim(a.edate)), '') = ''
                                          THEN CONVERT(VARCHAR(10), CAST(getdate() AS DATE), 120)
                                    ELSE CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.edate)) AS DATE), 120)
                                    END
      ) a
GROUP BY DATE
      , id
      , NAME
      , sdate
      , edate
      , Filter
      , locname

UNION ALL

SELECT min(DATE) AS DATE
      , id
      , NAME
      , sdate
      , edate
      , 'old' AS Filter
      , locname
FROM (
      SELECT a.id
            , a.NAME
            , a.sdate
            , a.edate
            , 'old' AS Filter
            , b.locname
            , b.DATE
      FROM emp a
      INNER JOIN emp1 b
            ON a.id = b.id
                  AND CONVERT(VARCHAR(10), CAST(b.DATE AS DATE), 120) BETWEEN CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.sdate)) AS DATE), 120)
                        AND CASE 
                                    WHEN coalesce(ltrim(rtrim(a.edate)), '') = ''
                                          THEN CONVERT(VARCHAR(10), CAST(getdate() AS DATE), 120)
                                    ELSE CONVERT(VARCHAR(10), CAST(ltrim(rtrim(a.edate)) AS DATE), 120)
                                    END
      ) a
GROUP BY DATE
      , id
      , NAME
      , sdate
      , edate
      , Filter
      , locname

Now that your query is more readable, it is easier to spot problems. For example, your dates are apparently stored as varchar data, since you convert them in the query. Why are the dates not stored as dates? It makes for easier queries and better error detection.

Where does the date column in your desired results come from? your sample query, even when corrected, does not produce this column,

Lastly, do not give columns names that are reserved words. So don't call the third column in table emp1, 'date'. Call it something that indicates what kind of date it is or what the date represents.

balukumar435 · October 22, 2015, 4:10am

Thank you.given query not given expected result when multiple date fall between same sdate and edate from emp that time max(date) we need to consider as new min(date) consider as old.Example
emp:

id | name | sdate | edate
1 | def | 2000-04-08 | 2000-04-11
emp1:

id | locname | date
1 | c | 2000-04-08
1 | d | 2000-04-10

Here expect result is
id | name | sdate | edate | Filter | locname | date
1 | def | 2000-04-08 |2000-04-11 | old | c | 2000-04-08
1 | def | 2000-04-08 |2000-04-11 | new | d | 2000-04-10.
please tell me how to write query to achieve this task in sql server

gbritton · October 22, 2015, 1:30pm

Why are the dates stored as type varchar instead of type date?
What does the column called 'id' represent? (Can't be employee id since the same id is used with different names)
Why did you (again) post sample data as bar-delimited when it should be a series of INSERT INTO statements?

balukumar435 · October 22, 2015, 5:03pm

Than you for given suggestion.this one test data.Now I am consider date related to date datatype.
here I am not able to get expected result to provider you are qyery.
CREATE TABLE emp (empid INT, NAME VARCHAR(20), sdate DATE, edate DATE)

INSERT INTO emp (empid, NAME, sdate, edate)
VALUES
(1, 'def', '2000-04-08', '2000-04-11'),
(1, 'abc', '2003-11-13', '2003-11-26'),
(1, 'ka', '2003-10-01', '2003-10-31')

CREATE TABLE emp1 (empid INT, locname CHAR(1), [date] DATE)

INSERT INTO emp1 (empid, locname, [date])
VALUES
(1, 'c', '2000-04-08'),
(1, 'd', '2000-04-10'),
(1, 'a', '2003-10-01'),

Here expect result is

empid | name | sdate | edate | Filter | locname | date
1 | def | 2000-04-08 |2000-04-11 | old | c | 2000-04-08
1 | def | 2000-04-08 |2000-04-11 | new | d | 2000-04-10
1 | abc | 2003-11-13 |2003-11-26 | new | NA | 1800-01-01(defaultdate)
1 | abc | 2003-11-13 |2003-11-26 | old | NA | 1800-01-01(defaultdate)
1 | ka | 2003-10-01 |2003-10-31 | old | a | 2003-10-01
1 | ka | 2003-10-01 |2003-10-31 | new | a | 2003-10-01
please tell me how to write query to achieve this task in sql server

gbritton · October 22, 2015, 5:32pm

Please answer:

What does the column called 'id' represent? (Can't be employee id since the same id is used with different names)

Without knowing this I can't proceed

balukumar435 · October 23, 2015, 2:22am

in emp table id is made by empid and name also converted deptname here main same empid worked in different tim with different deptnames
CREATE TABLE emp (id INT, deptNAME VARCHAR(20), sdate DATE, edate DATE)

INSERT INTO emp (empid, deptNAME, sdate, edate)
VALUES (1, 'abc', '2003-11-13', '2003-11-26'),
(1, 'def', '2000-04-08', '2000-04-11'),
(1, 'har', '2003-08-01', '2003-08-31'),
(1, 'ka', '2003-10-01', '2003-10-31')

CREATE TABLE emp1 (id INT, locname CHAR(1), [date] DATE)

INSERT INTO emp1 (id, locname, [date])
VALUES (1, 'a', '2003-10-01'),
(1, 'b', '2003-08-01'),
(1, 'c', '2000-04-08'),
(1, 'd', '2000-04-10')

empid | deptname | sdate | edate | Filter | locname | date
1 | def | 2000-04-08 |2000-04-11 | old | c | 2000-04-08
1 | def | 2000-04-08 |2000-04-11 | new | d | 2000-04-10
1 | abc | 2003-11-13 |2003-11-26 | new | NA | 1800-01-01(defaultdate)
1 | abc | 2003-11-13 |2003-11-26 | old | NA | 1800-01-01(defaultdate)
1 | ka | 2003-10-01 |2003-10-31 | old | a | 2003-10-01
1 | ka | 2003-10-01 |2003-10-31 | new | a | 2003-10-01
1 | har | 2003-08-01 |2003-08-31 | old | b | 2003-08-01
1 | har | 2003-08-01 |2003-08-31 | new | b | 2003-08-01

please tell me how to write query to achive this task in sql server

balukumar435 · October 24, 2015, 3:35am

Hi please help below query where I need to change to get expect result.
SELECT e.id,
e.NAME,
e.sdate,
e.edate,
CASE WHEN ROW_NUMBER() OVER( PARTITION BY e.id, e.name, e.sdate ORDER BY e1.date DESC) = 1
THEN 'new' ELSE 'old' END AS Filter,
COALESCE( e1.locname, 'NA') AS locname,
ISNULL( e1.date, '18000101') AS date
FROM emp e
LEFT
JOIN emp1 e1 ON e.id = e1.id AND e1.date BETWEEN e.sdate AND e.edate
union all
SELECT e.id,
e.NAME,
e.sdate,
e.edate,
CASE WHEN ROW_NUMBER() OVER( PARTITION BY e.id ORDER BY e1.date ) = 1
THEN 'new' ELSE 'old' END AS Filter,
COALESCE( e1.locname, 'NA') AS locname,
ISNULL( e1.date, '18000101') AS date
FROM emp e
LEFT
JOIN emp1 e1 ON e.id = e1.id AND e1.date BETWEEN e.sdate AND e.edate;

please help me to resolve this issue.

edisondraj1975 · October 24, 2015, 1:36pm

Hi Balu
Please Use following Query

SELECT e.id,
e.deptNAME,
e.sdate,
e.edate,
CASE WHEN ROW_NUMBER() OVER( PARTITION BY e.id, e.deptNAME, e.sdate ORDER BY e1.date DESC) = 1
THEN 'new' ELSE 'old' END AS Filter,
COALESCE( e1.locname, 'NA') AS locname,
ISNULL( e1.date, '18000101') AS date
FROM emp e
LEFT
JOIN emp1 e1 ON e.id = e1.id AND e1.date BETWEEN e.sdate AND e.edate
union
SELECT e.id,
e.deptNAME,
e.sdate,
e.edate,
CASE WHEN ROW_NUMBER() OVER( PARTITION BY e.id , e.deptNAME, e.sdate ORDER BY e1.date ) = 1
THEN 'old' ELSE 'New' END AS Filter,
COALESCE( e1.locname, 'NA') AS locname,
ISNULL( e1.date, '18000101') AS date
FROM emp e
LEFT
JOIN emp1 e1 ON e.id = e1.id AND e1.date BETWEEN e.sdate AND e.edate
order by date
;