SQL Query - Get additional info into query..
Hello,
I am having difficulty understanding how I can get some data to show in my query - This query sits in a stored procedure and called from report services report. Below is my query, the field I would like to have remain constant (in other words have all five records returned even if there is no data attached to it) is the: id.inventory_state_name_EN
Right now the data comes back by province and by state - So, I could see five records for
Alberta with two different inventory states because tghere is related data for all the other fields. I would like to see all these records and then for each provinces any of the other inventory states that do not having any related records. Have I just made this clear as mud..the field that is referenced comes from the view vwInventory_Denormalized. If anybody can point out to me how this can be done - much, much appreciated!
Select x.*
,CASE concentration_range WHEN 'QTY(L)' THEN
CASE when Concentration < 10000 AND Concentration <> 0 AND Concentration <> -1 Then
CASE WHEN phase_type_name = 'Solid' Then
(concentration * quantity * 1/1000000)
ELSE
(Concentration * quantity * 0.9 * 1/ 1000000)
END
when Concentration >= 10000 Then
CASE WHEN phase_type_name = 'Solid' Then
(Concentration * quantity * 0.9 * 1/ 1000000)
ELSE
(Concentration * quantity * 1.5 * 1/ 1000000)
END
ELSE CASE WHEN phase_type_name = 'Solid' Then
(700000 * quantity * 1/ 1000000)
ELSE
(700000 * quantity * 1.5 * 1/ 1000000)
END
END
WHEN 'QTY(L) 50/500' THEN
CASE WHEN phase_type_name = 'Solid' Then
Case WHEN Concentration IS NOT NULL AND Concentration <> 0 AND Concentration <> -1 Then
(275 * quantity * 1 / 1000000)
Else
(700000 * quantity * 1 / 1000000)
END
ELSE
Case WHEN Concentration IS NOT NULL AND Concentration <> 0 AND Concentration <> -1 Then
(275 * quantity * 0.9 * 1/ 1000000)
Else
(700000 * quantity * 1.5 * 1/ 1000000)
END
END
WHEN 'QTY(L) >500' THEN
CASE WHEN phase_type_name = 'Solid' Then
(700000 * quantity * 1 / 1000000)
ELSE
(700000 * quantity * 1.5 * 1/ 1000000)
END
END AS PCBInLiquid
FROM (Select id.report_id,rd.title, pc.PROV_STATE_NAME_EN as prov_state_name,rd.Operati
rd.report_type_name_en as report_type_name,
CASE id.inventory_state_name_EN
WHEN 'IN USE' Then
'1 In Use on Dec 31st'
WHEN 'STORED' Then
'3 Stored on Dec 1st'
WHEN 'TRANSFERRED' Then
'2 Sent to Transfer Site'
WHEN 'SENT FOR DESTRUCTION' Then
'4 Sent to Destruction'
WHEN 'DESTROYED' Then
'5 Destroyed'
END as inventory_state_name,id.ph
CASE id.in_equipment
WHEN 'false' THEN 'Liquid In Equipment'
WHEN 'true' THEN 'Liquid Not In Equipment'
END AS InEquipment , Round(id.quantity, 2, 0) As quantity,id.unit_en as unit,
id.Concentration_value AS Concentration,
CASE CAST(id.concentration_rang
WHEN '0' THEN 'QTY(L)'
WHEN '50' THEN 'QTY(L) 50/500'
WHEN '500' THEN 'QTY(L) >500'
END as concentration_range
FROM vwInventory_Denormalized AS id Inner JOIN vwReportLegalEntity_Denorm
ON id.report_id = rd.report_id Inner JOIN Company_location cl
ON cl.Legal_entity_id = rd.legal_entity_id Inner JOIN Location_address la
ON la.location_id = cl.location_id Inner JOIN ProvState_code pc
ON pc.Prov_state_code = la.Prov_state_code Inner JOIN EC_Region r
ON r.EC_Region_id = pc.Region_id Left outer join report_amendment AS a1
ON a1.report_old_id=id.report
WHERE id.quantity > 0
AND rd.REPORT_TYPE_ID = 2
AND rd.REPORT_STATE_NAME_EN = 'SUBMITTED'
AND rd.Year = @YEAR
AND a1.report_old_id is null
GROUP BY pc.PROV_STATE_NAME_EN,
rd.Operating_entity_name,
rd.YEAR,
rd.report_type_name_EN,
rd.title,
id.inventory_state_name_EN
id.phase_type_name_EN,
id.report_id,
id.unit_EN,
id.concentration_range,
id.in_equipment,
rd.report_type_name_EN,
id.quantity,
id.Concentration_value)
as x
ORDER BY PROV_STATE_NAME asc,
inventory_state_name asc,
Concentration desc
I am having difficulty understanding how I can get some data to show in my query - This query sits in a stored procedure and called from report services report. Below is my query, the field I would like to have remain constant (in other words have all five records returned even if there is no data attached to it) is the: id.inventory_state_name_EN
Right now the data comes back by province and by state - So, I could see five records for
Alberta with two different inventory states because tghere is related data for all the other fields. I would like to see all these records and then for each provinces any of the other inventory states that do not having any related records. Have I just made this clear as mud..the field that is referenced comes from the view vwInventory_Denormalized. If anybody can point out to me how this can be done - much, much appreciated!
Select x.*
,CASE concentration_range WHEN 'QTY(L)' THEN
CASE when Concentration < 10000 AND Concentration <> 0 AND Concentration <> -1 Then
CASE WHEN phase_type_name = 'Solid' Then
(concentration * quantity * 1/1000000)
ELSE
(Concentration * quantity * 0.9 * 1/ 1000000)
END
when Concentration >= 10000 Then
CASE WHEN phase_type_name = 'Solid' Then
(Concentration * quantity * 0.9 * 1/ 1000000)
ELSE
(Concentration * quantity * 1.5 * 1/ 1000000)
END
ELSE CASE WHEN phase_type_name = 'Solid' Then
(700000 * quantity * 1/ 1000000)
ELSE
(700000 * quantity * 1.5 * 1/ 1000000)
END
END
WHEN 'QTY(L) 50/500' THEN
CASE WHEN phase_type_name = 'Solid' Then
Case WHEN Concentration IS NOT NULL AND Concentration <> 0 AND Concentration <> -1 Then
(275 * quantity * 1 / 1000000)
Else
(700000 * quantity * 1 / 1000000)
END
ELSE
Case WHEN Concentration IS NOT NULL AND Concentration <> 0 AND Concentration <> -1 Then
(275 * quantity * 0.9 * 1/ 1000000)
Else
(700000 * quantity * 1.5 * 1/ 1000000)
END
END
WHEN 'QTY(L) >500' THEN
CASE WHEN phase_type_name = 'Solid' Then
(700000 * quantity * 1 / 1000000)
ELSE
(700000 * quantity * 1.5 * 1/ 1000000)
END
END AS PCBInLiquid
FROM (Select id.report_id,rd.title, pc.PROV_STATE_NAME_EN as prov_state_name,rd.Operati
rd.report_type_name_en as report_type_name,
CASE id.inventory_state_name_EN
WHEN 'IN USE' Then
'1 In Use on Dec 31st'
WHEN 'STORED' Then
'3 Stored on Dec 1st'
WHEN 'TRANSFERRED' Then
'2 Sent to Transfer Site'
WHEN 'SENT FOR DESTRUCTION' Then
'4 Sent to Destruction'
WHEN 'DESTROYED' Then
'5 Destroyed'
END as inventory_state_name,id.ph
CASE id.in_equipment
WHEN 'false' THEN 'Liquid In Equipment'
WHEN 'true' THEN 'Liquid Not In Equipment'
END AS InEquipment , Round(id.quantity, 2, 0) As quantity,id.unit_en as unit,
id.Concentration_value AS Concentration,
CASE CAST(id.concentration_rang
WHEN '0' THEN 'QTY(L)'
WHEN '50' THEN 'QTY(L) 50/500'
WHEN '500' THEN 'QTY(L) >500'
END as concentration_range
FROM vwInventory_Denormalized AS id Inner JOIN vwReportLegalEntity_Denorm
ON id.report_id = rd.report_id Inner JOIN Company_location cl
ON cl.Legal_entity_id = rd.legal_entity_id Inner JOIN Location_address la
ON la.location_id = cl.location_id Inner JOIN ProvState_code pc
ON pc.Prov_state_code = la.Prov_state_code Inner JOIN EC_Region r
ON r.EC_Region_id = pc.Region_id Left outer join report_amendment AS a1
ON a1.report_old_id=id.report
WHERE id.quantity > 0
AND rd.REPORT_TYPE_ID = 2
AND rd.REPORT_STATE_NAME_EN = 'SUBMITTED'
AND rd.Year = @YEAR
AND a1.report_old_id is null
GROUP BY pc.PROV_STATE_NAME_EN,
rd.Operating_entity_name,
rd.YEAR,
rd.report_type_name_EN,
rd.title,
id.inventory_state_name_EN
id.phase_type_name_EN,
id.report_id,
id.unit_EN,
id.concentration_range,
id.in_equipment,
rd.report_type_name_EN,
id.quantity,
id.Concentration_value)
as x
ORDER BY PROV_STATE_NAME asc,
inventory_state_name asc,
Concentration desc
Sorry - didn't notice your other left join
try this
FROM ProvState_code pc left outer join
(vwInventory_Denormalized AS id
Inner JOIN vwReportLegalEntity_Denorm
Inner JOIN Company_location cl ON cl.Legal_entity_id = rd.legal_entity_id
Inner JOIN Location_address la ON la.location_id = cl.location_id
Inner JOIN EC_Region r ON r.EC_Region_id = pc.Region_id
Left outer join report_amendment AS a1 ON a1.report_old_id=id.report
) ON pc.Prov_state_code = la.Prov_state_code
try this
FROM ProvState_code pc left outer join
(vwInventory_Denormalized AS id
Inner JOIN vwReportLegalEntity_Denorm
Inner JOIN Company_location cl ON cl.Legal_entity_id = rd.legal_entity_id
Inner JOIN Location_address la ON la.location_id = cl.location_id
Inner JOIN EC_Region r ON r.EC_Region_id = pc.Region_id
Left outer join report_amendment AS a1 ON a1.report_old_id=id.report
) ON pc.Prov_state_code = la.Prov_state_code
ASKER
No this does not work - it gives an error message...I was thinking, that this field, the inventory_state_name_en - it is within the vwInventory_denomalized view. But those values are from a look-up table called inventory_state. I have been trying to join that table to this query, and using the WHERE clause, specify only the five states that are relevant to this...What I tried is below. I would like the return data to have everything it has now, but if the province of Alberta (say), has five records, two are 'Destroyed', one is 'In Use' (these values are the inventory_state_name_en field) then the remaining three states ie/ transferred, sent to destruction, and stored still show up in the query results, just with NULLS everywhere..For the life of me, I cannot get that to happen - going in circles here.
FROM dbo.vwInventory_Denormaliz
dbo.vwReportLegalEntity_De
dbo.Location_Address AS la ON la.LOCATION_ID = cl.LOCATION_ID INNER JOIN
dbo.ProvState_Code AS pc ON pc.PROV_STATE_CODE = la.PROV_STATE_CODE INNER JOIN
dbo.EC_Region AS r ON r.EC_Region_ID = pc.REGION_ID LEFT OUTER JOIN
dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID RIGHT OUTER JOIN
dbo.Report_Amendment AS a1 ON a1.REPORT_OLD_ID = id.REPORT_ID
WHERE (id.QUANTITY > 0) AND (rd.REPORT_TYPE_ID = 2) AND (rd.REPORT_STATE_NAME_EN = 'SUBMITTED') AND (rd.YEAR = 2009) AND
(a1.REPORT_OLD_ID IS NULL) OR
(t.INVENTORY_STATE_ID = 1) OR
(t.INVENTORY_STATE_ID = 5) OR
(t.INVENTORY_STATE_ID = 6) OR
(t.INVENTORY_STATE_ID = 10) OR
(t.INVENTORY_STATE_ID = 11)
FROM dbo.vwInventory_Denormaliz
dbo.vwReportLegalEntity_De
dbo.Location_Address AS la ON la.LOCATION_ID = cl.LOCATION_ID INNER JOIN
dbo.ProvState_Code AS pc ON pc.PROV_STATE_CODE = la.PROV_STATE_CODE INNER JOIN
dbo.EC_Region AS r ON r.EC_Region_ID = pc.REGION_ID LEFT OUTER JOIN
dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID RIGHT OUTER JOIN
dbo.Report_Amendment AS a1 ON a1.REPORT_OLD_ID = id.REPORT_ID
WHERE (id.QUANTITY > 0) AND (rd.REPORT_TYPE_ID = 2) AND (rd.REPORT_STATE_NAME_EN = 'SUBMITTED') AND (rd.YEAR = 2009) AND
(a1.REPORT_OLD_ID IS NULL) OR
(t.INVENTORY_STATE_ID = 1) OR
(t.INVENTORY_STATE_ID = 5) OR
(t.INVENTORY_STATE_ID = 6) OR
(t.INVENTORY_STATE_ID = 10) OR
(t.INVENTORY_STATE_ID = 11)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, I realise it's hard ... I'm going to include a piece of the data output below - these are the results from the query without any additions for the inventory_state_name field. I tried the options above and none of them give the desired results...If I can offer any further explanation so as to help you ,help me :) please let me know!
example.xls
example.xls
Try this. I created example table from you data.
drop table #province_names
go
drop table #inventory_states
go
-- this is list of all provinces you have
select distinct province into #province_names from [example] order by province
go
-- this is list of all inventory_state_names you have
select distinct inventory_state_name into #inventory_states from [example] order by inventory_state_name
select p.province, s.inventory_state_name, sum(quantity)
from (#province_names p full outer join #inventory_states s on 1=1)
left join [example] data on p.province=data.province and s.inventory_state_name=data.inventory_state_name
group by p.province, s.inventory_state_name
order by p.province, s.inventory_state_name
ASKER
The WHERE clause had a condition that I continued to overlooked - this response held the key to correcting the query - Thanks SO much!
left outer JOIN ProvState_code pc
Move this join to be the last one in you joins