------=_NextPart_000_01A7_01BEC945.BB005480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
From: Webmaster <webmaster@elpasopaging.com>
>i am looking for a report that would tell you the sales done for that month
>and if possible the taxes on them
>so that we can complete our sales tax forms
This depends on how your bookkeeper/accountant defines a sale. Our
bookkeeper, accountant, general manager, and I all had differing oppinions
about how to correctly define this and the accountant has changed his own
views several times in the past about it. In other words, find out from
your bookkeeper/accountant the most precise definition possible for what
he/she wants to see at the end of every month. The one I use may not be
suitable for you.
We recently upgraded from Emerald 2.1 to 2.5, so I had to rewrite all of my
accounting scripts (I never got a chance to convert them into Crystal
Reports layout). None of them include taxes, because we don't use them, but
you could probably add them to the scripts without too much effort.
Anyway, the script I attached to this message is what I wrote for displaying
sales (via ISQL/w). Because we have two special billing groups called
"Trade" and "Charity" that need to be "separated" from all other normal
paying billing groups, I had to make exceptions for them in the script. In
your case, you may need to simply remove the sections that deal with them.
Josh Hillman
hillman@talstar.com
------=_NextPart_000_01A7_01BEC945.BB005480
Content-Type: application/octet-stream;
name="Monthly Sales.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="Monthly Sales.sql"
/* Monthly Sales (Emerald 2.5) */
/* Lists monthly sales/credits with totals for all billing groups */
SET NOCOUNT ON
DECLARE @GroupId int, @BeginMonth datetime
SELECT @BeginMonth =3D '6/1/1999'
SELECT "Monthly Sales"=3D(datename(month, @BeginMonth)+', =
'+datename(year, @BeginMonth)),
"Date Printed"=3DGetDate()
PRINT ''
DECLARE BillingGroup_cursor CURSOR
FOR SELECT GroupID
FROM Groups
ORDER BY SortOrder
OPEN BillingGroup_cursor
FETCH NEXT FROM BillingGroup_cursor INTO @GroupID
WHILE (@@fetch_status <> -1)
BEGIN /* beginning of main loop */
/* Don't display empty listings for the current billing group */
IF EXISTS (SELECT ma.GroupID
FROM MasterAccounts ma, Invoices i
WHERE ma.CustomerID =3D i.CustomerID
AND ma.GroupID =3D @GroupID
AND DATEDIFF(Month, i.Date, @BeginMonth) =3D 0)
BEGIN /* Display detailed listings */
SET NOCOUNT ON
SELECT ''=3DGroupName
FROM Groups
WHERE GroupID =3D @GroupID
SET NOCOUNT OFF
SELECT i.InvoiceID,
Name =3D (ma.FirstName + ' ' + ma.LastName),
ma.Company,
CreateDate =3D convert(char(10), i.Date, 101),
i.PreviousBalance,
"Sale this month"=3D(i.Amount - i.PreviousBalance),
"Invoice Total" =3D i.Amount,
i.Type
FROM MasterAccounts ma, Invoices i
WHERE ma.CustomerID =3D i.CustomerID
AND ma.GroupID =3D @GroupID
AND DATEDIFF(Month, i.Date, @BeginMonth) =3D 0
Order By i.InvoiceID
SET NOCOUNT ON
END /* end of detailed listings */
FETCH NEXT FROM BillingGroup_cursor INTO @GroupID
END /* end of main loop */
CLOSE BillingGroup_cursor
DEALLOCATE BillingGroup_cursor
/* TOTALS */
/* Sales */
SELECT "Non-Voided Sales"=3Dg.GroupName, i.Type, Total=3Dsum(i.Amount - =
i.PreviousBalance)
FROM MasterAccounts ma, Invoices i, Groups g
WHERE ma.CustomerID =3D i.CustomerID
AND DATEDIFF(Month, i.Date, @BeginMonth) =3D 0
AND ma.GroupID =3D g.GroupID
AND i.Type !=3D 'Void'
AND (g.GroupName NOT LIKE '%Trade%' AND g.GroupName NOT LIKE =
'%Charity%')
AND (i.Amount - i.PreviousBalance) > 0
GROUP BY g.GroupName, i.Type
ORDER BY g.GroupName, i.Type
Compute Sum(sum(i.Amount - i.PreviousBalance))
/* Voided Sales */
SELECT "Voided Sales"=3Dg.GroupName, i.Type, Total=3Dsum(i.Amount - =
i.PreviousBalance)
FROM MasterAccounts ma, Invoices i, Groups g
WHERE ma.CustomerID =3D i.CustomerID
AND DATEDIFF(Month, i.Date, @BeginMonth) =3D 0
AND ma.GroupID =3D g.GroupID
AND i.Type =3D 'Void'
AND (g.GroupName NOT LIKE '%Trade%' AND g.GroupName NOT LIKE =
'%Charity%')
AND (i.Amount - i.PreviousBalance) > 0
GROUP BY g.GroupName, i.Type
ORDER BY g.GroupName, i.Type
Compute Sum(sum(i.Amount - i.PreviousBalance))
/* Credits */
SELECT "Non-Voided Credits"=3Dg.GroupName, i.Type, Total=3Dsum(i.Amount =
- i.PreviousBalance)
FROM MasterAccounts ma, Invoices i, Groups g
WHERE ma.CustomerID =3D i.CustomerID
AND DATEDIFF(Month, i.Date, @BeginMonth) =3D 0
AND ma.GroupID =3D g.GroupID
AND i.Type !=3D 'Void'
AND (g.GroupName NOT LIKE '%Trade%' AND g.GroupName NOT LIKE =
'%Charity%')
AND (i.Amount - i.PreviousBalance) < 0
GROUP BY g.GroupName, i.Type
ORDER BY g.GroupName, i.Type
Compute Sum(sum(i.Amount - i.PreviousBalance))
/* Voided Credits */
SELECT "Voided Credits"=3Dg.GroupName, i.Type, Total=3Dsum(i.Amount - =
i.PreviousBalance)
FROM MasterAccounts ma, Invoices i, Groups g
WHERE ma.CustomerID =3D i.CustomerID
AND DATEDIFF(Month, i.Date, @BeginMonth) =3D 0
AND ma.GroupID =3D g.GroupID
AND i.Type =3D 'Void'
AND (g.GroupName NOT LIKE '%Trade%' AND g.GroupName NOT LIKE =
'%Charity%')
AND (i.Amount - i.PreviousBalance) < 0
GROUP BY g.GroupName, i.Type
ORDER BY g.GroupName, i.Type
Compute Sum(sum(i.Amount - i.PreviousBalance))
/* Non-Voided Trade / Charity */
SELECT "Non-Voided Trade/Charity"=3Dg.GroupName, i.Type, =
Total=3Dsum(i.Amount - i.PreviousBalance)
FROM MasterAccounts ma, Invoices i, Groups g
WHERE ma.CustomerID =3D i.CustomerID
AND DATEDIFF(Month, i.Date, @BeginMonth) =3D 0
AND ma.GroupID =3D g.GroupID
AND i.Type !=3D 'Void'
AND (g.GroupName LIKE '%Trade%' OR g.GroupName LIKE '%Charity%')
GROUP BY g.GroupName, i.Type
ORDER BY g.GroupName, i.Type
Compute Sum(sum(i.Amount - i.PreviousBalance))
/* Voided Trade / Charity */
SELECT "Voided Trade/Charity"=3Dg.GroupName, i.Type, =
Total=3Dsum(i.Amount - i.PreviousBalance)
FROM MasterAccounts ma, Invoices i, Groups g
WHERE ma.CustomerID =3D i.CustomerID
AND DATEDIFF(Month, i.Date, @BeginMonth) =3D 0
AND ma.GroupID =3D g.GroupID
AND i.Type =3D 'Void'
AND (g.GroupName LIKE '%Trade%' OR g.GroupName LIKE '%Charity%')
GROUP BY g.GroupName, i.Type
ORDER BY g.GroupName, i.Type
Compute Sum(sum(i.Amount - i.PreviousBalance))
------=_NextPart_000_01A7_01BEC945.BB005480--