Re: [Emerald] project for the day

Kenneth Gustav Eitenmiller Jr ( keitenmiller@hughestech.net )
Mon, 7 Feb 2000 15:25:27 -0600

That second part, which deals with payments, is NOT set up right.
I need the amount from the Payments.Date which falls in the entered dates,
not the amount form the Invoices.Date. Any ideas?
Ken

----- Original Message -----
From: "Kenneth Gustav Eitenmiller Jr" <keitenmiller@hughestech.net>
To: <emerald@iea-software.com>
Sent: Monday, February 07, 2000 15:17
Subject: Re: [Emerald] project for the day

> This is what I came up with...With help from Dale
> If anyone knows of anyways to clean this up, Please let us know...
> Ken
>
> declare @begindate char(10), @enddate char(10)
> set nocount on
> select @begindate = '01/01/2000'
> select @enddate = '01/31/2000'
> set nocount off
>
>
> Select TaxID, Tax, Tax2, Description from taxes /* List TaxID and
> Description */
>
> Select "Taxes Invoiced Summary for Month of February 2000" /* Must Change
> Mannually */
>
> Select ii.TaxID, beforetax=sum(ii.amount*ii.quantity),
> taxes=sum(ii.tax)+sum(ii.tax2),
> total=sum(ii.amount*ii.quantity)+sum(ii.tax)+sum(ii.tax2)
> From Invoices i, InvoiceItems ii
> Where i.InvoiceID = ii.InvoiceID
> and i.Type <> "VOID"
> and i.Date between @begindate and @enddate
> Group By TaxID
> Order By TaxID
>
> Select "Taxes Payed Summary"
>
> Select ii.TaxID, beforetax=sum(ii.amount*ii.quantity),
> taxes=sum(ii.tax)+sum(ii.tax2),
> total=sum(ii.amount*ii.quantity)+sum(ii.tax)+sum(ii.tax2)
> From Invoices i, InvoiceItems ii
> Where i.InvoiceID = ii.InvoiceID
> and ii.PaymentID <> null
> and i.Date between @begindate and @enddate
> Group By TaxID
> Order By TaxID
>
>
> With the print out of:
>
> TaxID Tax Tax2 Description
> ----------- ------------------------ ------------------------ ------------

--> ------------------> 1           6.25                     0.0                      Illinois> 2           5.0                      0.0                      Wisconsin> 3           5.0                      0.5                      Walworth Co> Wisconsin> 4           0.0                      0.0                      Exempt> 5           0.0                      0.0                      Resale>> (5 row(s) affected)>>> -------------------------------------------------> Taxes Invoiced Summary for Month of February 2000>> (1 row(s) affected)>> TaxID       beforetax                  taxes                      total> ----------- -------------------------- -------------------------- --------
--> ----------------> (null)      #####.##                    #####.##                #####.##> 1           #####.##                    #####.##                #####.##> 2           #####.##                    #####.##                #####.##> 4           #####.##                    #####.##                #####.##>> (4 row(s) affected)>>> -------------------> Taxes Payed Summary>> (1 row(s) affected)>> TaxID       beforetax                  taxes                      total> ----------- -------------------------- -------------------------- --------
--> ----------------> (null)      #####.##                    #####.##                #####.##> 1           #####.##                    #####.##                #####.##> 2           #####.##                    #####.##                #####.##> 4           #####.##                    #####.##                #####.##>> (4 row(s) affected)>>>>>>>> For more information about this list (including removal) go to:> http://www.iea-software.com/support/maillists/liststart

For more information about this list (including removal) go to:http://www.iea-software.com/support/maillists/liststart