[RESOLVED] Query Help - Derived table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

[RESOLVED] Query Help - Derived table?

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2009-09-30 : 09:32:43

Hey experts,

I have the following query which gets me a list of people who have had a specific job type in the past 12 months. "FS" now... you can also have a job type of "SD" can someone modify this query so that it will return Customers that have had an "FS" and "SD" in the past 12 months?

Here is what I have so far;


-- Current Service List
SET DATEFORMAT DMY
USE HSS2K5
SELECT
    Job.[Del Code],
    Job.Deladd1,
    Job.Deladd2,
    Job.Deladd3,
    Job.Deladd4,
    Job.[Post Code],
    Customer.Status
FROM
    Job INNER JOIN
    Customer
        ON Job.[Del Code] = Customer.Code
WHERE
    Job.[Job No] LIKE 'FS%' AND
    Job.[Date Rec'd] BETWEEN '30/09/08'
                     AND     '30/09/09' 
ORDER BY
	Customer.Status ASC

Many thanks,

Pace

"Impossible is Nothing"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 09:48:46

Try this


-- Current Service List
SET DATEFORMAT DMY
USE HSS2K5
SELECT
    Job.[Del Code],
    Job.Deladd1,
    Job.Deladd2,
    Job.Deladd3,
    Job.Deladd4,
    Job.[Post Code],
    Customer.Status
FROM
    Job INNER JOIN
    Customer
        ON Job.[Del Code] = Customer.Code
WHERE
    (Job.[Job No] LIKE 'FS%' or Job.[Job No] LIKE 'FS%') AND
    Job.[Date Rec'd] BETWEEN '30/09/08'
                     AND     '30/09/09' 
GROUP BY
	Job.[Del Code],
    Job.Deladd1,
    Job.Deladd2,
    Job.Deladd3,
    Job.Deladd4,
    Job.[Post Code],
    Customer.Status
HAVING
	COUNT(DISTINCT Job.[Job No])=2
ORDER BY
	Customer.Status ASC

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 09:53:30

Either FS or SD, or must have both?

N 56°04'39.26"
E 12°55'05.63"

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2009-09-30 : 09:54:45

In this instance, the customer should have both and SD and FS withing 12 months of one another.

Thanks

"Impossible is Nothing"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 10:03:55

Madhi, second LIKE should be 'SD%', right?
And the distinct COUNT should be 'COUNT(DISTINCT LEFT(Job.[Job No], 2)) = 2', right?

Other wise a job "FS1" and "FS2" would be counted as two different jobs.

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 10:10:48

quote:
Originally posted by Peso

Madhi, second LIKE should be 'SD%', right?
And the distinct COUNT should be 'COUNT(DISTINCT LEFT(Job.[Job No], 2)) = 2', right?

Other wise a job "FS1" and "FS2" would be counted as two different jobs.

N 56°04'39.26"
E 12°55'05.63"

Yes. Copy and paste error

Madhivanan

Failing to plan is Planning to fail

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2009-10-01 : 11:28:02

You guys rock. I understand the syntax. I need to make more use of HAVING :) Many thanks

"Impossible is Nothing"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources