Seguimos buscando a Arshak. Ayudanos compartiendo!
Encuesta no oficial de docentes
Resultados de la encuesta no oficial de docentes
Probaste el SIGA Helper?

Donar $100 Donar $200 Donar $500 Donar mensualmente


Enviar respuesta 
 
Calificación:
  • 0 votos - 0 Media
  • 1
  • 2
  • 3
  • 4
  • 5
Buscar en el tema
Parcial resuelto Gdd Zaffaroni
Autor Mensaje
Julian.O Sin conexión
Militante
ing :)
***

Ing. en Sistemas
Facultad Regional Buenos Aires

Mensajes: 50
Agradecimientos dados: 91
Agradecimientos: 149 en 35 posts
Registro en: Apr 2016
Mensaje: #1
Parcial resuelto Gdd Zaffaroni Parciales Gestión de Datos
Hola les dejo el parcial del 2do cuatri de 2019 con las resoluciones del profe.

Éxitos!!

Query



CREATE VIEW comprasFabricanteLider (nombreFabricante, nomCliente, apeCliente, producto, totalPesos, totalCantidad)
AS
SELECT manu_name,
fname,
lname,
description, sum(unit_price * quantity) totalPesos,
sum(quantity) totalCantidad
FROM orders o JOIN items i ON o.order_num=i.order_num
JOIN customer c ON o.customer_num=c.customer_num
JOIN product_types p ON i.stock_num=p.stock_num
JOIN manufact m ON i.manu_code=m.manu_code
JOIN (SELECT TOP 1 manu_code, SUM(unit_price * quantity) totsum
FROM items
GROUP BY manu_code
ORDER BY totsum DESC) max ON max.manu_code=i.manu_code
WHERE description LIKE '%ball%'
GROUP BY manu_name, c.customer_num, fname, lname, description
HAVING SUM(unit_price * quantity)/SUM(quantity)>150



Stored Procedure



CREATE Procedure vxmProc
@fecha datetime
as
begin
begin try
begin transaction

declare @cadenaFecha varchar(6)
set @cadenaFecha = cast(year(@fecha)*100+month(@fecha) as varchar(6))

insert into VENTASxMES
Select @cadenaFecha, i.stock_num, i.manu_code,
sum(case when p.unit_code = 1 then quantity
when p.unit_code = 2 then quantity*2
when p.unit_code = 3 then quantity*12
end),
sum(quantity*i.unit_price)
from orders o
join items i on o.order_num = i.order_num
join products p on p.manu_code = i.manu_code
and p.stock_num = i.stock_num
where year(order_date) = year(@fecha) and
month(order_date) = month(@fecha)
group by i.manu_code, i.stock_num;
--
commit;
end try
begin catch
rollback;
end catch
end



Trigger



create trigger auditCustomer on customer
after delete, update as
begin
declare @customer_num int,
@apeynomNew varchar(40), @stateNew char(2),
@customer_num_refered_byNew int,
@apeynomOld varchar(40), @stateOld char(2),
@customer_num_refered_byOld int

declare auditCur CURSOR FOR
select d.customer_num,
i.lname + '' + i.fname, i.state, i.customer_num_referedby,
d.lname + '' + d.fname, d.state, d.customer_num_referedby
from deleted d
left join inserted i on i.customer_num = d.customer_num;

OPEN auditCur
FETCH NEXT FROM auditCur
into @customer_num,
@apeynomNew, @stateNew, @customer_num_refered_byNew,
@apeynomOld, @stateOld, @customer_num_refered_byOld;

while (@@FETCH_STATUS = 0)
begin
begin try
begin transaction
if not exists(select 1 from inserted)
begin
insert into customer_audit(customer_num, update_Date, apeynom_OLD,
state_Old, customer_num_referedby_OLD, update_user)
values (@customer_num, getDate(), @apeynomOld, @stateOld,
@customer_num_refered_byOld, SYSTEM_USER)
end
else
begin
if not exists(select 1 from customer
where customer_num = @customer_num_refered_byNew)
THROW 50001, 'Referente inexistente', 1;
if not exists (select 1 from state where state = @stateNEW)
THROW 50002, 'Estado inexistente', 1;

insert into customer_audit(customer_num, update_Date,
apeynom_NEW, state_NEW, customer_num_referedby_NEW,
apeynom_OLD, state_Old, customer_num_referedby_OLD,
update_user)
values (@customer_num, getDate(),
@apeynomNew, @stateNew, @customer_num_refered_byNew,
@apeynomOld, @stateOld, @customer_num_refered_byOld, SYSTEM_USER)
end
commit transaction
end try

begin catch
rollback transaction
end catch

FETCH NEXT FROM auditCur
into @customer_num,
@apeynomNew, @stateNew, @customer_num_refered_byNew,
@apeynomOld, @stateOld, @customer_num_refered_byOld;
end

close auditCur
deallocate auditCur
END;




Archivo(s) adjuntos
.pdf  parcial.pdf (Tamaño: 2,63 MB / Descargas: 168)
20-12-2019 18:36
Encuentra todos sus mensajes Agregar agradecimiento Cita este mensaje en tu respuesta
[-] Julian.O recibio 3 Gracias por este post
Mauro_bilo (14-01-2020), Franco123 (09-06-2020), rocioleguizamon (31-05-2021)
Buscar en el tema
Enviar respuesta 




Usuario(s) navegando en este tema: 1 invitado(s)