May 29, 2014 at 8:41 pm
OK, thanks everyone. Luis's is actually a reverse splitter, the way I see it.
A quick overview of splitter options including Jeff Moden's high performance TSQL approach is here http://sqlperformance.com/2012/07/t-sql-queries/split-strings"> http://sqlperformance.com/2012/07/t-sql-queries/split-strings .
Delicious, thanks again for the story. It gave the real life back drop for this that I was hoping for.
May 30, 2014 at 7:17 am
Luis Cazares (5/29/2014)
Sean Lange (5/29/2014)
Here is an example of a real world case for using a splitter. This iTVF will return a string with the proper case. I did not write this and sadly my notes don't have who posted this. I know it came from this site at one point.I wonder why it was easy for me to remember that post. 😀
How do you find this stuff? I remembered the thread and the discussion but couldn't for the life of me remember who was involved and couldn't find it. Well done!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2014 at 7:36 am
Sean Lange (5/30/2014)
Luis Cazares (5/29/2014)
Sean Lange (5/29/2014)
Here is an example of a real world case for using a splitter. This iTVF will return a string with the proper case. I did not write this and sadly my notes don't have who posted this. I know it came from this site at one point.I wonder why it was easy for me to remember that post. 😀
How do you find this stuff? I remembered the thread and the discussion but couldn't for the life of me remember who was involved and couldn't find it. Well done!!!
Luis has a photographic memory;-)
Stuff like that, I put it in my briefcase or add the url to the source thread to a script file that has a descriptive name. Makes it much easier for me to find.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 30, 2014 at 8:05 am
Luis has a photographic memory;-)
Me too...but they stopped making film for this model a long time ago.
Stuff like that, I put it in my briefcase or add the url to the source thread to a script file that has a descriptive name. Makes it much easier for me to find.
Adding the url is a good idea. I will do that. I usually note who provided it but in this case I seem to have missed it. :blush:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2014 at 8:10 am
KoldCoffee (5/29/2014)
OK I'm taking a look at Delicious's first. So, basically you have the equivalent of a build-a-bear business, with nearly limitless variations available though the combination and recombination of a limited set of options. The reporting limitation on front end was overcome by your getting your hands on the flat file and importing it to a table. Reason for your using a 'temp table' less important, but ultimately it sounds like you gave your friend a TVF that she could pass an attribute to (as a parameter) and get returned a top 10 or top 100 table.Great! Thanks for the real life example.
No problem!
Another great use of the splitter was that it enabled her to print a list of all the items she needed to assemble for that day's orders. Her cart software had no way of printing a list of individual products by size, flavor, strength, etc. and quantity that she could prepare in order to fill that day's orders. Previously, she had to print each invoice and fill it individually which was an inefficient use of time.
With a list that looked like this, she was able to work a LOT faster:
Flavor Size Strength Quantity
Lime 12 oz 12.75% 4
Mint 8 oz 9% 10
etc.
The laptop that has this code on it is sitting uncharged on a shelf. I really should dust it off and pull the scripts off there so I can share it. I just need to steel myself for the "holy Christmas, delicious....what is THIS? This stinks!" 😉
May 30, 2014 at 8:13 am
I added that thread to my briefcase because I'm sure it might get handy some time. I use a digital camera for my photographic memory but from time to time someone steals the SD card. That's why I use a backup.
I started using the briefcase (and chrome bookmarks) when I realized that I might change job at any time and I might not be able to collect all the code that I could use.
May 30, 2014 at 8:22 am
Another use is to work with parameters that work as arrays contained in strings.
I know that this can be done using table-valued parameters but some might like the splitter option to avoid some extra work.
Was I clear here? :hehe:
May 30, 2014 at 10:17 am
SQL is delicious (5/30/2014)
KoldCoffee (5/29/2014)
OK I'm taking a look at Delicious's first. So, basically you have the equivalent of a build-a-bear business, with nearly limitless variations available though the combination and recombination of a limited set of options. The reporting limitation on front end was overcome by your getting your hands on the flat file and importing it to a table. Reason for your using a 'temp table' less important, but ultimately it sounds like you gave your friend a TVF that she could pass an attribute to (as a parameter) and get returned a top 10 or top 100 table.Great! Thanks for the real life example.
No problem!
Another great use of the splitter was that it enabled her to print a list of all the items she needed to assemble for that day's orders. Her cart software had no way of printing a list of individual products by size, flavor, strength, etc. and quantity that she could prepare in order to fill that day's orders. Previously, she had to print each invoice and fill it individually which was an inefficient use of time.
With a list that looked like this, she was able to work a LOT faster:
Flavor Size Strength Quantity
Lime 12 oz 12.75% 4
Mint 8 oz 9% 10
etc.
The laptop that has this code on it is sitting uncharged on a shelf. I really should dust it off and pull the scripts off there so I can share it. I just need to steel myself for the "holy Christmas, delicious....what is THIS? This stinks!" 😉
Delicious, First of all, you gained for your friend a superior user experience without costing her the trouble of changing ecommerce platform. I also totally agree it's easier to prepare that day's orders with a list with counts, then to look at each invoice separately and run around like a clerk at McDonalds never knowing what the next customer will order. Even if the software was intended for low volume, I can't think of any small volume for which I wouldn't rather reach into one bin, one time, to retrieve X items, than reach into that bin X times each time an invoice required me to. My hands would be a tangled mess!).
If you do get those scripts out, I'd love to see them and then you'll have them for your own reference!
May 30, 2014 at 10:19 am
Luis Cazares (5/30/2014)
I added that thread to my briefcase because I'm sure it might get handy some time. I use a digital camera for my photographic memory but from time to time someone steals the SD card. That's why I use a backup.I started using the briefcase (and chrome bookmarks) when I realized that I might change job at any time and I might not be able to collect all the code that I could use.
Luis, what's this briefcase you're talking about? I use One Note, but one unfortunate time, after syncing it and leaving a job, I discovered that the sync didn't work and I had lost information.
May 30, 2014 at 10:22 am
KoldCoffee (5/30/2014)
Luis Cazares (5/30/2014)
I added that thread to my briefcase because I'm sure it might get handy some time. I use a digital camera for my photographic memory but from time to time someone steals the SD card. That's why I use a backup.I started using the briefcase (and chrome bookmarks) when I realized that I might change job at any time and I might not be able to collect all the code that I could use.
Luis, what's this briefcase you're talking about? I use One Note, but one unfortunate time, after syncing it and leaving a job, I discovered that the sync didn't work and I had lost information.
Briefcase is part of SSC. You can add any thread to your briefcase if you scroll up to the top of the page. Then way up top you have a link to view your briefcase. This stays with your account on SSC so you can "take it with you". 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2014 at 10:26 am
Cool, will try it. I do come back here to review old posts and sometimes have a hard time finding by my moniker. Thanks.
May 30, 2014 at 10:30 am
Luis Cazares (5/30/2014)
Another use is to work with parameters that work as arrays contained in strings.I know that this can be done using table-valued parameters but some might like the splitter option to avoid some extra work.
Was I clear here? :hehe:
Luis, can you give background 'story' to this? I know what an array, a parameter, and a string are, but 'parameters that work as arrays contained in strings' I can't visualize.
May 30, 2014 at 10:42 am
Let's say that there's an app where you can select multiple items to generate a report. The web developer chooses to send a string as 'Item1,Item3,Item7' (which could be integers, codes or any other thing to identify them). You would need to separate the values to create the report.
CREATE PROCEDURE ItemSplittingSample(
@Items varchar(8000)
)
AS
SELECT d.ItemID,
i.ItemDescription,
SUM(d.Quantity) AS TotalSold
FROM SalesDetails d
JOIN Items i ON d.itemID = i.ItemID
CROSS APPLY dbo.YourFavoriteSplitter( @Items) split
WHERE split.Item = d.ItemID
Now you can call your procedure as:
EXEC ItemSplittingSample '1,3,7'
And you'll have the total sales for those items. I hope that makes it clear.
EDIT: Here's a larger (& better) example. http://www.sqlservercentral.com/articles/T-SQL/63003/
May 30, 2014 at 10:47 am
Hi Luis, thanks. Yes, that's the standard application for a splitter. I got it. I guess I love the scenarios behind the scenes.
Delicious got the Ace on this one but it's become apparent that this is something I'm going to use in the future for some real life situation, I betcha. I see by the way this thread developed it's one of those things you take for granted.
That's excellent to see as well. Thanks for that all of you.
...and I'll read Jeff Moden's article you passed link for. He's encyclopedia Brittanica in treatment of this topic.
May 30, 2014 at 11:50 am
KoldCoffee (5/30/2014)Delicious, First of all, you gained for your friend a superior user experience without costing her the trouble of changing ecommerce platform. I also totally agree it's easier to prepare that day's orders with a list with counts, then to look at each invoice separately and run around like a clerk at McDonalds never knowing what the next customer will order. Even if the software was intended for low volume, I can't think of any small volume for which I wouldn't rather reach into one bin, one time, to retrieve X items, than reach into that bin X times each time an invoice required me to. My hands would be a tangled mess!).
If you do get those scripts out, I'd love to see them and then you'll have them for your own reference!
Aww, thanks; that was the goal, honestly!
I guess what kills me is that since she knows nothing about databases, she didn't appreciate (and still doesn't) how complicated the problem was or what a head-banger it was to solve it. As I was working on it (it took several days to nail it, working an hour or two at a time), she kept asking me what was taking so long. Augh!
I do need to dust off those scripts, if for no other reason than to have them for my own reference and to continuously try to improve upon the solution when I have down-time. I was never happy with the sloppiness of it; I thought it was sloppy, anyway. I'll bookmark this thread and post the scripts when I get hold of them...someday! 😉 Hopefully sooner rather than later.
Viewing 15 posts - 31 through 45 (of 59 total)
You must be logged in to reply to this topic. Login to reply