Forum Replies Created

Viewing 15 posts - 361 through 375 (of 7,626 total)

  • Reply To: Splitting delimited values across multiple columns on same row.

    It's just another expression per value, not statement.  You could pre-code 10 or even 20 values without a lot of trouble:

    SELECT ResourceType, CustomerID, DOB,
       ...

    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".

    October 11, 2023 at 3:34 pm

    #4305987

  • Reply To: Can LEAD and LAG skip rows or can we add a where clause?

    I'm not sure I fully understand the requirements.

    Do you need the first and last [activityId] = 0 / IN (0, 1) in the partition regardless, or does some other row...

    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".

    October 11, 2023 at 3:17 pm

    #4305977

  • Reply To: How to get 2nd max value if 1st max is null

     

    WITH cteRowNum(GroupName,AgentName,AgentEmail,

    TicketsCompleted, RowNums) AS (

    SELECT GroupName,AgentName,AgentEmail,TicketsCompleted,

    DENSE_RANK() OVER(PARTITION BY GroupName ORDER BY TicketsCompleted DESC) AS RowNums

    FROM Table

    WHERE AgentName IS NOT NULL

    )

    SELECT cteRowNum.GroupName,cterowNum.AgentName,cteRowNum.AgentEmail, cteRowNum.TicketsCompleted

    FROM cteRowNum

    WHERE cteRowNum.RowNums = 1;

    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".

    October 9, 2023 at 2:55 pm

    #4304511

  • Reply To: Select a distinct list of values from tables, return the dataset

    I assume you could have a Function2 and then data for it.  If so, there needs to be something to order the rows: an identity column, a datetime, etc..  Do...

    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".

    October 6, 2023 at 9:24 pm

    #4302997

  • Reply To: Reorganising field in a table

    Perhaps you could create a view with the columns in your (client's) preferred order?  It it was a full view, it should be updatable and therefore the view name could...

    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".

    October 4, 2023 at 4:19 pm

    #4301393

  • Reply To: I can query one by one. Need help how to become Single Query 

    SELECT
        SUM(CASE WHEN HowManyDays <= 30 THEN thisAmt ELSE 0 END) AS aging_0_30,
        SUM(CASE WHEN HowManyDays >= 31 AND HowManyDays <=...

    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".

    October 3, 2023 at 8:27 pm

    #4300756

  • Reply To: Baking up and restores VLDBs

    (1) Use differentials rather than full backups for most backups.  For example, daily diffs and only, say, weekly full backups.

    (2) If you're not already using backup compression, and particularly if...

    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".

    October 3, 2023 at 2:22 pm

    #4300544

  • Reply To: Convert common Separated number to # table

    The value needs to be a string:

    STRING_SPLIT('1008,1009,1011,1014',',')

    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".

    October 2, 2023 at 4:13 pm

    #4299783

  • Reply To: ASCII value of certain column is null

     

    A NULL value should not cause an error, ASCII(NULL) should just return NULL.

    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".

    October 1, 2023 at 4:05 pm

    #4299133

  • Reply To: get week number of month

    How do you define "week number"?  Are the first 7 days of the month "week 1", or is it based on specific day of the week?  For example, all weeks...

    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".

    September 27, 2023 at 8:22 pm

    #4296441

  • Reply To: High tempdb usage alternatives

    Increase the size of  your tempdb files.

    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".

    September 27, 2023 at 8:20 pm

    #4296439

  • Reply To: Hello i am new to Sql and idk why i get this error and also not getting results.

    Inside that same db, run this command:

    EXEC sys.sp_help tblstudent

    or this:

    SELECT * FROM sys.columns WHERE name = 'tblstudent'

    Either of those should give you info about the table to compare to the...

    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".

    September 25, 2023 at 6:04 pm

    #4293844

  • Reply To: Hello i am new to Sql and idk why i get this error and also not getting results.

    "tblstudent" already exists -- with a different number of columns -- so the create table statement is failing, but the insert still tries to execute.

    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".

    September 25, 2023 at 5:37 pm

    #4293838

  • Reply To: table with varcahar(max)

    sqlguy80 wrote:

    3409177 rows, of course only way we query is using logid ,

    If, as you say, you are querying by a single, unique PK value, the lookup should be quick...

    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".

    September 24, 2023 at 2:51 am

    #4292829

  • Reply To: Next visit date after discharge

    I'll take a look at it a bit later today.

    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".

    September 19, 2023 at 3:03 pm

    #4289696

Viewing 15 posts - 361 through 375 (of 7,626 total)