/* SWPRODUCT TOTALS VIEW */
CREATE VIEW dbo.arg_swproduct_totals_view
AS
select
    isnull(ca_company.company_name, '') company_name,
    isnull(model_name, '') model_name,
    isnull(ca_software_def.name, '') software_product,
    isnull(license_type, '') license_type,
    isnull(owned_count, 0) owned_count,
    isnull(discovered_count, 0) discovered_count,
    isnull(owned_count, 0) - isnull(discovered_count, 0) variance,
    ca_company.company_uuid,
    model_uuid,
    ca_software_def.sw_def_uuid
from
    ca_software_def
    left outer join arg_discovered_swproducts_view on arg_discovered_swproducts_view.sw_def_uuid = ca_software_def.sw_def_uuid
    left outer join arg_owned_swproducts_view on arg_owned_swproducts_view.sw_def_uuid = ca_software_def.sw_def_uuid
    inner join ca_company on ca_company.company_uuid = ca_software_def.manufacturer_uuid
where
    owned_count is not null or discovered_count is not null
GO
GRANT SELECT ON  [dbo].[arg_swproduct_totals_view] TO [swcmadmin]
GO