Forum Replies Created

Viewing 15 posts - 1 through 15 (of 1,438 total)

  • Reply To: How to append data to the previous row

    Jeff Moden wrote:

    Can you post the actual question that you asked it?  I'm curious as to how much it needed to know.

    Literally all of the original question starting with "I have...

  • Reply To: How to append data to the previous row

    Just for fun I tried cutting and pasting the original question into ChatGPT, this is what it came up with. As far as I can tell it works.

    You can use...

  • Reply To: Exporting from SQL to XML issue

    Something like this?

    WITH GroupedNational_Identity AS (
    SELECT uniqueid, natid_type, STRING_AGG(natid_num,'|') AS natid_num, active, individual_list_id_fk
    FROM national_identity_test
    GROUP BY uniqueid, natid_type, active, individual_list_id_fk
    )
    SELECT 'CNIC' as ID_TYPE_01,
    MAX(CASE WHEN NatID_Type = 'CNIC'...
  • Reply To: Exporting from SQL to XML issue

    Use ISNULL

    SELECT ISNULL(
    (SELECT 'CNIC' as ID_TYPE_01,
    MAX(CASE WHEN NatID_Type = 'CNIC' THEN NatID_Num END) AS ID_NUMBER_01,
    'PASSPORT' as ID_TYPE_02,
    ...
  • Reply To: Exporting from SQL to XML issue

    This should work for you

    SELECT 'CNIC' as ID_TYPE_01,
    MAX(CASE WHEN NatID_Type = 'CNIC' THEN NatID_Num END) AS ID_NUMBER_01,
    'PASSPORT'...
  • Reply To: Tsql Query xml columns by nodes

    Duplicate deleted

    • This reply was modified 1 year, 12 months ago by  Mark Cowne.
  • Reply To: Tsql Query xml columns by nodes

    Try this

    s.PO.value('(/UserArea/Property/NameValue[@name="ManufacturerPart"])[1]', 'nvarchar(50)') as MFG
  • Reply To: Query with a twist having pivot requirement

    I'm not really clear about how the data fits together, but this query matches your expected results.

    This is for a fixed number of approaches, if you want this to be...

  • Reply To: Problem extracting Data from JSON string

    This should handle multiple channels

    SELECT device.DeviceSerialNumber,Readings.ChannelId,Readings.DataHashDto,
    channel_list.Si,channel_list.Raw,channel_list.Conversion,channel_list.TimeStamp
    FROM OPENJSON(@json)
    WITH
    (
    ChannelReadings NVARCHAR(MAX) '$.ChannelReadings' AS JSON,
    DeviceSerialNumber NVARCHAR(100) '$.DeviceSerialNumber'
    ) AS device
    OUTER APPLY OPENJSON(device.ChannelReadings)
    WITH
    (
    ChannelId INT '$.ChannelId',
    DataHashDto NVARCHAR(MAX)...
  • Reply To: How to get record based on nearest date in SQL

    Just remove the 'order by'

    select t.* from (
    select a.Dept,SUM(t.Amount) AS Amount
    from TableA a
    outer apply(select top 1 b.Amount
    ...
  • Reply To: How to get record based on nearest date in SQL

    Try this

    select a.Dept,SUM(t.Amount) AS Amount
    from TableA a
    outer apply(select top 1 b.Amount
    from TableB b
    where b.CustNo =...
  • Reply To: How to get record based on nearest date in SQL

    select a.CustNo,t.Amount
    from TableA a
    outer apply(select top 1 b.Amount
    from TableB b
    where b.CustNo = a.CustNo
    order by abs(datediff(day,a.[Date],b.[Date])))...
  • Reply To: Json extract

    declare @mytable table(mydata nvarchar(max));

    insert into @mytable(mydata)
    values(N'
    {
    "_Journals": [
    {
    "SourceType": 18,
    "JournalID": "sdfdsf-fdfs-dfdf-dfdfd-dfdsfsfdd",
    "SourceID": "sdfsdf-sdfds-sdf-dfd-sdfsdf",
    "JournalLines": [
    {
    "AccountType": 3,
    "JournalLineID": "sdfsdfsd-sdfsdf-sdfsd-sdfsdf-sdfsdf",
    "TaxName": "Some tax name",
    "TrackingCategories": []
    },
    {
    "AccountType": 3,
    "JournalLineID": "sdfsdfsdf-sdfsdf-sdf-sdf-sdf",
    "TaxName": "text",
    "TrackingCategories": []
    }
    ]
    },
    {
    "SourceType": 18,
    "JournalID": "sdfsd-sdfd-sdfd-sdf-dfdsfd",
    "SourceID": "61da35a7-4f8c-4d3a-9a18-e531734b419c",
    "JournalLines": [
    {
    "AccountType": 3,
    "JournalLineID": "sdfdsf-dsfds-sdf-sdfsd-sdfd",
    "TaxName":...
  • Reply To: SQL XML help

    This, I think

    SELECT 'Class1' AS "@ClassName",
    'String' AS "ExternalId/@TYPE",
    AccountNumber AS "ExternalId"
    FROM #Account
    FOR XML PATH('Element'),TYPE;
  • Reply To: Query for abnormal differences / find ouliers

    Try this

    with cte as (
    select AccountId,Values_1,Time_1,Designation,
    lag(Values_1) over(partition by AccountId order by Time_1) as PrevValues_1
    from DataTable
    )
    select AccountId,Values_1,PrevValues_1,Time_1,Designation,abs(Values_1 - PrevValues_1) as Diff
    from...

Viewing 15 posts - 1 through 15 (of 1,438 total)