Hi All
I'm really stuck on this one so would appreciate any help you can give.
In essence, I have 1 SQL 2000 table with rows of data logging stock
movement. To differenciate between a stock sale and a stock receipt the
table has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equals a
receipt.
I've been asked to report on this data by suming the total qty used on
invoices and the total qty recvd for each stock item, but I can't figure out
how I sum the same rows twice in the one query.
For example, my query is as follows:
select st.stockid as 'STYLE',
s.picture as 'COLOUR',
'' as 'IN FIRST IN LAST WEEK',
'' as 'THIS WEEK IN',
'' as 'TOTAL IN',
'' as 'OUT FIRST OUT LAST WEEK',
SUM(st.quantity) as 'THIS WEEK OUT',
'' as 'TOTAL OUT',
'' as 'REMAINING',
'' as 'TOTAL DIGESTION %'
from stocktransactions st, stock s
where st.stockid = s.stockid and
st.transactiontype in (8,7) and
st.transactiondate >= '2005-07-12 00:00:00' and
st.transactiondate <= '2005-07-12 23:59:59'
group by st.stockid,s.picture
order by st.stockid
Apart from the 'THIS WEEK OUT' column SUMing all of the stock sales by
transactiontype 7,8, I also want the 'THIS WEEK IN' column to SUM all of the
transactions by transactiontype 3, so that I get the following results:
STYLE COLOUR .... THIS WEEK IN .... THIS WEEK OUT .......
IVP Red 12 23
STP Blue 4 15
etc etc
My problem is that I don't want to exclude a stock item if it hasn't got a
row/value for the THIS WEEK IN and/or the THIS WEEK OUT. Am I asking too
much of SQL?
My table schemas are as follows:
create table STOCKTRANSACTIONS
(
STOCKTRANSACTIONID T_STOCKTRANSACTIONSDOMAIN not null
identity(1,1),
TRANSACTIONTYPE smallint not null,
TRANSACTIONDATE datetime null ,
REFERENCE varchar(40) null ,
Comment varchar(255) null ,
STOCKID T_STOCKDOMAIN null ,
DESCRIPTION varchar(255) null ,
UNITOFSALE varchar(20) null ,
WAREHOUSEID T_WAREHOUSESDOMAIN null ,
PEOPLEID T_PEOPLEDOMAIN null ,
AccountID T_AccountsDomain null ,
AgentID T_AgentsDomain null ,
PLRate float null ,
CONTACTID T_CONTACTDETAILSDOMAIN null ,
JOBID T_JOBSDOMAIN null ,
QUANTITY float null ,
CURRENCYID T_CURRENCIESDOMAIN null ,
SELLINGPRICE float null ,
DISCOUNTPERCENT float null ,
COSTPRICE float null ,
MINIMUMPRICE float null ,
TILLID T_TILLSDOMAIN null ,
UserID T_UsersDomain null ,
ClockDate DateTime null ,
TimeStamp TimeStamp ,
constraint pk_stocktransactions primary key (STOCKTRANSACTIONID)
)
go
create table 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 default 1,
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 default
0.00 ,
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,
constraint pk_stock primary key (STOCKID)
)
go
Thanks
Robbie
