Excel/SQL Query Quandry

[Laphan]

Hi Dick/OneDay

Sorry for the spare info. It wasn't because I didn't want to display it. I
just didn't know how to get the data that you needed. I think I've got it
now so please note the following:

1) The DB is SQL 6.5.

2) My DDL for the tables in question:

/****** Object: Table [dbo].[SALESCENTRES] Script Date: 17/04/04
21:27:00 ******/
CREATE TABLE [dbo].[SALESCENTRES] (
[SALESCENTREID] [T_SALESCENTRESDOMAIN] NOT NULL ,
[NAME] [varchar] (40) NOT NULL ,
[COMMENT] [varchar] (255) NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO

/****** Object: Table [dbo].[SPOOLEDINVOICELINES] Script Date: 17/04/04
21:27:05 ******/
CREATE TABLE [dbo].[SPOOLEDINVOICELINES] (
[SPOOLEDINVOICEID] [T_SpooledInvoicesDomain] NOT NULL ,
[LINE] [int] NOT NULL ,
[STOCKID] [T_STOCKDOMAIN] NULL ,
[DESCRIPTION] [varchar] (255) NULL ,
[FREETEXTLINE] [tinyint] NULL ,
[UNITOFSALE] [varchar] (20) NULL ,
[QUANTITY] [float] NULL ,
[COSTPRICE] [float] NULL ,
[SELLINGPRICE] [float] NULL ,
[PRICELEVELID] [T_PRICELEVELSDOMAIN] NULL ,
[DISCOUNTPERCENT] [float] NULL ,
[NOMINALACCOUNTID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[VATRATEID] [T_VATRATESDOMAIN] NULL ,
[VATPERCENT] [float] NULL ,
[VATAMOUNT] [money] NULL ,
[LINEPRICE] [money] NULL ,
[TimeStamp] [timestamp] NULL
)
GO

/****** Object: Table [dbo].[SPOOLEDINVOICES] Script Date: 17/04/04
21:27:09 ******/
CREATE TABLE [dbo].[SPOOLEDINVOICES] (
[SPOOLEDINVOICEID] [T_SpooledInvoicesDomain] IDENTITY (1, 1) NOT NULL ,
[INVOICETYPE] [smallint] NOT NULL ,
[PEOPLEID] [T_PEOPLEDOMAIN] NOT NULL ,
[ACCOUNTID] [T_ACCOUNTSDOMAIN] NOT NULL ,
[ORDERNUMBER] [varchar] (40) NULL ,
[INVOICENUMBER] [int] NULL ,
[PREFIX] [varchar] (10) NULL ,
[SUFFIX] [varchar] (10) NULL ,
[INVOICEDATE] [datetime] NULL ,
[DATEDUE] [datetime] NULL ,
[PLACEDBY] [varchar] (40) NULL ,
[TELEPHONE] [varchar] (40) NULL ,
[LANGUAGEID] [T_LANGUAGESDOMAIN] NULL ,
[AGENTID] [T_AGENTSDOMAIN] NULL ,
[CUSTOMERTYPEID] [T_CUSTOMERTYPESDOMAIN] NULL ,
[SALESCENTREID] [T_SALESCENTRESDOMAIN] NULL ,
[DEPARTMENTID] [T_DEPARTMENTSDOMAIN] NULL ,
[WAREHOUSEID] [T_WAREHOUSESDOMAIN] NULL ,
[TRADINGTYPE] [smallint] NULL ,
[TRADINGDAYOFMONTH] [smallint] NULL ,
[TRADINGDAYSTIME] [smallint] NULL ,
[DISCOUNTPERCENT] [float] NULL ,
[SETTLEMENTPERCENT] [float] NULL ,
[PayNow] [tinyint] NULL ,
[AmountTendered] [money] NULL ,
[Balance] [money] NULL ,
[DiscountTaken] [money] NULL ,
[MethodOfPayment] [varchar] (40) NULL ,
[PayReference] [varchar] (40) NULL ,
[Authorisation] [varchar] (40) NULL ,
[PRICELEVELID] [T_PRICELEVELSDOMAIN] NULL ,
[TOTALNETT] [money] NULL ,
[TOTALVAT] [money] NULL ,
[TOTALDISCOUNT] [money] NULL ,
[TRADINGTERMS] [varchar] (40) NULL ,
[INVOICEMESSAGES] [varchar] (255) NULL ,
[COMMENT] [varchar] (255) NULL ,
[PLRate] [float] NULL ,
[BSRate] [float] NULL ,
[INVOICECONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[DELIVERYCONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[ORDERCONTACTID] [T_CONTACTDETAILSDOMAIN] NULL ,
[UserID] [T_UsersDomain] NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO

/****** Object: Table [dbo].[STOCK] Script Date: 17/04/04 21:27:13
******/
CREATE TABLE [dbo].[STOCK] (
[STOCKID] [T_STOCKDOMAIN] NOT NULL ,
[NAME] [varchar] (40) NOT NULL ,
[PICTURE] [varchar] (40) NULL ,
[WEIGHT] [float] NULL ,
[VOLUME] [float] NULL ,
[BARCODE] [smallint] NULL ,
[NumberOfPriceBreaks] [smallint] NOT NULL ,
[STOCKCATEGORYID] [T_STOCKCATEGORIESDOMAIN] NULL ,
[SALESNOMINALID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[PURCHASENOMINALID] [T_NOMINALACCOUNTSDOMAIN] NULL ,
[SELLINGCOMMENT] [varchar] (255) NULL ,
[INCLUDESELLINGCOMMENT] [tinyint] NULL ,
[DISPLAYSELLINGCOMMENT] [tinyint] NULL ,
[COSTCOMMENT] [varchar] (255) NULL ,
[DISPLAYCOSTCOMMENT] [tinyint] NULL ,
[PRODUCTTRACKING] [smallint] NULL ,
[ITEMTYPE] [smallint] NULL ,
[VALUATIONPRICE] [float] NOT NULL ,
[INCLUDEINCUSTOMERSTURNOVER] [tinyint] NULL ,
[INCLUDEINAGENTSTURNOVER] [tinyint] NULL ,
[SUPERCEDED] [tinyint] NULL ,
[SUPERCEDEDBY] [T_STOCKDOMAIN] NULL ,
[SUPPLIERID] [T_PEOPLEDOMAIN] NULL ,
[SUPPLIERSTOCKID] [varchar] (40) NULL ,
[SUPPLIERCOMMENT] [varchar] (255) NULL ,
[NEXTSERIALNUMBER] [int] NULL ,
[SERIALNUMBERLENGTH] [smallint] NULL ,
[SERIALNUMBERPREFIX] [varchar] (10) NULL ,
[SERIALNUMBERSUFFIX] [varchar] (10) NULL ,
[SERIALNUMBERPREFIXLENGTH] [smallint] NULL ,
[SERIALNUMBERSUFFIXLENGTH] [smallint] NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO

/****** Object: Table [dbo].[STOCKCATEGORIES] Script Date: 17/04/04
21:27:16 ******/
CREATE TABLE [dbo].[STOCKCATEGORIES] (
[STOCKCATEGORYID] [T_STOCKCATEGORIESDOMAIN] NOT NULL ,
[NAME] [varchar] (40) NOT NULL ,
[COMMENT] [varchar] (255) NULL ,
[TIMESTAMP] [timestamp] NOT NULL
)
GO

ALTER TABLE [dbo].[SALESCENTRES] WITH NOCHECK ADD
CONSTRAINT [pk_salescentres] PRIMARY KEY CLUSTERED
(
[SALESCENTREID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SPOOLEDINVOICELINES] WITH NOCHECK ADD
CONSTRAINT [pk_spooledinvoicelines] PRIMARY KEY CLUSTERED
(
[SPOOLEDINVOICEID],
[LINE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SPOOLEDINVOICES] WITH NOCHECK ADD
CONSTRAINT [pk_spooledinvoices] PRIMARY KEY CLUSTERED
(
[SPOOLEDINVOICEID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[STOCK] WITH NOCHECK ADD
CONSTRAINT [pk_stock] PRIMARY KEY CLUSTERED
(
[STOCKID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[STOCKCATEGORIES] WITH NOCHECK ADD
CONSTRAINT [pk_stockcategories] PRIMARY KEY CLUSTERED
(
[STOCKCATEGORYID]
) ON [PRIMARY]
GO

I know there appears to be a lot of data here, but believe me I am only
wanting to use a small porttion of it.

3) My current query is as follows:

SELECT SALESCENTRES.NAME, STOCKCATEGORIES.NAME,
SPOOLEDINVOICELINES.QUANTITY, SPOOLEDINVOICES.INVOICEDATE
FROM SALESCENTRES, SPOOLEDINVOICELINES, SPOOLEDINVOICES, STOCK,
STOCKCATEGORIES
WHERE SPOOLEDINVOICELINES.SPOOLEDINVOICEID =
SPOOLEDINVOICES.SPOOLEDINVOICEID AND STOCK.STOCKID =
SPOOLEDINVOICELINES.STOCKID AND STOCKCATEGORIES.STOCKCATEGORYID =
STOCK.STOCKCATEGORYID AND SALESCENTRES.SALESCENTREID =
SPOOLEDINVOICES.SALESCENTREID AND ((SPOOLEDINVOICES.INVOICEDATE>{ts
'2004-04-01 00:00:00'}) AND (SPOOLEDINVOICES.INVOICEDATE<{ts '2004-08-01
00:00:00'}))

Where the 2 date values would actually be variable data that the user had
entered.

As I said before when I put the SALESCENTRES.NAME field in the left hand
portion of my Pivot table, the STOCKCATEGORIES.NAME in the top portion of my
Pivot table and the SPOOLEDINVOICELINES.QUANTITY in the data/middle section
of the pivot table I get the kind of result I want (see results below), but
it only retrieves data where I have values for the SALESCENTRES.NAME and the
STOCKCATEGORIES.NAME. What I want is to show all of the SALESCENTRES.NAME
fields (down the left) and all of the STOCKCATEGORIES.NAME fields (across
the top) and if there isn't a SPOOLEDINVOICELINES.QUANTITY value for a
particular cross section then it simply shows a zero. My examples below
should show it better.

My current pivot table shows:

StockCat1 StockCat4
SalesC1 25 3
SalesC2 4 6
Grand Total 29 9

But I want to show:

StockCat1 StockCat2 StockCat3
StockCat4 (etc...)
SalesC1 25 0 0
3
SalesC2 4 0 0
6
SalesC3 0 0 0
0
etc...
Grand Total 29 0 0
9

I'm sure it's down to joins, but I'm at a loss on how to do it.

I hope the above helps and appreciate any time you can give to it.

Rgds

Laphan


Laphan

If you don't, you'll probably only get a theoretical answer along the
lines of, 'You need an OUTER JOIN specifically, and some GROUP BY
clauses generally'.

Along the theoretical lines, you just need to series a bunch of outer joins
using only two tables at a time, like

Query1: tbl_SALESPERSONS outer join tbl_INVOICEHEADER
Query2: tbl_INVOICELINES outer join tbl_STOCK
Query3: Query1 outer join Query2
Query4: tbl_STOCKCAT outer join Query3

Then use Query4 as the source of your pivot table. All this needs to be
done in SQL Server or Access or whatever you're using, not in MSQuery.

 

[onedaywhen]

I've only just seen this. Nice one! I'll take a look at it tomorrow
when I'm at my SQL Server machine. If you see this first, how about
some sample data too?

 

[onedaywhen]

I've been busy on a couple of crises (users, eh?) and have only got as
far as creating your tables locally, putting in some test data and
re-writing your query (retaining all INNER JOINs) in SQL-92 join
syntax. Two reasons for this: 1) it removes the join criteria from the
WHERE clause, making it easier to read; 2) nested *outer* joins are
only supported for SQL-92 syntax. Watch this space (you should also
consider posting in one of the SQL Server ngs). FWIW, here's the
re-write with nested INNER JOINs:

SELECT
SALESCENTRES.NAME,
STOCKCATEGORIES.NAME,
SPOOLEDINVOICELINES.QUANTITY,
SPOOLEDINVOICES.INVOICEDATE FROM
(
(
(
SALESCENTRES INNER JOIN SPOOLEDINVOICES
ON SALESCENTRES.SALESCENTREID =
SPOOLEDINVOICES.SALESCENTREID
)
INNER JOIN SPOOLEDINVOICELINES
ON SPOOLEDINVOICELINES.SPOOLEDINVOICEID =
SPOOLEDINVOICES.SPOOLEDINVOICEID
)
INNER JOIN STOCK
ON STOCK.STOCKID =
SPOOLEDINVOICELINES.STOCKID
)
INNER JOIN STOCKCATEGORIES
ON STOCKCATEGORIES.STOCKCATEGORYID =
STOCK.STOCKCATEGORYID
WHERE
SPOOLEDINVOICES.INVOICEDATE

'01 APR 2004 00:00:00'

AND SPOOLEDINVOICES.INVOICEDATE
< '01 AUG 2004 00:00:00'
;

--

 

[onedaywhen]

Laphan,
You've gone quiet on me. In absence of some meaningful data from you,
this is the best I can do (tested in the MS Query SQL window):

SELECT
SALESCENTRES.NAME,
STOCKCATEGORIES.NAME,
SPOOLEDINVOICELINES.QUANTITY,
SPOOLEDINVOICES.INVOICEDATE FROM
(
(
(
SALESCENTRES LEFT JOIN SPOOLEDINVOICES
ON SALESCENTRES.SALESCENTREID =
SPOOLEDINVOICES.SALESCENTREID
)
LEFT JOIN SPOOLEDINVOICELINES
ON SPOOLEDINVOICELINES.SPOOLEDINVOICEID =
SPOOLEDINVOICES.SPOOLEDINVOICEID
)
LEFT JOIN STOCK
ON STOCK.STOCKID =
SPOOLEDINVOICELINES.STOCKID
)
FULL OUTER JOIN STOCKCATEGORIES
ON STOCKCATEGORIES.STOCKCATEGORYID =
STOCK.STOCKCATEGORYID
WHERE
SPOOLEDINVOICES.INVOICEDATE

'01 APR 2004 00:00:00'

AND SPOOLEDINVOICES.INVOICEDATE
< '01 AUG 2004 00:00:00'
;

--