declare @datos table
(iddevice int null,
SerialNumber varchar(100) null,
Estado varchar(50) null,
Factura varchar(30))
insert into @datos
select id_device, SerialNumber, click_TipoEstado.Name,null from tabla_Devices
inner join tabla_TiposEstado
on tabla_Devices.FK_id_DeviceEstado = tabla_TiposEstado.id_Estado
update @datos
set Factura = InvoiceNumber
from Facturas
where fk_id_device = iddevice and YEAR(invoicedate) = 2011 and MONTH(invoicedate) = 5
select Estado, COUNT(Estado) as Cuantos, COUNT(Factura) as Facturados from @datos
group by Estado
select * from @datos where Estado = 'Funcionando' and Factura is null
(iddevice int null,
SerialNumber varchar(100) null,
Estado varchar(50) null,
Factura varchar(30))
insert into @datos
select id_device, SerialNumber, click_TipoEstado.Name,null from tabla_Devices
inner join tabla_TiposEstado
on tabla_Devices.FK_id_DeviceEstado = tabla_TiposEstado.id_Estado
update @datos
set Factura = InvoiceNumber
from Facturas
where fk_id_device = iddevice and YEAR(invoicedate) = 2011 and MONTH(invoicedate) = 5
select Estado, COUNT(Estado) as Cuantos, COUNT(Factura) as Facturados from @datos
group by Estado
select * from @datos where Estado = 'Funcionando' and Factura is null
Comentarios
Publicar un comentario