Forum Discussion
Calculate Latest Transaction Date Time
Need the correct way to Calculate LastTransaction Date Time
--What i have written, Time also should be added to date.
Showng wrong Value
Expected Result
--Create Table Scripts
--USE DB
GO
Create Schema PowerBiTest
DROP TABLE IF EXISTS PowerBiTest.D_SalesHeader
DROP TABLE IF EXISTS PowerBiTest.D_Store
DROP TABLE IF EXISTS PowerBiTest.D_Date
DROP TABLE IF EXISTS PowerBiTest.D_Customer
DROP TABLE IF EXISTS PowerBiTest.F_Sales
CREATE TABLE PowerBiTest.D_SalesHeader
(
BK_HeaderID INT
,SalesTransactionDate Date
,SalesTrnsactionTime Time
)
CREATE TABLE PowerBiTest.D_Store
(
BK_StoreName Varchar(100)
)
CREATE TABLE PowerBiTest.D_Date
(
BK_DATE Date
)
CREATE TABLE PowerBiTest.D_Customer
(
BK_CustName Varchar(200)
)
CREATE TABLE PowerBiTest.F_Sales
(
FK_HeaderID INT
,FK_StoreID VARCHAR(100)
,FK_DateID DATE
,FK_CustID Varchar(200)
,LineId INT
,SaleAmount MONEY
)
INSERT INTO PowerBiTest.D_SalesHeader(BK_HeaderID,SalesTransactionDate,SalesTrnsactionTime)
VALUES(101,'20231201','10:44:57.6133333'),
(102,'20231201','11:10:15.4115111'),
(103,'20231201','13:22:45.1211000'),
(104,'20231202','11:00:00.4115111'),
(105,'20231202','15:15:00.2115111'),
(106,'20231203','17:01:00.3312444')
INSERT INTO PowerBiTest.D_Store(BK_StoreName)
VALUES('HYD'),('BLR'),('CHN')
INSERT INTO PowerBiTest.D_Customer(BK_CustName)
VALUES('AAA'),('BBB'),('CCC'),('DDD'),('EEE')
INSERT INTO PowerBiTest.D_Date(BK_DATE)
VALUES('20231201'),('20231202'),('20231203'),('20231204'),('20231205')
INSERT INTO PowerBiTest.F_Sales
VALUES(101,'HYD','20231201','AAA',1,10.00),
(101,'HYD','20231201','AAA',2,20.00),
(103,'HYD','20231201','BBB',1,25.00),
(104,'BLR','20231202','BBB',1,45.00),
(105,'CHN','20231202','CCC',1,45.00),
(106,'HYD','20231203','AAA',1,50.00)
1 Reply
- lbendlin
Super User
You are trying to "filter up" from the sales fact table into the sales header dimension table. It would be much simpler to add the transaction timestamp to the fact table.
see attached