October 20, 2017 at 7:42 am
I am trying to figure out how to take a results set and have it show differences between row and have that show up in columns
I hope this makes since. here is the code
use tempdb
go
create table [dbo].[resultsTable](
[number] int not null,
[Loc] nvarchar(20),
[basis] float,
[mkt] nvarchar(10),
[futureMonth] int,
FreightPrice float,
DueDate date,
FuturesPrice float,
[MktDescription] nvarchar(100)
)
go
insert into resultstable values('2188','816','1.37','MTY','201712','0','9/30/2017','3.65','MONTERREY , MEXICO')
insert into resultstable values('2188','816','2.37','MTY','201712','0','9/30/2017','3.75','MONTERREY , MEXICO')
insert into resultstable values('2563','606','56','WQA','201710','0','9/30/2017','306.4','GUELPH ON')
insert into resultstable values('2650','606','56','86','201710','0','9/30/2017','310.2','Ontario')
insert into resultstable values('7716','390','157.39','ASI',NULL,'-5','9/30/2017','0','ASIA')
insert into resultstable values('7920','390','208.653','VTM',NULL,'0','9/30/2017','0','VIETNAM')
insert into resultstable values('7925','390','165.18','ASI',NULL,'-9','9/30/2017','0','ASIA')
insert into resultstable values('10589','465','0.45','589','201712','0','9/30/2017','3.58','SOTX')
insert into resultstable values('10643','240','1.32','MTY','201712','0','9/30/2017','3.65','MONTERREY, MX')
insert into resultstable values('11472','241','1.27','CH1','201712','0','9/30/2017','4.3875','CHICAGO 12.0')
insert into resultstable values('16069','750','0.54','TPS','201711','0','9/30/2017','9.74','Tallulah Port Sale')
insert into resultstable values('16145','750','0.46','DLH','201712','0','9/30/2017','3.56','DELHI , LA PURCHASES')
insert into resultstable values('16145','750','0.64','DLH','201712','0','9/30/2017','3.56','DELHI , LA PURCHASES')
insert into resultstable values('16145','750','0.69','DLH','201712','0','9/30/2017','3.56','DELHI , LA PURCHASES')
insert into resultstable values('17203','241','0.9','K15','201709','0','9/30/2017','4.74','KC 11.5')
insert into resultstable values('22178','510','0.85','BTS','201709','-0.3','9/30/2017','3.87','BLISS TRUCK SALES')
insert into resultstable values('23465','510','-0.33','GFT','201709','-0.15','9/30/2017','4.43','GLENNS FERRY TRUCK SALES')
insert into resultstable values('23784','510','0.75','BTS','201709','-0.15','9/30/2017','3.71','BLISS TRUCK SALES')
insert into resultstable values('23941','510','0.8','BTS','201709','-0.165','9/30/2017','3.46','BLISS TRUCK SALES')
As you will see in the data I have duplicate and beyond records to sort through Number 2188 has a change in the basis column and the futuresPrice column. Number 16145 will have 3 records which the only change in the basis column, ever other column is a single value
The output should look like this
<table><tbody><tr><th>Number</th><th>PMLoc</th><th>CMLoc</th><th>LocChange</th><th>PmBasis</th><th>CMBasis</th><th>BasisChange</th><th>PMMkt</th><th>CMMkt</th><th>MktChange</th><th>PMFutureMonth</th><th>CMFutureMonth</th><th>FutureMonthChange</th><th>PmFreightPrice</th><th>CMFreightPrice</th><th>FreightPriceChange</th><th>PMDueDate</th><th>CMDueDate</th><th>DueDateChange</th><th>PMFuturesPrice</th><th>CMFuturesPrice</th><th>FuturesPriceChange</th><th>PMMktDescription</th><th>CMMktDescription</th><th>MktDescriptionChange</th></tr><tr><td>2188</td><td>816</td><td>816</td><td> </td><td>1.37</td><td>2.37</td><td>1.00</td><td>MTY</td><td>MTY</td><td> </td><td>201712</td><td>201712</td><td> </td><td>0</td><td>0</td><td> </td><td>2017-09-30</td><td>2017-09-30</td><td> </td><td>3.65</td><td>3.75</td><td>0.10</td><td>Monterrey, Mexico</td><td>Monterrey, Mexico</td><td> </td></tr></tbody></table>
Sorry for the crappy code for the results set so did it in HTML so you could see what it's suppose to look like.
I'm not sure whether to do a unpivot, but there could be multiple values which in that case you would you the first one as the PM the second one as CM then on the second row PM would be the second row and CM would be the third record and so on.
Any assistance on either the code or where to look would be greatly appreciated.
Thanks in advance
October 20, 2017 at 8:14 am
Without a specialized data comparison tool, this is NOT an easy task. T-SQL doesn't really have much for data comparison on a column by column basis. While you can use EXCEPT to show differences between result sets, that gets you entire records, and is a one-way comparison, in that SELECT * FROM A EXCEPT SELECT * FROM B is different from SELECT * FROM B EXCEPT SELECT * FROM A. However, in your case, you would have to pivot every column in order to get what you're looking for, and that's going to be difficult at best, even using a cross tab instead of the PIVOT operator. I don't see any way around the use of CASE statements and either LEAD or LAG, with a rather sizable ORDER BY portion of your OVER clause.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 20, 2017 at 9:08 am
Thanks sgmunson, I'll look into either a tool or Programmatic way to solve the problem.
October 20, 2017 at 11:12 am
Well, I won't do all the process to render the html. Can you post something easier to read? You could just get a SELECT with the results you need.
October 20, 2017 at 12:18 pm
Luis Cazares - Friday, October 20, 2017 11:12 AMWell, I won't do all the process to render the html. Can you post something easier to read? You could just get a SELECT with the results you need.
As a further requirement to what Luis says above, the results set should show what you want based on the sample data provided, not just one row.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply