Another SQL Query

[GrimStoner]

Hi

Suppose this :

Table A (Batches)
--------------------

Columns :
BatchID
...

Table B (Emails)
------------------

Columns :
EmailID
BatchID
SentStatus
...

I'm writing a program (argument's sake) where a user uploads a batch of emails to a server. The server then sends the batch, allowing the user to continue to work. A batch is created to keep track of who uploaded the batch, when it was uploaded etc. Emails are linked to the Batches by the BatchID column. The SentStatus column in the Emails table is either 0 (Pending), 1 (Sent) or 2 (Error).

I need a query that will return the progress of a batch. Basically, this is the number of Emails that have been sent (or had an error) divided by the number of Emails that are Pending.

I've tried

Code: Select all

SELECT BatchID, BatchDate,
           (((SELECT COUNT(*) FROM Emails WHERE BatchStatus = 1) + 
             (SELECT COUNT(*) FROM Emails WHERE BatchStatus = 2))/
             (SELECT COUNT(*) FROM Emails WHERE BatchStatus = 3)) as                     
             'Progress'
FROM Batches INNER JOIN EMAILS ON Emails.BatchID = Batches.BatchID

but then it returns a row for each Email, and not one for the whole batch. I've tried adding DISTINCT, but the results are not correct.

EDIT : "correct" meaning either 0% or 100%, and not in between.

[SoulBlade]

Hi

Post some table examples.

[endev8003]

Grim

Are you using sql server or access?

Your problem lies that in the nested selects you are not checking for which batch you are busy with,so it will return the entire set of emails.

Try adding 'AND BatchID = Batches.BatchID' to each nested select.
It seems that you also do not need the join since the non-computed columns for the primary select comes from one table.

[GrimStoner]

I'm using SQL Server Express 2005.

This is what I ended up with.

Firstly, and I know this happens alot, I'm so focused on the SQL that my basic math goes out the window...

Progress = SentEmails / TotalEmails, and not Progress = SentEmails / PendingEmails.

That, and adding the "AND BatchID = Batches.BatchID" also made a difference.

Code: Select all

SELECT     BatchID AS 'Batch ID', 
               CASE BatchStatus 
                            WHEN 1 THEN 'Pending' 
                            WHEN 2 THEN 'Completed' 
                            WHEN 0 THEN 'Sending' 
               END AS 'Status', 
               BatchCreator AS 'Creator', 
               BatchDate AS 'Date added',
                           ((SELECT     COUNT(*) AS Expr1
                            FROM          Emails
                            WHERE      (EmailStatus = 0) AND 
                                            (EmailBatchID = Batches.BatchID)) *100) /
                            (SELECT     COUNT(*) AS Expr1
                            FROM          Emails
                            WHERE     (EmailBatchID = Batches.BatchID)) AS  
                            'Progress (%)'
FROM         Batches
WHERE     (BatchStatus = 1)

[endev8003]

Another possible way to do it is to use a cursor. Not really necessary for this case, but if you have too many nested selects a cursor will be better, as well as avoid a 50 line select query.

[GrimStoner]

Sorry... I've never heard of cursors in SQL... how would you do this?

[endev8003]

A cursor is used to loop through the results from a select query. You use them in stored procedures and functions. AFAIK, you can't use them in a view, since a view is a single select statement.

A sample for a cursor would be like this:

Code: Select all

DECLARE @BatchID int
DECLARE batchcursor CURSOR FOR
    SELECT BatchID FROM Batches

OPEN batchcursor
FETCH NEXT FROM batchcursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    --Add sql commands for the loop that uses the batchid

    FETCH NEXT FROM batchcursor INTO @BatchID
END
CLOSE batchcursor
DEALLOCATE batchcursor

1) The declare statement creates the cursor and reserves the name
2) The open statement opens the cursor so values can be read.
3) The first fetch next statement reads the first value from the cursor
4) the while statements loops until @@fetch_status is not 0. That occurs when the last value has already been read or an error occurs.
5) You add your sql commands in the loop, like saving the results from calculations or selects inside variables
6) The fetch next statement at the end of the loop reads the next value from the cursor. This should always be at the end of the loop
7) The close statement closes the cursor after it is done
The deallocate statement removes the cursor from memory and unreserves the name.

Normal cursors only move forward through the result set. You can also use a scroll cursor (DECLARE name SCROLL CURSOR FOR) but I haven't used it yet.

Cursors seem complicated in the beginning, but as you use them then it becomes easier.

[GrimStoner]

Is there a way I can check for 0 values in my query? the

Code: Select all

((SELECT     COUNT(*) AS Expr1
                  FROM          Emails
                  WHERE      (EmailStatus = 0) AND
                  (EmailBatchID = Batches.BatchID)) *100) /
                  (SELECT     COUNT(*) AS Expr1
                  FROM          Emails
                  WHERE     (EmailBatchID = Batches.BatchID)) AS 
                  'Progress (%)' 

produces a divide by zero error when a batch is uploaded with no emails in it. I'm using a typed dataset in VB, so I want to do the check in SQL before it gets to the client.

[endev8003]

Not that I know of. You could try to put the second select inside an if statement, and return the select if it is not zero. But I don't think sql will allow that. Even if it works, the entire query will become confusing.

My advice is to put the query inside a multi-statement function that returns a table. Use a cursor like I showed above to scroll through the batches. You can save the results from the two nested queries inside local variables. Then you can check the values for the variables before using the final query.