• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

C# -Exporting query results to excel, and manipulating the look of the spreadsheet


I have a query that produces a result as seen attached, below, (Sample Result).  The result is correct, just not displayed correctly.
If you notice the "Acc No" field, it has a few duplicates.  I need to seperate the results to show the correct data on one side, and the incorrect on the other side.  For eg. Mrs.K Drier is the correct record, this should be on the left and the incorrect one, Mr S Drier, on the right.

I have attached, the sample of what the result should look like in Excel.
  • 3
  • 2
1 Solution
For better responses to your question, how do you determine what is correct or incorrect data? In the case of duplicate account numbers, is the oldest account correct?

Is this something that could be handled easily in your query?
Jasmin01Author Commented:
Yes, the correct data is the oldest account.

Getting the query result is easy, its getting it in the format attached (Excel file) that's the problem.  If I could get it displaying correctly in SQL I can easily print it to Excel, but I have been struggling for days to get the query to display correctly, and have not been successful.  I can only get it as I had attached, one below the other.
This should get you moving with your query.

I create a table variable @table with this (to test my query against)
declare @table table ([Name] varchar(25),Acc_No integer,	Pol_No varchar(10), Date_Sold datetime, Product varchar(10))

insert into @table values ('Mr A Green', 3321234, 'A BANK', '2011/04/28', 'ND')
insert into @table values ('MRS T Flower', 8876543, 'A BANK', '2011/01/17', 'JT')
insert into @table values ('H TOBY', 5543456, 'B BANK', '2012/08/06', 'H')
insert into @table values ('MRS K DRIER', 3457543, 'C BANK', '2011/12/02', 'J')
insert into @table values ('MR S DRIER', 3457543, 'C BANK', '2012/05/22', 'S')
insert into @table values ('MRS T JACK', 1100876, 'A BANK', '2011/10/12', 'SE')
insert into @table values ('MR J JACK', 1100876, 'A BANK', '2012/01/19', 'JP')
insert into @table values ('MR R REDMAN', 2134523, 'B BANK', '2012/03/09', 'JJ')
insert into @table values ('MISS A FISHER', 2134523, 'B BANK', '2012/04/14', 'AD')
insert into @table values ('MR F TOOLE', 2134523, 'B BANK', '2012/05/21', 'F')
insert into @table values ('MR BOLTON', 2134523, 'B BANK', '2012/05/24', 'JY')
insert into @table values ('MR R RIGHT', 2134523, 'B BANK', '2012/06/04', 'RF')
insert into @table values ('MRS C KING', 2134523, 'B BANK', '2012/06/12', 'C')

Open in new window

and then running this query:
a.[name] as correctName,
a.acc_no as correctAcc_No,
a.pol_no as correctPol_No,
b.Date_Sold as correctDate_Sold,
a.product as correctProduct,
c.[name] as incorrectName,
c.acc_no as incorrectAcc_No,
c.pol_no as incorrectPol_No,
c.Date_Sold as incorrectDate_Sold,
c.product as incorrectProduct
from @table a inner join
(select min(Date_Sold) as Date_Sold, acc_no from @table a2 group by a2.acc_no) b
on a.acc_no = b.acc_no and a.[date_sold] = b.[date_sold] 
left outer join
(select * from @table) c
on a.acc_no = c.acc_no and a.[date_sold] < c.[date_sold]

Open in new window

will get you the attached results.
Jasmin01Author Commented:
Thanks for that.  My tables are a bit more complex.  The structure looks like this:

Table a               Table b             Table c
FK_LeadID         PK_LeadID        PKItemID
Name                 FK_ItemID        DateSold
Acc_No               LText                 PolicyNo

In order to get the Date Sold and the PolicyNo, I need to join Table b to the query and then join table c in order to get the result.  I'm trying to tweak your query to do this, but am not making much progress.
Jasmin01Author Commented:
I figured it out with your help.  Thanks for the awesome query.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now