?
Solved

Three variables in SQL Query clause

Posted on 2014-08-28
12
Medium Priority
?
319 Views
Last Modified: 2014-08-29
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
0
Comment
Question by:visionetv
  • 6
  • 5
12 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40291842
? 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
 

Author Comment

by:visionetv
ID: 40291928
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40291958
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:visionetv
ID: 40291978
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40291991
:(
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
 

Author Comment

by:visionetv
ID: 40292002
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40292093
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
 
LVL 32

Expert Comment

by:awking00
ID: 40292434
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
 

Author Comment

by:visionetv
ID: 40293246
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40294018
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
 

Author Closing Comment

by:visionetv
ID: 40294086
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40294088
Thanks and good luck the the Delphi DBGrid (that's not something I can assist with). Cheers, Paul
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question