USE northwind

SELECT * FROM Employees

--CONSULTA PARA OBTENER LA FECHA ACTUAL
SELECT 
	BIRTHDATE, GETDATE() AS 'ACTUAL'
FROM 
	EMPLOYEES

--CONSULTA PARA MOSTRAR ENCABEZADOS

SELECT 
	'EMPLEADOS'= FirstName+' '+ LASTNAME,
	'CUMPLEAÑOS'=BirthDate,
	'EDAD ACTUAL'= DATEDIFF(YY,BIRTHDATE,GETDATE()) 
FROM 
	EMPLOYEES;

--CONSULTA PARA OBTENER EL FILTRO

SELECT 
	AVG(DATEDIFF(YY,BIRTHDATE,GETDATE())) AS 'EDAD'
FROM
	EMPLOYEES

-- SUBCONSULTA QUE MUESTRE LOS EMPLEADOS MENORES A LA EDAD PROMEDIO

SELECT
	'EMPLEADOS' = FIRSTNAME+' '+LASTNAME,
	'CUMPLEAÑOS' = BIRTHDATE,
	'EDAD ACTUAL' = DATEDIFF(YY,BIRTHDATE,GETDATE()),
	'PAIS' = COUNTRY
FROM
	EMPLOYEES
WHERE
	DATEDIFF(YY,BIRTHDATE,GETDATE()) <= (SELECT
	AVG(DATEDIFF(YY,BIRTHDATE,GETDATE())) FROM
	EMPLOYEES)

--OBTENIENDO EL SEXO DEL EMPLEADO

SELECT
	FIRSTNAME,
	LASTNAME,
	TITLEOFCOURTESY,
	'SEXO' = 
			CASE TITLEOFCOURTESY
			WHEN 'Ms.' THEN 'F'
			WHEN 'Mrs.' THEN 'F'
			WHEN 'Mr.' THEN 'M'
			--ELSE 'SIN ASIGNACION' APLICANDO UN FALSO
			WHEN 'Dr.' THEN 'M'
		END
FROM
	Employees


--CONSULTA QUE MUESTRA QUIENES TIENEN MENOR EDAD A LA DE PROMEDIO
SELECT
	'EMPLEADOS' = FIRSTNAME+' '+LASTNAME,
	'CUMPLEAÑOS' = BIRTHDATE,
	'EDAD ACTUAL' = DATEDIFF(YY,BIRTHDATE,GETDATE()),
	'PAIS' = COUNTRY,
	'STATUS' = 
		CASE  
			WHEN DATEDIFF(YY,BIRTHDATE,GETDATE()) <= 58 THEN 'A CONTRATAR'
			WHEN DATEDIFF(YY,BIRTHDATE,GETDATE()) > 58 THEN 'A JUBILAR' 
		END
FROM
	EMPLOYEES
WHERE
	DATEDIFF(YY,BIRTHDATE,GETDATE()) <= (SELECT
	AVG(DATEDIFF(YY,BIRTHDATE,GETDATE())) FROM
	EMPLOYEES)

--CONSULTA CASE CON AVG
SELECT
	'EMPLEADOS' = FIRSTNAME+' '+LASTNAME,
	'CUMPLEAÑOS' = BIRTHDATE,
	'EDAD ACTUAL' = DATEDIFF(YY,BIRTHDATE,GETDATE()),
	'PAIS' = COUNTRY,
	'STATUS' = 
		CASE  
			WHEN DATEDIFF(YY,BIRTHDATE,GETDATE()) <= (SELECT
	AVG(DATEDIFF(YY,BIRTHDATE,GETDATE())) FROM
	EMPLOYEES) THEN 'A CONTRATAR'
			ELSE 'A JUBILAR' 
		END
FROM
	EMPLOYEES
WHERE
	DATEDIFF(YY,BIRTHDATE,GETDATE()) <= (SELECT
	AVG(DATEDIFF(YY,BIRTHDATE,GETDATE())) FROM
	EMPLOYEES)

	SELECT * FROM ORDERS

--CONSULTA DE VENTA TOTALES POR CLIENTE
SELECT C.CUSTOMERID, 'TOTAL' = (SUM(QUANTITY * UNITPRICE))
FROM CUSTOMERS C 
INNER JOIN ORDERS O
ON C.CUSTOMERID = O.CUSTOMERID
INNER JOIN [ORDER DETAILS] OD
ON O.ORDERID = OD.ORDERID
GROUP BY C.CUSTOMERID


/*SELECT 
	QUANTITY,
	UNITPRICE,
	'SUBTOTAL' = UNITPRICE * QUANTITY,
	'TOTAL' = SUM(UNITPRICE * QUANTITY)
FROM
	[ORDER DETAILS] OD*/


--CONSULTA PARA OBTENER LA VENTA TOTAL
SELECT 
	'TOTAL' = SUM(UNITPRICE * QUANTITY)
FROM
	[ORDER DETAILS] OD


-- VENTA TOTAL POR CLIENTE
SELECT 
	COMPANYNAME,
	'TOTAL' = SUM(UNITPRICE * QUANTITY)
FROM
	CUSTOMERS C, ORDERS O, [ORDER DETAILS] OD
WHERE 
	C.CUSTOMERID = O.CUSTOMERID
GROUP BY 
	COMPANYNAME




--CREANDO VISTA DE VENTA TOTAL POR CLIENTES
CREATE VIEW VENTAS_TOTALES_CLIENTES
AS
SELECT 
	COMPANYNAME,
	'TOTAL' = SUM(UNITPRICE * QUANTITY)
FROM
	CUSTOMERS C, ORDERS O, [ORDER DETAILS] OD
WHERE 
	C.CUSTOMERID = O.CUSTOMERID
AND 
	O.ORDERID = OD.ORDERID
GROUP BY 
	COMPANYNAME 



SELECT * FROM
VENTAS_TOTALES_CLIENTES
where companyname = 'hilarion-abastos'



--OBTENIENDO PROMEDIO SOBRE LA VISTA VENTA TOTALES POR CLIENTE
SELECT 
	AVG(TOTAL)
FROM 
	VENTAS_TOTALES_CLIENTES




/*
SELECT 
	C.COMPANYNAME,
	'TOTAL' = AVG(TOTAL)
FROM 
	CUSTOMERS C, VENTAS_TOTALES_CLIENTES V
WHERE
	C.COMPANYNAME = V.COMPANYNAME
AND 
	
GROUP BY 
	C.COMPANYNAME*/




/* CONSULTA PARA PROMEDIO
SELECT 
	COMPANYNAME,
	'TOTAL' = SUM(UNITPRICE * QUANTITY)
FROM
	CUSTOMERS C
INNER JOIN 
	ORDERS O
ON
	C.CUSTOMERID = O.CUSTOMERID
INNER JOIN 
	[Order Details] OD
ON
	O.ORDERID = OD.ORDERID

GROUP BY 
	COMPANYNAME
	*/


-- OBTENIENDO PROMEDIO CON VISTAS
SELECT
	COMPANYNAME, TOTAL
FROM
	VENTAS_TOTALES_CLIENTES
WHERE
	TOTAL >= (SELECT AVG(TOTAL) FROM VENTAS_TOTALES_CLIENTES)
ORDER BY
	TOTAL

/*25/enero/14*/

SELECT 
	companyname, country
from 
	customers
where 
	country = 'germany'


/*creando STORED PROCEDURE busqueda clientes por pais*/

CREATE Proc sp_clintes_pais
	@pais varchar(40)
	As
	Select 
		* 
	from
		customers c
	where
		country = @pais
	;
		
/*Executando mi store*/

EXEC sp_clintes_pais 'germany'
EXEC sp_clintes_pais 'spain'

/*consultar el store original PARA VERIFICAR SU ESTRUCTURA*/

sp_helptext sp_clintes_pais

/*create proc sp_empleado_ventas
	@fecha_ini date, 
	@fecha_term date,
	@empleado varchar(45)
	As
	Select 
		'codigo' = orderid, 
		'empleado' = lastname+' '+firstname, 
		'empleado' = orderdate
	from 
		(select	
			*
		from
		employees e
		)emp
	inner join
		orders o
	on
		emp.employeeid = @*/

/* ejemplo del profesor*/

create proc sp_empleado_ventas
	@fecha_ini date, 
	@fecha_term date
	As
	Select 
		'codigo' = orderid, 
		'empleado' = lastname+' '+firstname, 
		'empleado' = orderdate
	from 
		(select	
			*
		from
		employees e
	where 
		employeeid = 7
		)emp
	inner join
		orders o
	on
		emp.employeeid = o.employeeid
	where
		orderdate between @fecha_ini
			and
		@fecha_term

/*modificando mi store*/

alter proc sp_empleado_ventas
	@fecha_inicio date,
	@fecha_termino date, 
	@codigo_empleado int /*campo agregado*/
	as
		select
			'codigo' = orderid,
			'empleado' = lastname+' '+firstname,
			'fecha' = orderdate
		from
			(select
				*
			from
				employees
			where
				employeeid =  @codigo_empleado
			)emp
		inner join
		orders o
	on
		emp.employeeid = o.employeeid
	where
		orderdate between @fecha_inicio
			and
		@fecha_termino

--EJECUTANDO EL STORED PROCEDURES MODIFICADO
EXEC sp_empleado_ventas '19970501', '19970530', 7



create 
SELECT * FROM
VENTAS_TOTALES_CLIENTES
where companyname = 'hilarion-abastos'

SELECT C.COMPANYNAME, O.SHIPPEDDATE 
	FROM CUSTOMERS C, ORDERS O




SELECT 
	C.COMPANYNAME,
	'TOTAL' = SUM(UNITPRICE * QUANTITY)
FROM
	CUSTOMERS C, ORDERS O, [ORDER DETAILS] OD
WHERE 
	C.CUSTOMERID = O.CUSTOMERID
AND 
	O.ORDERID = OD.ORDERID
GROUP BY 
	COMPANYNAME



/*OBTENER EL TOTAL DE VENTA HECHO POR UN CLIENTE*/
SELECT
	companyname,
	'Total'=SUM(unitprice*quantity)
FROM
	customers c
INNER JOIN
	(SELECT * FROM orders WHERE shippeddate IS NOT NULL) o
ON
	c.CustomerID = o.CustomerID
INNER JOIN
	[Order Details] od
ON

	o.OrderID = od.OrderID
GROUP BY
	companyname
order by
	Total
;

/***************************************************************************************************************************/
/*ESTE ES CON SUBCONSULTA*/
SELECT
	companyname,
	Total
FROM
	customers c
INNER JOIN
	(SELECT
		customerid,'Total'=SUM(unitprice*quantity)
	 FROM
		orders o
	 INNER JOIN
		[Order Details] od
	 ON
		o.OrderID = od.OrderID			
	WHERE
		shippeddate IS NOT NULL
	 GROUP BY
		CustomerID
	 ) totales
ON
	c.CustomerID = totales.CustomerID
WHERE
	Total >= 34000
order by
	total
;

/**************************************************************************************************/
/*CREAR EL STORE*/
CREATE PROC SP_CLIENTE_VENTAS
	@CLIENTE VARCHAR(20),
	@fecha_inicio date,
	@fecha_termino date
	AS
SELECT
	companyname,
	Total
FROM
	customers c
INNER JOIN
	(SELECT
	 customerid,'Total'=SUM(unitprice*quantity)
	 FROM
		orders o
	 INNER JOIN
		[Order Details] od
	 ON
		o.OrderID = od.OrderID			
	WHERE
		shippeddate IS NOT NULL
	and
		o.orderdate
	between 
		@fecha_inicio
	and
		@fecha_termino
	 GROUP BY
		CustomerID
	 ) totales
ON
	c.CustomerID = totales.CustomerID
WHERE
	COMPANYNAME = @CLIENTE
order by
	total



EXEC SP_CLIENTE_VENTAS 'Alfreds Futterkiste','19950101', '19990530'




select * from customers
where companyname = 'Alfreds Futterkiste'

/*trabajando variables*/

DECLARE
	@MIN DECIMAL, @MAX DECIMAL
--DA CONTINUALCION A LA LECTURA DE LA SIGUIENTE LINEA
SELECT
	@MIN=MIN(QUANTITY),
	@MAX=MAX(QUANTITY)
FROM
[Order Details]

PRINT 'VALOR DE MINIMO '+STR(@MIN)
PRINT 'VALOR DE MAXIMO '+STR(@MAX)

/*OBTENER EL CLIENTE CON MENOR COMPRA EN ENERO DEL 98*/

SELECT * FROM ORDERS

/*NO FUNCIONA SI PARA OBTENER EL CLIENTE
DECLARE 
	@MIN DECIMAL
SELECT
	@MIN = (OD.QUANTITY)
FROM
	[Order Details] OD, ORDERS O
WHERE
	O.ORDERID = OD.ORDERID
AND
	O.ORDERDATE
between 
	'19980101'
and
	'19980131'

PRINT 'VALOR DE MINIMO '+STR(@MIN)
PRINT CUSTOMERID
*/



SELECT
	companyname, Total
FROM
	customers c
INNER JOIN
	(SELECT
		customerid,'Total'=SUM(unitprice*quantity)
	 FROM
		orders o
	 INNER JOIN
		[Order Details] od
	 ON
		o.OrderID = od.OrderID			
	WHERE
		YEAR(ORDERDATE)=1998
	AND
		MONTH(ORDERDATE)=1
	 GROUP BY
		CustomerID
	 ) totales
ON
	c.CustomerID = totales.CustomerID
order by
	total


/*OBTENER EL CLIENTE CON MENOR VENTA EN ENERO DEL 98 EJEMPLO PROFESOR VICTOR VALENCIA*/
DECLARE
	@ventamenor DECIMAL, @cliente NVARCHAR(45)

SELECT
	@ventamenor=MIN(total)
FROM
	customers c
INNER JOIN
	(SELECT
		customerid,'Total'=SUM(unitprice*quantity)
	 FROM
		(SELECT * FROM orders WHERE YEAR(OrderDate)=1998 AND MONTH(orderdate)=1) o
	 INNER JOIN
		[Order Details] od
	 ON
		o.OrderID = od.OrderID			
	 GROUP BY
		CustomerID
	 ) totales
ON
	c.CustomerID = totales.CustomerID

/*Obteniendo el cliente*/
SELECT
	@cliente=companyname
FROM
	customers c
INNER JOIN
	(SELECT
		customerid,'Total'=SUM(unitprice*quantity)
	 FROM
		(SELECT * FROM orders WHERE YEAR(OrderDate)=1998 AND MONTH(orderdate)=1) o
	 INNER JOIN
		[Order Details] od
	 ON
		o.OrderID = od.OrderID			
	 GROUP BY
		CustomerID
	 ) totales
ON
	c.CustomerID = totales.CustomerID
WHERE
	total = @ventamenor

PRINT 'El cliente con la menor venta fue '+@cliente
PRINT 'El total de venta es de $ '+STR(@ventamenor)



/****************************************************************************************
*****************************************************************************************
***************************************************************************************
CLASE PARA MODIFICAR Y OBTENER MINIMO DE VENTAS Y NOMBRE DEL CLIENTE*/

DECLARE @ventaminima DECIMAL, @clientazo NVARCHAR(40)
SELECT
	@ventaminima=MIN(Total)
FROM
	customers c
INNER JOIN
	(SELECT
		customerid,'Total'=SUM(unitprice*quantity)
	 FROM
		orders o
	 INNER JOIN
		[Order Details] od
	 ON
		o.OrderID = od.OrderID			
	WHERE
		YEAR(orderdate)=1998 AND MONTH(orderdate)=1
	 GROUP BY
		CustomerID
	 ) totales
ON
	c.CustomerID = totales.CustomerID

/*OBTENIENDO EL CLIENTE*/
SELECT
	@clientazo=CompanyName
FROM
	customers c
INNER JOIN
	(SELECT
		customerid,'Total'=SUM(unitprice*quantity)
	 FROM
		orders o
	 INNER JOIN
		[Order Details] od
	 ON
		o.OrderID = od.OrderID			
	WHERE
		YEAR(orderdate)=1998 AND MONTH(orderdate)=1
	 GROUP BY
		CustomerID
	 ) totales
ON
	c.CustomerID = totales.CustomerID
WHERE
	total = @ventaminima

PRINT 'Este es el cliente '+@clientazo
PRINT 'Esta es su venta menor '+STR(@ventaminima)

/*----------------------------------------------------------
------------------------------------------------------------*/
--OBTENER EL PRODUCTO QUE SEA SU PRECIO MAYOR A 50
Declare @precio decimal

SET @precio= 50

select
	*
From
	products p
where
	unitprice > @precio


GO

DECLARE @mx DECIMAL, @mn DECIMAL

SELECT 
	@mx = MAX(unitprice),
	@mn = MIN(unitprice)
FROM
	products
--IMPRIMIR LOS VALORES DE LAS VARIABLES

PRINT 'Precio mas alto:'+ STR(@mx)
PRINT 'Precio mas bajo:'+ STR(@mn)
/*
IF (<expression>)
	BEGIN
		...
	END
ELSE IF(<expression>)
	BEGIN
		...
	END
ELSE IF(<expression>)
	BEGIN
		...
	END
*/

/*DETERMINADO UN NUMERO MAYOR DE 2 VARIABLES */

DECLARE @numero1 INT, @numero2 INT

SET @numero1=51
SET @numero2=50

IF @numero1 >= @numero2
	BEGIN
		PRINT 'Número 1 es mayor con '+STR(@numero1)
	END
ELSE
	BEGIN
		PRINT 'Número 2 es mayor con '+STR(@numero2)
	END

select * from Orders

--OBTENIENDO ENVIOS ATRASADOS Y POR CUANTO ESTAN ATRASADOS
SELECT 
	orderdate,
	shippeddate,
	'atraso'=DATEDIFF(dd,orderdate,shippeddate)
FROM
	orders
WHERE
	orderdate BETWEEN '19980101' AND '19980228'
ORDER BY
	DATEDIFF(dd,orderdate,shippeddate)

/*USANDO LAS VARIABLES PARA DETERMINAR CUANTOS DIAS DE ATRASO Y POR CLIENTE*/

DECLARE @diamaximo INT, @diaminimo INT, @clientemax varchar(45), @clientemin varchar (45)

SELECT 
	@diamaximo=MAX(DATEDIFF(dd,orderdate,shippeddate)),
	@diaminimo=MIN(DATEDIFF(dd,orderdate,shippeddate))
FROM
	orders
WHERE
	orderdate BETWEEN '19980101' AND '19980228'

SELECT 
	@clientemax = companyname
FROM
	Customers c
INNER JOIN 
	orders o
ON
	c.Customerid = o.CustomerID
WHERE
	DATEDIFF(dd,orderdate,shippeddate) = @diamaximo
AND
	OrderDate BETWEEN '19980101' AND '19980228'

/**/
SELECT 
	@clientemin = companyname
FROM
	Customers c
INNER JOIN 
	orders o
ON
	c.Customerid = o.CustomerID
WHERE
	DATEDIFF(dd,orderdate,shippeddate) = @diaminimo
AND
	OrderDate BETWEEN '19980101' AND '19980228'


PRINT 'El dia Maximo es '+ STR(@diamaximo) + ' el cliente es ' + @clientemax
PRINT 'El dia Minimo es '+ STR(@diaminimo) + ' el cliente es ' + @clientemin

/*crear un proceso que me muestre las empresas de envios que tienen entregas 
incumplidas superiores a 20 dias */

select * from Shippers
select * from Suppliers

select * from orders 

/*
SELECT 
	s.companyname,
	'dias'=DATEDIFF(dd,orderdate,shippeddate)
FROM
	orders o, shippers s

WHERE
	o.ShipVia = s.shipperid
and
	DATEDIFF(dd,orderdate,shippeddate) > 20



SELECT 
	@clientemax = companyname
FROM
	Customers c
INNER JOIN 
	orders o
ON
	c.Customerid = o.CustomerID
WHERE
	DATEDIFF(dd,orderdate,shippeddate) = @diamaximo
AND
	OrderDate BETWEEN '19980101' AND '19980228'
*/

/*CREANDO EL PROCEDIMIENTO QUE FILTRA Y MUESTRA UN ERROR AL CAPTURAR UN VALOR INVALIDO*/

CREATE PROCEDURE dbo.[sp_ventas_por_rangos]
	@fechainicio datetime, @fechafin datetime
AS
	IF @fechainicio IS NULL OR @fechafin IS NULL
		BEGIN
			RAISERROR('Debe de asignar un dato válido', 14,1) RETURN
		END
		SELECT
			o.shippeddate,
			o.orderid,
			od.subtotal,
			DATENAME(yy,shippeddate) AS año
		FROM
			orders o
		INNER JOIN
			[order subtotals] od --UTILIZANDO UNA VISTA DEL SISTEMA
		ON 
			o.orderid = od.orderid
		WHERE
			o.shippeddate BETWEEN @fechainicio AND @fechafin
GO

/*PROBANDO EL STORED CON FECHAS VALIDAS SIN VALORES NULOS*/

[sp_ventas_por_rangos] '19980101','19980730';



/* PROBANDO EL STORED CON FECHAS NULAS*/

[sp_ventas_por_rangos] NULL, '19980730'

/*CREAR STORED QUE MUESTRE UN STATUS DE LOS ENVIOS Y SI ESTE ESTA RETRASADO POR MAS DE 5 DIAS
MUESTRE RETRASADO SI ES MENOR QUE MUESTRE EN TIEMPO*/

CREATE PROCEDURE dbo.[sp dias de atraso]

--ALTER PROCEDURE dbo.[sp dias de atraso]
	@fechainicio datetime, @fechafin datetime, @dias INT
AS
	IF @fechainicio IS NULL OR @fechafin IS NULL
		BEGIN
			RAISERROR('Debe de asignar un dato válido', 14,1) RETURN
		END

SELECT
	shipvia, orderdate,shippeddate, 'Dias'=DATEDIFF(dd,orderDate,shippedDate),
	'STATUS' = 
			CASE 
			WHEN DATEDIFF(dd,orderDate,shippedDate) < 5 THEN 'En Tiempo'
			ELSE 'Retrasado'
		END
FROM
	orders o
JOIN
	Shippers s
ON
	o.shipvia = s.ShipperID
WHERE
	o.shippeddate BETWEEN @fechainicio AND @fechafin
	AND DATEDIFF(dd,orderDate,shippedDate) > @dias
ORDER BY
	shipvia, Dias


[sp dias de atraso] '19980101', '19980730','1'





ALTER PROCEDURE dbo.[sp dias de atraso]
	@fechainicio datetime, @fechafin datetime, @dias INT
AS
	IF @fechainicio IS NULL OR @fechafin IS NULL
		BEGIN
			RAISERROR('Debe de asignar un dato válido', 14,1) RETURN
		END
	IF @fechainicio IS NULL OR @fechafin IS NULL
		BEGIN
			RAISERROR('Debe de asignar un dato válido', 14,1) RETURN
		END

SELECT
	shipvia,orderdate,shippeddate, 
	'Dias'=DATEDIFF(dd,orderDate,shippedDate),
	'STATUS' = iif(DATEDIFF(dd,orderdate,shippeddate) >= 0
		AND
	DATEDIFF(dd,orderdate,shippeddate) <= 15, 'Tolerancia', 'Incumplidos'
		)
FROM
	orders o
JOIN
	Shippers s
ON
	o.shipvia = s.ShipperID
WHERE
	YEAR(orderdate)=1998 AND MONTH(orderdate)=1
ORDER BY
	shipvia, Dias


/*-----------------EJERCICIOS------------------------------------
Obtener el total de ventas caidas en el primer cuatrimestre del 98 shippeddate IS NULL
Mostrar el total de ventas caidas en el primer cuatrimestre del 98 por cliente
Mostrar el total de ventas caidas en el primer cuatrimestre del 98 por empleado
Mostrar el total de ventas caidas en el primer cuatrimestre del 98 por compania de envios*/


select * from orders

/*********** 08-02-2014 *****************************/

/* - 1. Creacion de funciones escalares son solamente para generar operaciones aritmeticas 
sencillas o basicas*/

CREATE FUNCTION Iva (@impuesto money)
	RETURNS money --Declaramos tipos de datos "solamente numericos"
		AS
		BEGIN
			Declare @iva money
				SET @iva=@impuesto * .16 -- permite asignar un valor a la variable iva
			RETURN(@iva)
		END

/* - 3. Revisar la funcion debe de escribirse el nombre con dos partes */

SELECT 
	unitprice, dbo.iva(unitprice) as Iva,
	'integrado'=dbo.iva(unitprice)+unitprice
from [order details]

SELECT * from Categories -- la de las categorias 1 son bebidas

SELECT * from products order by categoryid

--CREAR OPERACION QUE CALCULE IVA
SELECT 
	productname, unitprice, unitsinstock,
	'subtotal'=unitprice*unitsinstock,
	'IVA'=dbo.iva(unitprice*UnitsInStock),
	'Total con iva'= dbo.iva(unitprice*unitsinstock)+unitprice*UnitsInStock
from products
where categoryid =1
order by CategoryID

/*crear funcion de castigo para todas las empresas de envios del 12% de lo que ganan se les retira lo
mencionado de porcentaje*/

CREATE FUNCTION castigo (@flete money)
	RETURNS money 
		AS
		BEGIN
			Declare @castigo money
				SET @castigo=@flete * .12
			RETURN(@castigo)
		END

select * from Orders
/*LA FUNCION QUE HICE SE LLAMA castigo(campo)*/
SELECT 
	shipvia, freight,
	'Descontar'=dbo.castigo(freight),
	'Flete con descuento'= freight-dbo.castigo(freight)
from Orders

/*SELECT dbo.fin sancion(campo,campocalculado,camposcalculadosconfunciones) FROM Orders*/


/*Funcion que convierte cadenas en mayusculas UPPER o minusculas LOWER*/

CREATE FUNCTION EnMayusculas(@Nombre varchar(50),@Apellido Varchar(50))
	RETURNS Varchar (100)
		AS
		--DECLARE @VARIABLES Zona para declarar variables
		BEGIN
			RETURN (UPPER(@Apellido) + ' - '+ UPPER(@nombre))
		END

--Ejecutar

PRINT dbo.EnMayusculas('victor','valencia')

SELECT
	dbo.enmayusculas(lastname,firstname) as 'Empleado'
FROM
	employees

-- elaborar una funcion que obtenga la edad de los empleados fn_edad


CREATE FUNCTION fn_edad (@fechanac DATE, @fecha_actual DATE)
	RETURNS INT 
		AS
			-- DECLARE @variable zona para declarar variables
		BEGIN
			-- SET al declarar variables en el set se asigna valor
			RETURN (DATEDIFF(yy,@fechanac,@fecha_actual)) -- solo se hace la diferencia entre dos variables de fecha
		END

SELECT 
	firstname,lastname,birthdate,
	'fecha actual'=GETDATE(),
	'Edad'=dbo.fn_edad(birthdate,GETDATE())
FROM
	Employees

CREATE FUNCTION fn_edad_2 (@fechanac DATE)
	RETURNS INT
		AS
		--DECLARE
		BEGIN
		--SET
		RETURN
			DATEDIFF(yy,@fechanac,GETDATE())
		END

SELECT 
	firstname,lastname,birthdate,
	'fecha actual'=GETDATE(),
	'Edad'=dbo.fn_edad_2(birthdate)
FROM
	Employees
	
-- 8.Ejemplo de una funcion con valores de tabla en linea

CREATE FUNCTION DBO.DETALLEPEDIDO_FECHA(@inicio DATETIME, @fin DATETIME)
RETURNS TABLE --retorna mis valores en una tabla
AS
RETURN --@variable en funcion escalar ahora es
(SELECT
	O.ORDERID, O.ORDERDATE, P.PRODUCTID, P.PRODUCTNAME,(OD.UNITPRICE * OD.QUANTITY) AS Total
FROM
	ORDERS AS o
	INNER JOIN [ORDER DETAILS] AS OD
ON
	O.ORDERID=OD.ORDERID INNER JOIN PRODUCTS AS P
ON
	OD.PRODUCTID=P.PRODUCTID
WHERE
	O.ORDERDATE BETWEEN @INICIO AND @FIN
)

-- EJECUTAR LA FUNCION

SELECT * FROM DBO.DETALLEPEDIDO_FECHA('19980101','19981231')

/* OBTENER LOS TOTALES DE VENTA POR EMPLEADO EN UN DETERMINADO MES FN_VENTAS_TOTALES*/

CREATE FUNCTION FN_VENTAS_TOTALES(@inicio DATETIME, @fin DATETIME)
RETURNS TABLE 
AS
RETURN 
(

SELECT 
	FIRSTNAME,LASTNAME, --OD.UNITPRICE,'TOTAL' = SUM(UNITPRICE * QUANTITY)
	--''SUBTOTAL'=OD.UNITPRICE*QUANTITY,
	'Total'=SUM(od.unitprice*quantity)
FROM
	EMPLOYEES E
INNER JOIN 
	ORDERS O
ON
	E.EMPLOYEEID = O.EMPLOYEEID
INNER JOIN
	[ORDER DETAILS]OD
ON
	O.ORDERID = OD.ORDERID
WHERE
	YEAR(ORDERDATE)=1997 AND MONTH(ORDERDATE)=8 AND E.EMPLOYEEID = 2
GROUP BY
	FIRSTNAME, LASTNAME

)



SELECT 
	FIRSTNAME,LASTNAME, --OD.UNITPRICE,'TOTAL' = SUM(UNITPRICE * QUANTITY)
	--''SUBTOTAL'=OD.UNITPRICE*QUANTITY,
	'Total'=SUM(od.unitprice*quantity)
FROM
	(SELECT * FROM EMPLOYEES WHERE EMPLOYEEID=2) E

INNER JOIN 
	(SELECT * FROM ORDERS WHERE YEAR(ORDERDATE)=1997 AND MONTH(ORDERDATE)=8) O
ON
	E.EMPLOYEEID = O.EMPLOYEEID
INNER JOIN
	[ORDER DETAILS]OD
ON
	O.ORDERID = OD.ORDERID
GROUP BY
	FIRSTNAME, LASTNAME



SELECT 
	FIRSTNAME,LASTNAME, --OD.UNITPRICE,'TOTAL' = SUM(UNITPRICE * QUANTITY)
	--''SUBTOTAL'=OD.UNITPRICE*QUANTITY,
	'Total'=SUM(od.unitprice*quantity)
FROM
	(SELECT * FROM EMPLOYEES E, ORDERS O WHERE E.EMPLOYEEID=2 AND YEAR(ORDERDATE)=1997 AND MONTH(ORDERDATE)=8) O
INNER JOIN
ORDERS
ON
	O.ORDERID = OD.ORDERID
GROUP BY
	FIRSTNAME, LASTNAME

/**********15/02/14************/
--CURSOR
DECLARE probando_cursor cursor
	FOR SELECT * FROM Categories;
	--abriendo el cursor
	OPEN probando_cursor
	--recorriendo las lineas del cursor
	FETCH NEXT FROM probando_cursor
	--cerrando el cursor
	CLOSE probando_cursor
	--liberando la memoria ram del cursor
	DEALLOCATE probando_cursor
	
/*Declarando variables en el cursor */

DECLARE @compania VARCHAR(80),
	@contacto VARCHAR(55),
	@pais VARCHAR(20)

DECLARE clientes_alemanes CURSOR GLOBAL SCROLL -- EL SCROLL ES FUNCIONAL CUANDO DESEAMOS QUE COTEJE REGISTROS INICIALES Y FINALES
	FOR SELECT companyname,contactname,country FROM	customers
	
OPEN clientes_alemanes

FETCH clientes_alemanes INTO @compania,@contacto,@pais
	WHILE(@@FETCH_STATUS=0)
	BEGIN
		PRINT @compania+' '+@contacto+' '+@pais
		FETCH clientes_alemanes INTO @compania,@contacto, @pais
	END
CLOSE clientes_alemanes

/* Cursor para empleados */

DECLARE cursor_empleado CURSOR
FOR SELECT * FROM employees
OPEN cursor_empleado 
FETCH NEXT FROM cursor_empleado
CLOSE cursor_empleado
DEALLOCATE cursor_empleado

/* Cursor para productos */

DECLARE @productos VARCHAR(40),
	@stock INT
	
DECLARE cursor_productos CURSOR GLOBAL SCROLL
	FOR SELECT productname,unitsinstock FROM products
OPEN cursor_productos 
FETCH cursor_productos INTO @productos,@stock
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT @productos+' '+CAST(@stock AS VARCHAR(10))
		FETCH cursor_productos INTO @productos,@stock
	END
CLOSE cursor_productos
DEALLOCATE cursor_productos	

/*****************/

DECLARE @productos VARCHAR(40),
	@stock INT,
	@categoriaid INT,
	@nombrecategoria VARCHAR (40)

	
DECLARE cursor_productos CURSOR GLOBAL SCROLL
	FOR SELECT productname,unitsinstock,categoryid,productname FROM products order by categoryid
OPEN cursor_productos 
FETCH cursor_productos INTO @productos,@stock,@categoria 
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT @productos+' '+CAST(@stock AS VARCHAR(10))+' '+CAST(@categoria AS VARCHAR(10))
		FETCH cursor_productos INTO @productos,@stock,@categoria
	END
CLOSE cursor_productos
DEALLOCATE cursor_productos	

SELECT p.categoryid, c.categoryname FROM products p, categories c 
WHERE p.CategoryID = c.categoryid

/*** codigo profesor ***/

DECLARE cursorcp CURSOR
for select categoryid, categoryname FROM categories
OPEN cursorcp
declare @cat int, @nom varchar (50)
fetch next from cursorcp into @cat, @nom
while @@FETCH_STATUS=0
BEGIN
print 'categoria: '+ @nom
print '==========================='

declare cursorcp1 CURSOR
FOR SELECT productid, productname, unitprice FROM products where Categoryid=@cat
OPEN cursorCP1
DECLARE @codpro int,@prod VARCHAR (50), @prec MONEY
FETCH NEXT FROM cursorcp1 INTO @codpro, @prod, @prec
WHILE (@@FETCH_STATUS=0)
begin
print 'Codigo : ' + CAST(@codpro AS VARCHAR(10))
PRINT 'Producto : ' + @prod
PRINT 'Precio : ' + CAST (@prec AS VARCHAR (10))
FETCH NEXT FROM cursorcp1 INTO @codpro, @prod, @prec
					end
			close cursorCP1	
			deallocate cursorCP1
			fetch next from cursorCP into @cat,@nom
		end
close cursorCP
deallocate cursorCP
go

close cursorcp
DEALLOCATE cursorcp	




declare cursorCP cursor
	for select Categoryid,categoryname from categories
		open cursorCP
			declare @cat int, @nom varchar(50)
			fetch next from cursorCP into @cat,@nom
				while @@fetch_status=0
					begin
						print 'Categoria : ' + @nom
						print '=================================='
					declare cursorCP1 cursor
						for select productid, productname, unitprice from products where Categoryid=@cat
					open cursorCP1
					declare @codpro int,@prod varchar(50) ,@prec money
						fetch next from cursorCP1 into @codpro, @prod, @prec
							while (@@fetch_status=0)
								begin	
									print 'Codigo : ' + cast(@codpro as varchar(10))
									print 'Producto : ' + @prod
									print 'Precio : ' + cast(@prec as varchar(10))
								fetch next from cursorCP1 into @codpro, @prod, @prec
							end
			close cursorCP1	
			deallocate cursorCP1
			fetch next from cursorCP into @cat,@nom
		end
close cursorCP
deallocate cursorCP
go




DECLARE cursorsexo CURSOR 
	FOR SELECT firstname,lastname titleofcourtesy, 
		'sexo' =
		case titleofcourtesy
			WHEN 'Ms.' THEN 'Femenino'
			WHEN 'Mrs.' THEN 'Femenino'
			WHEN 'Mr.' THEN 'Masculino'
			WHEN 'Dr.' THEN 'Masculino'
		END
	FROM
	employees
OPEN cursorsexo
	DECLARE
		@nombre VARCHAR(40),
		@apellido VARCHAR(40),
		@sexo CHAR(10)
	FETCH cursorsexo INTO @nombre,@apellido,@sexo
WHILE (@@FETCH_STATUS=0)
BEGIN
	PRINT @nombre +' '+@apellido+' '+@sexo
	FETCH cursorsexo INTO @nombre,@apellido,@sexo
END
CLOSE cursorsexo
DEALLOCATE cursorsexo