Add values of distinct rows

Hello All,

I have a scenario wherein I wanted to add the values of distinct transaction number, how do I do that. i.e.

Financial organisation (GROUP (Company))

12/31/2003 606.60 approved 123 (Transaction Number) (1st Row)

5643577(Wok Report) (2nd Row)

01/05/2004 1000.00 approved 124 (Transaction Number) (to Satisfy $1000 three work reports were raised)

5643500(Wok Report)

5643501(Wok Report)

5643502(Wok Report)

02/05/2004 2000.00 approved 125 (Transaction Number) (to Satisfy $2000 three work reports were raised)

5643300(Wok Report)

5643301(Wok Report)

5643302(Wok Report)

Total 3606.60 (Total per Company group)

I want to add the visible row values only. Because the 1000.00 and 2000.00 was repeating for 01/05/2004 and 02/05/04 I am hinding the repeated values.

Please help me completing the above task.

Thanks,

Deepak Vodethela

Deekpak,

Unfortunatley, from my experiences, this is not possible as of SSRS 2005. I tried to do the same thing recently and I ended up changing my SQL to give me what I wanted.

|||

Thanks for the reply Todd. Can't we write a custom function to do the needful i.e. which has to check for the distict Transaction number and pick up the value and do the sum by group. I am not an expert at SSRS but just curious.

To change my query it will be a very big task. Right now I am joining 11 tables to get the required fields and if I want to add the sum on a group then it'll be a laborious task.

Any help would be appriciated.

add users in a transaction SQL Server 2000

I need to be able to add users to roles in serveral databases and if one of
the inserts fails I need to be able to roll back the whole transaction. I am
getting the following error:
The procedure 'sp_grantlogin' cannot be executed within a transaction.
The procedure 'sp_defaultdb' cannot be executed within a transaction.
The procedure 'sp_grantdbaccess' cannot be executed within a transaction.
The procedure 'sp_addrolemember' cannot be executed within a transaction.You will have to use your own logic to make it RollbackAble:
"sp_grantlogin cannot be executed within a user-defined transaction."
http://msdn2.microsoft.com/en-us/library/ms173449.aspx
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"maddog" <maddog@.discussions.microsoft.com> wrote in message
news:2B94A051-19CC-41F1-96B6-AB51D1E5C3B3@.microsoft.com...
>I need to be able to add users to roles in serveral databases and if one of
> the inserts fails I need to be able to roll back the whole transaction. I
> am
> getting the following error:
> The procedure 'sp_grantlogin' cannot be executed within a transaction.
> The procedure 'sp_defaultdb' cannot be executed within a transaction.
> The procedure 'sp_grantdbaccess' cannot be executed within a transaction.
> The procedure 'sp_addrolemember' cannot be executed within a transaction.

Add new table to sql server 2005 transaction replication

I am trying to add a new table to tansaction replication on sql server 2005.
The problem I am having is that if I restrt the snapshot agent, it is doing
all the tables in the publisher. Ho can I make the snapshot agent do only the
new table?
Steps I have taken:
1. Add new table to publisher and the publication.
2. Star the snapshot agent
This is all done using the SSMS Gui.
Thanks,
George Gopie
George,
can you check to see if you have enabled anonymous subscribers. If you have,
then this behaviour is normal and you could reset it (using
sp_changepublication) to create a snapshot of only the one table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Use sp_addarticle for this. If it is a pull subscription you will need to do
a sp_refreshsubscriptions
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"georgeg" <ggg@.hotamil.com> wrote in message
news:5D774662-3B4D-4B58-9091-36F05F8CDB46@.microsoft.com...
>I am trying to add a new table to tansaction replication on sql server
>2005.
> The problem I am having is that if I restrt the snapshot agent, it is
> doing
> all the tables in the publisher. Ho can I make the snapshot agent do only
> the
> new table?
> Steps I have taken:
> 1. Add new table to publisher and the publication.
> 2. Star the snapshot agent
> This is all done using the SSMS Gui.
> Thanks,
> --
> George Gopie
>
|||Hilary,
I have used the sp_addarticle, and sp_refreshSubscriptions succesfully.
sp_addarticle
@.publication = RYS_SPRS77_PUBL,
@.article = InstrumentCheckResult,
@.source_object = InstrumentCheckResult
sp_refreshsubscriptions
@.publication = RYS_SPRS77_PUBL
However, when I checked the ReplicationMonitor
I see message: The initial snapshot for article InstrumrntCheckResult is not
yet available
Do I still need to run the snapshot agent again?
Thanks,
George Gopie
"Hilary Cotter" wrote:

> Use sp_addarticle for this. If it is a pull subscription you will need to do
> a sp_refreshsubscriptions
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "georgeg" <ggg@.hotamil.com> wrote in message
> news:5D774662-3B4D-4B58-9091-36F05F8CDB46@.microsoft.com...
>
>
|||Yes, it should only generate a snapshot for the new article.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"georgeg" <ggg@.hotamil.com> wrote in message
news:D26B8402-F678-43AA-9144-98C9A5172991@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I have used the sp_addarticle, and sp_refreshSubscriptions succesfully.
> sp_addarticle
> @.publication = RYS_SPRS77_PUBL,
> @.article = InstrumentCheckResult,
> @.source_object = InstrumentCheckResult
> sp_refreshsubscriptions
> @.publication = RYS_SPRS77_PUBL
> However, when I checked the ReplicationMonitor
> I see message: The initial snapshot for article InstrumrntCheckResult is
> not
> yet available
> Do I still need to run the snapshot agent again?
> Thanks,
> --
> George Gopie
>
> "Hilary Cotter" wrote:

Add Article(table) to Trans Repl - Move to Subscriber

How to you add an Article (Table) to a Transaction Replication Publication,
and have it move to the Subscribers without having to re-generate the Whole
Snapshot?
What to I monitor to see this happen and how long to I wait to see it update?
Thanks and God Bless,
ThomBeaux
Thanks and God Bless,
ThomBeaux
"ThomBeaux" wrote:

> How to you add an Article (Table) to a Transaction Replication Publication,
> and have it move to the Subscribers without having to re-generate the Whole
> Snapshot?
> What to I monitor to see this happen and how long to I wait to see it update?
> --
> Thanks and God Bless,
> ThomBeaux
|||Will a new snpahot be created for the whole Publication?
Thanks and God Bless,
ThomBeaux
"Paul Ibison" wrote:

> Just running sp_addarticle and sp_addsubscription then running the snapshot
> agent should be fine. Unlike merge, just the new article will get created.
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

add a column if product is in discrepancies table (was "sql query")

I have a transaction table (which contains details of all products on all orders)
and is have a discrepancies table which includes details on products that couldnt been found (hence error in stock count)...

Now I want to write a query returning details of all products from a specfic orders.. pretty simple

SELECT product, description, qty
from TRANSACTIONS
where order_no = 'xxx'

but i also want to add an extra boolean column (true if product is in discrepancies table and false if product is not in discrepancies table...will become checkbox at front end)... im not sure how to write this bit...

I know it would a transaction table LEFT JOIN to discrepancies table, but I do not know how to write syntax to add extra column...

Be garteful for any help :confused:

Thanksselect product
, description
, qty
, case when exists
( select 937
from discrepancies
where product_id = t.product_id )
then 'oui'
else 'non'
end as discrepancy_exists
from TRANSACTIONS as t
where order_no = 'xxx'|||thanks worked like a treat :)

Actual Situation

Hi,
I am at the head office of the company and we have 9 locations nationwide.
We don't generate any transaction in the database in the head office. We
only collect data from other location.
I am asked to transfer the Inventory system about the partnumber and
quantity on hand to the head office every hour.
Which replication should i use, Snapshot, Merge, or Transactional?
Thanks for any suggestion
Ed
Probably transactional - what you are trying to do is called a central
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:AE5E3B36-1D43-4BA5-984D-7C02745FFA72@.microsoft.com...
> Hi,
> I am at the head office of the company and we have 9 locations
nationwide.
> We don't generate any transaction in the database in the head office. We
> only collect data from other location.
> I am asked to transfer the Inventory system about the partnumber and
> quantity on hand to the head office every hour.
> Which replication should i use, Snapshot, Merge, or Transactional?
> Thanks for any suggestion
> Ed