Hi All
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
2  1402 
"Laphan" <ne**@DoNotEmailMe.co.uk> wrote in message
news:40********@127.0.0.1... Hi All
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
Hi
Your syntax is not ANSI format. What you require is an outer join. This is
described with examples in Books Online:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_09_0zqr.htm
It is also useful to include your example data as insert statements along
with all the DDL.
e.g
SELECT C.NAME, T.NAME,
SPOOLEDINVOICELINES.QUANTITY, I.INVOICEDATE
FROM SALESCENTRES C LEFT JOIN SPOOLEDINVOICES I ON
C.SALESCENTREID = I.SALESCENTREID
JOIN SPOOLEDINVOICELINES L ON L.SPOOLEDINVOICEID = I.SPOOLEDINVOICEID
JOIN STOCK S ON S.STOCKID = L.STOCKID
STOCKCATEGORIES T ON T.STOCKCATEGORYID = S.STOCKCATEGORYID
WHERE I.INVOICEDATE > '2004-04-01 00:00:00'
AND I.INVOICEDATE < '2004-08-01 00:00:00'
John
"Laphan" <ne**@DoNotEmailMe.co.uk> wrote in message
news:40********@127.0.0.1... Hi All
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jaysonsch |
last post by:
Hello!
I am having some problems with a database query that I am trying to do.
I am trying to develop a way to search a database for an entry and
then edit the existing values. Upon submit, the...
|
by: Jack Carter |
last post by:
I have been delegated to produce a tool that has
python embedded in it. The desire is to have a
command line interface that inherits all the python
scripting functionality so people can use the...
|
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules"...
|
by: The Plankmeister |
last post by:
Hi,
I am converting some queries from stored procedures in MS Access to MySQL,
and have hit my first problem. The Access query references another stored
procedure, which is where the problem...
|
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use...
|
by: Laphan |
last post by:
Hi All
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:
...
|
by: Harvey |
last post by:
Hi,
I try to write an asp query form that lets client search any text-string and
display all pages in my web server that contain the text. I have IIS 6.0 on a
server 2003. The MSDN site says...
|
by: Diamondback |
last post by:
I have two tables, WIDGETS and VERSIONS. The WIDGETS table has
descriptive information about the widgets while the VERSIONS table
contains IDs relating to different iterations of those widgets...
|
by: Lumpierbritches |
last post by:
I have a query that pulls information for a report. Sometimes the report is in
tact and works wonderful; other times the report gives me a syntax error. The
information is only different in one...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
