REPASO FINAL BDA-I

/*
Pregunta 1
Utilizando Script (OpenRowSet), cargue a esta tabla creada los datos contenidos en el archivo Excel proporcionado por su profesor (04 puntos)
*/
use master
go

sp_configure 'show advanced options', 1
go
Reconfigure with override
go

sp_configure 'Ad Hoc Distributed Queries', 1
go
Reconfigure with override
go

exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
go

exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
go


use Negocios2013
go

Select * into TBVenta
from Ventas.pedidoscabe
where IdPedido='99999'
go

select * from TBVenta
go

insert into TBVenta
Select * from openrowset(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\DATA\DatosLC3Noche.xlsx', [Hoja1$])
go

select * from TBVenta
go

/*
Pregunta 2
Crear un procedimiento que reciba como parámetro el código de cliente, luego utilizando MERGE entre las tablas TBVenta y PedidosCabe, efectúe la operación de Inserción en caso no coincida el id pedido y código de cliente en caso contrario que actualice los campos fecha de entrega, fecha de envio, envio_pedido y cantidad de pedido, dirección del destinatario, ciudad del destinatario y país de destinatario, con valor NULL.Utilice TRY / CATCH y RAISERROR para cuando no exista el parámetro ingresado. (08 puntos)
*/

create proc Pregunta2
@IdCliente varchar(5)
as
begin
    begin try
        if not exists (Select * from Ventas.clientes C where C.IdCliente=@IdCliente)
            raiserror('No esta registrado el cliente',16,1)
        else
        Begin
            merge TBVenta as target
            using Ventas.pedidoscabe as source
            on (target.idPedido=source.idPedido)
            when matched and Source.idCliente=@IdCliente then
                update set [FechaEntrega]=Source.[FechaEntrega], [FechaEnvio]=Source.[FechaEnvio],
                [EnvioPedido]=Source.[EnvioPedido], [CantidaPedido]=Source.[CantidaPedido],
                [DirDestinatario]=Source.[DirDestinatario], [CiuDestinatario]=Source.[CiuDestinatario],
                [PaiDestinatario]= NULL
            when not matched  and Source.idCliente=@IdCliente then
                 INSERT VALUES (Source.[IdPedido], Source.[IdCliente], Source.[IdEmpleado], Source.[FechaPedido],
                 Source.[FechaEntrega], Source.[FechaEnvio], Source.[EnvioPedido], Source.[CantidaPedido], Source.[Destinatario],
                 Source.[DirDestinatario], Source.[CiuDestinatario], Source.[RefDestnatario], Source.[DepDestinatario],
                 Source.[PaiDestinatario]);
        End
    end try
    begin catch
        if ERROR_NUMBER()=50000
            print error_message()
    end catch
end
go

exec Pregunta2 'SPLIR'
go


/*
Pregunta 3
Crear una función de tabla en línea que devuelva el año de la fecha de pedido, nombre del Empleado, idPedido y el monto total del pedido. Utilice Cube o RollUp. (04 puntos)*/
create function Pregunta3()
returns table
as
    return (select isnull(cast(PC.FechaPedido as varchar(15)),replicate('*',15)) [Fecha Pedido],
                   isnull(E.NomEmpleado, replicate('*',15) ) [Nombre de Empleado],
                    isnull(cast(PC.IdPedido as varchar(15)),  '*** TOTAL ***') [IdPedido],
                   (PD.Cantidad*PD.PrecioUnidad-PD.Descuento) as [Monto]
            from RRHH.empleados E inner join  Ventas.pedidoscabe PC
            on E.IdEmpleado=PC.IdEmpleado inner join Ventas.pedidosdeta PD
            on PC.IdPedido=PD.IdPedido
            group by PC.FechaPedido, E.NomEmpleado, PC.IdPedido, PD.Cantidad, PD.PrecioUnidad, PD.Descuento with cube)
go

select * from Pregunta3()
go

/*
Pregunta 4
Crear un procedimiento que reciba como parámetro el Id del cliente y que permita devolver un el nombre del cliente, el año y la relación de ID de pedidos que efectuó, así como el monto total de las ventas. Utilice un cursor. (04 puntos)
*/

create proc Pregunta4
@IdCliente varchar(5)
as
begin
    declare @vc_nc varchar(40), @vc_anhio smallint, @vc_idPedido int, @vc_totalVenta money

    declare miCursor cursor for select C.NomCliente, Year(PC.FechaPedido) as [Año], PC.IdPedido, sum(PD.Cantidad*PD.PrecioUnidad-PD.Descuento) as [Total]
                                from Ventas.clientes C inner join Ventas.pedidoscabe PC
                                on C.IdCliente=PC.IdCliente inner join Ventas.pedidosdeta PD
                                on PC.IdPedido=PD.IdPedido where C.IdCliente=@IdCliente
                                group by PC.IdPedido, C.NomCliente, PC.FechaPedido

    open miCursor
    fetch next from miCursor into @vc_nc, @vc_anhio, @vc_idPedido, @vc_totalVenta

    while @@FETCH_STATUS=0
    Begin
        print 'Nombre del cliente: '+@vc_nc
        print 'Año de pedido     : '+ Convert(Varchar(4), @vc_anhio)
        print 'Id de Pedido      :'+ Convert(Varchar(5), @vc_idPedido)
        print 'Monto Total       :'+ Convert(Varchar(8), @vc_totalVenta)
        print '**********************************************************************'

        fetch next from miCursor into @vc_nc, @vc_anhio, @vc_idPedido, @vc_totalVenta
    End

    close miCursor
    deallocate miCursor

end
go

exec Pregunta4 'WILMK'
go












--Transacciones
use Negocios2013
go

insert Ventas.paises
Values
('95','Malasia')
go

select * from Ventas.paises

Rollback


/***Transaccion Explícita****/
Begin Tran Tranns01
    insert Ventas.paises
    Values
    ('94','Noruega');

select * from Ventas.paises

/*Deshacemos la transaccion*/
Rollback Tran Tranns01




/***Transaccion Explícita - Confirmada****/
Begin Tran Tranns01
    insert Ventas.paises
    Values
    ('94','Noruega');

commit Tran Transs01

select * from Ventas.paises

/*Deshacemos la transaccion*/
Rollback Tran Tranns01




/***Transaccion Explícita con Marca***/
Begin Tran Tranns01 with mark 'Marca25032234'
    insert Ventas.paises
    Values
    ('93','Australia'),
    ('92','Holanda');

commit tran Tranns01















--XML
use Negocios2013
go

select * from Ventas.pedidoscabe
for xml auto
go


select C.NomCliente, P.IdPedido, P.FechaPedido
from Ventas.pedidoscabe P inner join Ventas.clientes C
on P.IdCliente=C.IdCliente
for xml auto
go

select C.NomCliente, P.IdPedido, P.FechaPedido
from Ventas.pedidoscabe P inner join Ventas.clientes C
on P.IdCliente=C.IdCliente
for xml raw
go


/************************/
/*Creando tabla con campo XML*/
CREATE TABLE DeudasXTienda
(
    id int primary key,
    detalleDeuda XML not null
)
go

/*Insertando datos XML*/

insert into DeudasXTienda
select 1, detalleDeuda
        from (select *
            from openrowset
                (bulk 'C:\Data\Cliente.xml', single_blob)
            AS detalleDeuda) as R(detalleDeuda)
go


insert into DeudasXTienda
select 2, detalleDeuda
        from (select *
            from openrowset
                (bulk 'C:\Data\ClienteSurco.xml', single_blob)
            AS detalleDeuda) as R(detalleDeuda)
go


select * from DeudasXTienda


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

CREATE XML SCHEMA COLLECTION schemaFactura
AS
N'<?xml version="1.0" ?>
   <xsd:schema
   targetNamespace="http://schemas.mi_base.com/schema1"
   xmlns="http://schemas. mi_base.com/schema1" elementFormDefault="qualified" attributeFormDefault="unqualified"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
   <xsd:element name="Pedido">
   <xsd:complexType>
   <xsd:sequence>
   <xsd:element name="Numero" type="xsd:string"   minOccurs="1" maxOccurs="1"/>
   <xsd:element name="Cliente" type="xsd:string"   minOccurs="1" maxOccurs="1"/>
   <xsd:element name="Importe" type="xsd:int"   minOccurs="1" maxOccurs="1"/>
   </xsd:sequence>
   </xsd:complexType>
   </xsd:element>
   </xsd:schema>'


CREATE TABLE Factura (IdPedido int, Pedido xml (schemaFactura))
Go

INSERT INTO Factura VALUES
(1, '<?xml version="1.0" ?>
<Pedido xmlns="http://schemas.mi_base.com/schema1">
<Numero>2231-AX</Numero>
<Cliente>Microsoft</Cliente>
<Importe>250</Importe>
</Pedido>')
Go

Select * from Factura
Go


/****************/
create XML SCHEMA COLLECTION schemaAlumno
AS
N'<?xml version="1.0" ?>
   <xsd:schema
   targetNamespace="http://schemas.mi_base.com/schema2"
   xmlns="http://schemas. mi_base.com/schema2" elementFormDefault="qualified" attributeFormDefault="unqualified"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
   <xsd:element name="Alumnos">
   <xsd:complexType>
   <xsd:sequence>
   <xsd:element name="codigo" type="xsd:string"   minOccurs="1" maxOccurs="1"/>
   <xsd:element name="nombre" type="xsd:string"   minOccurs="1" maxOccurs="1"/>
   <xsd:element name="apePat" type="xsd:string"   minOccurs="1" maxOccurs="1"/>
   <xsd:element name="apeMat" type="xsd:string"   minOccurs="1" maxOccurs="1"/>
   <xsd:element name="f_nac" type="xsd:date"   minOccurs="1" maxOccurs="1"/>
   </xsd:sequence>
   </xsd:complexType>
   </xsd:element>
   </xsd:schema>'


CREATE TABLE Alumno (IdAlumno int, Alumnos xml (schemaAlumno))
Go

INSERT INTO Alumno VALUES
(1, '<?xml version="1.0" ?>
<Alumnos xmlns="http://schemas.mi_base.com/schema2">
<codigo>A201310358</codigo>
<nombre>Hernán</nombre>
<apePat>Oscanoa</apePat>
<apeMat>Ventocilla</apeMat>
<f_nac>1985-12-02</f_nac>
</Alumnos>
<Alumnos xmlns="http://schemas.mi_base.com/schema2">
<codigo>A201341236</codigo>
<nombre>Sophia</nombre>
<apePat>Castro</apePat>
<apeMat>Romero</apeMat>
<f_nac>1988-11-23</f_nac>
</Alumnos>')
Go

Select * from Alumno
Go










--Backup
USE MASTER
GO


/******BACKUP FULL*****/

backup Database [Neptuno]
to disk ='C:\BACKUP\BKFULLNeptuno1930.bak'
With Init, Format

backup Database [Negocios2013]
to disk ='C:\BACKUP\BKFULLNegocios20131930.bak'
go












/******BACKUP Differential*****/
--Creando BK FULL
backup Database [Neptuno]
To disk='C:\BACKUP\BKFULLNeptuno1950.bak'
With Init, Format

--Abrir la BD
use Neptuno
go

--Creando Tabla01
create table MyTabla01
(
    id int,
    nom char(50)
)
Go

--llenar datos
Declare @c int = 1
While @c<=100
Begin
    Insert Into MyTabla01
    Values
    (@c, 'Sandra'+Cast(@c as varchar(30)))
    set @c=@c+1
End
GO

-- Visualiza la tabla
Select * from MyTabla01
GO



--BK differential
use MASTER
GO

backup Database [Neptuno]
to Disk = 'C:\BACKUP\BKDiff_1_Neptuno2000.bak'
With Differential
GO


--Creando Tabla02
create table MyTabla02
(
    id int,
    nom char(50)
)
Go

--llenar datos
Declare @c int = 1
While @c<=100
Begin
    Insert Into MyTabla02
    Values
    (@c, 'Koko'+Cast(@c as varchar(30)))
    set @c=@c+1
End
GO

-- Visualiza la tabla
Select * from MyTabla02
GO


drop table MyTabla01
Go


use MASTER
GO

backup Database [Neptuno]
to disk='C:\BACKUP\BKDiff_2_Neptuno2006.bak'
with differential
go











/**** Backup Log Transaction ****/

use Master
Go


---Backup Full
Backup Database [Negocios2013]
to disk = 'C:\BACKUP\Negocios2017.bak'
Go


--Relizar transacciones en la abla paises
--de la base de datos Negocios2013
use Negocios2013
GO

Select * from Ventas.paises
GO

Insert Ventas.paises
Values
('200','Australia'),
('201','Lusitania'),
('202','Mongolia')
go


--Creando el primer Backup log
use Master
Go

Backup Log [Negocios2013]
to disk = 'C:\BACKUP\BKLogNegocios2025.trn'
with init
Go


use Negocios2013
GO

Insert Ventas.paises
Values
('300','Bélgica'),
('301','Grecia'),
('302','Turquía')
go


--Creando el segundo Backup log
use Master
Go

Backup Log [Negocios2013]
to disk = 'C:\BACKUP\BKLog_2_Negocios2030.trn'
--with init  es al primero
Go










--Configurar compress de backup
EXEC sys.sp_configure N'backup compression default', N'1'
Go
Reconfigure with override
Go

--Creando un backup comprimido
backup database [Neptuno]
to disk = 'C:\BACKUP\BKFullCompressNeptuno2120.bak'
with Compression, format
go



--dispositivo de seguridad
USE [master]
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'BKFullDeviceNeptuno',
@physicalname = N'C:\BACKUP\BKFullDeviceNeptuno.bak'
GO


--
Backup Database [Neptuno]
to [BKFullDeviceNeptuno]
go






--en ojetos de servidor(menu lateral)
--dispositivo de seguridad
USE [master]
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'BKFullDeviceNeptunoMirror',
@physicalname = N'C:\BACKUP\BKFullDeviceNeptunoMirror.bak'
GO


--backup con Mirror
Backup Database [Neptuno]
to [BKFullDeviceNeptuno]
Mirror to [BKFullDeviceNeptunoMirror]
with Format, Checksum
Go


--Estrategia de verificación de backup
Restore verifyonly from [BKFullDeviceNeptuno]
GO

--Solo para verificar
Restore verifyonly from disk = 'C:\BACKUP\BKFULLNegocios1933.bak'
go




/**** Restaurando base de datos ***/

Restore Database [Neptuno]
from Disk = 'C:\BACKUP\BKFULLNeptuno1930.bak'
with Replace
GO
--Se debe de poner with replace sino reclama tabla en uso


/**
Restaurando en una base de datos temporal
**/
--se recupera con otro nombre para ver si contiene lo necesario
--los nombres de los archivos se ve en las propiedades de la base
--de datos
Restore Database [NeptunoTesting]
from Disk = 'C:\BACKUP\BKFULLNeptuno1930.bak'
with Restricted_user,
File=1,
Move 'Neptuno_dat' to 'C:\FileTemp\Neptuno.mdf',
Move 'Neptuno_log' to 'C:\FileTemp\NeptunoLog.ldf'
GO




/****/
Restore Database [Neptuno]
from Disk = 'C:\BACKUP\BKFULLNeptuno1950.bak'
with norecovery, replace
GO


Restore Database [Neptuno]
From  Disk = 'C:\BACKUP\BKDiff_2_Neptuno2006.bak'
with recovery
GO








/*******/
--restaurando un backup log
Restore Database [Negocios2013]
from disk = 'C:\BACKUP\Negocios2017.bak'
with norecovery, replace
GO

Restore log [Negocios2013]
from disk = 'C:\BACKUP\BKLogNegocios2025.trn'
With recovery
GO





/*******/
--restaurando un backup log
Restore Database [Negocios2013]
from disk = 'C:\BACKUP\Negocios2017.bak'
with norecovery, replace
GO

Restore log [Negocios2013]
from disk = 'C:\BACKUP\BKLogNegocios2025.trn'
With norecovery
GO

Restore log [Negocios2013]
from disk = 'C:\BACKUP\BKLog_2_Negocios2030.trn'
With recovery
GO





/****************************/
/*REPASO*/
use Negocios2013
go

select * into #TempCliente
from Ventas.clientes
where IdCliente='XXX'
go

select * from #TempCliente
go

insert #TempCliente
Select * from Ventas.clientes
where NomCliente like '[AEIOU]%'
go

select * from #TempCliente
go

Create table #Vendedor
(
        id_ven char(5) not null,
        nom_ven varchar(25) not null,
        sue_ven money,
        dir_ven varchar(25) not null
)
go

bulk insert #Vendedor
from 'C:\BDS\VendedorPlano.txt'
with(
    fieldterminator=',',
    firstrow=2,
    rowterminator='\n'
)
go

select * from #Vendedor
Go


use master
go


sp_configure 'show advanced options', 1
go
Reconfigure with override
go

sp_configure 'Ad Hoc Distributed Queries', 1
go
Reconfigure with override
go

exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
go

exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
go


use Negocios2013
go

Create table tbServicios
(
        id_ven char(5) not null,
        nom_ven varchar(25) not null,
        sue_ven money,
)
go

select * from tbServicios
go

insert into tbServicios
Select * from openrowset(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\BDS\Libro.xlsx', [Hoja1$])
go


*Eliminación*/

select * into Customers
from Ventas.clientes
go

select * into Ciudad
from  RRHH.Distritos
go

--------------------------

select * from Customers

select * from ciudad

-----

alter table Customers
Add primary key (IdCliente)
go

alter table Ciudad
Add primary key (IdDistrito)
go

-----
select * into Country
from Ventas.paises
go
---
alter table Country
add primary key (IdPais)
go

alter table Customers
add foreign key (idpais)
References Country
go



/*----------------------------*/
select * from Customers
select * from Country

---------------
Delete From Country
Where IdPais='002'
go

---Modificar la restricion FK
alter table Customers
drop constraint [FK__Customers__idpai__34C8D9D1]
go

---Foranea
Alter Table customers
Add foreign key (idpais)
references country
on delete Cascade
go


/*-----------------------------------*/
Create table TBPersona
(
        idper int identity,
        nomper varchar(50)
)
go

print @@identity


/*---*/
Insert TBPersona
Select NomCliente
from customers
go


--
select * from TBPersona
go

Delete from TBPersona
go

truncate table TBpersona
go




Declare @pais varchar(50), @id char(3)
set @pais='NiXeria'
set @id='99'
Merge Ventas.paises as target
using (select @id, @pais) as source (idpais, nombrepais)
on (target.idpais=source.idpais)
when matched then
    update set nombrepais=source.nombrepais
when not matched then
    insert values (source.idpais, source.nombrepais);

SELECT * FROM VENTAS.paises
GO

/*------------------------*/

SELECT * into Produ
FROM Compras.productos
WHERE idproducto=0


select * from Compras.productos
select * from produ


merge produ as target
using Compras.productos as source
on (target.idproducto=source.idproducto)
when matched then
    update set [precioUnidad]=0, [UnidadesEnExistencia]=0, [UnidadesEnPedido]=0
when not matched then
     INSERT VALUES (Source.[IdProducto], Source.[NomProducto], Source.[IdProveedor], Source.[IdCategoria], Source.[CantxUnidad], Source.[PrecioUnidad], Source.[UnidadesEnExistencia], Source.[UnidadesEnPedido]);

insert Produ values
(80, 'arroz', NULL,NULL,2,      0,      0,      0)
go



merge produ as target
using Compras.productos as source
on (target.idproducto=source.idproducto)
When  MATCHED THEN
        UPDATE SET [precioUnidad]=0, [UnidadesEnExistencia]=0, [UnidadesEnPedido]=0
WHEN NOT MATCHED By Source THEN
        DELETE;



/************************************/
select top 10 percent *
from Compras.productos
order by PrecioUnidad asc
go



/*
dos tipos de funciones
*del sistema
*definidas por el usuario
    -Escalares
    -Tablas en lineas
    -Multisentencias
*/

USE Negocios2013
GO

--Creando una funcion escalar
create function dbo.fnCalculoCom(@p_monto Money, @p_porcentaje numeric(6,3))
Returns Money
As
Begin
    Declare @v_resultado Money
    Set @v_resultado=@p_monto*@p_porcentaje/100
    Return @v_resultado
End
Go

--Utilizando la función escalar
Print dbo.fnCalculoCom(1000,15)


--Utilizando la fución escalar dentro de una consulta
Select
P.IdPedido,
E.NomEmpleado,
sum(D.Cantidad * D.PrecioUnidad) as Monto,
sum(dbo.fnCalculoCom((D.Cantidad * D.PrecioUnidad), 5)) as Comision
From Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido=D.IdPedido
Where Year(P.FechaPedido)=2010
group by P.IdPedido,
    E.NomEmpleado
go


create function dbo.fnConcat(@p_cad1 as varchar(max), @p_cad2 as varchar(max))
returns varchar(max)
As
Begin
    Return CONCAT(@p_cad1, ' ' ,@p_cad2)--space(1)
End


Print dbo.fnConcat('Hernán', 'Oscanoa')
Go


Select
P.IdPedido,
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado) as [Nombre y Apellidos],
sum(D.Cantidad * D.PrecioUnidad) as Monto,
sum(dbo.fnCalculoCom((D.Cantidad * D.PrecioUnidad), 5)) as Comision
From Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido=D.IdPedido
Where Year(P.FechaPedido)=2010
group by P.IdPedido,
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado)
go

Select
P.IdPedido,
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado) as [Nombre y Apellidos],
sum(D.Cantidad * D.PrecioUnidad) as Monto,
sum(dbo.fnCalculoCom((D.Cantidad * D.PrecioUnidad), 5)) as Comision
From Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido=D.IdPedido
Where Year(P.FechaPedido)=2010
group by P.IdPedido,
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado)  with cube
go


Select
isNull(cast(P.IdPedido as Varchar(15)), 'Stotal' ) [Pedido],
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado) as [Nombre y Apellidos],
sum(D.Cantidad * D.PrecioUnidad) as Monto,
sum(dbo.fnCalculoCom((D.Cantidad * D.PrecioUnidad), 5)) as Comision
From Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido=D.IdPedido
Where Year(P.FechaPedido)=2010
group by P.IdPedido,
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado)  with cube
go


Select
isNull(cast(P.IdPedido as Varchar(15)), replicate('*', 15)) [Pedido],
isNull(dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado), '**T O T A L**' ) as [Nombre y Apellidos],
sum(D.Cantidad * D.PrecioUnidad) as Monto,
sum(dbo.fnCalculoCom((D.Cantidad * D.PrecioUnidad), 5)) as Comision
From Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido=D.IdPedido
Where Year(P.FechaPedido)=2010
group by P.IdPedido,
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado)  with cube
go


Select
P.IdPedido [Pedido],
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado) as [Nombre y Apellidos],
sum(D.Cantidad * D.PrecioUnidad) as Monto,
sum(dbo.fnCalculoCom((D.Cantidad * D.PrecioUnidad), 5)) as Comision
From Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido=D.IdPedido
Where Year(P.FechaPedido)=2010
group by P.IdPedido,
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado)  with ROLLUP
go

SELECT
P.IdPedido [Pedido],
PR.NomProducto,
PR.CantxUnidad,
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado) as [Nombre y Apellidos],
sum(D.Cantidad * D.PrecioUnidad) as Monto,
sum(dbo.fnCalculoCom((D.Cantidad * D.PrecioUnidad), 5)) as Comision,
count(D.IdProducto) as [Cantidad de Productos]
From Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido = D.IdPedido join Compras.productos PR
on D.IdProducto=PR.IdProducto
Where Year(P.FechaPedido)=2010
group by
P.IdPedido,
E.NomEmpleado,E.ApeEmpleado,
PR.NomProducto,
PR.CantxUnidad with cube
go


SELECT
P.IdPedido [Pedido],
dbo.fnConcat(E.NomEmpleado,E.ApeEmpleado) as [Nombre y Apellidos],
PR.NomProducto,
PR.CantxUnidad,
sum(D.Cantidad * D.PrecioUnidad) as Monto,
sum(dbo.fnCalculoCom((D.Cantidad * D.PrecioUnidad), 5)) as Comision,
count(D.IdProducto) as [Cantidad de Productos]
From Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido = D.IdPedido join Compras.productos PR
on D.IdProducto=PR.IdProducto
Where Year(P.FechaPedido)=2010
group by
P.IdPedido,
E.NomEmpleado,E.ApeEmpleado,
PR.NomProducto,
PR.CantxUnidad with rollup
go


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


/*************FUNCION DE TABLA EN LINEA**************/
create function IFPedidosxAño
(@p_año smallint)
Returns table
as
    return (select * from Ventas.pedidoscabe
            Where year(FechaPedido)=@p_año)
go

/*Verificamos la existencia de la función
IF=In line table function
*/

select * from sys.objects
Where type = 'IF'
go
/*Verificamos la existencia de la función escalar
*/

select * from sys.objects
Where type = 'Fn'
go


select * from IFPedidosxAño(2010)
go


/*
Crear una funcion de tabla en linea que visualice
los productos que son sumistrados por el por un
determinado proveedor, mostrando la cantidad total suministrada.
Se deberá considerar dos parametros ID DE PROVEEDOR Y iD DE PRODUCTO,
los datos a visualizar sera nombre dl proveedor, nombre del producto
y el total de unidades en existencia
*/

create function IFProveedorProducto
    (@p_idprov int, @p_idprod int)
Returns table
as
    return (Select PV.NomProveedor, PR.NomProducto, PR.UnidadesEnExistencia
    from Compras.proveedores PV inner join Compras.productos PR
    on PV.IdProveedor=PR.IdProveedor
    Where PV.IdProveedor=@p_idprov and PR.IdProducto=@p_idprod)
Go


begin try
    declare @p_idprov int, @p_idprod int
    set @p_idprov = 1
    set @p_idprod = 10
    if not exists(select * from IFProveedorProducto( @p_idprov ,@p_idprod))
        raiserror('No hay datos para mostrar', 10, 1)
    else
        select * from IFProveedorProducto( @p_idprov ,@p_idprod)
end try
begin catch
    if error_number()=50000
        print error_message()
end catch
GO



--PIVOT
/*******************************************/
/*
Consulta normal con group by
*/
select Year(P.Fechapedido) [Año],
    sum(D.cantidad*D.PrecioUnidad) AS MontoTotal
from Ventas.pedidoscabe P join RRHH.empleados E
on P.IdEmpleado=E.IdEmpleado join Ventas.pedidosdeta D
on P.IdPedido = D.IdPedido
group by Year(P.FechaPedido)
order By 1 asc



/****************************************************************/
/***Consulta usando Pivot*****/
Select 'Monto Total' As Monto,
        [1996],[1997],[1998],[2007],[2008],[2009],[2010],[2011]
From (Select Year(P.FechaPedido) 'Año',
            D.Cantidad * D.PrecioUnidad as ST
    From Ventas.pedidoscabe P Join RRHH.empleados E
        On P.IdEmpleado = E.IdEmpleado Join Ventas.pedidosdeta D
        On P.IdPedido = D.IdPedido  ) As SourceTable
    Pivot
        (Sum(ST)
         For Año In ([1996],[1997],[1998],[2007],[2008],[2009],[2010],[2011])
            ) As PivotTable
go