How to nest where clause? - 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)

How to nest where clause?

Author

Topic

densma
Starting Member

21 Posts

Posted - 2004-04-28 : 14:46:13

i have a query like this

SELECT Count( DISTINCT Create_Date) 'Total Active tickets',
Submitter,
(EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS Manager
FROM RReports.dbo.raw_data
inner join Web.dbo.CNB on Submitter = ID
WHERE group_Assigned_to = 'Eng level1' and Status <> 'closed'
..this query returns........

'Total Active tickets', submiter, Agent, Supervisor, Manager
..........................................
i have another same query with no where clause.. im trying to put the togther..
here query 2
SELECT Count( DISTINCT Create_Date) 'Total Tickets',
Submitter,
(EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS Manager
FROM RReports.dbo.raw_data
inner join Web.dbo.CNB on Submitter = ID
this returns..
'Total tickets', submiter, Agent, Supervisor, Manager

i want one query that will give me this result below

'Total Active tickets','Total tickets', submiter, Agent, Supervisor, Manager

same query and with with where clause and another without..
i need to put them together and get one output..
is this possible?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-28 : 14:48:16

UNION them together.

Tara

densma
Starting Member

21 Posts

Posted - 2004-04-28 : 15:48:48

im getting
All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.
i also use groupby and order by
i'll post the whole query

densma
Starting Member

21 Posts

Posted - 2004-04-28 : 16:28:18

i fixed the error but the output ddesn't look like wht i want. it actually showing 2 records for one person..
and no column for 'Total tickets'
ex.
'Total tickets'
50
2

instead like below which i want..just one record
eg
'Total tickets' 'Total active tickets'
50 2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-28 : 16:57:20

[code]

SELECT
( SELECT
Count( DISTINCT Create_Date) ,
Submitter,
(EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS Manager
FROM RReports.dbo.raw_data
INNER JOIN Web.dbo.CNB
ON Submitter = ID
WHERE group_Assigned_to = 'Eng level1' AND
Status <> 'closed'
) AS 'Total Active tickets',
Count( DISTINCT Create_Date) AS 'Total Tickets',
Submitter,
(EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS Manager
FROM RReports.dbo.raw_data
INNER JOIN Web.dbo.CNB
ON Submitter = ID
[/code]

Tara

densma
Starting Member

21 Posts

Posted - 2004-04-28 : 17:31:04

im getting error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

check your email for real query,,b/c i also have group by and order by

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-28 : 18:39:34

Please post the DDL for your table, DML for sample data, and the expected result set for your sample data. DDL is the CREATE TABLE statements, DML would be INSERT INTO statements for sample data. We need this in order to help you as I can not test the query without it. So we could be going back and forth with this for quite a bit if we don't have the information to run on our machines.

Tara

densma
Starting Member

21 Posts

Posted - 2004-04-29 : 08:40:19

How do i get DDL and DML statement?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-29 : 12:16:46

You write them. DDL can be generated from Enterprise Manager by right clicking on your objects, going to all tasks then to generate SQL script. DML is manual though.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-29 : 12:21:23

Check out this thread for an example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34602

Tara

densma
Starting Member

21 Posts

Posted - 2004-04-29 : 15:44:12

nevermind..
i figured out

densma
Starting Member

21 Posts

Posted - 2004-04-30 : 10:30:58

i tried to create view with two result.. results doesn't seem accurate.
this is the best i can explain. im going to take out all join. this is exactly with im trying to do. i put join later.. i only id that to get names and supervisors
disregard the first query and see if you can join this..
goal:I'm trying to count total records and also total escalation from total record for a day/per agent for previous day. first query gives me total escalation and 2nd one gives me total recordsmy new query shoud look like this..

query 1.

SELECT Count( DISTINCT Create_Date) 'Total Escalation',
Submitter 'Remedy Id',
Group_Assigned_To,
(getdate() - 1) as stampdate
FROM RemWebReports.dbo.raw_data
WHERE datediff(day, Create_Date, GetDate()) = 1 and IsDate(Create_Date) = 1 and Status <> 'closed'
group by Submitter, Group_Assigned_To
ORDER BY Submitter, Group_Assigned_To

query 2.

SELECT Count( DISTINCT Create_Date) 'Total Call vol',
Submitter 'Remedy Id',
(getdate() - 1) as stampdate
FROM RemWebReports.dbo.raw_data
WHERE datediff(day, Create_Date, GetDate()) = 1 and IsDate(Create_Date) = 1
group by Submitter, Group_Assigned_To
ORDER BY Submitter, Group_Assigned_To

wanna combine both to get
result like
'Total Escalation' 'Total call vol' 'Remedy Id', 'Group_Assigned_To' 'stampdate'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources