July 17, 2018 at 2:38 pm
Hi,
I have a table:
| IDRq
| Date
| IDShow
| Show
|
| 5236
| 2018-10-06
| 300
| Forget Me Not - The Alzheimer's Whodunnit
|
| 5526
| 2018-10-11
| 250
| 4Square
|
| 5458
| 2018-10-11
| 150
| The Importance of Digging Holes: The Great War and More
|
| 5632
| 2018-10-12
| 500
| A Parlour Concert with Mister Keith
|
| 5369
| 2018-10-13
| 500
| A Parlour Concert with Mister Keith
|
| 5829
| 2018-10-12
| 150
| The Importance of Digging Holes: The Great War and More
|
| 5492
| 2018-10-20
| 500
| A Parlour Concert with Mister Keith
|
| 5381
| 2018-10-20
| 550
| Heartspur
|
| 5331
| 2018-10-22
| 500
| A Parlour Concert with Mister Keith
|
I need a query or view to make the above table to look like this. I.e. I need to:
IDRq is unique ID for the table record. Each show has a unique ID (IDShow). Date is the performance date, so each show can have many performances dates, each date can have many shows but each date have only have one performance by any particular show.
| IDRq
| Date
| IDShow
| Show
|
| 5236
| 2018-10-06
| 300
| Forget Me Not - The Alzheimer's Whodunnit
|
| 5526
| 2018-10-11
| 250
| 4Square
|
| 5458
| 2018-10-11
| 150
| The Importance of Digging Holes: The Great War and More
|
| 5829
| 2018-10-12
| 150
| The Importance of Digging Holes: The Great War and More
|
| 5632
| 2018-10-12
| 500
| A Parlour Concert with Mister Keith
|
| 5369
| 2018-10-13
| 500
| A Parlour Concert with Mister Keith
|
| 5492
| 2018-10-20
| 500
| A Parlour Concert with Mister Keith
|
| 5331
| 2018-10-22
| 500
| A Parlour Concert with Mister Keith
|
| 5381
| 2018-10-20
| 550
| Heartspur
|
Here's hoping after hours many forums and tries!
Many thanks
July 17, 2018 at 3:01 pm
;WITH cte_EarliestShowDates AS (
SELECT Show, MIN(Date) AS EarliestShowDate
FROM dbo.TableName
GROUP BY Show
)
SELECT TN.*
FROM dbo.TableName TN
INNER JOIN cte_EarliestShowDates CESD ON CESD.Show = TN.Show
ORDER BY CESD.EarliestShowDate, Show
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 17, 2018 at 4:39 pm
Dear SSC Guru,
Thank you! Worked like a charm first time inside SQL Server whether as a view or a query. However I also need to access the resulting recordset outside of SQL Server for purposes such as mail merges and it is vital that the recordset does not loose its order and of course a view doesn't retain its sort order. What is the best way to present the recordset outside of SQL server where it retains the sort order you have brilliantly produced?
Many thanks
July 17, 2018 at 6:39 pm
Hi,
Actually I spoke too soon. It is nearly working perfectly however the sort order of each 'show' group is in the wrong order. I need it to show the earliest date first as shown in th example. I have tried adding ASC or DESC in the Order By but this either does nothing or ruins the grouping. So nearly there! Can you deal with this final issue?
Regarding the 'presentation' issue I've managed to get it exported to an excel spreadsheet via an SSIS Dataflow task which has maintained the sort order.
Many thanks
July 17, 2018 at 7:38 pm
john-997885 - Tuesday, July 17, 2018 6:39 PMHi,
Actually I spoke too soon. It is nearly working perfectly however the sort order of each 'show' group is in the wrong order. I need it to show the earliest date first as shown in th example. I have tried adding ASC or DESC in the Order By but this either does nothing or ruins the grouping. So nearly there! Can you deal with this final issue?Regarding the 'presentation' issue I've managed to get it exported to an excel spreadsheet via an SSIS Dataflow task which has maintained the sort order.
Many thanks
"I need it to show the earliest date first "
If you had two entries for the same Show, and one of the rows happen to be the earliest then how do you want it to be shown as
IDRq | Date | IDShow | Show |
5236 | 2018-10-06 | 300 | Forget Me Not - The Alzheimer's Whodunnit |
5526 | 2018-10-20 | 300 | Forget Me Not - The Alzheimer's Whodunnit |
5458 | 2018-10-11 | 150 | The Importance of Digging Holes: The Great War and More |
5829 | 2018-10-12 | 150 | The Importance of Digging Holes: The Great War and More |
5632 | 2018-10-12 | 500 | A Parlour Concert with Mister Keith |
5369 | 2018-10-13 | 500 | A Parlour Concert with Mister Keith |
5492 | 2018-10-20 | 500 | A Parlour Concert with Mister Keith |
5331 | 2018-10-22 | 500 | A Parlour Concert with Mister Keith |
5381 | 2018-10-20 | 550 | Heartspur |
July 17, 2018 at 8:29 pm
john-997885 - Tuesday, July 17, 2018 6:39 PMHi,
Actually I spoke too soon. It is nearly working perfectly however the sort order of each 'show' group is in the wrong order. I need it to show the earliest date first as shown in th example. I have tried adding ASC or DESC in the Order By but this either does nothing or ruins the grouping. So nearly there! Can you deal with this final issue?Regarding the 'presentation' issue I've managed to get it exported to an excel spreadsheet via an SSIS Dataflow task which has maintained the sort order.
Many thanks
Have a look at the following
I am creating a pseudo column which will indicate the lowest date per show and sorting by that column first.
That will give me the sort order of the overall result set.
After that i sort based on the show(to clump all the shows together) followed by sorting on the (clumped data by the date1 column).
drop table t
create table t(idrq int, date1 date,idshow int,show varchar(100));
insert into t values(5236,convert(date,'10/6/2018' ,101),300,'Forget Me Not - The Alzheimer''s Whodunnit');
insert into t values(5526,convert(date,'10/11/2018',101),250,'4Square');
insert into t values(5458,convert(date,'10/11/2018',101),150,'The Importance of Digging Holes: The Great War and More');
insert into t values(5829,convert(date,'10/12/2018',101),150,'The Importance of Digging Holes: The Great War and More');
insert into t values(5632,convert(date,'10/12/2018',101),500,'A Parlour Concert with Mister Keith');
insert into t values(5369,convert(date,'10/13/2018',101),500,'A Parlour Concert with Mister Keith');
insert into t values(5492,convert(date,'10/20/2018',101),500,'A Parlour Concert with Mister Keith');
insert into t values(5331,convert(date,'10/22/2018',101),500,'A Parlour Concert with Mister Keith');
insert into t values(5381,convert(date,'10/20/2018',101),550,'Heartspur');
select * from (
select x.*
,min(date1) over(partition by show) as min_date_rnk
from t x
)y
order by min_date_rnk,show,date1
July 18, 2018 at 3:44 am
Dear SSC Eights and SSC Guru
That's certainly cracked the core problem. Many thanks! I now have a table in SQL Server with exactly the right grouping and sort(s).
I need to access the resulting recordset outside of SQL Server for a mail merges (in Word) and the only way I can preserve the sort order is by using SSIS and inserting the data from your table into an excel spreadsheet and connecting the merge template document to that sheet. It works, but seems a bit clunky. Is there any way to connect a word mail merge document direct to a table or view or query that preserves the grouping and sorting. This is required because the Word merge template is grouping the records in the same way as in your new table. I can live without but it would be good to remove the intervening SSIS Data flow task if at all possible.
Thank you in advance
July 18, 2018 at 10:44 am
I didn't have any data to test with, but I'm reasonably sure my sort order is correct. As I think you've since discovered, the rows get out of order outside of SQL.
We could add a sort_order column to the cte that will "tell" later tasks how the data should be sorted:
;WITH cte_EarliestShowdates1 AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY EarliestShowdate1, Show) AS sort_order
FROM (
SELECT Show, MIN(date1) AS EarliestShowdate1
FROM dbo.t
GROUP BY Show
) AS derived
)
SELECT TN.*
FROM dbo.t TN
INNER JOIN cte_EarliestShowdates1 CESD ON CESD.Show = TN.Show
ORDER BY CESD.sort_order, date1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 18, 2018 at 12:29 pm
john-997885 - Wednesday, July 18, 2018 3:44 AMDear SSC Eights and SSC Guru
That's certainly cracked the core problem. Many thanks! I now have a table in SQL Server with exactly the right grouping and sort(s).
I need to access the resulting recordset outside of SQL Server for a mail merges (in Word) and the only way I can preserve the sort order is by using SSIS and inserting the data from your table into an excel spreadsheet and connecting the merge template document to that sheet. It works, but seems a bit clunky. Is there any way to connect a word mail merge document direct to a table or view or query that preserves the grouping and sorting. This is required because the Word merge template is grouping the records in the same way as in your new table. I can live without but it would be good to remove the intervening SSIS Data flow task if at all possible.
Thank you in advance
You need to define the sort order in Word - after adding the table as the recipient, edit the recipient and you can define the sort and filters from there.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 19, 2018 at 3:23 am
Hi,
I've found no way to fully group and sort the data as I need it to be grouped and sorted once its 'in' Word. This being the reason why the complex initial grouping and sorting has been done in SQL. A standard datasource created from Word does not retain the grouping and sorting and does not appear to be sophisticated enough to re-impose the sorting (the grouping can be handled by the Word merge template)... or at least I haven't managed to achieve this. Certainly however an SSIS package that deposits the data into an excel workbook to which the Word merge template is connected does retain the full group/sort from the SQL Server table.
Many thanks
July 19, 2018 at 1:01 pm
john-997885 - Thursday, July 19, 2018 3:23 AMHi,
I've found no way to fully group and sort the data as I need it to be grouped and sorted once its 'in' Word. This being the reason why the complex initial grouping and sorting has been done in SQL. A standard datasource created from Word does not retain the grouping and sorting and does not appear to be sophisticated enough to re-impose the sorting (the grouping can be handled by the Word merge template)... or at least I haven't managed to achieve this. Certainly however an SSIS package that deposits the data into an excel workbook to which the Word merge template is connected does retain the full group/sort from the SQL Server table.Many thanks
Your problem may be that your data source needs to be a pass-through query that returns the data in the proper order. As you haven't shared the nitty gritty details on HOW you created your data source in Word, we can only speculate as to the source of the problem. Word mail merges have to sort on values that are present in the labels, so when you need sorted labels, you just need to "white out" the unneeded for the label field by making the font color white (or the same exact color as the labels). That way, you can get the labels in the sorted order, but not have any printed evidence of the sorting values.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply