Documentation Generator | DAX

SQL Script for Weekly Sales Total

This SQL script fetches the total sales for each week based on sales order information.


Empty image or helper icon

This Query related with thread "SQL Weekly Sales Summary; OVERVIEW: This SQL code extract demonstrate..."

Prompt

SELECT
    DATE_TRUNC('week', soh.orderdate) AS week_start_date,
    SUM(sod.orderqty * sod.unitprice) AS total_sales
FROM
    {{ Table("salesorderheader") }} soh
JOIN
    {{ Table("salesorderdetail") }} sod ON soh.salesorderid = sod.salesorderid
GROUP BY
    week_start_date
ORDER BY
    week_start_date;

Answer

Documentation

This SQL script is used to fetch the total sales for each week based on sales order information.

Query Explanation

SELECT
    DATE_TRUNC('week', soh.orderdate) AS week_start_date,
    SUM(sod.orderqty * sod.unitprice) AS total_sales
FROM
    {{ Table("salesorderheader") }} soh -- Table Alias
JOIN
    {{ Table("salesorderdetail") }} sod ON soh.salesorderid = sod.salesorderid -- Joining on salesorderid
GROUP BY
    week_start_date --Grouping results by week_start_date
ORDER BY
    week_start_date; --Ordering results by week_start_date
  1. The query is selecting data and performing computations to aggregate the sales information
  2. The DATE_TRUNC('week', soh.orderdate) function is used to truncate the order date to the beginning of the week. This essentially gives us the week start date for each record.
  3. The SUM(sod.orderqty * sod.unitprice) operation is calculating the total sales for each week. It does this by multiplying the order quantity (orderqty) by the unit price (unitprice) for each record, and then summing these results.
  4. The FROM {{ Table("salesorderheader") }} soh statement is selecting from the ECMAScript (no SQL standard for table defining as functions known) salesorderheader table with an alias soh for further reference in the query.
  5. The JOIN {{ Table("salesorderdetail") }} sod ON soh.salesorderid = sod.salesorderid statement is joining the salesorderdetail table with alias sod with the salesorderheader table based on the salesorderid. This links together related records from the two tables.
  6. The GROUP BY week_start_date clause is grouping the result set by the start date of the week.
  7. Finally, the ORDER BY week_start_date clause is ordering the result set in ascending order by the start date of the week.

For deeper knowledge of SQL query creation and optimization, you might find relevant the SQL courses on the Enterprise DNA Platform.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

This SQL script calculates the total sales for each week by joining the salesorderheader and salesorderdetail tables and grouping the results by the start date of the week. It uses the DATE_TRUNC function to truncate the order date to the beginning of the week and performs the SUM operation to calculate the total sales. The script is structured with clear explanations and can be used as a reference for creating similar queries. For further learning and optimization of SQL query creation, you can explore the SQL courses available on the Enterprise DNA Platform.

More Documentation Generators
Apache Flink Documentation GeneratorApache Pig Documentation GeneratorAzure Data Factory Documentation GeneratorC/C++ Documentation GeneratorCouchDB Documentation GeneratorDAX Documentation GeneratorExcel Documentation GeneratorFirebase Documentation GeneratorGoogle BigQuery Documentation GeneratorGoogle Sheets Documentation GeneratorGraphQL Documentation GeneratorHive Documentation GeneratorJava Documentation GeneratorJavaScript Documentation GeneratorJulia Documentation GeneratorLua Documentation GeneratorM (Power Query) Documentation GeneratorMATLAB Documentation GeneratorMongoDB Documentation GeneratorOracle Documentation GeneratorPostgreSQL Documentation GeneratorPower BI Documentation GeneratorPython Documentation GeneratorR Documentation GeneratorRedis Documentation GeneratorRegex Documentation GeneratorRuby Documentation GeneratorSAS Documentation GeneratorScala Documentation GeneratorShell Documentation GeneratorSPSS Documentation GeneratorSQL Documentation GeneratorSQLite Documentation GeneratorStata Documentation GeneratorTableau Documentation GeneratorVBA Documentation Generator