Forum Replies Created
Viewing 15 posts - 1 through 15 (of 1,439 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...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
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...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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'...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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,
...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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'...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Reply To: Tsql Query xml columns by nodes
Duplicate deleted
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Reply To: Tsql Query xml columns by nodes
Try this
s.PO.value('(/UserArea/Property/NameValue[@name="ManufacturerPart"])[1]', 'nvarchar(50)') as MFG____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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...
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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)...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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
...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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 =...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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])))...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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":...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
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...____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1 through 15 (of 1,439 total)