sql server (alternate): Hi All

Wondered if you could help.

Basically I have 2 tables that contain all the data I want for my report,
but I need to put it in a particular way and I need to display it in an ASP
page so my queries got to be manual rather than an MS Excel/Query 'munge'.

To be honest, the report itself is very basic, so hopefully my ramble will
make sense. I need to report that shows 6 columns at best, 4 columns if
it's easier. Report looks like the following:

Account Code - standard text field
Account Name - standard text field

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

(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. 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 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%

Any ideas??

Rgds

Robbie

AstrA (noemail@noemail.com) writes:
[quoted text, click to view]


This is a complete guessing game, but this may get you started:

SELECT a.accountid, a.name, a.ledger,
turnover = colaesce(ab.turnover, 0), ab.stdev
FRON accounts a
LEFT JOIN (SELECT accountid, turnover = SUM(turnover),
stdev = STDEV(turnover)
FROM accountbudgets ab
GROUP BY accountid, year) ON a.accoundid = b.accountid

If you desire further assistance, here are the standard recommendation
for this kind of inquiries:

o Include CREATE TABLE definitions for your tables.
o Include INSERT statements with sample data.
o Include the desired output from that data.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Hi Erland

Thanks for getting back to me. The breakdown 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 2003 12 500.0000

I want the total sumation 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

Robbie


[quoted text, click to view]


This is a complete guessing game, but this may get you started:

SELECT a.accountid, a.name, a.ledger,
turnover = colaesce(ab.turnover, 0), ab.stdev
FRON accounts a
LEFT JOIN (SELECT accountid, turnover = SUM(turnover),
stdev = STDEV(turnover)
FROM accountbudgets ab
GROUP BY accountid, year) ON a.accoundid = b.accountid

If you desire further assistance, here are the standard recommendation
for this kind of inquiries:

o Include CREATE TABLE definitions for your tables.
o Include INSERT statements with sample data.
o Include the desired output from that data.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Laphan (news@DoNotEmailMe.co.uk) writes:
[quoted text, click to view]

Thanks for tables and the sample data, but:

1) The table definitions refers to user-defined types that are
not installed.
2) The sample data is not in form of INSERT statements.
3) You mention a requirement of a ledger 1, but there is no appearance
of any ledger at all in the sample data, nor the output, so I can't
verify that I am using it correctly.
4) The sample output does not match with with the sample data. The
fact that the names and ids are different are one thing, but the
sample data only has data for 2003, so I can't see where the data
for 2002 comes in. And I cannot really make any connection from
the values in the sample data to the values in the sample output.

Also, the sample output is difficult to read. I don't know if you
composed the message with a proportional font, of you if you used
tab characters that were mangled. The tables for the sample input
were OK with regards to format. (But they should have been INSERT
statements.)

It's not that I want to slam you, but you are the one who is having a
problem and want help, so it seems fair if you on your part to do
the dirty work. And while correct sample data with coherent input
and output may take some to compose, this is is data that you will
have use for in future tests of your development.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at