Details

Geschrieben von: Thorsten Böing

Kategorie: Power BI - MDX & SQL

Veröffentlicht: 20. Dezember 2022

Zugriffe: 94

• SQL
• substring
• datetime

[TIMESTAMP] = TRY_CAST(substring([TIMESTAMP], 1, 19) as datetime)

Details

Geschrieben von: Thorsten Böing

Kategorie: Power BI - MDX & SQL

Veröffentlicht: 20. Dezember 2022

Zugriffe: 122

• Datum
• SQL
• Format
• Convert

ppedv Team Blog | SQL – Datum, Format() und Convert()

Details

Geschrieben von: Thorsten Böing

Kategorie: Power BI - MDX & SQL

Veröffentlicht: 20. Dezember 2022

Zugriffe: 112

• SQL
• Year
• GROUP
• Month
• SUM
• COUNT
• MSEG

SELECT [SourceSystem]
          ,[Client]
          ,[MaterialDocumentYear]
          ,[MovementType]
          ,[MaterialId]
          ,[PlantId]
          ,[StorageLocationId]
          ,[CurrencyId]
          ,[BaseUnitOfMeasure]
          ,Year = Year([DocumentPostingDate])
          ,Month = Month([DocumentPostingDate])
          , #Pos = count([MaterialDocumentId])
          , Quantity = sum([Quantity])
          , Value = sum([AmountLocalCurrency])
FROM [VartaDataWarehouse].[fct].[MaterialDocumentSegment]
WHERE [SourceSystem] = 'M' and [StorageLocationId] <> '' -- and [DocumentPostingDate] >= '2021-01-01'
GROUP BY [SourceSystem]
          ,[Client]
          ,[MaterialDocumentYear]
          ,[MovementType]
          ,[MaterialId]
          ,[PlantId]
          ,[StorageLocationId]
          ,[CurrencyId]
          ,[BaseUnitOfMeasure]
          ,Year([DocumentPostingDate])
          ,Month([DocumentPostingDate])

Details

Geschrieben von: Thorsten Böing

Kategorie: Power BI - MDX & SQL

Veröffentlicht: 20. Dezember 2022

Zugriffe: 125

• SQL
• ISNULL
• CAST
• CONCAT

ISNULL(DocumentPostingDate, CAST(CONCAT(MaterialDocumentYear, '-12-31') AS Date)) AS DocumentPostingDate

SELECT [SourceSystem]
          ,[Client],[MaterialDocumentId],[MaterialDocumentYear],[Line],[MovementType],[MaterialId],[PlantId],[StorageLocationId],[BatchNumber],[StockTypeId],[SpecialStock]
          ,[SupplierId],[CustomerId],[SalesOrderNumberId],[SalesOrderItem],[DeliveryScheduleForSalesOrder],[DebitCredit],[CurrencyId],[AmountLocalCurrency],[ValuationType]
          ,[Quantity],[BaseUnitOfMeasure],[PurchasingDocumentId],[PurchasingDocumentItem],[ReferenceDocumentId],[ReferenceDocumentItem],[DeliveryCompletedIndicator]
          ,[ItemText],[EquipmentNumber],[ShipToPartyId],[UnloadingPoint],[CostCenterId],[ProjectNumberOld],[OrderNumber],[CompanyId],[ReceivingIssuingMaterialId]
          ,[ReceivingIssuingPlantId],[ReceivingIssuingStorageLocationId],[ReceivingIssuingBatch],[ValuationTypeOfTransferBatch],[SpecialStockIndicatorForPhysicalStockTransfer]
          ,[MovementIndicatorId],[MovementTypeForWarehouseManagement],[GLAccountId]

          ,ISNULL(DocumentPostingDate, CAST(CONCAT(MaterialDocumentYear, '-12-31') AS Date)) AS DocumentPostingDate

          ,[DeliveryId],[DeliveryItem],[StgCreateDate],[StgChangeDate]
FROM [VartaDataWarehouse].[fct].[MaterialDocumentSegment]
WHERE SourceSystem = 'M'