Three variables in SQL Query clause

I need three variables in two tables to qualify the data I want displayed in a Delphi DBGrid. They are [Batch] .BatchNumber,[Batch].ClosingTime in Table [Batch] and [TenderEntry].RegisterID, in Table [TenderEntry] , in the following clause;

WHERE [TenderEntry].BatchNumber = :BatNum  AND [Batch].ClosingTime = :DateNum… AND [Batch].RegisterID = :RegID does not work as it retrieves no data.

I need some help in properly placing the above variables into the following Query.
SELECT
      CAST([Batch].ClosingTime AS date) AS Date
    , [Batch].RegisterID                AS Num
    , [Batch].BatchNumber          AS Batch
    , [Batch].OpeningTotal           AS Opening
    , [Batch].ClosingTotal             AS Closing

    , SUM(CASE
      WHEN [Description] = 'Check' THEN TenderEntry.Amount
            ELSE 0 END)           AS Checks
    , SUM(CASE
            WHEN [Description] = 'Credit' THEN TenderEntry.Amount
            ELSE 0 END)           AS BankCards
    , SUM(CASE
            WHEN [Description] = 'Cash' THEN TenderEntry.Amount
            ELSE 0 END)           AS Cash
    , SUM(CASE
            WHEN [Description] = 'Coupons' THEN TenderEntry.Amount
            ELSE 0 END)           AS Coupons
    , SUM(CASE
            WHEN [Description] = 'GiftCert' THEN TenderEntry.Amount
            ELSE 0 END)           AS GiftCert
    , SUM(CASE
            WHEN [Description] = 'CusCredit' THEN TenderEntry.Amount
            ELSE 0 END)           AS CusCredit

FROM TenderEntry
      INNER JOIN [batch]
                  ON TenderEntry.BatchNumber = [Batch].BatchNumber

< Doesn't work >--WHERE [TenderEntry].BatchNumber = :BatNum  AND [Batch].ClosingTime = :DateNum… AND [Batch].RegisterID = :RegID

GROUP BY
      CAST([Batch].ClosingTime AS date)
    , [Batch].RegisterID
    , [Batch].BatchNumber
    , [Batch].OpeningTotal
    , [Batch].ClosingTotal;

Open in new window

Thank you for the help,
Visionetv
visionetvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
? is it because you need to use @ instead of :
(this may be a dumb question, but I'm not sure how you are using the query)

--------
Not really sure I quite follow. It is quite possible there is no data for the wanted parameter values.

OR; is the problem something like this:

I have 3 parameters, but they might all be empty or some of them empty, and I want a single query for all combinations of parameters including empty
0
visionetvAuthor Commented:
Hi Paul;

WHERE [TenderEntry].BatchNumber = :BatNum works but I need the data by date, and Register Number

Adding: AND [Batch].ClosingTime = :DateNum… AND [Batch].RegisterID = :RegID returns no data.

The purpose of the Query is to SUM the data in the [TenderEntry] table and display the results by Registers Number, Batch Number and Date.  (See attached graphic)

The addition of the queries using the [Batch] table are after the INNER JOIN statement for the [TenderEntry] table if that makes a difference.
3-Variables.jpg
0
PortletPaulfreelancerCommented:
I'm afraid that doesn't help a lot, I believe you when you say it returns nothing.
Have you tried it without the date parameter?

WHERE [TenderEntry].BatchNumber = :BatNum  
AND [Batch].RegisterID = :RegID

When working with date/time information it is easy to overlook that possibility that the date information might be
2014-08-11 13:14:15 +12345
and getting a record "equal to" 2014-08-11 would fail, but a date range might work:
AND ( [Batch].ClosingTime >= :DateNum AND [Batch].ClosingTime < dateadd(day,1, :DateNum )  )



oh! and you don't tell me what the parameter values you used are, so I'm still none the wiser :) You could have asked for batch number 98765.6397 for all I know.


---
btw:
flipping between this page and images of your data does not thrill me, it is ever so much easier to, either:

use text copy/paste into a code block, (my preference) or
use an "embedded image" , like this

Insert Image, last icon on the right in the toolbar
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

visionetvAuthor Commented:
WHERE [TenderEntry].BatchNumber = :BatNum  AND [Batch].RegisterID = :RegID returns no data

WHERE [TenderEntry].BatchNumber = :BatNum  AND [Batch].ClosingTime = :DateNum
returns no data

The value for :BatNum is an integer = to 1
The value for :RegisterID is an Integer = to 1
The value for :ClosingTime is a whole date = to 8/28/2014
0
PortletPaulfreelancerCommented:
:(
you don't display RegisterID in the image, so how do I know if you have a batch=1 with register=1

& The only date I can see would match NOT to a parameter of 8/28/2014


How about trying this?

WHERE [TenderEntry].BatchNumber = :BatNum  
OR [Batch].RegisterID = :RegID
OR ( [Batch].ClosingTime >= :DateNum AND [Batch].ClosingTime < dateadd(day,1, :DateNum )  )

I cannot do this work for you, only you have the db with the tables and the data, if records do not exist that do not meet all your conditions, then that is what you will get, nothing.
0
visionetvAuthor Commented:
I put a record in the DB that specifically meets the conditions in the query, why adding two additional variable statements that match the data in the record won't return anything is the question. I'll try your suggestion and see if it makes a difference.
0
PortletPaulfreelancerCommented:
visionetv I will believe you, but as I cannot verify your data against any query myself I cannot directly help.

You have to get one parameter work first.
If you can't get data from WHERE [TenderEntry].BatchNumber = :BatNum  
then adding more conditions isn't going to help.

Are you certain :BatNum is the right syntax?

    CREATE TABLE TenderEntry
    	([Num] int, [Date] datetime, [Batch] int, [Opening] int, [Closing] int, [Checks] int, [BankCards] int, [Cash] int, [Coupons] int, [GiftCert] int, [CusCredit] int, [RegisterID] int)
    ;
    	
    INSERT INTO TenderEntry
    	([Num], [Date], [Batch], [Opening], [Closing], [Checks], [BankCards], [Cash], [Coupons], [GiftCert], [CusCredit], [RegisterID])
    VALUES
    	(1, '2014-08-11 12:13:14', 1, 0, 0, 0, 0, 0, 0, 0, 0, 1)
    ;

**Query 1**:

    select
    *
    from TenderEntry
    where Batch = 1
    

**[Results][2]**:
    
    | NUM |                          DATE | BATCH | OPENING | CLOSING | CHECKS | BANKCARDS | CASH | COUPONS | GIFTCERT | CUSCREDIT | REGISTERID |
    |-----|-------------------------------|-------|---------|---------|--------|-----------|------|---------|----------|-----------|------------|
    |   1 | August, 11 2014 12:13:14+0000 |     1 |       0 |       0 |      0 |         0 |    0 |       0 |        0 |         0 |          1 |


**Query 2**:

    select
    *
    from TenderEntry
    where RegisterID = 1
    

**[Results][3]**:
    
    | NUM |                          DATE | BATCH | OPENING | CLOSING | CHECKS | BANKCARDS | CASH | COUPONS | GIFTCERT | CUSCREDIT | REGISTERID |
    |-----|-------------------------------|-------|---------|---------|--------|-----------|------|---------|----------|-----------|------------|
    |   1 | August, 11 2014 12:13:14+0000 |     1 |       0 |       0 |      0 |         0 |    0 |       0 |        0 |         0 |          1 |


**Query 3**:

    select
    *
    from TenderEntry
    where Date >= '2014-08-11' and Date < dateadd(day,1,'2014-08-11')
    

**[Results][4]**:
    
    | NUM |                          DATE | BATCH | OPENING | CLOSING | CHECKS | BANKCARDS | CASH | COUPONS | GIFTCERT | CUSCREDIT | REGISTERID |
    |-----|-------------------------------|-------|---------|---------|--------|-----------|------|---------|----------|-----------|------------|
    |   1 | August, 11 2014 12:13:14+0000 |     1 |       0 |       0 |      0 |         0 |    0 |       0 |        0 |         0 |          1 |


**Query 4**:

    select
    *
    from TenderEntry
    where Batch = 1
    and RegisterID = 1
    and Date >= '2014-08-11' and Date < dateadd(day,1,'2014-08-11')
    

**[Results][5]**:
    
    | NUM |                          DATE | BATCH | OPENING | CLOSING | CHECKS | BANKCARDS | CASH | COUPONS | GIFTCERT | CUSCREDIT | REGISTERID |
    |-----|-------------------------------|-------|---------|---------|--------|-----------|------|---------|----------|-----------|------------|
    |   1 | August, 11 2014 12:13:14+0000 |     1 |       0 |       0 |      0 |         0 |    0 |       0 |        0 |         0 |          1 |



  [1]: http://sqlfiddle.com/#!3/0d814/2
You have to be the detective. 

Open in new window

see http://sqlfiddle.com/#!3/0d814/2
0
awking00Commented:
Can you post some sample data for both the TenderEntry and Batch tables and your expected results? Include the data for the entry you made that meets the criteria.
0
visionetvAuthor Commented:
Hi awking00;

Attached are two Excel files with the data used for testing the Query. The [Batch] table has 2 rows and the [TenderEntry] contains 45 rows.
dbo-Batch.xls
dbo-TenderEntry.xls
0
PortletPaulfreelancerCommented:
This result:
|       DATE | NUM | BATCH | OPENING | CLOSING | CHECKS | BANKCARDS | CASH | COUPONS | GIFTCERT | CUSCREDIT |
|------------|-----|-------|---------|---------|--------|-----------|------|---------|----------|-----------|
| 2014-08-11 |   1 |     1 |     250 |     416 |      0 |         0 |  388 |       0 |        0 |         0 |

Open in new window

Was produced by the following query. Note that the date filter is best applied in the HAVING clause as you are grouping by a cast(... date) column.
declare @BatNum  as int = 1
declare @RegID   as int = 1
declare @DateNum as date = '20140811'

SELECT
      CAST([Batch].ClosingTime AS date) AS Date
    , [Batch].RegisterID                AS Num
    , [Batch].BatchNumber          AS Batch
    , [Batch].OpeningTotal           AS Opening
    , [Batch].ClosingTotal             AS Closing

    , SUM(CASE
      WHEN [Description] = 'Check' THEN TenderEntry.Amount
            ELSE 0 END)           AS Checks
    , SUM(CASE
            WHEN [Description] = 'Credit' THEN TenderEntry.Amount
            ELSE 0 END)           AS BankCards
    , SUM(CASE
            WHEN [Description] = 'Cash' THEN TenderEntry.Amount
            ELSE 0 END)           AS Cash
    , SUM(CASE
            WHEN [Description] = 'Coupons' THEN TenderEntry.Amount
            ELSE 0 END)           AS Coupons
    , SUM(CASE
            WHEN [Description] = 'GiftCert' THEN TenderEntry.Amount
            ELSE 0 END)           AS GiftCert
    , SUM(CASE
            WHEN [Description] = 'CusCredit' THEN TenderEntry.Amount
            ELSE 0 END)           AS CusCredit

FROM TenderEntry
      INNER JOIN [batch]
                  ON TenderEntry.BatchNumber = [Batch].BatchNumber

WHERE [TenderEntry].BatchNumber = @BatNum  
AND [Batch].RegisterID = @RegID

GROUP BY
      CAST([Batch].ClosingTime AS date)
    , [Batch].RegisterID
    , [Batch].BatchNumber
    , [Batch].OpeningTotal
    , [Batch].ClosingTotal

HAVING CAST([Batch].ClosingTime AS date) = @DateNum
;

Open in new window

You can see this working at:  http://sqlfiddle.com/#!3/95428/6

more details, DDL truncated to fit in 8000 char limit
**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE Batch
    ([CustomerDepositMade] int, [CustomerDepositRedeemed] int, [LastUpdated] datetime, [StoreID] int, [BatchNumber] int, [Status] int, [RegisterID] int, [OpeningTime] datetime, [ClosingTime] datetime, [OpeningTotal] int, [ClosingTotal] int, [Sales] numeric, [Returns] numeric, [Tax] numeric, [SalesPlusTax] numeric, [Commission] int, [PaidOut] int, [Dropped] int, [PaidOnAccount] int, [PaidToAccount] int, [CustomerCount] int, [NoSalesCount] int, [AbortedTransCount] int, [TotalTendered] numeric, [TotalChange] numeric, [Discounts] int, [CostOfGoods] numeric, [LayawayPaid] int, [LayawayClosed] int, [Shipping] int, [DBTimeStamp] varchar(1), [TenderRoundingError] int, [DebitSurcharge] int, [CashBackSurcharge] int, [Vouchers] int)
    ;
    
    INSERT INTO Batch
    ([CustomerDepositMade], [CustomerDepositRedeemed], [LastUpdated], [StoreID], [BatchNumber], [Status], [RegisterID], [OpeningTime], [ClosingTime], [OpeningTotal], [ClosingTotal], [Sales], [Returns], [Tax], [SalesPlusTax], [Commission], [PaidOut], [Dropped], [PaidOnAccount], [PaidToAccount], [CustomerCount], [NoSalesCount], [AbortedTransCount], [TotalTendered], [TotalChange], [Discounts], [CostOfGoods], [LayawayPaid], [LayawayClosed], [Shipping], [DBTimeStamp], [TenderRoundingError], [DebitSurcharge], [CashBackSurcharge], [Vouchers])
    VALUES
    (0, 0, '2014-08-11 12:59:05', 0, 1, 7, 1, '2014-08-09 19:00:08', '2014-08-11T12:59:04', 250, 416, 197.93, 0, 18.8, 216.73, 0, 0, 0, 0, 0, 1, 0, 0, 216.73, 0, 0, 108.27, 0, 0, 0, 'x', 0, 0, 0, 0),
    (0, 0, '2014-08-11 13:09:04', 0, 2, 7, 1, '2014-08-11 12:59:04', '2014-08-11T13:03:07', 250, 500, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'x', 0, 0, 0, 0),
    (0, 0, '2014-08-11 13:15:06', 0, 3, 1, 1, '2014-08-11 13:03:07', '2014-08-11T13:09:46', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'x', 0, 0, 0, 0),
    (0, 0, '2014-08-13 11:42:54', 0, 4, 0, 1, '2014-08-11 13:09:46', NULL, 0, 0, 568.32, -19.99, 50.89, 621.11, 0, 0, 0, 0, 0, 28, 0, 0, 616.7, -17.48, 0, 811.45, 0, 0, 0, 'x', 0, 0, 0, 0)
    ;
    
    CREATE TABLE TenderEntry
    ([BatchNumber] int, [CreditCardExpiration] int, [OrderHistoryID] int, [DropPayoutID] int, [StoreID] int, [ID] int, [TransactionNumber] int, [TenderID] int, [PaymentID] int, [Description] varchar(5), [CreditCardNumber] int, [CreditCardApprovalCode] int, [Amount] numeric, [AccountHolder] int, [DBTimeStamp] int, [RoundingError] int, [AmountForeign] numeric, [BankNumber] int, [SerialNumber] int, [State] int, [License] int, [BirthDate] varchar(4), [TransitNumber] int, [VisaNetAuthorizationID] int, [DebitSurcharge] int, [CashBackSurcharge] int, [IsCreateNew] varchar(5))
    ;
    
    INSERT INTO TenderEntry
    ([BatchNumber], [CreditCardExpiration], [OrderHistoryID], [DropPayoutID], [StoreID], [ID], [TransactionNumber], [TenderID], [PaymentID], [Description], [CreditCardNumber], [CreditCardApprovalCode], [Amount], [AccountHolder], [DBTimeStamp], [RoundingError], [AmountForeign], [BankNumber], [SerialNumber], [State], [License], [BirthDate], [TransitNumber], [VisaNetAuthorizationID], [DebitSurcharge], [CashBackSurcharge], [IsCreateNew])
    VALUES
    (1, 1.1, 0, 0, 0, 1, 1, 1, 0, 'Cash', 1.1, 1.1, 85.36, 1.1, 1.1, 0, 85.36, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (1, 1.1, 0, 0, 0, 2, 2, 1, 0, 'Cash', 1.1, 1.1, 86.38, 1.1, 1.1, 0, 86.38, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (1, 1.1, 0, 0, 0, 3, 3, 1, 0, 'Cash', 1.1, 1.1, 216.73, 1.1, 1.1, 0, 216.73, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 4, 4, 1, 0, 'Cash', 1.1, 1.1, -21.89, 1.1, 1.1, 0, -21.89, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 5, 5, 1, 0, 'Cash', 1.1, 1.1, 13.09, 1.1, 1.1, 0, 13.09, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 6, 6, 1, 0, 'Cash', 1.1, 1.1, 22.83, 1.1, 1.1, 0, 22.83, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 7, 7, 1, 0, 'Cash', 1.1, 1.1, 35.08, 1.1, 1.1, 0, 35.08, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 8, 8, 1, 0, 'Cash', 1.1, 1.1, 6.52, 1.1, 1.1, 0, 6.52, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 9, 9, 1, 0, 'Cash', 1.1, 1.1, 6.52, 1.1, 1.1, 0, 6.52, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 10, 10, 3, 0, 'Check', 1.1, 1.1, 7.61, 1.1, 1.1, 0, 7.61, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 11, 11, 3, 0, 'Check', 1.1, 1.1, 14.18, 1.1, 1.1, 0, 14.18, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 12, 12, 3, 0, 'Check', 1.1, 1.1, 30.5, 1.1, 1.1, 0, 30.5, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 13, 13, 3, 0, 'Check', 1.1, 1.1, 30.5, 1.1, 1.1, 0, 30.5, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 14, 14, 3, 0, 'Check', 1.1, 1.1, 46.91, 1.1, 1.1, 0, 46.91, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 15, 15, 1, 0, 'Cash', 1.1, 1.1, 3.23, 1.1, 1.1, 0, 3.23, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 16, 16, 1, 0, 'Cash', 1.1, 1.1, 14.02, 1.1, 1.1, 0, 14.02, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 17, 17, 1, 0, 'Cash', 1.1, 1.1, 10, 1.1, 1.1, 0, 10, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 18, 17, 1, 0, 'Cash', 1.1, 1.1, -1.29, 1.1, 1.1, 0, -1.29, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 19, 18, 1, 0, 'Cash', 1.1, 1.1, 15, 1.1, 1.1, 0, 15, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 20, 18, 1, 0, 'Cash', 1.1, 1.1, -0.82, 1.1, 1.1, 0, -0.82, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 21, 19, 1, 0, 'Cash', 1.1, 1.1, 11, 1.1, 1.1, 0, 11, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 22, 19, 1, 0, 'Cash', 1.1, 1.1, -0.66, 1.1, 1.1, 0, -0.66, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 23, 20, 1, 0, 'Cash', 1.1, 1.1, 7, 1.1, 1.1, 0, 7, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 24, 20, 1, 0, 'Cash', 1.1, 1.1, -0.48, 1.1, 1.1, 0, -0.48, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 25, 21, 1, 0, 'Cash', 1.1, 1.1, 8, 1.1, 1.1, 0, 8, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 26, 21, 1, 0, 'Cash', 1.1, 1.1, -0.39, 1.1, 1.1, 0, -0.39, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 27, 22, 1, 0, 'Cash', 1.1, 1.1, 10, 1.1, 1.1, 0, 10, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 28, 22, 1, 0, 'Cash', 1.1, 1.1, -0.58, 1.1, 1.1, 0, -0.58, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 29, 23, 1, 0, 'Cash', 1.1, 1.1, 11, 1.1, 1.1, 0, 11, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 30, 23, 1, 0, 'Cash', 1.1, 1.1, -0.1, 1.1, 1.1, 0, -0.1, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 31, 24, 1, 0, 'Cash', 1.1, 1.1, 5, 1.1, 1.1, 0, 5, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 32, 24, 1, 0, 'Cash', 1.1, 1.1, -0.67, 1.1, 1.1, 0, -0.67, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 33, 25, 1, 0, 'Cash', 1.1, 1.1, 3, 1.1, 1.1, 0, 3, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 34, 25, 1, 0, 'Cash', 1.1, 1.1, -0.82, 1.1, 1.1, 0, -0.82, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 35, 26, 1, 0, 'Cash', 1.1, 1.1, 6, 1.1, 1.1, 0, 6, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 36, 26, 1, 0, 'Cash', 1.1, 1.1, -0.97, 1.1, 1.1, 0, -0.97, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 37, 27, 3, 0, 'Check', 1.1, 1.1, 134.68, 1.1, 1.1, 0, 134.68, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 38, 28, 1, 0, 'Cash', 1.1, 1.1, 53.92, 1.1, 1.1, 0, 53.92, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 39, 29, 1, 0, 'Cash', 1.1, 1.1, 80, 1.1, 1.1, 0, 80, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x'),
    (4, 1.1, 0, 0, 0, 40, 29, 1, 0, 'Cash', 1.1, 1.1, -0.9, 1.1, 1.1, 0, -0.9, 1.1, 1.1, 1.1, 1.1, NULL, 1.1, 0, 0, 0, 'x')
    ;
    

**Query 1**:

    declare @BatNum  as int = 1
    declare @RegID   as int = 1
    declare @DateNum as date = '20140811'
    
    SELECT
          CAST([Batch].ClosingTime AS date) AS Date
        , [Batch].RegisterID                AS Num
        , [Batch].BatchNumber          AS Batch
        , [Batch].OpeningTotal           AS Opening
        , [Batch].ClosingTotal             AS Closing
    
        , SUM(CASE
          WHEN [Description] = 'Check' THEN TenderEntry.Amount
                ELSE 0 END)           AS Checks
        , SUM(CASE
                WHEN [Description] = 'Credit' THEN TenderEntry.Amount
                ELSE 0 END)           AS BankCards
        , SUM(CASE
                WHEN [Description] = 'Cash' THEN TenderEntry.Amount
                ELSE 0 END)           AS Cash
        , SUM(CASE
                WHEN [Description] = 'Coupons' THEN TenderEntry.Amount
                ELSE 0 END)           AS Coupons
        , SUM(CASE
                WHEN [Description] = 'GiftCert' THEN TenderEntry.Amount
                ELSE 0 END)           AS GiftCert
        , SUM(CASE
                WHEN [Description] = 'CusCredit' THEN TenderEntry.Amount
                ELSE 0 END)           AS CusCredit
    
    FROM TenderEntry
          INNER JOIN [batch]
                      ON TenderEntry.BatchNumber = [Batch].BatchNumber
    
    WHERE [TenderEntry].BatchNumber = @BatNum  
    AND [Batch].RegisterID = @RegID
    
    GROUP BY
          CAST([Batch].ClosingTime AS date)
        , [Batch].RegisterID
        , [Batch].BatchNumber
        , [Batch].OpeningTotal
        , [Batch].ClosingTotal
    
    HAVING CAST([Batch].ClosingTime AS date) = @DateNum
    

**[Results][2]**:
    
    |       DATE | NUM | BATCH | OPENING | CLOSING | CHECKS | BANKCARDS | CASH | COUPONS | GIFTCERT | CUSCREDIT |
    |------------|-----|-------|---------|---------|--------|-----------|------|---------|----------|-----------|
    | 2014-08-11 |   1 |     1 |     250 |     416 |      0 |         0 |  388 |       0 |        0 |         0 |



  [1]: http://sqlfiddle.com/#!3/95428/6

Open in new window

nb: in the spreadsheets time columns carry odd data that wouldn't parse easily, they were replaced. null columns filled with 1.1 etc. to get the data into sqlfiddle.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
visionetvAuthor Commented:
Hi Paul;

Thank you for the new Query code.

The solution I was working was to combine the DB tables ...
SELECT
[Batch].BatchNumber, [Batch].ClosingTime, [Batch].RegisterID, [TenderEntry].BatchNumber,
[TenderEntry].Amount
FROM [Batch], [TenderEntry]

Filter with the variable...
WHERE [Batch].BatchNumber = TenderEntry.BatchNumber
AND [Batch].ClosingTime = :DateNum... AND [Batch].RegisterID = :RegID

and sum the data with a series of subqueries...
SELECT
SUM(Amount) AS Cash
FROM [TenderEntry]
WHERE Description = 'Cash'... etc,

Your solution is far more efficient, and I thank you again.

The remaining issue is, while the Query works without issue outside the Delphi IDE, it is not displaying  the results in a Delphi DBGrid component which I am trying to debug now.

Regards,
Visionetv
0
PortletPaulfreelancerCommented:
Thanks and good luck the the Delphi DBGrid (that's not something I can assist with). Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.