U-SQL to T-SQL conversion

Transact-SQL
1

Hello Community,

I have the following table

CREATE TABLE SFBI.dbo.Fee_Reporting_Month (
["Record_Sequence_Number"] VARCHAR(50) NULL
,["Source_Fee_Item_ID"] VARCHAR(50) NULL
,["Reporting_Month"] VARCHAR(50) NULL
,["Unbilled_Reporting_Month"] VARCHAR(50) NULL
,["Revision_Date"] VARCHAR(50) NULL
) ON [PRIMARY]
GO

I'm trying to convert the following simple U-SQL to T-SQL

SELECT 
	COUNT(*) AS returncode
FROM
	@output
         WHERE Record_Sequence_Number ==  "" OR Source_Fee_Item_ID  == ""  OR 
          (Reporting_Month == "" AND Unbilled_Reporting_Month == "" ) ;

The data is a follows:

"Record_Sequence_Number","Source_Fee_Item_ID","Reporting_Month","Unbilled_Reporting_Month","Revision_Date"
"2","H3MIG144405712","201601","","2019-04-05 10:53:13"
"1","","201601","","2019-04-05 10:53:13"

Can someone please help with the conversion?

Cheers

Carlton

2

I figured it out guys

SELECT
  COUNT(*) AS returncode
FROM dbo.Fee_Reporting_Month
WHERE Fee_Reporting_Month.["Record_Sequence_Number"] = '""'
OR Fee_Reporting_Month.["Source_Fee_Item_ID"] = '""'
OR Fee_Reporting_Month.["Reporting_Month"] = '201601'
AND Fee_Reporting_Month.["Unbilled_Reporting_Month"] = '""'
3

@Standardize_MSS_Account_Address =
COMBINE @data AS record WITH @keywordRules AS keywordRule
ON record.Country_Join == keywordRule.Country
AND record.[PartitionID] == keywordRule.[PartitionID]
PRODUCE RowNumber string,
FieldName string,
OriginalValue string,
Value string,
IsDummy bool
USING new standardization.Implementations.
ReplacementCombiner(@"{'FieldName':'Address','Domain':'Address','KeywordRules':['Noise','Transliteration','SpecialCharacter']}");

4

can any on help me to convert this u-sql to t-sql query
@data and @keywordRules are the declared tables

5

Bing Copilot produced the following:

-- Assuming you have a table named @data with columns: Country_Join, [PartitionID], and other relevant fields
-- Also, you have a table named @keywordRules with columns: Country, [PartitionID], and other relevant fields

WITH StandardizedAddresses AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber,
        'Address' AS FieldName,
        OriginalValue AS Value,
        CASE
            WHEN OriginalValue IS NULL THEN 1
            ELSE 0
        END AS IsDummy
    FROM @data AS record
    JOIN @keywordRules AS keywordRule
        ON record.Country_Join = keywordRule.Country
        AND record.[PartitionID] = keywordRule.[PartitionID]
    WHERE keywordRule.Domain = 'Address'
        AND keywordRule.KeywordRules IN ('Noise', 'Transliteration', 'SpecialCharacter')
)

-- Now you can use the StandardizedAddresses CTE in your subsequent queries
SELECT *
FROM StandardizedAddresses;
6

Thanks for this query

7

thanks @Ifor
great instructions