CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Srivastav
Srivastav

Reputation: 359

Consolidate SQL Table Data

I have a table named tblCustomerproductsstatus which is feeded through an automated data processing engine. It contains columns like - TradeID, CustomerInfoNo, product, Status, ProductQuantity, BoughtDate... as shown below.

TradeID  CustomerNo Product Name    Status   ProductQuantity     BoughtDate
---------------------------------------------------------------------------------------------------------------
320046  A157BV17034 Martin Mazorra Bonds    Done    125 4/16/2015
324825  A157BV17034 Martin Mazorra Bonds    Done    125 4/17/2015
326673  A157BV17034 Martin Mazorra Bonds    Done    125 4/18/2015
337943  A157BV17034 Martin Mazorra Bonds    Done    125 4/19/2015
337944  A157BV17034 Martin Mazorra Bonds    Done    180 4/20/2015
344243  A157BV17034 Martin Mazorra Bonds    Done    180 4/21/2015
350815  A157BV17034 Martin Mazorra Bonds    Done    180 4/22/2015
360105  A157BV17034 Martin Mazorra Bonds    Done    180 4/23/2015
362186  A157BV17034 Martin Mazorra Bonds    Done    170 4/24/2015
368117  A157BV17034 Martin Mazorra Bonds    Done    170 4/25/2015
376820  A157BV17034 Martin Mazorra Bonds    Done    170 4/26/2015
380321  A157BV17034 Martin Mazorra Bonds    Done    170 4/27/2015
385615  A157BV17034 Martin Mazorra Bonds    Done    255 4/28/2015
386136  A157BV17034 Martin Mazorra Bonds    Done    377 4/29/2015
398302  A157BV17034 Martin Mazorra Bonds    Done    377 4/30/2015
387825  A157BV17034 Martin Mazorra Bonds    Done    125 5/1/2015
388088  A157BV17034 Martin Mazorra Bonds    Done    125 5/2/2015
389001  A157BV17034 Martin Mazorra Bonds    Done    170 5/3/2015
391003  A157BV17034 Martin Mazorra Bonds    Done    170 5/4/2015
----
----
----**

I'm supposed to get results in such a way, as shown below.

TradeID  CustomerNo Product Name    Status   ProductQuantity     BoughtDate
--------------------------------------------------------------------------------------------------------------
320046  A157BV17034 Martin Mazorra Bonds    Done    125 4/16/2015
337944  A157BV17034 Martin Mazorra Bonds    Done    180 4/20/2015
362186  A157BV17034 Martin Mazorra Bonds    Done    170 4/24/2015
385615  A157BV17034 Martin Mazorra Bonds    Done    255 4/28/2015
386136  A157BV17034 Martin Mazorra Bonds    Done    377 4/29/2015
387825  A157BV17034 Martin Mazorra Bonds    Done    125 5/1/2015
389001  A157BV17034 Martin Mazorra Bonds    Done    170 5/3/2015
----
----
----

if a customer buys a product on '4/16/2015' and continues to have it till '4/19/2015' then we only display one initial record with his initial TradeID, and this continues... I hope i've made myself clear on this.

i've been thinking of using outer apply/ inner joins but not sure what to do.

I may need to run this query through OPENROWSET for remote servers, so please, anybody have any suggestions.

Thanks,

Upvotes: 0

Views: 60

Answers (1)

Sam Abushanab
Sam Abushanab

Reputation: 493

For this situation, I would use a stored procedure. I have written up a stored procedure that will produce the results you are looking for.

For some reason I can't put the stored procedure in my answer, so you will need to look at the SQL Fiddle.

SQL Fiddle: http://sqlfiddle.com/#!6/c977e/14

Upvotes: 1

Related Questions