Forum Replies Created

Viewing 15 posts - 16 through 30 (of 1,439 total)

  • Reply To: Finding first record that meets conditions + allowing grouping

    Here's another way

    WITH CTE AS (
     SELECT CityID, MonthID, LowTemp, HiTemp,ROW_NUMBER() OVER(PARTITION BY CityID ORDER BY MonthID) AS rn
     FROM dbo.CityTemperature
     WHERE LowTemp >= 64
    )
    SELECT m.MonthName, c.CityName
    FROM...

    August 31, 2022 at 5:43 pm

    #4082546

  • Reply To: Record with same process id > 1

    Maybe this?

    select [User]
    from mytable
    group by [User]
    having count(ProcessId) > count(distinct ProcessId);

    August 2, 2022 at 11:10 am

    #4071302

  • Reply To: How to give unique number to every different group of numbers ?

    This looks like a 'relational division' problem. Solution here not pretty but should work.

    WITH Grps AS (
    SELECT PartNumber,COUNT(*) AS GroupCount
    FROM #parts
    GROUP BY PartNumber
    ),
    OrderedSrc AS (
    SELECT PartNumber,PartValue,ROW_NUMBER() OVER(PARTITION...

    • This reply was modified 3 years, 2 months ago by Mark Cowne.
    • This reply was modified 3 years, 2 months ago by Mark Cowne.

    July 25, 2022 at 1:22 pm

    #4067863

  • Reply To: Count of word inside cell

    planetmatt wrote:

    Mark Cowne wrote:

    That will match PageDefintions and PageDefintion giving a count of 2 for ID=2

    Then can't you just change the search string to:

    DECLARE @Find VARCHAR(MAX)='PageDefintion>'

    ?

    Yes you could, that would work...

    July 12, 2022 at 3:47 pm

    #4062238

  • Reply To: Count of word inside cell

    planetmatt wrote:

    You don't need all those REPLACE functions.

    You can just do

    DECLARE @Find VARCHAR(MAX)='PageDefintion'  

    SELECT 
    Detail_Form
    ,LEN(@Find) ,(LEN(Detail_Form)-LEN(REPLACE(Detail_Form,@Find,'')))/LEN(@Find)  AS OccurrenceCount  
    FROM dbo.Table1

     

    That will match PageDefintions and...

    July 12, 2022 at 2:47 pm

    #4062186

  • Reply To: Count of word inside cell

    Try this

     

    select ID,detail_form,
          (len(detail_form) - len(replace(replace(replace(detail_form,'<PageDefintion>','<>'),'<PageDefintion/>','</>'),'</PageDefintion>','</>'))) / len('PageDefintion') as PageDefintionCount
    from mytable

    July 12, 2022 at 10:11 am

    #4062036

  • Reply To: How To Flatten JSON Data?

    Maybe this?

    WITH CTE AS (
    SELECT t.id,
           j2.[key] AS field,
       j2.value AS val,
       row_number() over(partition by j2.[key] order...

    July 1, 2022 at 11:11 am

    #4057498

  • Reply To: DENSE_RANK different sequences

    DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number

    • This reply was modified 3 years, 4 months ago by Mark Cowne.
    • This reply was modified 3 years, 4 months ago by Mark Cowne.

    May 19, 2022 at 10:59 am

    #4035525

  • Reply To: Query nested JSON data

    Small tweak to Phils code should do it

    SELECT
        q1.success
    ,   q1.timestamp
    ,   q1.date
    ,   q1.base
    ,   q1.unit
    ,   q2.[key]...

    • This reply was modified 3 years, 5 months ago by Mark Cowne.

    April 27, 2022 at 1:23 pm

    #4023050

  • Reply To: Convert rows to column 

    SELECT id,
           MIN(Game_Time) AS Game_Start,
           MAX(Game_Time) AS Game_END
    FROM Game
    GROUP BY id
    ORDER BY id;

    • This reply was modified 3 years, 5 months ago by Mark Cowne.

    April 12, 2022 at 7:42 am

    #4014401

  • Reply To: Assign Category to Varying Length of Rows 

    SELECT Date,ID,CHAR(ASCII('A') + (ROW_NUMBER() OVER(PARTITION BY Date ORDER BY ID) -1) % 3) AS Grp
    FROM #s
    ORDER BY Date,ID;

    March 18, 2022 at 5:17 pm

    #4001996

  • Reply To: Parsing XML data to table

     

    @shogunSQL, this should work for you.

    SELECT C.x.value('@Id', 'varchar(20)') as Id 
    ,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
    ,C.x.value('title[1]', 'varchar(100)') as Title
    ,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
    FROM @doc.nodes('ROOT/*/*/item') as C(x)

    @jeff - interesting idea,...

    March 1, 2022 at 10:47 am

    #3991027

  • Reply To: Parsing XML data to table

    Another way

     

    SELECT C.x.value('@Id', 'varchar(20)') as Id 
    ,C.x.value('@ChapterName', 'varchar(20)') as ChapterName
    ,C.x.value('concat(local-name(parent::*/parent::*),"\",local-name(parent::*),"\",local-name(.)) ', 'VARCHAR(1000)') AS [Path]
    FROM @doc.nodes('ROOT/*/*/item') as C(x)

    February 25, 2022 at 4:00 pm

    #3989027

  • Reply To: JSON building from configuration table

    Deleted, wrong solution

    • This reply was modified 3 years, 8 months ago by Mark Cowne.
    • This reply was modified 3 years, 8 months ago by Mark Cowne.
    • This reply was modified 3 years, 8 months ago by Mark Cowne.
    • This reply was modified 3 years, 8 months ago by Mark Cowne. Reason: Problems with square brackets
    • This reply was modified 3 years, 8 months ago by Mark Cowne.

    January 20, 2022 at 1:36 pm

    #3975207

  • Reply To: Extract data from XML field

    Not totally clear to me what you're asking for, maybe this?

    with cte as (
    select REF_NO,
           n.x.value('Country[1]','varchar(30)') as Country,
       ...

    October 22, 2021 at 10:49 am

    #3943198

Viewing 15 posts - 16 through 30 (of 1,439 total)