create a sap hana query to answer the question: when was my last sale?
- User
create a sap hana query to answer the question:
when was my last sale?
Assistant
To determine "When was my last sale?" in SAP HANA, you typically look at sales-related documents and their dates. The most common interpretation of a "sale" is a Billing Document (Invoice), as it signifies the final revenue event.
Here's a SAP HANA SQL query using the most relevant standard SAP tables:
Scenario 1: My Last Sale (for a specific customer)
This is the most common interpretation of "my last sale" – you want to know the latest sale date for your specific customer ID.
SELECT VBRK.KUNNR AS "CustomerNumber", MAX(VBRK.FKDAT) AS "LastSaleDate" FROM VBRK AS VBRK -- Billing Document: Header Data WHERE VBRK.MANDT = 'XXX' -- **IMPORTANT: Replace 'XXX' with your SAP Client (e.g., '100', '800')** AND VBRK.KUNNR = 'YYYYYYYYYY' -- **IMPORTANT: Replace 'YYYYYYYYYY' with your specific Customer Number** AND VBRK.FKTYP = 'M' -- 'M' for Invoice. This filters for actual invoices. Other types exist (e.g., 'N' for Credit Memo, 'O' for Debit Memo). -- AND VBRK.FKART IN ('F2', 'G2') -- Optional: Filter for specific billing types if needed (e.g., 'F2' for standard invoice, 'G2' for credit memo) GROUP BY VBRK.KUNNR;Explanation:
VBRK(Billing Document: Header Data): This table stores the header information for all billing documents (invoices, credit memos, debit memos, etc.).MANDT: Client. Always specify your SAP client.KUNNR: Sold-to Party (Customer Number). This is how you identify your customer.FKDAT: Billing Date. This is the key date field that represents when the sale (invoice) was processed.FKTYP: Billing Category. Filtering by'M'ensures you're looking at actual invoices.MAX(VBRK.FKDAT): This aggregate function finds the latest (maximum) billing date for the specified customer.GROUP BY VBRK.KUNNR: While not strictly necessary if you're only querying for one specific customer usingWHERE KUNNR, it's good practice for clarity and would be essential if you remove theWHERE KUNNRclause to find the last sale for multiple customers.
Scenario 2: Last Sale for Each Customer (Overview)
If you want an overview of the last sale date for all your customers, you can remove the specific customer filter:
SELECT VBRK.KUNNR AS "CustomerNumber", MAX(VBRK.FKDAT) AS "LastSaleDate" FROM VBRK AS VBRK -- Billing Document: Header Data WHERE VBRK.MANDT = 'XXX' -- **IMPORTANT: Replace 'XXX' with your SAP Client** AND VBRK.FKTYP = 'M' -- 'M' for Invoice -- AND VBRK.FKART IN ('F2', 'G2') -- Optional: Filter for specific billing types GROUP BY VBRK.KUNNR ORDER BY "LastSaleDate" DESC; -- Order by the latest sale date (globally)Scenario 3: My Last Sales Document (Detailed)
If you want more details about the last actual sales document (Sales Order, not necessarily invoiced yet), you would look at the Sales Order header table:
SELECT TOP 1 VBAK.VBELN AS "SalesOrderNumber", VBAK.ERDAT AS "OrderCreationDate", VBAK.AUDAT AS "DocumentDate", VBAK.KUNNR AS "CustomerNumber", VBAK.AUART AS "SalesOrderType" FROM VBAK AS VBAK -- Sales Document: Header Data WHERE VBAK.MANDT = 'XXX' -- **IMPORTANT: Replace 'XXX' with your SAP Client** AND VBAK.KUNNR = 'YYYYYYYYYY' -- **IMPORTANT: Replace 'YYYYYYYYYY' with your specific Customer Number** -- AND VBAK.AUART = 'OR' -- Optional: Filter for standard orders, if needed ORDER BY VBAK.ERDAT DESC; -- Order by creation date descending to get the latestExplanation:
VBAK(Sales Document: Header Data): This table contains the header information for sales orders.ERDAT: Date on which the record was created. This is often the most reliable "last activity" date for an order.AUDAT: Document Date.TOP 1: Limits the result to only the very latest sales order for that customer.
How to run these queries:
You can execute these SQL queries in:
- SAP HANA Studio: Open a SQL console and paste the query.
- SAP HANA Database Explorer (Web-based): Access via your SAP Fiori launchpad or direct URL.
- Any SQL Client: Tools like DBeaver, SQL Workbench/J, or others that can connect to your SAP HANA database.
Remember to replace the placeholder values (
XXXfor Client andYYYYYYYYYYfor Customer Number) with your actual data before executing the query.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.