CONSULTAS_ETL.sql

--------------- PARA LA Dim_employees------------------------------
SELECT EMPLOYEE_ID, CONCAT(FNAMEE,LNAMEE) FULL_NAME FROM EMPLOYEES;


--------------- PARA LA Dim_Customers------------------------------
SELECT CUSTOMER_ID, CONCAT(FNAMEC,LNAMEC) FULL_NAME FROM CUSTOMERS;

--------------- PARA LA Dim_Products------------------------------
SELECT PRODUCT_ID, NAMEP,SIZES.DESCRIPTION SIZE, PRICES.DESCRIPTION PRICE,CATEGORIES.DESCRIPTION CATEGORY FROM PRODUCTS
INNER JOIN SIZES ON SIZES.SIZE_ID = PRODUCTS.SIZE_ID
INNER JOIN PRICES ON PRICES.PRICE_ID = PRODUCTS.PRICE_ID
INNER JOIN CATEGORIES ON CATEGORIES.CATEGORY_ID = PRODUCTS.CATEGORY_ID;


--------------- PARA LA Dim_Tiempo------------------------------
SELECT CONVERT(DATETIME,ORDER_DATE) TIME_ID, 
DATEPART(MONTH, ORDER_DATE) MONTHO,
DATEPART(QUARTER, ORDER_DATE)trimester, CASE WHEN DATEPART(MONTH,ORDER_DATE) between 1 and 6 then 1
WHEN DATEPART(MONTH,ORDER_DATE) between 7 and 12 then 2 
Else 'no existe el semestre' END semester,DATENAME(WEEKDAY,  ORDER_DATE) week_day
FROM ORDERS
GROUP BY ORDERS.ORDER_DATE

--------------- PARA LA Tabla de Hechos_OrdeRS------------------------------
SELECT ORDERS.EMPLOYEE_ID, ORDERS.CUSTOMER_ID, ORDERS.PRODUCT_ID,DATEPART(MONTH,ORDER_DATE),
CONVERT(DATETIME,ORDER_DATE)TIME_ID, SUM(ORDERS.AMOUNT*PRICE.DESCRIPTION) TOTAL_AMOUNT,
COUNT(ORDERS.PRODUCT_ID) QUANTITY_SALE_PRODUCTS,COUNT(ORDERS.CUSTOMER_ID) QUANTITY_CLIENTS FROM ORDERS
INNER JOIN EMPLOYEES ON EMPLOYEES.EMPLOYEE_ID = ORDERS.EMPLOYEE_ID
INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID 
INNER JOIN PRODUCTS ON PRODUCTS.PRODUCT_ID = ORDERS.PRODUCT_ID
INNER JOIN PRICES ON PRICES.PRICE_ID = PRODUCTS.PRODUCT_ID
GROUP BY DATEPART(MONTH,ORDER_DATE),ORDERS.EMPLOYEE_ID, ORDERS.CUSTOMER_ID,ORDERS.PRODUCT_ID,ORDER_DATE