Getting 2 SUMs from the same table

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

RobbieDont repeat the question
http://groups-beta.google.com/group...e663f7fc429d1a3

Madhivanan|||Robbie,

You have some data types in your schema that aren't really data types.
You have STOCKTRANSACTIONID as a data type of
T_STOCKTRANSACTIONSDOMAIN. Are you using SQL Server? How about
posting with good data types and some inserts so people can help you
better.

Thanks,
Jennifer|||(jennifer1970@.hotmail.com) writes:
> You have some data types in your schema that aren't really data types.
> You have STOCKTRANSACTIONID as a data type of
> T_STOCKTRANSACTIONSDOMAIN. Are you using SQL Server? How about
> posting with good data types and some inserts so people can help you
> better.

I assume that these are so-called user-defined data types created with
sp_addtype.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

get rid of an anonymous subscription

Hi all

I've got the following situation:

setup up an merge publication (distributor and publisher at the same server)

setup an anonymous subscription

deleted the subscription

In the replication monitor, I still see the deleted subscription.

Why?

And how do I get rid of it?

Thanks a lot for any informations and greetz

Aline

1. try to delete the subscription on the publisher side using sp_dropmergesubscription, (see http://msdn2.microsoft.com/en-us/library/ms176045.aspx) and

2. try to delete the subscription on the subscriber side using sp_dropmergepullsubscription, (see http://msdn2.microsoft.com/en-us/library/ms187336.aspx) if hte subscription is a Pull Subscription.

3. Restart the replication monitor.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Unfortunetaly, I've already done this (with the management studio). But without getting in touch with the distributor.

I thought no metadata are stored at the distributor for an anonymous subscriber, but as I see it in the replication monitor there got to be some traces...

I'm still thankful for any hint, to get rid of these traces after deleting.

|||

Let me make sure

1. You have run the SP (sp_dropmergesubscription and sp_dropmergepullsubscription) manaully on the publisher/subscriber right (not going through the UI)? If not please run them manually.

2. Can you check the subscription by running select * from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

I've done both by now (dropping via UI and with sp_dropmergepullsubscription). And still have it on the replication monitor, even after a restart of the replication monitor.

The "select * from sysmergesubscriptions" on the publication database doesn't show any dropped subscriptions.

The problem on the productive system is even more complex. A subscriber db was deleted (without dropping the subscription properly) and a new subscription from this subscriber to the same publication was made. As a result, the subscription appeared two times on the replication monitor (some Laptops appear up to 4 times!!). Now, there seems to be also a performance problem. So I hoped, I first would tidy up a little bit.

So, what happens if I drop the subscription with sp_dropmergepullsubscription, I remove the still working subscription without removing it from the replication monitor. And this dilemma I already have...

Any other ideas?

Thanks for your help so far.

|||

Sorry for the late reply.

Can you try to run sp_replmonitorhelpsubscription on the distribution database, http://msdn2.microsoft.com/en-us/library/ms188073.aspx?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

sp_replmonitorhelpsubscription shows all subscriptions - the already dropped included.

I now realise, I need another approach. The new questions are:

After deletion of an anoymous pull subscriber database (without first dropping the subscription regularly dropped with sp_dropmergepullsubscription), how do I remove all traces of it at the distributor / publisher?

Can I force the cleanup to remove entries, which are waiting to be synchronized only to this deleted subscription?

Thanks for your help

Aline

|||

Hi Aline,

This is a bug.

Currently when you create an anonymous subscription and delete it with:

sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, the subscription is removed from the subscriber and publisher databases. However some trace is left behind in the distribution database. Now replication monitor reads this data from the distributor and hence this subscriptions still shows up there.

You can use the follwoing workaround/trick :)

On the same publication, pub database, and sub database, create a dummy PULL subscription. You dont need to synchronize. Then call sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, this time with the appropriate parameters saying that it is a pull subscription. This will clear the entry in the distribution database and your monitor will not show this subscription anymore.

get rid of an anonymous subscription

Hi all

I've got the following situation:

setup up an merge publication (distributor and publisher at the same server)

setup an anonymous subscription

deleted the subscription

In the replication monitor, I still see the deleted subscription.

Why?

And how do I get rid of it?

Thanks a lot for any informations and greetz

Aline

1. try to delete the subscription on the publisher side using sp_dropmergesubscription, (see http://msdn2.microsoft.com/en-us/library/ms176045.aspx) and

2. try to delete the subscription on the subscriber side using sp_dropmergepullsubscription, (see http://msdn2.microsoft.com/en-us/library/ms187336.aspx) if hte subscription is a Pull Subscription.

3. Restart the replication monitor.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Unfortunetaly, I've already done this (with the management studio). But without getting in touch with the distributor.

I thought no metadata are stored at the distributor for an anonymous subscriber, but as I see it in the replication monitor there got to be some traces...

I'm still thankful for any hint, to get rid of these traces after deleting.

|||

Let me make sure

1. You have run the SP (sp_dropmergesubscription and sp_dropmergepullsubscription) manaully on the publisher/subscriber right (not going through the UI)? If not please run them manually.

2. Can you check the subscription by running select * from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

I've done both by now (dropping via UI and with sp_dropmergepullsubscription). And still have it on the replication monitor, even after a restart of the replication monitor.

The "select * from sysmergesubscriptions" on the publication database doesn't show any dropped subscriptions.

The problem on the productive system is even more complex. A subscriber db was deleted (without dropping the subscription properly) and a new subscription from this subscriber to the same publication was made. As a result, the subscription appeared two times on the replication monitor (some Laptops appear up to 4 times!!). Now, there seems to be also a performance problem. So I hoped, I first would tidy up a little bit.

So, what happens if I drop the subscription with sp_dropmergepullsubscription, I remove the still working subscription without removing it from the replication monitor. And this dilemma I already have...

Any other ideas?

Thanks for your help so far.

|||

Sorry for the late reply.

Can you try to run sp_replmonitorhelpsubscription on the distribution database, http://msdn2.microsoft.com/en-us/library/ms188073.aspx?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

sp_replmonitorhelpsubscription shows all subscriptions - the already dropped included.

I now realise, I need another approach. The new questions are:

After deletion of an anoymous pull subscriber database (without first dropping the subscription regularly dropped with sp_dropmergepullsubscription), how do I remove all traces of it at the distributor / publisher?

Can I force the cleanup to remove entries, which are waiting to be synchronized only to this deleted subscription?

Thanks for your help

Aline

|||

Hi Aline,

This is a bug.

Currently when you create an anonymous subscription and delete it with:

sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, the subscription is removed from the subscriber and publisher databases. However some trace is left behind in the distribution database. Now replication monitor reads this data from the distributor and hence this subscriptions still shows up there.

You can use the follwoing workaround/trick :)

On the same publication, pub database, and sub database, create a dummy PULL subscription. You dont need to synchronize. Then call sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, this time with the appropriate parameters saying that it is a pull subscription. This will clear the entry in the distribution database and your monitor will not show this subscription anymore.

get rid of an anonymous subscription

Hi all

I've got the following situation:

setup up an merge publication (distributor and publisher at the same server) setup an anonymous subscription deleted the subscription

In the replication monitor, I still see the deleted subscription.

Why?

And how do I get rid of it?

Thanks a lot for any informations and greetz

Aline

1. try to delete the subscription on the publisher side using sp_dropmergesubscription, (see http://msdn2.microsoft.com/en-us/library/ms176045.aspx) and

2. try to delete the subscription on the subscriber side using sp_dropmergepullsubscription, (see http://msdn2.microsoft.com/en-us/library/ms187336.aspx) if hte subscription is a Pull Subscription.

3. Restart the replication monitor.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Unfortunetaly, I've already done this (with the management studio). But without getting in touch with the distributor.

I thought no metadata are stored at the distributor for an anonymous subscriber, but as I see it in the replication monitor there got to be some traces...

I'm still thankful for any hint, to get rid of these traces after deleting.

|||

Let me make sure

1. You have run the SP (sp_dropmergesubscription and sp_dropmergepullsubscription) manaully on the publisher/subscriber right (not going through the UI)? If not please run them manually.

2. Can you check the subscription by running select*from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

I've done both by now (dropping via UI and with sp_dropmergepullsubscription). And still have it on the replication monitor, even after a restart of the replication monitor.

The "select * from sysmergesubscriptions" on the publication database doesn't show any dropped subscriptions.

The problem on the productive system is even more complex. A subscriber db was deleted (without dropping the subscription properly) and a new subscription from this subscriber to the same publication was made. As a result, the subscription appeared two times on the replication monitor (some Laptops appear up to 4 times!!). Now, there seems to be also a performance problem. So I hoped, I first would tidy up a little bit.

So, what happens if I drop the subscription with sp_dropmergepullsubscription, I remove the still working subscription without removing it from the replication monitor. And this dilemma I already have...

Any other ideas?

Thanks for your help so far.

|||

Sorry for the late reply.

Can you try to run sp_replmonitorhelpsubscription on the distribution database, http://msdn2.microsoft.com/en-us/library/ms188073.aspx?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

sp_replmonitorhelpsubscription shows all subscriptions - the already dropped included.

I now realise, I need another approach. The new questions are:

After deletion of an anoymous pull subscriber database (without first dropping the subscription regularly dropped with sp_dropmergepullsubscription), how do I remove all traces of it at the distributor / publisher?

Can I force the cleanup to remove entries, which are waiting to be synchronized only to this deleted subscription?

Thanks for your help

Aline

|||

Hi Aline,

This is a bug.

Currently when you create an anonymous subscription and delete it with:

sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, the subscription is removed from the subscriber and publisher databases. However some trace is left behind in the distribution database. Now replication monitor reads this data from the distributor and hence this subscriptions still shows up there.

You can use the follwoing workaround/trick :)

On the same publication, pub database, and sub database, create a dummy PULL subscription. You dont need to synchronize. Then call sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, this time with the appropriate parameters saying that it is a pull subscription. This will clear the entry in the distribution database and your monitor will not show this subscription anymore.

get resulset from stored procedure

Hi all!
I have one stored procedure than returns a resulset (select). I want to
execute this in other stored procedure and get the resulset... How can i
get it?
TIA
Alfonsohttp://www.sommarskog.se/share_data.html
David Portas
SQL Server MVP
--|||Hi,
CREATE TABLE #TEMP
(
COL1 INT NOT NULL
)
INSERT INTO #TEMP EXEC SP
Also seen on:
http://groups.google.de/groups?hl=d...r />
40phx.gbl
%26rnum%3D8
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Alfonso" <aNOSPAMparedes@.rtspain.com> schrieb im Newsbeitrag
news:%234DizOAQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi all!
> I have one stored procedure than returns a resulset (select). I want to
> execute this in other stored procedure and get the resulset... How can i
> get it?
> TIA
> Alfonso
>|||
Thanks David & Jens for your answers
I got it :-)))