need to help a query in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have a question about SQL Server.
Table : emp

SQL

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

SQL

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:

SQL

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 servr

Posted 20-Oct-15 21:57pm

Member 12046790

Updated 20-Oct-15 22:10pm

Schatak

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Answer 1 · 2015-10-21T01:03:00

First of all, do not store dates as strings, you will save yourself a lot of trouble. You code is hard to read because all of the conversions. Assuming dates are real dates you can do this:

SQL

with cte as
(
  select emp.id, emp1.id as id1
  from emp
  left join emp1 on emp1.[date] between emp.sdate and emp.edate
)
, cte2 as
(
  select id, min(id1) as id1, 'old' as filter
  from cte
  group by id
  
  union all
  
  select id, max(id1) as id1, 'new' as filter
  from cte
  group by id  
)
select * from cte2 -- join to emp and emp1 to get the other columns

I don't think the code needs much comment. If you need to convert strings to dates prepend all this with another cte where you do just that. Here is the SQL fiddle[^], have fun.

Edit - ids are not unique
If your ids are not unique and for example names are then you can do this:

SQL

with cte as
(
  select name, locname
  from emp
  left join emp1 on emp1.[date] between emp.sdate and emp.edate
)
, cte2 as
(
  select name, min(locname) as locname, 'old' as filter
  from cte
  group by name
  
  union all
  
  select name, max(locname) as locname, 'new' as filter
  from cte
  group by name 
)
select * from cte2

SQL fiddle[^]