any help is appreicated - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

any help is appreicated

Author

Topic

sushil
Starting Member

11 Posts

Posted - 2007-08-03 : 20:15:05

Employee Date Enquired Title Company

Bryan Cole 2007-05-01 Recruiter ABC
Bryan Cole 2007-05-15 Recruiter ABC
Bryan Cole 2007-05-21 Recruiter ABC
Bryan Cole 2007-06-15 Recruiter ABC
Bryan Cole 2007-07-01 Recruiter ABC
Bryan Cole 2007-07-30 Recruiter ABC

I have to do row by row date comparisons in a date column. If the date difference is more than 30 days we keep it , otherwise we suppress it. How can we write the query without using cursor so that only the bold rows will come ?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2007-08-03 : 23:02:50

SELECT * FROM THE_TABLE_WHOS_NAME_I_HAVE_TO_GUESS_BECAUSE_YOU_DIDNT_PROVIDE_THE_INFO
WHERE
DateDiff(d, [date], GetDate()) <= 30

You need the square brackets around the [date] because it's a reserved work in SQL, which makes it a really really really really really bad name for a column.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-04 : 04:08:32

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Employee VARCHAR(20),
DateEnquired DATETIME,
Title VARCHAR(20),
Company VARCHAR(20)
)

INSERT @Sample
SELECT 'Bryan Cole', '2007-05-01', 'Recruiter', 'ABC' UNION ALL
SELECT 'Bryan Cole', '2007-05-15', 'Recruiter', 'ABC' UNION ALL
SELECT 'Bryan Cole', '2007-05-21', 'Recruiter', 'ABC' UNION ALL
SELECT 'Bryan Cole', '2007-06-15', 'Recruiter', 'ABC' UNION ALL
SELECT 'Bryan Cole', '2007-07-01', 'Recruiter', 'ABC' UNION ALL
SELECT 'Bryan Cole', '2007-07-30', 'Recruiter', 'ABC'

-- Setup CTE
;WITH smpl (Employee, DateEnquired, Title, Company)
AS (
SELECT TOP 1 Employee,
DateEnquired,
Title,
Company
FROM @Sample
ORDER BY DateEnquired

UNION ALL

SELECT TOP 1 s1.Employee,
s1.DateEnquired,
s1.Title,
s1.Company
FROM @Sample AS s1
INNER JOIN smpl AS s2 ON s2.DateEnquired < DATEADD(Day, -30, s1.DateEnquired)
ORDER BY s1.DateEnquired
)

-- Show the expected result
SELECT Employee,
DateEnquired,
Title,
Company
FROM smpl
ORDER BY DateEnquired
WITH (MAXRECUSION 0)[/code]

E 12°55'05.25"
N 56°04'39.16"

sushil
Starting Member

11 Posts

Posted - 2007-08-04 : 15:35:15

Thanks Peso , Merkin. It so kind of you for the advise. I always miss austraila .Most of my best friends are in stockholm, I plan to visit soon..

My question is that I cannot manually feed all the employees. There are more than 20,000 employees and each employees there are multiple date inquiry. Therefore I have to do row by row calcuation so that only the dateinquiry less than 30 days difference pops up for the given employee.

I cannot use cursor because it slows down the database.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-04 : 15:42:07

The sample data above if just for demonstration and for have an environment equal to yours.
What you need, is the code CTE and below...

E 12°55'05.25"
N 56°04'39.16"

sushil
Starting Member

11 Posts

Posted - 2007-08-06 : 15:02:41

I really appreciate your help.This query works great for a single student. What changes I should make if there are multiple employees and i want to execute for every set of employee. For eg,

bryan cole 2007-05-18
bryan cole 2007-06-04
bryan cole 2007-07-14

susan wu 2007-04-04
susan wu 2007-05-05
susan wu 2007-06-08
susan wu 2007-06-18

The result will be
bryan cole 2007-05-18
bryan cole 2007-07-14

susan wu 2007-04-04
susan wu 2007-05-05
susan wu 2007-06-08

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-06 : 15:44:52

That CTE code does not work on my machine (Doesn't like the TOP 1 in the recursive portion), but if it works for you, then you can put it into a Function and use the CROSS APPLY operator to get your results.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-06 : 16:53:45

~~INNER JOIN smpl AS s2 ON s2.DateEnquired < DATEADD(Day, -30, s1.DateEnquired)~~
INNER JOIN smpl AS s2 ON s2.Employee = s1.Employee AND s2.DateEnquired < DATEADD(Day, -30, s1.DateEnquired)

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-06 : 17:17:21

Peso, does that query run on your machine? I did a little messing around with it and my server doesn't like the TOP 1 on the recursive portion and without the TOP 1 it won't work.

Just curious as I thought I had all the latest patches and I can see how being able to do a TOP clause would be of benefit.

-Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-06 : 17:20:51

You are right. You need to change the anchor part too.

E 12°55'05.25"
N 56°04'39.16"

sushil
Starting Member

11 Posts

Posted - 2007-08-06 : 17:58:01

Hi Peso,

I appreciate for your reply.However, since we are using the TOP , the join is not matching the employee before the union all statement. The dateEnquried for the second employee will be the continuation from the first employee.

I will try using a function and see if it works.

Cheers

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-06 : 18:25:38

There in lies the rub..

I just did some quick testing, but because you cannot use a TOP 1 on the recursive portion, you will get multiple dates that fall outside the 30 day period. I didn't have time to play with it enough, but I don't think a recursive CTE will work for this case. However, if you can figure out how to make it work, I'd love to see the solution. If I have some time I might mess with it later.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-06 : 18:34:59

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Employee VARCHAR(20),
DateEnquired DATETIME
)

INSERT @Sample
SELECT 'bryan cole', '2007-05-18' UNION ALL
SELECT 'bryan cole', '2007-06-04' UNION ALL
SELECT 'bryan cole', '2007-07-14' UNION ALL
SELECT 'susan wu', '2007-04-04' UNION ALL
SELECT 'susan wu', '2007-05-05' UNION ALL
SELECT 'susan wu', '2007-06-08' UNION ALL
SELECT 'susan wu', '2007-06-18'

-- Show the expected output
SELECT Employee,
MIN(DateEnquired) AS DateEnquired
FROM (
SELECT s1.Employee,
s1.DateEnquired,
(SELECT MAX(s2.DateEnquired) FROM @Sample AS s2 WHERE s2.Employee = s1.Employee AND s2.DateEnquired < DATEADD(DAY, -30, s1.DateEnquired)) AS highDate
FROM @Sample AS s1
) AS d
GROUP BY Employee,
highDate
ORDER BY Employee,
highDate[/code]

E 12°55'05.25"
N 56°04'39.16"

sushil
Starting Member

11 Posts

Posted - 2007-08-06 : 19:03:22

wow, thats awesome. I am so grateful to all of you especially peso. Please let me know if any help I can do for you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources