Need Help Creating Complicated SELECT Statement

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)

Need Help Creating Complicated SELECT Statement

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-23 : 13:15:52

I have a table which details a call log. Users log in when people call, and enter various information. The fields I am working with are:

TechID (string)
TotalCalls (number - total # records)
TicketedCalls (number - if ticket field <> 0)
NonTicketCalls (number - if ticket field = 0)

I cannot for the life of me figure out how to create this Select statement. Here is where I have left off...

SELECT techID, techID AS TechName,
(SELECT COUNT(calllog.ID)
FROM [dbo].[calllog]
WHERE (calllog.Date BETWEEN DATEADD(m, - 1, DATEADD(d, - 1, GETDATE())) AND GETDATE()))
AS TotalCalls
FROM calllog
GROUP BY techID, Date, caller, ticketNumber
HAVING (Date BETWEEN DATEADD(m, - 1, DATEADD(d, - 1, GETDATE())) AND GETDATE()) AND (caller <> '*') AND (ticketNumber <> '0')
ORDER BY techID

- - - -
- Will -
- - - -
http://www.servicerank.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 13:32:30

What does your query need to do? Please provide sample data and the expected result set.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-23 : 13:58:56

When is Graz gonna post a "sticky"....

Try an dpost stuff like this...


USE Northwind
Go

-- Here's my Table

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

-- My Sample Data Looks like

INSERT INTO myTable99(Col2)
SELECT 'X' UNION ALL
SELECT 'X' UNION ALL
SELECT 'X' UNION ALL
SELECT 'Y' UNION ALL
SELECT 'Y' UNION ALL
SELECT 'Z'
GO

-- And I want a query that would show my Sample Results to look like:
-- Col2   Count_Col2
-- X      3
-- Y      2
-- Z      1

DROP TABLE myTable99
GO

That should get the help you want...

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-24 : 07:21:33

will this help?
SET ESP ON


SELECT 	techID, techID AS TechName,
	COUNT(calllog.ID) AS TotalCalls
FROM 	calllog
WHERE 	(caller <> '*') AND (ticketNumber <> '0') AND 
	(Date BETWEEN DATEADD(m, - 1, DATEADD(d, - 1, GETDATE())) AND GETDATE()) 
GROUP BY techID, Date
ORDER BY techID

SET ESP OFF

Go with the flow & have fun! Else fight the flow

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-24 : 10:56:13

Sorry... I didn't realize that I should post in that manner. The table information should look something like the following:

(I tried, but I couldn't get the fields to line up right. I am missing the trick here.)

-------------------------------------------------------------
TechID.......TotalCalls...TicketedCalls...NonTicketedCalls...
-------------------------------------------------------------
John Dough...2316.........150.............250................
Jane Dough...2316.........123.............200................
Jim Dandy....2316.........175.............103................
-------------------------------------------------------------
TechID = string
TotalCalls = int
TicketedCalls = int
NonTicketedCalls = int
-------------------------------------------------------------

All of the above data would come from the same table. The TotalCalls would just be a COUNT() of the entire result set from a specific time period. However, the TicketCalls should only be the total amount per TechID if the TicketNumber field is not equal to 0 (string). If it is equal to 0 (string), then it is a NonTicketedCall.

Here is what the original table is like:
-------------------------------------------------------------
TktNum...Date........Time.........TechID.........Caller......
-------------------------------------------------------------
0........9/24/2004...8:50:24 AM...John Dough.....Phil Smith..
123456...9/23/2004...8:50:24 AM...Jane Dough.....Wendy Day...
-------------------------------------------------------------
TktNum = nvarchar
Date = datetime
Time = varchar
TechID = nvarchar
Caller = nvarchar
-------------------------------------------------------------

There are more fields. Let me know if you need them.

- - - -
- Will -
- - - -
http://www.servicerank.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 12:20:22

Please post your information like in Brett's example.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources