Grouping within a SQL Statement?

Question

I have a query in SQL in which I am presenting whether or not a person has payments or outstanding charges. In this query I have a case statement, but I need to group the records together to make sure payments are added together so that what I am checking for is correct.

Let me show the code and hopefully this will make a little more sense.

Here is the query I am currently using:

Declare @tmpTable Table
(
   [Bid #] int,
   Name varchar(200),
   Spent numeric(18,2),
   Paid numeric(18,2),
   [Credit Card On File] varchar(3),
   SaleCounter int,
   Notes varchar(max)
)

Insert into @tmpTable([Bid #], Name, Spent, Paid, [Credit Card On File], SaleCounter, Notes)

Select s.[BidderNumber] as 'Bid #', ltrim(rtrim(b.bidderName)) as 'Name', isnull(s.saleprice * s.Quantity,0) as 'Spent',
isnull(t.Amount,0) as 'Paid', 
case
    when b.cconfile = 1 then 'Yes'
    else
        'No'
    end as 'Credit Card On File', 

    s.SaleCounter, isnull(t.Notes, '') as 'Notes' 

from sales s inner join Bidders b on s.BidderNumber = b.BidderNumber
  Left outer join transactions t on t.BidderNumber = s.BidderNumber 

order by s.Biddernumber, b.biddername, b.cconfile, SaleCounter

 Select [Bid #], Name, Spent as 'Total Purchases', Paid as 'Current Payments',
       case
        when [Credit Card On File] = 'Yes' then 
            case 
                when cast(Paid as numeric(18,2)) = 0 then     cast(Spent as numeric(18,2)) 
                else
                    case when (sum(cast(Paid as numeric(18,2)))) > sum(cast(Spent as numeric(18,2))) then (cast(Paid as numeric(18,2)))- sum(cast(Spent as numeric(18,2)))
                         else (cast(Spent as numeric(18,2)) - cast(Paid as numeric(18,2)))
                         end
                end

        else 0

        end as 'Amount To Charge Credit Card',
    case
        when [Credit Card On File] = 'No' then 
            case 
                when cast(Paid as numeric(18,2)) = 0 then cast(Spent as numeric(18,2)) 
                else
                    case when (sum(cast(Paid as numeric(18,2)))) > sum(cast(Spent as numeric(18,2))) then (cast(Paid as numeric(18,2)))- sum(cast(Spent as numeric(18,2)))
                         else (cast(Spent as numeric(18,2)) - cast(Paid as numeric(18,2)))
                         end
                end--sum(Outstanding)
        ELSE 0

        end as 'Outstanding Balance',  Notes
from @tmpTable 

group by [Bid #], name, spent, paid, [Credit Card On File], SaleCounter, Notes
order by [Bid #], Name, spent, paid, [Credit Card On File], SaleCounter, Notes

Here is the recordset that is returned and inserted into the @tmpTable:

Bid #       Name                  Total Purchases   Current Payments    Amount To Charge Credit Card    Outstanding Balance    Notes
101         Tom & Joan Bergland   7500.00           0.00                0.00                             7500.

102         John & Bonnie Black   50.00             50.00               0.00                              0.00  

108         Cindy Davidson        3600.00           1600.00             0.00                            2000.00                 250

108         Cindy Davidson        3600.00           2000.00             0.00                            1600.00 

109         Cynthia Davis         315.00            315.00              0.00                              0.00                  2355

117         Susan Harris          75.00             75.00               0.00                             0.00   

119         Jim & Julie Hill      520.00              0.00              520.00                             0.00

125         Bill & Amy Lee        526.00            526.00              0.00                             0.00

Now my issue is that I am showing a balance owed by Bid # 108 when there is no balance owed. The two records reflect two payments made against the balance and when the two are totaled together they equal the balance. I need to show each payment to the user, but when the payments equal the total purchase, then no balance owed needs to be shown. however, if there is a balance, that needs to be shown. (Credit Card Payments are different...what I am having issues with are non CC payments)

Is there a way to group inside the case statement or is there something in sql I am missing that will allow what I need to happen?

I appreciate your help in advance.

score 0 · Answer 1 · 2014-01-05T01:17:33.363

0

My suspicion is that you are including some fields in your GROUP BY that you don't need and some you should be aggregating. I looked at your output and this is what I could reason as a query to achieve that. Try this out and see if it is what you were attempting:

EDIT: This will concatenate your Notes column

SELECT
    acct.[Bid #],
    acct.Name,
    SUM(acct.Spent) AS [Total Purchases],
    SUM(acct.Paid) AS [Current Payments],
    CASE WHEN acct.[Credit Card On File] = 'Yes' THEN ABS(SUM(acct.Spent) - SUM(acct.Paid)) ELSE 0 END AS [Amount To Charge Credit Card],
    CASE WHEN acct.[Credit Card On File] = 'No' THEN ABS(SUM(acct.Spent) - SUM(acct.Paid)) ELSE 0 END AS [Outstanding Balance],
    note.Notes
FROM
    @tmpTable acct
LEFT JOIN
    (
    SELECT DISTINCT
        t2.[Bid #],
        SUBSTRING((
            SELECT ',' + t1.Notes AS [text()]
            FROM @tmpTable t1
            WHERE t1.[Bid #] = t2.[Bid #]
            ORDER BY [Bid #]
            FOR XML PATH (''))
        ,2,1000) AS Notes
    FROM
        @tmpTable t2
    ) note
    ON (acct.[Bid #] = note.[Bid #])
GROUP BY
    acct.[Bid #],
    acct.Name,
    acct.[Credit Card On File],
    note.Notes

edited Jan 05 '14 at 01:17

answered Jan 05 '14 at 00:30

I tried this but the result was the same as my original code. Appreciate the answer. Much cleaner than what I have so far. Unfortunately, same result. – DadTo2 Jan 05 '14 at 00:40
Actually... the issue is the Notes column. You need to remove it. See my edit – Jan 05 '14 at 00:41
No, the only values it would be is 'Yes' or 'No'. In the database, the record for that bit field is actually NULL, but I thought the way I have to first part coded would account for NULL values with the 'Else' part of the case statement in the first part of the query. – DadTo2 Jan 05 '14 at 00:47
There is a note for one of the entries for Bid # 108, so it is throwing it off. You need to remove the Notes column from your statement. – Jan 05 '14 at 00:48
In populating the @tmpTable originally I was doing a sum of the transactions with a 0 if NULL, but that would not show the individual transactions. Do you think there should be a modification of the first part of the code somehow that would show the individual transactions but still show a 0 balance for multiple records showing payments made towards the balance that actually equal the balance? (hope that made sense.) – DadTo2 Jan 05 '14 at 00:49
Are you suggesting that so you could retain the notes column? – Jan 05 '14 at 00:50
Yes. Part of what the user needs is the notes column. – DadTo2 Jan 05 '14 at 00:52
You have two choices then: 1) Concatenate all notes (specific to Bid#) or 2) Do as you suggested, though this might be confusing for the reader of it – Jan 05 '14 at 00:54
If you don't mind, how would you suggest the concatenation in the SQL? I do appreciate the help. – DadTo2 Jan 05 '14 at 00:55
I should've asked which version of SQL Server you are running, but this should work for you. – Jan 05 '14 at 01:02
Version is Sql 2008 R2. One quick question. When I run the edited code it is not finding the t1 multipart identifier. Am I missing it? – DadTo2 Jan 05 '14 at 01:16
Still working on it. I think I am going to have to drop the notes from the query after all. – DadTo2 Jan 05 '14 at 01:48
No errors, but for Bid# 108, the record is returning double the amounts for spent and paid. '108 Cindy Davidson 7200.00 3600.00 0.00 3600.00 ,250' – DadTo2 Jan 05 '14 at 01:54
What are the original Spent and Paid amount in each of the rows for 108? – Jan 05 '14 at 01:58
3600 spent on both rows, row with Note is 1600 and row without note is 2000. the 1600 and 2000 represent paid amounts. – DadTo2 Jan 05 '14 at 02:03
Run just the subquery (inside the LEFT JOIN) and make sure you are getting only 1 record per Bid# – Jan 05 '14 at 02:07
Sorry, had to run and take care of a family emergency. I ran the subquery and it is returning one record per Bid#. Really weird. – DadTo2 Jan 05 '14 at 03:04

Grouping within a SQL Statement?

1 Answers1