I've been helped a number of times here regarding to SQL statements, my thx again :-)
This time it is no different.
I have a SQL statement that works but I want it to run faster (now takes about 3 sec.) an design it better. The DB is SQL Server.
Alright one table is used here : Participations.
It contains phone numbers bound to a specific session (broadcast on tv) and some info about when they called. The nr. of rows has a max. of about 10000, because each session is being backupped and then deleted.
Table SQL :
To be clear, I didn't design the table, otherwise I would have used DateTime, but it's irrelevant for the example.
CREATE TABLE [dbo].[Participations] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[SessionId] [int] NOT NULL ,
[DateVal] [char] (10) NOT NULL ,
[TimeVal] [char] (10) NOT NULL ,
[szTelephone] [char] (10) NOT NULL ,
[Post_FLAG] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Participations] WITH NOCHECK ADD
CONSTRAINT [PK_Participations] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
ALTER TABLE [dbo].[Participations] ADD
CONSTRAINT [DF_Participations_Post_FLAG] DEFAULT (1) FOR [Post_FLAG]
CREATE INDEX [Participations_tel] ON [dbo].[Participations]([szTelephone]) ON [PRIMARY]
CREATE INDEX [Participations_time] ON [dbo].[Participations]([TimeVal]) ON [PRIMARY]
Now the SQL statement itself.
Purpose : to give me all the phone numbers for a specific session, the number of times they occurred for that session, their unique ID (pk) and the date and time they entered the system. If any phone number occurs more than once, return only the one that entered for the first time along with it's corresponding date, time and Id (pk) values.
So some of you already will notice the combining of aggregate functions and normal fields.
Now the way I did it :
This thing runs at 3 sec. average.
SELECT b.szTelephone, b.ANumber, b.DateVal, b.TimeVal, a.ID, a.Session
FROM Participations A,
SELECT szTelephone, COUNT(szTelephone) as ANumber, MAX(DateVal) as DateVal, MAX(TimeVal) as TimeVal
WHERE Session = 143 AND Post_FLAG = 0
GROUP BY szTelephone
WHERE A.szTelephone = B.szTelephone
AND A.Session = 143 AND POST_Flag = 0
WHERE C.ID = (SELECT MIN(ID) FROM Participations WHERE C.szTelephone = szTelephone AND Session = 143 AND Post_Flag = 0)
ORDER BY C.ID
I used a subselect as table B, because I have to fetch the unique ID (and Session) for each phone number, but because I use aggregate functions in that subselect, I can't just select the Id in there, either use it in aggregate function or put it in group by, but neither of those options is what I want, hence table B.
Table C is for selecting the first ID occurred in that table.
Any improvements ? :-)
Thx in advance !