Simple but complex report - ping Bob/Aaron

[Laphan]

Dear Bob

Sorry for the delay in getting back to you. My PC has been out of action
this festive period due to a nagging wife!!!

In answer to your 1st few queries, the DB version is SQL Server 6.5.

A breakdown of my tables is as follows:

TABLE 1 - ACCOUNTS

CREATE TABLE [dbo].[ACCOUNTS] (
[ACCOUNTID] [T_ACCOUNTSDOMAIN] NOT NULL ,
[NAME] [varchar] (40) NOT NULL ,
[COMMENT] [varchar] (255) NULL ,
[PEOPLEID] [T_PEOPLEDOMAIN] NULL ,
[LEDGER] [smallint] NULL ,
[Status] [smallint] NOT NULL ,
[CURRENCYID] [T_CURRENCIESDOMAIN] NULL ,
[FIXEDVATRATE] [tinyint] NOT NULL ,
[VATRATEID] [T_VATRATESDOMAIN] NULL ,
[OURACCOUNTID] [varchar] (40) NULL ,
[AGENTID] [T_AGENTSDOMAIN] NULL ,
[SALESCENTREID] [T_SALESCENTRESDOMAIN] NULL ,
[COSTCENTREID] [T_COSTCENTRESDOMAIN] NULL ,
[DEPARTMENTID] [T_DEPARTMENTSDOMAIN] NULL ,
[WAREHOUSEID] [T_WAREHOUSESDOMAIN] NULL ,
[DELETETRANSACTIONS] [tinyint] NOT NULL ,
[BALANCE] [money] NOT NULL ,
[TURNOVER] [money] NOT NULL ,
[CREDITLIMIT] [money] NOT NULL ,
[REMITTANCECONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[STATEMENTCONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[InvoiceContactID] [T_CONTACTDETAILSDOMAIN] NULL ,
[DeliveryContactID] [T_CONTACTDETAILSDOMAIN] NULL ,
[OrdersContactID] [T_CONTACTDETAILSDOMAIN] NULL ,
[TRADINGTERMS] [varchar] (100) NULL ,
[SETTLEMENTPERCENT] [float] NULL ,
[TRADINGTYPE] [smallint] NULL ,
[TRADINGDAYOFMONTH] [smallint] NULL ,
[TRADINGDAYSTIME] [smallint] NULL ,
[USETRADINGTERMS] [tinyint] NOT NULL ,
[PRICELEVELID] [T_PRICELEVELSDOMAIN] NULL ,
[DISCOUNTPERCENT] [float] NULL ,
[LASTINVOICEDATE] [datetime] NULL ,
[LASTPAYMENTDATE] [datetime] NULL ,
[PRINTSTATEMENTS] [tinyint] NOT NULL ,
[PRINTRUNNINGBALANCES] [tinyint] NOT NULL ,
[BANKNAME] [varchar] (40) NULL ,
[BANKADDRESSLINE1] [varchar] (40) NULL ,
[BANKADDRESSLINE2] [varchar] (40) NULL ,
[BANKADDRESSLINE3] [varchar] (40) NULL ,
[BANKTOWN] [varchar] (40) NULL ,
[BANKCOUNTY] [varchar] (40) NULL ,
[BANKCOUNTRY] [varchar] (40) NULL ,
[BANKPOSTCODE] [varchar] (40) NULL ,
[BANKTITLE] [varchar] (40) NULL ,
[BANKFIRSTNAME] [varchar] (40) NULL ,
[BANKSURNAME] [varchar] (40) NULL ,
[BANKPOSITION] [varchar] (40) NULL ,
[BANKTELEPHONE] [varchar] (40) NULL ,
[BANKTELEX] [varchar] (40) NULL ,
[BANKFAX] [varchar] (40) NULL ,
[BANKEMAIL] [varchar] (40) NULL ,
[BANKSORTCODE] [varchar] (40) NULL ,
[BANKACCOUNTID] [varchar] (40) NULL ,
[BANKACCOUNTNAME] [varchar] (40) NULL ,
[BANKACCOUNTSWIFT] [varchar] (40) NULL ,
[SPOOLLOCK] [tinyint] NOT NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO

Sample data from this is as follows:

accountid name
---------------------------------------- -----------------------------------
-----
G01 German Customer
NL01 Netherlands Customer
UK01 UK Customer

I only want the Account ID and Name from this table where the Ledger = 1


TABLE 2 - ACCOUNTSBUDGET

CREATE TABLE [dbo].[ACCOUNTSBUDGET] (
[ACCOUNTID] [T_ACCOUNTSDOMAIN] NOT NULL ,
[DEPARTMENTID] [T_DEPARTMENTSDOMAIN] NOT NULL ,
[YEAR] [smallint] NOT NULL ,
[PERIOD] [smallint] NOT NULL ,
[TURNOVER] [money] NOT NULL ,
[TURNOVERBUDGET] [money] NOT NULL ,
[PAYMENTS] [money] NOT NULL ,
[PAYMENTSBUDGET] [money] NOT NULL ,
[PROFIT] [money] NOT NULL ,
[PROFITBUDGET] [money] NOT NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO

Sample data from this is as follows:

accountid year period turnover
---------------------------------------- ------ ------ ---------------------
UK01 2003 3 200.0000
UK01 2003 6 300.0000
UK01 2002 5 0.0000
UK01 2002 1 0.0000
UK01 2002 9 356.2300
UK01 2003 12 500.0000
UK01 2002 12 120.0000

I want the total summation from period 1 to whatever the current month is
(eg
it is 12 at the moment) for last year (ie, currently 2002) and this year
(ie, currently 2003) for each account in the ACCOUNTS table. The reason for
this is that there might not be entries for certain accounts in the
ACCOUNTSBUDGET table, as it is a sort of line-by-line storer when a
transaction is placed for a particular period (ie, month) and year.


RESULTS

The results I want are as follows:

December Turnover YTD Deviation
Acc No: Account Name 2003 2002 £ %
A001 Alpha Motor 9,250 5,398 3,852 71%
etc etc etc etc etc
etc

I hope this defines it better, but please ask me if you need anymore.

Rgds

Laphan


Bob Barrows <reb01501@NOyahoo.SPAMcom> wrote in message
news:#nugs0tzDHA.1668@TK2MSFTNGP10.phx.gbl...
AstrA wrote:

> Hi All
>
> Wondered if you could help.
>
> Basically I have 2 tables that contain all the data I want for my

What database? Version as well, please.

>
> Account Code - standard text field
> Account Name - standard text field
>
> Turnover (Year to Date) 2002 - standard decimal number (single??)

You're asking me?

> field Turnover (Year to Date) 2003 - standard decimal number
> (single??) field

Again, you're asking me?

>
> (not vital, but would be nice)
> Deviation as a straight figure between what the 2003 turnover figure
> is against the 2002 figure - ad hoc calc field
> Deviation as a % between what the 2003 turnover figure is against the
> 2002 figure - ad hoc calc field
>
> My table structure is as follows:
>
> TABLE 1 - ACCOUNTS
>
> This contains all of the account codes (PK) and names as ACCOUNTID
> and NAME respectively.

"Name" is a reserved keyword and should not be used for
table/column/parameter/variable names. See here for a list of reserved
keywords: [url]http://www.aspfaq.com/show.asp?id=2080[/url]

AcctName would probably be a better name for this column.

> It also contains a field called LEDGER which I would need to use in a
> Where clause to set this to 1 (1 = sales and 2 = purchases).
>
> TABLE 2 - ACCOUNTBUDGETS
>
> This contains the Turnover details and is linked to the ACCOUNTS
> table by an ACCOUNTID (FK??). This table contains a TURNOVER field
> for the actual turnover amount, a PERIOD field for the months of a
> year (eg, 1 - 12 - that being Jan to Dec) and a YEAR field for the
> relevant years.
>
> In this ACCOUNTBUDGETS table it basically keeps a line-by-line record
> of each turnover for each period and year for each account code. As
> you probably would with any transaction line storing table.
>
> An important part of this is that I want to show all of the Account
> codes and names that relate to LEDGER=1 even if they have no entries
> in the ACCOUNTSBUDGETS table. The reason being is that I want to be
> able to show that an account might not have traded for 2002 and
> therefore their 2003 Year to date might be really good cos they've
> sold loads in 2003.
>
> So the final part is that I need summarise the Year to date figures
> next to each account code and name. To do this I need to take what
> the month number is now (eg, Dec = 12) and summarise all the turnover

So you always want to report on the current month and year? You don't want
the users to specify the month/year?

> figures for 2002 upto 12 and then all the turnover figures for 2003
> upto 12. If you can imagine when this report is run in March the
> report would summarize from 1 to 3 rather than 1 - 12 in the last
> examples.
>
> The nice bits on the end would be that they would give 2 different
> calcs against what the 2 turnover figures stated.
>
> The result should be something like the following:
>
> December Turnover YTD
> Deviation
> Acc No: Account Name 2003 2002 £
> %
> A001 Alpha Motor 9,250 5,398
> 3,852 71%
> A003 A J V Secure 0
> 94 -1,294 -100%
> A004 Aye Gee 0
> 39 -4,239 -100%
> A007 P H Allin 2,235
> 08 -1,973 -47%
>

You will need to use a group-by query utilizing an inner join between the
two tables. I cannot get specific until I know the type and version of
database you are using.

Also, if you could explain further exactly where you need assistance
(creating the query? obtaining the results? formatiing the results?) it
would help.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows]

Patience!!
I already replied in the original thread. There was no need to start a new
thread!

We do have lives ourselves, you know! It may look like we're here 24/7, but
.... never mind.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Brynn]

Come on ... programmers don't have lives silly ... that is why we look
to these boxes for companionship .. LOL


On Mon, 5 Jan 2004 07:29:09 -0500, "Bob Barrows"
<reb01501@NOyahoo.SPAMcom> wrote:

>Patience!!
>I already replied in the original thread. There was no need to start a new
>thread!
>
>We do have lives ourselves, you know! It may look like we're here 24/7, but
>... never mind.
>
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my spam trap so I
>don't check it very often. If you must reply off-line, then remove the
>"NO SPAM"
>
>

[Roland Hall]

"Brynn" wrote:
: Come on ... programmers don't have lives silly ... that is why we look
: to these boxes for companionship .. LOL

Well the cat's out of the bag now. Now everyone's gonna' know I'm a NG
junkie rather than cyberin' in chat and downloadin' porn all day like I told
'em.

[Laphan]

Sorry Bob

Didn't mean to be disrespectful.

My ISP is very strict on Newsgroup postings and chops them after only a few
days. I didn't want to lose the thread so I re-posted. I wasn't meaning to
be impatient. I really appreciate your help on this and other bits that you
have done for me.

Forgiven??

Rgds

Laphan



Bob Barrows <reb01501@NOyahoo.SPAMcom> wrote in message
news:#JFfCe40DHA.1752@tk2msftngp13.phx.gbl...
Patience!!
I already replied in the original thread. There was no need to start a new
thread!

We do have lives ourselves, you know! It may look like we're here 24/7, but
.... never mind.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Ray at]

You can bypass your ISP's news server for the MS newsgroups and connect
directly to MS's news server (while still keeping your current usenet
account with your ISP). Tools--Accounts--Add..... The name of MS's news
server can be msnews.microsoft.com. I assume that OE5 supports multiple
usenet groups like OE6.

Ray at work

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3ffb2d28_3@127.0.0.1...

>
> My ISP is very strict on Newsgroup postings and chops them after only a

few

> days.

[Laphan]

Many thanks for the info Ray

Rgds Laphan

Ray at <%=sLocation%> <myfirstname at lane34 dot com> wrote in message
news:O6rH1$J1DHA.2972@TK2MSFTNGP09.phx.gbl...
You can bypass your ISP's news server for the MS newsgroups and connect
directly to MS's news server (while still keeping your current usenet
account with your ISP). Tools--Accounts--Add..... The name of MS's news
server can be msnews.microsoft.com. I assume that OE5 supports multiple
usenet groups like OE6.

Ray at work

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:3ffb2d28_3@127.0.0.1...

>
> My ISP is very strict on Newsgroup postings and chops them after only a

few

> days.

[Brynn]

ROFL :)

On Tue, 6 Jan 2004 01:18:59 -0600, "Roland Hall" <nobody@nowhere>
wrote:

>"Brynn" wrote:
>: Come on ... programmers don't have lives silly ... that is why we look
>: to these boxes for companionship .. LOL
>
>Well the cat's out of the bag now. Now everyone's gonna' know I'm a NG
>junkie rather than cyberin' in chat and downloadin' porn all day like I told
>'em.
>
>