SQL Query - 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 2000 Forums

Transact-SQL (2000)

SQL Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-28 : 08:08:21

Adrian Doyle writes "hi I have a list of people each of them have different totals of requests done, but sometimes in the DB there are new names. So I created this label called Other where anything that is not the names of the team will come up.
I created the sql query below basically saying if the name on the PSC Rep Assigned column on the db is not say Andrea Herrling or or then count it and give it the value of other on a label. The sql statement is below, I just want to know if there is an easier way to do it.
Thanks for your help

SELECT COUNT(PSC_tbl_Request.Status)
FROM PSC_tbl_Request, PSC_tbl_PSC_Rep
Where PSC_tbl_Request.Request_Number = PSC_tbl_PSC_Rep.Request_Number
AND (PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Andrea Herrling' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Aine ORiordan' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Angel Casado' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Christian Graninger' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Dave M Ryan' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Dave DeBeuckeleer' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Denise ORiordan' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Eoghan Long' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Francesca Mora' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Herve LeGouguec' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Ian Leech' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Karin Reisinger' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Maria Hernandez' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Cathy Murphy' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Noreen McCarron' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Orla Donnellan' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Pepijn Tijhuis' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Tanya Tobin' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Tony Hanley' OR PSC_tbl_PSC_Rep.PSC_Rep_Assigned <> 'Xavier Frichot')"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 08:20:41

[code]SELECT COUNT(r.Status)
FROM PSC_tbl_Request AS r
INNER JOIN PSC_tbl_PSC_Rep AS p ON p.Request_Number = r.Request_Number
WHERE p.PSC_Rep_Assigned NOT IN ('Andrea Herrling', 'Aine ORiordan', 'Angel Casado', 'Christian Graninger', 'Dave M Ryan', 'Dave DeBeuckeleer', 'Denise ORiordan', 'Eoghan Long', 'Francesca Mora', 'Herve LeGouguec', 'Ian Leech', 'Karin Reisinger', 'Maria Hernandez', 'Cathy Murphy', 'Noreen McCarron', 'Orla Donnellan', 'Pepijn Tijhuis', 'Tanya Tobin', 'Tony Hanley', 'Xavier Frichot')

SELECT COUNT(r.Status)
FROM PSC_tbl_Request AS r
INNER JOIN PSC_tbl_PSC_Rep AS p ON p.Request_Number = r.Request_Number
LEFT JOIN (
SELECT 'Andrea Herrling' AS Rep_Assigned UNION ALL
SELECT 'Aine ORiordan' UNION ALL
SELECT 'Angel Casado' UNION ALL
SELECT 'Christian Graninger' UNION ALL
SELECT 'Dave M Ryan' UNION ALL
SELECT 'Dave DeBeuckeleer' UNION ALL
SELECT 'Denise ORiordan' UNION ALL
SELECT 'Eoghan Long' UNION ALL
SELECT 'Francesca Mora' UNION ALL
SELECT 'Herve LeGouguec' UNION ALL
SELECT 'Ian Leech' UNION ALL
SELECT 'Karin Reisinger' UNION ALL
SELECT 'Maria Hernandez' UNION ALL
SELECT 'Cathy Murphy' UNION ALL
SELECT 'Noreen McCarron' UNION ALL
SELECT 'Orla Donnellan' UNION ALL
SELECT 'Pepijn Tijhuis' UNION ALL
SELECT 'Tanya Tobin' UNION ALL
SELECT 'Tony Hanley' UNION ALL
SELECT 'Xavier Frichot'
) AS x ON x.Rep_Assigned = p.PSC_Rep_Assigned
WHERE x.Rep_Assigned IS NULL[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources