SQL query to extract primary address from CUSTTABLE

The following query can be used to extract the primary address from the CUSTTABLE table:

 select CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS  
 from CUSTTABLE  
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY  
 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.PRIMARYADDRESSLOCATION  
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID  
 Order by CUSTTABLE.ACCOUNTNUM  

SQL query to extract financial dimension value from CUSTTABLE

The following SQL query can be used to extract a financial dimension value from the CUSTTABLE table:

 select CustTable.ACCOUNTNUM, SubscriberType.DISPLAYVALUE as SubscriberType  
 from CustTable  
 left outer join DIMENSIONATTRIBUTEVALUESETITEM AS SubscriberType ON SubscriberType.DIMENSIONATTRIBUTEVALUESET = CUSTTABLE.DEFAULTDIMENSION  
 left outer join DIMENSIONATTRIBUTEVALUE ON DIMENSIONATTRIBUTEVALUE.RECID = SubscriberType.DIMENSIONATTRIBUTEVALUE  
 left outer join DIMENSIONATTRIBUTE ON DIMENSIONATTRIBUTE.RECID = DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE and DIMENSIONATTRIBUTE.NAME = 'SubscriberType'  

Deleting duplicate records via SQL

 WITH DUP_BARCODE (ITEMBARCODE, DUPCOUNT) AS  
 (SELECT ITEMBARCODE, COUNT(RECID) AS DUPCOUNT  
  FROM INVENTITEMBARCODE   
  GROUP BY ITEMBARCODE  
  HAVING COUNT(RECID) > 1)  
    
  DELETE FROM INVENTITEMBARCODE  
  FROM INVENTITEMBARCODE AS I1  
  JOIN DUP_BARCODE ON I1.ITEMBARCODE = DUP_BARCODE.ITEMBARCODE  
  WHERE I1.RECID != (SELECT TOP 1 RECID FROM INVENTITEMBARCODE AS I2 WHERE I2.ITEMBARCODE = I1.ITEMBARCODE ORDER BY I1.CREATEDDATETIME ASC)  

SELECTING latest INVENTRANS record by ItemId and InventDimId

On the project I'm currently working on we had a request to produce a list of the latest INVENTTRANS record per Item and InventDimId. After a little bit of work I came up with the following SQL query.

 SELECT DISTINCT INV1.ITEMID, INV1.INVENTDIMID, INV2.DATEFINANCIAL, INV2.QTY, INV2.COSTAMOUNTPOSTED FROM INVENTTRANS AS INV1  
 JOIN INVENTTRANS INV2 ON INV2.ITEMID = INV1.ITEMID and INV2.INVENTDIMID = INV1.INVENTDIMID  
 WHERE INV2.RECID =  
 (SELECT TOP 1 RECID FROM INVENTTRANS WHERE ITEMID = INV2.ITEMID and INVENTDIMID = INV2.INVENTDIMID ORDER BY DATEFINANCIAL DESC, CREATEDDATETIME DESC)  

Handling AX UTC dates in SQL Server

SELECT *, DATEADD(hour,DATEDIFF(hour,GETUTCDATE(),GETDATE()),MODIFIEDDATETIME) As ModifiedDateTime from SYSRECORDLEVELSECURITY