October 23, 2015 at 9:39 am
I have a question about SQL Server.
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')
Here name means: deptname and id means empid and 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
and 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
LEFT 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
LEFT 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
above query not qiven expected result .please tell me how to write query to achive this task in sql server
October 23, 2015 at 11:46 am
This one is a bit of a sticky wicket, in that I have no idea what you are trying to accomplish. I'm guessing that English is not your primary language, as what was written just doesn't mean much. You're indicating a specific set of conditions, but the words you're using end up being totally lacking in specifics. Try to remember that we don't have any idea what your data is for when the fields are just a couple of character fields and a few dates. There's some suggestion that perhaps employee data is involved, but we have no idea what the data represents.. Please be more detailed about what this data represents and what business objective you're trying to meet.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2015 at 1:33 pm
I'm not sure if this is what you're looking for:
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, --Changed to convert char(1) to char(2) instead of the other way around.
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;
October 23, 2015 at 1:35 pm
Hi There,
I feel the requirement is a bit unclear, but i have tried to write something up based on your output. Seeing the expected result I kinda got what you are looking for, though I am not sure about it.
Below is the code that should work. I have created three groups per your requirements to make things easy, you can change as appropriate. Let me know if this works. 🙂
----------- YOUR TABLE STRUCTURE --------------------------------------
DECLARE @emp table (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')
DECLARE @emp1 table (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')
Declare @defaultDate Date = '1800-01-01'
----------- YOUR TABLE STRUCTURE ENDS --------------------------------------
DECLARE @NoMatch table(
id INT,
NAME varchar(20),
sdate date,
edate date
)
INSERT INTO @NoMatch
SELECT emp.id, NAME, sdate, edate FROM @emp emp
WHERE NAME NOT IN (SELECT NAME FROM @emp emp JOIN @emp1 emp1 ON emp.id = emp1.id
WHERE date between sdate and edate)
--SELECT * FROM @NoMatch
DECLARE @OldRecords table(
id INT,
NAME varchar(20),
sdate date,
edate date,
Filter varchar(20),
locName char(2),
date date
)
INSERT INTO @OldRecords
SELECT id, NAME, sdate, edate, 'Old' AS FILTER ,'NA', @defaultDate FROM @NoMatch
UNION ALL
SELECT emp.id, emp.NAME, emp.sdate, emp.edate,b.Filter,emp1.locname, emp1.date
from @emp emp join @emp1 emp1 ON emp.id = emp1.id
INNER JOIN
(
SELECT emp.id, NAME, sdate, edate,MIN(date) as [date], 'Old' as Filter FROM @emp emp JOIN @emp1 emp1 ON emp.id = emp1.id
WHERE date between sdate and edate
GROUP BY emp.id, NAME, sdate, edate
)B ON emp1.date = b.date and emp.NAME = b.NAME
--SELECT * FROM @OldRecords
DECLARE @NewRecords table(
id INT,
NAME varchar(20),
sdate date,
edate date,
Filter varchar(20),
locName char(2),
date date
)
INSERT INTO @NewRecords
SELECT id, NAME, sdate, edate,'New' AS FILTER,'NA', @defaultDate FROM @NoMatch
UNION ALL
SELECT emp.id, emp.NAME, emp.sdate, emp.edate, b.Filter, emp1.locname, emp1.date
from @emp emp join @emp1 emp1 ON emp.id = emp1.id
INNER JOIN
(
SELECT emp.id, NAME, sdate, edate,MAX(date) as [date], 'New' as Filter FROM @emp emp JOIN @emp1 emp1 ON emp.id = emp1.id
WHERE date between sdate and edate
GROUP BY emp.id, NAME, sdate, edate
)B ON emp1.date = b.date and emp.NAME = b.NAME
--SELECT * FROM @NewRecords
SELECT * FROM @OldRecords
UNION ALL
SELECT * FROM @NewRecords
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply