SQL Query Quandry

[Laphan]

Hi All

I know I've got to post my schema, etc, but I think its more of a logic
thing than table-specific.

If you can imagine how an Excel pivot table would display it, I want all of
my sales people listed down the left hand side of my report, all my stock
categories listed along the top of my report and then for each value that
these 'axis's cover I want the sum of my qty sold field.

Getting this on an ASP page is my problem, but if I can get the desired
result in an Excel pivot table then I will be happy to continue the rest.

My basic 4 data fields are SALESPERSON, STOCKCAT, QTYSOLD and DATESOLD (the
last one being because I want to offer the user the ability to enter a date
range and this is what it will come from.

I have created a query that shows me the exact data that I want, but only
where there is a QTYSOLD for a SALESPERSON and STOCKCAT.

My actual problem is that I want to show all of the SALESPERSONs and
STOCKCATs available and if some don't have a value for the above then they
show a value of 0.00.

I've really been trying since you last helped me and I think the reason why
the above isn't working is because I need to perform a different kind of
JOIN so that I can get all of the SALESPERSONs and STOCKCATs from the
relevant tables.

The only problem with this is that my tables are as follows and when I do a
JOIN in MS Query it says it can't be done on more than 2 tables:


tbl_SALESPERSONS contains SALESPERSONID and SALESPERSONNAME (I need all of
the SALESPERSONNAME)

which is linked to the SALESPERSONID in

tbl_INVOICEHEADER contains SALESPERSONID, INVOICEID

which is linked to the INVOICEID in

tbl_INVOICELINES contains INVOICEID, STOCKID, QTYSOLD, DATESOLD (I need
QTYSOLD and DATESOLD)

which is linked to the STOCKID in

tbl_STOCK contains STOCKID, STOCKCATID

which is linked to the STOCKCATID in

tbl_STOCKCAT contains STOCKCATID, STOCKCATNAME (I need all of the
STOCKCATNAME)


How can I do this when I need the above to link of it all together??

Your help (as always) would be appreciated.

Rgds

Laphan

[Chris Hohmann]

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:OZbef0yIEHA.2256@TK2MSFTNGP10.phx.gbl...

> Hi All
>
> I know I've got to post my schema, etc, but I think its more of a

logic

> thing than table-specific.
>
> If you can imagine how an Excel pivot table would display it, I want

all of

> my sales people listed down the left hand side of my report, all my

stock

> categories listed along the top of my report and then for each value

that

> these 'axis's cover I want the sum of my qty sold field.
>
> Getting this on an ASP page is my problem, but if I can get the

desired

> result in an Excel pivot table then I will be happy to continue the

rest.

>
> My basic 4 data fields are SALESPERSON, STOCKCAT, QTYSOLD and DATESOLD

(the

> last one being because I want to offer the user the ability to enter a

date

> range and this is what it will come from.
>
> I have created a query that shows me the exact data that I want, but

only

> where there is a QTYSOLD for a SALESPERSON and STOCKCAT.
>
> My actual problem is that I want to show all of the SALESPERSONs and
> STOCKCATs available and if some don't have a value for the above then

they

> show a value of 0.00.
>
> I've really been trying since you last helped me and I think the

reason why

> the above isn't working is because I need to perform a different kind

of

> JOIN so that I can get all of the SALESPERSONs and STOCKCATs from the
> relevant tables.
>
> The only problem with this is that my tables are as follows and when I

do a

> JOIN in MS Query it says it can't be done on more than 2 tables:
>
>
> tbl_SALESPERSONS contains SALESPERSONID and SALESPERSONNAME (I need

all of

> the SALESPERSONNAME)
>
> which is linked to the SALESPERSONID in
>
> tbl_INVOICEHEADER contains SALESPERSONID, INVOICEID
>
> which is linked to the INVOICEID in
>
> tbl_INVOICELINES contains INVOICEID, STOCKID, QTYSOLD, DATESOLD (I

need

> QTYSOLD and DATESOLD)
>
> which is linked to the STOCKID in
>
> tbl_STOCK contains STOCKID, STOCKCATID
>
> which is linked to the STOCKCATID in
>
> tbl_STOCKCAT contains STOCKCATID, STOCKCATNAME (I need all of the
> STOCKCATNAME)
>
>
> How can I do this when I need the above to link of it all together??

Since you didn't/wouldn't provide database/version/schema/sample data
all I can provide is pseudo code. Do a cross-tab on the following query:

SELECT
SP.SALESPERSONNAME,
SC.STOCKCATNAME,
IL.QTYSOLD
FROM
tbl_SALESPERSONS AS SP,
tbl_INVOICEHEADER AS IH,
tbl_INVOICELINES AS IL,
tbl_STOCK AS S,
tbl_STOCKCAT AS SC
WHERE
SP.SALESPERSONID = IH.SALESPERSONID AND
IH.INVOICEID = IL.INVOICEID AND
IL.STOCKID = S.STOCKID AND
S.STOCKCATID = SC.STOCKCATID AND
IL.DATESOLD >= prmDATESOLD_GTE AND
IL.DATESOLD < prmDATESOLD_LT

UNION ALL

SELECT
SP.SALESPERSONNAME,
SC.STOCKCATNAME,
0
FROM
tbl_SALESPERSONS AS SP,
tbl_STOCKCAT AS SC

HTH
-Chris Hohmann

[Laphan]

Hi Chris

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


Chris Hohmann <nospam@thankyou.com> wrote in message
news:uT8oDuzIEHA.3588@TK2MSFTNGP10.phx.gbl...
"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:OZbef0yIEHA.2256@TK2MSFTNGP10.phx.gbl...

> Hi All
>
> I know I've got to post my schema, etc, but I think its more of a

logic

> thing than table-specific.
>
> If you can imagine how an Excel pivot table would display it, I want

all of

> my sales people listed down the left hand side of my report, all my

stock

> categories listed along the top of my report and then for each value

that

> these 'axis's cover I want the sum of my qty sold field.
>
> Getting this on an ASP page is my problem, but if I can get the

desired

> result in an Excel pivot table then I will be happy to continue the

rest.

>
> My basic 4 data fields are SALESPERSON, STOCKCAT, QTYSOLD and DATESOLD

(the

> last one being because I want to offer the user the ability to enter a

date

> range and this is what it will come from.
>
> I have created a query that shows me the exact data that I want, but

only

> where there is a QTYSOLD for a SALESPERSON and STOCKCAT.
>
> My actual problem is that I want to show all of the SALESPERSONs and
> STOCKCATs available and if some don't have a value for the above then

they

> show a value of 0.00.
>
> I've really been trying since you last helped me and I think the

reason why

> the above isn't working is because I need to perform a different kind

of

> JOIN so that I can get all of the SALESPERSONs and STOCKCATs from the
> relevant tables.
>
> The only problem with this is that my tables are as follows and when I

do a

> JOIN in MS Query it says it can't be done on more than 2 tables:
>
>
> tbl_SALESPERSONS contains SALESPERSONID and SALESPERSONNAME (I need

all of

> the SALESPERSONNAME)
>
> which is linked to the SALESPERSONID in
>
> tbl_INVOICEHEADER contains SALESPERSONID, INVOICEID
>
> which is linked to the INVOICEID in
>
> tbl_INVOICELINES contains INVOICEID, STOCKID, QTYSOLD, DATESOLD (I

need

> QTYSOLD and DATESOLD)
>
> which is linked to the STOCKID in
>
> tbl_STOCK contains STOCKID, STOCKCATID
>
> which is linked to the STOCKCATID in
>
> tbl_STOCKCAT contains STOCKCATID, STOCKCATNAME (I need all of the
> STOCKCATNAME)
>
>
> How can I do this when I need the above to link of it all together??

Since you didn't/wouldn't provide database/version/schema/sample data
all I can provide is pseudo code. Do a cross-tab on the following query:

SELECT
SP.SALESPERSONNAME,
SC.STOCKCATNAME,
IL.QTYSOLD
FROM
tbl_SALESPERSONS AS SP,
tbl_INVOICEHEADER AS IH,
tbl_INVOICELINES AS IL,
tbl_STOCK AS S,
tbl_STOCKCAT AS SC
WHERE
SP.SALESPERSONID = IH.SALESPERSONID AND
IH.INVOICEID = IL.INVOICEID AND
IL.STOCKID = S.STOCKID AND
S.STOCKCATID = SC.STOCKCATID AND
IL.DATESOLD >= prmDATESOLD_GTE AND
IL.DATESOLD < prmDATESOLD_LT

UNION ALL

SELECT
SP.SALESPERSONNAME,
SC.STOCKCATNAME,
0
FROM
tbl_SALESPERSONS AS SP,
tbl_STOCKCAT AS SC

HTH
-Chris Hohmann

[Laphan]

I know I've talked about putting this in a pivot table, but is there anyway
of doing this kind of report without a pivot table?

All I want is the stock cats across the top, the sales centres down the left
and then if the 'cross-sectioning' means that a summed qty was valid for
that stock cat against that sales centre then display it else show 0.00?

It looks like all the pivot table would do once my SQL was right would be to
sum the qtys that's it. Is that right?? I'd love toi create this sort of
report in ASP, but it seems impossible.

Thanks

Laphan


Laphan <news@DoNotEmailMe.co.uk> wrote in message
news:40819634_3@127.0.0.1...
Hi Chris

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


Chris Hohmann <nospam@thankyou.com> wrote in message
news:uT8oDuzIEHA.3588@TK2MSFTNGP10.phx.gbl...
"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:OZbef0yIEHA.2256@TK2MSFTNGP10.phx.gbl...

> Hi All
>
> I know I've got to post my schema, etc, but I think its more of a

logic

> thing than table-specific.
>
> If you can imagine how an Excel pivot table would display it, I want

all of

> my sales people listed down the left hand side of my report, all my

stock

> categories listed along the top of my report and then for each value

that

> these 'axis's cover I want the sum of my qty sold field.
>
> Getting this on an ASP page is my problem, but if I can get the

desired

> result in an Excel pivot table then I will be happy to continue the

rest.

>
> My basic 4 data fields are SALESPERSON, STOCKCAT, QTYSOLD and DATESOLD

(the

> last one being because I want to offer the user the ability to enter a

date

> range and this is what it will come from.
>
> I have created a query that shows me the exact data that I want, but

only

> where there is a QTYSOLD for a SALESPERSON and STOCKCAT.
>
> My actual problem is that I want to show all of the SALESPERSONs and
> STOCKCATs available and if some don't have a value for the above then

they

> show a value of 0.00.
>
> I've really been trying since you last helped me and I think the

reason why

> the above isn't working is because I need to perform a different kind

of

> JOIN so that I can get all of the SALESPERSONs and STOCKCATs from the
> relevant tables.
>
> The only problem with this is that my tables are as follows and when I

do a

> JOIN in MS Query it says it can't be done on more than 2 tables:
>
>
> tbl_SALESPERSONS contains SALESPERSONID and SALESPERSONNAME (I need

all of

> the SALESPERSONNAME)
>
> which is linked to the SALESPERSONID in
>
> tbl_INVOICEHEADER contains SALESPERSONID, INVOICEID
>
> which is linked to the INVOICEID in
>
> tbl_INVOICELINES contains INVOICEID, STOCKID, QTYSOLD, DATESOLD (I

need

> QTYSOLD and DATESOLD)
>
> which is linked to the STOCKID in
>
> tbl_STOCK contains STOCKID, STOCKCATID
>
> which is linked to the STOCKCATID in
>
> tbl_STOCKCAT contains STOCKCATID, STOCKCATNAME (I need all of the
> STOCKCATNAME)
>
>
> How can I do this when I need the above to link of it all together??

Since you didn't/wouldn't provide database/version/schema/sample data
all I can provide is pseudo code. Do a cross-tab on the following query:

SELECT
SP.SALESPERSONNAME,
SC.STOCKCATNAME,
IL.QTYSOLD
FROM
tbl_SALESPERSONS AS SP,
tbl_INVOICEHEADER AS IH,
tbl_INVOICELINES AS IL,
tbl_STOCK AS S,
tbl_STOCKCAT AS SC
WHERE
SP.SALESPERSONID = IH.SALESPERSONID AND
IH.INVOICEID = IL.INVOICEID AND
IL.STOCKID = S.STOCKID AND
S.STOCKCATID = SC.STOCKCATID AND
IL.DATESOLD >= prmDATESOLD_GTE AND
IL.DATESOLD < prmDATESOLD_LT

UNION ALL

SELECT
SP.SALESPERSONNAME,
SC.STOCKCATNAME,
0
FROM
tbl_SALESPERSONS AS SP,
tbl_STOCKCAT AS SC

HTH
-Chris Hohmann

[Laphan]

Dear Chris

I don't know how the hell you could make something out of my ramblings and I
did doubt it when I put it through Query Analyser, but it is absolutely spot
on when put through a Pivot Table!!!

This union thing is completely different to what I expected.

How the hell do you guys do it???

I've got 2 SQL books (1 called Sams 24 hour SQL and the other 'the Practical
SQL handbook') and neither gives me any clue on how to do the things that
you guys know. Its so frustrating!!

Do you know of a place/site that will explain this sort of thing to me in a
'I'm thick - help me' sort of way??

Many thanks all the same.

Rgds

Laphan

Chris Hohmann <nospam@thankyou.com> wrote in message
news:uT8oDuzIEHA.3588@TK2MSFTNGP10.phx.gbl...
"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:OZbef0yIEHA.2256@TK2MSFTNGP10.phx.gbl...

> Hi All
>
> I know I've got to post my schema, etc, but I think its more of a

logic

> thing than table-specific.
>
> If you can imagine how an Excel pivot table would display it, I want

all of

> my sales people listed down the left hand side of my report, all my

stock

> categories listed along the top of my report and then for each value

that

> these 'axis's cover I want the sum of my qty sold field.
>
> Getting this on an ASP page is my problem, but if I can get the

desired

> result in an Excel pivot table then I will be happy to continue the

rest.

>
> My basic 4 data fields are SALESPERSON, STOCKCAT, QTYSOLD and DATESOLD

(the

> last one being because I want to offer the user the ability to enter a

date

> range and this is what it will come from.
>
> I have created a query that shows me the exact data that I want, but

only

> where there is a QTYSOLD for a SALESPERSON and STOCKCAT.
>
> My actual problem is that I want to show all of the SALESPERSONs and
> STOCKCATs available and if some don't have a value for the above then

they

> show a value of 0.00.
>
> I've really been trying since you last helped me and I think the

reason why

> the above isn't working is because I need to perform a different kind

of

> JOIN so that I can get all of the SALESPERSONs and STOCKCATs from the
> relevant tables.
>
> The only problem with this is that my tables are as follows and when I

do a

> JOIN in MS Query it says it can't be done on more than 2 tables:
>
>
> tbl_SALESPERSONS contains SALESPERSONID and SALESPERSONNAME (I need

all of

> the SALESPERSONNAME)
>
> which is linked to the SALESPERSONID in
>
> tbl_INVOICEHEADER contains SALESPERSONID, INVOICEID
>
> which is linked to the INVOICEID in
>
> tbl_INVOICELINES contains INVOICEID, STOCKID, QTYSOLD, DATESOLD (I

need

> QTYSOLD and DATESOLD)
>
> which is linked to the STOCKID in
>
> tbl_STOCK contains STOCKID, STOCKCATID
>
> which is linked to the STOCKCATID in
>
> tbl_STOCKCAT contains STOCKCATID, STOCKCATNAME (I need all of the
> STOCKCATNAME)
>
>
> How can I do this when I need the above to link of it all together??

Since you didn't/wouldn't provide database/version/schema/sample data
all I can provide is pseudo code. Do a cross-tab on the following query:

SELECT
SP.SALESPERSONNAME,
SC.STOCKCATNAME,
IL.QTYSOLD
FROM
tbl_SALESPERSONS AS SP,
tbl_INVOICEHEADER AS IH,
tbl_INVOICELINES AS IL,
tbl_STOCK AS S,
tbl_STOCKCAT AS SC
WHERE
SP.SALESPERSONID = IH.SALESPERSONID AND
IH.INVOICEID = IL.INVOICEID AND
IL.STOCKID = S.STOCKID AND
S.STOCKCATID = SC.STOCKCATID AND
IL.DATESOLD >= prmDATESOLD_GTE AND
IL.DATESOLD < prmDATESOLD_LT

UNION ALL

SELECT
SP.SALESPERSONNAME,
SC.STOCKCATNAME,
0
FROM
tbl_SALESPERSONS AS SP,
tbl_STOCKCAT AS SC

HTH
-Chris Hohmann

[Chris Hohmann]

"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:4082d9f1_3@127.0.0.1...

> Dear Chris
>
> I don't know how the hell you could make something out of my ramblings

and I

> did doubt it when I put it through Query Analyser, but it is

absolutely spot

> on when put through a Pivot Table!!!
>
> This union thing is completely different to what I expected.
>
> How the hell do you guys do it???
>
> I've got 2 SQL books (1 called Sams 24 hour SQL and the other 'the

Practical

> SQL handbook') and neither gives me any clue on how to do the things

that

> you guys know. Its so frustrating!!
>
> Do you know of a place/site that will explain this sort of thing to me

in a

> 'I'm thick - help me' sort of way??
>
> Many thanks all the same.

Most of what I know (which believe it or not is very little) comes from
fields combat/trial by fire. Joe Celko's insights in relational
databases are pretty amazing. I frequently filter my google group
searches with him as the author when I want the "definitive" answer to
something. His book "SQL for Smarties" is also a must have. And, as
always this newsgroup can be an excellent resource for your ASP/DB
related questions. There's no real black magic in the query I provided.
I simply added a dummy/filler set of data to create all the rows/columns
needed. That's what the second query is. Omitting the WHERE/JOIN clause
creates a Cartesian product, i.e. every sales person matched up with
every stock category.

HTH
-Chris Hohmann

[Laphan]

thanks Chris

Chris Hohmann <nospam@thankyou.com> wrote in message
news:#lELVcjJEHA.3436@tk2msftngp13.phx.gbl...
"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:4082d9f1_3@127.0.0.1...

> Dear Chris
>
> I don't know how the hell you could make something out of my ramblings

and I

> did doubt it when I put it through Query Analyser, but it is

absolutely spot

> on when put through a Pivot Table!!!
>
> This union thing is completely different to what I expected.
>
> How the hell do you guys do it???
>
> I've got 2 SQL books (1 called Sams 24 hour SQL and the other 'the

Practical

> SQL handbook') and neither gives me any clue on how to do the things

that

> you guys know. Its so frustrating!!
>
> Do you know of a place/site that will explain this sort of thing to me

in a

> 'I'm thick - help me' sort of way??
>
> Many thanks all the same.

Most of what I know (which believe it or not is very little) comes from
fields combat/trial by fire. Joe Celko's insights in relational
databases are pretty amazing. I frequently filter my google group
searches with him as the author when I want the "definitive" answer to
something. His book "SQL for Smarties" is also a must have. And, as
always this newsgroup can be an excellent resource for your ASP/DB
related questions. There's no real black magic in the query I provided.
I simply added a dummy/filler set of data to create all the rows/columns
needed. That's what the second query is. Omitting the WHERE/JOIN clause
creates a Cartesian product, i.e. every sales person matched up with
every stock category.

HTH
-Chris Hohmann