Please help contribute to the Reddit categorization project here

    LeoCurtss

    + friends - friends
    7,100 link karma
    391 comment karma
    send message redditor for

    [–] Looking for SQL training LeoCurtss 3 points ago in SQL

    Wise Owl on YouTube is great for learning SQL Server.

    Here's a beginner playlist (might cover what you already know): https://www.youtube.com/watch?v=2-1XQHAgDsM&list=PL6EDEB03D20332309

    Here's a playlist with some more advanced concepts: https://www.youtube.com/watch?v=fjNsRV4zLdc&list=PLNIs-AWhQzcleQWADpUgriRxebMkMmi4H

    He also has tutorials on SSIS, SSRS, and C#--all extremely useful skills in the SQL Server world.

    [–] Adding a new column with data from a join LeoCurtss 4 points ago in SQL

    The ALTER and UPDATE statements probably have to run in separate batches. Try adding GO (or a semicolon if you're using MySQL) before UPDATE.

    [–] How to import data from a website LeoCurtss 4 points ago in SQL

    Python or another programming language. Web scraping cannot be accomplished with SQL alone.

    [–] Why isn’t this working?? LeoCurtss 1 points ago in SQL

    GROUP_CONCAT is an amazing MySQL-exclusive function that can convert multiple rows from this:

    A
    B
    C
    

    To this:

    A,B,C
    

    [–] Pull 1% of available records with row value LeoCurtss 2 points ago * (lasted edited 2 months ago) in SQL

    Off the top of my head, an easy way to do it would be dumping your data into a second temp table and creating your own row numbers based on a random order.

    Since you're using SQL Server, you could use ROW_NUMBER():

    SELECT  User
        , ROW_NUMBER() OVER (PARTITION BY User ORDER BY Rand()) AS RandomRowID
    INTO #TempTable2
    FROM YourTable
    

    This will create new row numbers for each user in a random order. Then use the same code as above, but replace Row_ID with the new RandomRowID in the WHERE clause.

    You'll want to generate the new random row numbers in a temp table, CTE, or sub-query. ROW_NUMBER() is a window function, and as such cannot be referenced in a WHERE or JOIN predicate.

    [–] Help with a query LeoCurtss 1 points ago in SQL

    Union-ing two queries and using LEFT() function in the WHERE condition on both hardly seems more performant than a single query with a couple of inline functions. But I get where you're coming from.

    Different strokes for different folks, I guess.

    [–] Help with a query LeoCurtss 2 points ago in SQL

    This is a static solution and tbh, kinda clunky. Why not just use one query that would handle both situations? (no unions or where clause needed)

    MySQL:

    SELECT LEFT('xx-xxxxx',LOCATE('-','xx-xxxxx') - 1)
           , RIGHT('xx-xxxxx',LOCATE('-','xx-xxxxx') + 2)
    

    Replace the 'xx-xxxxx's with the column name and Bob's your uncle.

    [–] Help with a query LeoCurtss 1 points ago in SQL

    You could SUBSTRING(), LEFT(), RIGHT(), and CHARINDEX() functions to do this very easily.

    Here is a good write up on this:
    https://social.technet.microsoft.com/wiki/contents/articles/17948.t-sql-right-left-substring-and-charindex-functions.aspx

    This is assuming you're using MS SQL Server.

    [–] Pull 1% of available records with row value LeoCurtss 5 points ago * (lasted edited 2 months ago) in SQL

    You could use a CTE or temp table to determine the total row count for each user. Divide that value by 100 and you have 1%.

    WITH TotalRows AS
    (
    SELECT  User  --Whatever key is used (UserID, etc.)
            , COUNT(Row_ID) AS TotalRecords --You could use MAX(), too
    FROM    YourTable
    GROUP BY User
    )
    
    SELECT  YT.User
            --Whatever other columns you want to include
    FROM    YourTable YT
            JOIN TotalRows AS TR
                ON YT.User = TR.User --Whatever key is used (UserID, etc.)
    WHERE    YT.Row_ID <= CEILING(TR.TotalRecords / 100.0)
                        --CEILING rounds up to the next whole number
    

    EDIT: You could also use EXISTS in the WHERE clause in lieu of joining the CTE

    Also, this implementation will always return the first Row_IDs. If the rows need to be random, you'll have to get a bit more creative :)

    [–] Weird situation with empty decimal columns LeoCurtss 1 points ago * (lasted edited 2 months ago) in SQL

    What is the data type for the column? Is it a proper decimal column or something like varchar(255)?
    EDIT: Glad to see you found an answer.

    [–] Weird situation with empty decimal columns LeoCurtss 1 points ago in SQL

    You could try:

    CASE
     WHEN [Column] IS NULL OR [Column] = 0 THEN 0
         ELSE [Column]
    END AS [Column]
    

    [–] 1 * 0 DECIMAL = 1 LeoCurtss 3 points ago in SQL

    You need to set the scale and precision of your decimals.

    [–] Looking to break into the industry as a self taught dev, should I put my answers to SQLZoo on my github? LeoCurtss 2 points ago in SQL

    Sure, but it's likely that potential employers will glance over it since the queries have no practical application.

    I would suggest creating an interesting project that utilizes SQL and solves a problem -- even if it has been solved before. Kind of like a programming assignment. This and contributions to open source projects are the type of things interviewers look for on GitHub.

    Tbh, there is soft divide between software development jobs and database development jobs. I can't say that I've ever interviewed a database developer that included a GitHub link on their application. Maybe 1 or 2 if they had software development experience.

    [–] Customized week number with date LeoCurtss 2 points ago in SQL

    datediff(day, '2018-05-01', order_date) / 7 as weekorder

    datediff(day, '2018-05-01', order_date) / 7 + 1 as weekorder

    [–] Just got granted full access to our SQL Server but don’t know where to start. LeoCurtss 1 points ago * (lasted edited 2 months ago) in SQL

    In SSMS, you can right-click SPs in the object explorer and choose "View Dependencies." You can view objects that depend on the SP and objects that the SP depends on. Could be helpful to get a high-level picture.

    But like the other guy said, you'll just need to spend a lot of time familiarizing yourself with the database. If the stored procedures don't insert/delete/update, pull the query from the SP definition and run the individual statements to see what they produce. It would be best if you have the same DB on a dev instance. From the sound of it, that's probably not available to you.

    Also adding to what has already been said: don't be afraid to ask another dev that is more familiar with the code. It's not a sign of weakness. Don't waste time trying to figure something out when someone has already spent the time to do it.

    I'd wager that you are probably in a position where there is no "senior dev" to help out. If that's the case, this is a great opportunity for you to document your findings.

    What's your comfort level is with T-SQL? Do you have a software development background? Just out of curiosity.

    Edit: the SSMS suggestion is only applicable if you're working with MS SQL.

    [–] [MS SQL] Reporting Store Procedure Nightmare LeoCurtss 2 points ago in SQL

    It sounds like you have a good idea of what issues need to be addressed.

    For learning execution plans, here's a great conference talk/presentation from Brent Ozar to get your feet wet:

    https://youtu.be/uwGCPtga06U

    Good luck!

    [–] [MS SQL] Reporting Store Procedure Nightmare LeoCurtss 3 points ago in SQL

    Complicated queries aside: it is bad practice to have an application or report use a single stored procedure for multiple datasets. Split them up into multiple stored procedures ASAP. I would argue that this would be easier to maintain.

    As far as simplifying the queries goes, I would suggest finding common nested queries and creating views to replace them (if the data is not heavily aggregated). If that's not possible or plausible, convert the larger sub-queries to CTEs to make it more readable.

    If you're still struggling with performance, learn how to read an execution plan and troubleshoot from there. Knowing how to work with temp tables, think in sets, reducing logical reads and table scans, indexing, etc. will take you from pro to super-pro.

    If all else fails and the data doesn't need to be live, you could always run a daily or hourly job to store the results in a table and simply SELECT that table for faster results. But since you're working with parameters, this may not be the best solution.

    [–] What are good query applications for MS SQL Server? LeoCurtss 10 points ago in SQL

    The industry standard for querying and administration for MS SQL is SSMS (SQL Server Management Studio). It's free.

    Microsoft is also developing a modernized alternative to SSMS called Azure Data Studio. It doesn't have all the bells and whistles that SSMS has, but they are slowing rolling out features. I still prefer SSMS to Azure Data Studio.

    [–] Nothing to see here LeoCurtss 21 points ago * (lasted edited 3 months ago) in Simulated

    I agree, it definitely does not behave like realistic sand.

    [–] [SV] [Pics] 50 lbs in 4 months on keto! LeoCurtss 3 points ago in keto

    Congrats! I'm 5'11", so that could be the difference. Plus people's body's are shaped differently. I have very little muscle so that might be part of why my appearance changed so much lol.

    [–] [SV] [Pics] 50 lbs in 4 months on keto! LeoCurtss 3 points ago in keto

    Thanks! No--it's not a ratchet belt but that would be nice! I just had to punch a new hole in the belt. Probably time for a new one...