T-SQL Puzzle, well to me at least...

10 Feb 2006 10:50 PM

Josh Crosby

Hello, I was asked by the finance dept. to come up with a solution to produce
a report for them for tax area... anyway here's the probelm

have a table 3 columns (invoicenumber, total sale, tax)
so i have data that looks a bit like this

1 $200 $1.75
1 $200 $4.00
1 $200 $3.00
2 $400 $0.99
2 $400 $7.88
etc.. etc..

so the goal is to sum these to give a total sale, total tax per invoice
right, but the problem is when i sum for invoice #1 total sale is $600 when
it is actually $200, it's just the way Great Plains stores it.

So question, how can I produce a query to output something like this?
1 -- $1.75
1 -- $4.00
1 $200 $3.00
2 -- $0.99
2 $400 $7.88

see what I mean, any help would be great.

10 Feb 2006 10:57 PM

Mark Williams

Not sure if this is what you want, but

SELECT t1.invoicenumber, t1.[total sale], t2.[sum tax]
FROM
(
SELECT DISTINCT invoicenumber, [total sale] FROM [YourTable]
) t1
INNER JOIN
(
SELECT invoicenumber, SUM(tax) FROM [YourTable]
GROUP BY invoicenumber
) t2
ON t1.invoicenumber = t2.invoicenumber
ORDER BY t1.invoicenumber

--

Show quote

"Josh Crosby" wrote:

> Hello, I was asked by the finance dept. to come up with a solution to produce
> a report for them for tax area... anyway here's the probelm
>
> have a table 3 columns (invoicenumber, total sale, tax)
> so i have data that looks a bit like this
>
> 1 $200 $1.75
> 1 $200 $4.00
> 1 $200 $3.00
> 2 $400 $0.99
> 2 $400 $7.88
> etc.. etc..
>
> so the goal is to sum these to give a total sale, total tax per invoice
> right, but the problem is when i sum for invoice #1 total sale is $600 when
> it is actually $200, it's just the way Great Plains stores it.
>
> So question, how can I produce a query to output something like this?
> 1 -- $1.75
> 1 -- $4.00
> 1 $200 $3.00
> 2 -- $0.99
> 2 $400 $7.88
>
> see what I mean, any help would be great.
>

10 Feb 2006 11:52 PM

Rob Morhaime

Your example output doesn't match your description of what you say you want:
"total sale, total tax per invoice".

Here's something that gives you one row per invoice:

    Select InvoiceId, Max(Total) As TotalSale, Sum(Tax) As TotalTax
    From YourTaxTable
    Group By InvoiceId

-Rob

11 Feb 2006 12:45 AM

Dave Frommer

Here is a reproduction script and a couple of examples:

CREATE TABLE Invoice
(
InvoiceNumber int,
TotalSale int,
Tax Decimal(9,2)
)
GO

INSERT INTO Invoice
SELECT 1,200,1.75
UNION SELECT 1,200,4
UNION SELECT 1,200,3
UNION SELECT 2,400,0.99
UNION SELECT 2,400,7.88

-- Just return the totals
SELECT MAX(InvoiceNumber) AS InvoiceNumber,
MAX(TotalSale) AS TotalSale,
SUM(Tax) AS TotalTax
FROM Invoice
GROUP BY InvoiceNumber

-- Return the details with the TotalSale in only 1 row per InvoiceNumber
SELECT Invoice.InvoiceNumber,
CASE
   WHEN Invoice.Tax = MaxTable.Tax THEN Invoice.TotalSale
   ELSE NULL
END,
Invoice.Tax
FROM Invoice
INNER JOIN
   (SELECT InvoiceNumber,
    MAX(Tax) AS Tax
    FROM Invoice
    GROUP BY InvoiceNumber) AS MaxTable
ON Invoice.InvoiceNumber = MaxTable.InvoiceNumber

DROP TABLE Invoice
GO
Show quote

"Josh Crosby" <Josh Cro***@discussions.microsoft.com> wrote in message
news:EE331952-8B01-4872-B046-FAF159189DE2@microsoft.com...
> Hello, I was asked by the finance dept. to come up with a solution to
> produce
> a report for them for tax area... anyway here's the probelm
>
> have a table 3 columns (invoicenumber, total sale, tax)
> so i have data that looks a bit like this
>
> 1 $200 $1.75
> 1 $200 $4.00
> 1 $200 $3.00
> 2 $400 $0.99
> 2 $400 $7.88
> etc.. etc..
>
> so the goal is to sum these to give a total sale, total tax per invoice
> right, but the problem is when i sum for invoice #1 total sale is $600
> when
> it is actually $200, it's just the way Great Plains stores it.
>
> So question, how can I produce a query to output something like this?
> 1 -- $1.75
> 1 -- $4.00
> 1 $200 $3.00
> 2 -- $0.99
> 2 $400 $7.88
>
> see what I mean, any help would be great.
>

13 Feb 2006 1:35 AM

Josh Crosby

Wow thanks guys, I'm going to give it a shot tomorrow, will post the results.
thanks for your help

--
Josh Crosby

Show quote

"Josh Crosby" wrote:

> Hello, I was asked by the finance dept. to come up with a solution to produce
> a report for them for tax area... anyway here's the probelm
>
> have a table 3 columns (invoicenumber, total sale, tax)
> so i have data that looks a bit like this
>
> 1 $200 $1.75
> 1 $200 $4.00
> 1 $200 $3.00
> 2 $400 $0.99
> 2 $400 $7.88
> etc.. etc..
>
> so the goal is to sum these to give a total sale, total tax per invoice
> right, but the problem is when i sum for invoice #1 total sale is $600 when
> it is actually $200, it's just the way Great Plains stores it.
>
> So question, how can I produce a query to output something like this?
> 1 -- $1.75
> 1 -- $4.00
> 1 $200 $3.00
> 2 -- $0.99
> 2 $400 $7.88
>
> see what I mean, any help would be great.
>