Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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.Operating_entity_name,rd.YEAR,
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.phase_type_name_en as phase_type_name,
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_range as varchar(30))
      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_Denormalized AS rd
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_id
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
0
Mosquitoe
Asked:
Mosquitoe
  • 4
  • 3
1 Solution
 
Lara FEACommented:
Try  outer join
left outer JOIN ProvState_code pc
 
Move this join to be the last one in you joins
0
 
Lara FEACommented:
Sorry - didn't notice your other left join

try this
 FROM ProvState_code pc left outer join
(vwInventory_Denormalized AS id
Inner JOIN vwReportLegalEntity_Denormalized AS rd 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 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_id
) ON pc.Prov_state_code = la.Prov_state_code
0
 
MosquitoeAuthor Commented:
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_Denormalized AS id INNER JOIN
                      dbo.vwReportLegalEntity_Denormalized AS rd ON id.REPORT_ID = rd.REPORT_ID INNER JOIN dbo.Company_Location AS cl ON cl.LEGAL_ENTITY_ID = rd.LEGAL_ENTITY_ID INNER JOIN
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)
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Lara FEACommented:
It is difficult to debug it without data.
here are things to look:
1) You do "left join as
LEFT OUTER JOIN
dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID

and at the same time  in your "where" you have
((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))

Your where eliminates your left join completely since in result of left join if there are any t.INVENTORY_STATE_ID will be null
move this where to join
as
LEFT OUTER JOIN
dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID
   and t.INVENTORY_STATE_ID in (1,5,6,10,11)

or if state ID is always populated change "where"
(t.INVENTORY_STATE_ID in (1,5,6,10,11) or t.INVENTORY_STATE_ID is null)

2) the same problem with right join. You can not put anything into outer joined table into where without explicitly checking for nulls.

3)
also check parentheses in you "where". I would say you enclose all "or" conditions as

(rd.YEAR = 2009) AND
         (            (a1.REPORT_OLD_ID IS NULL) OR
                      (t.INVENTORY_STATE_ID in (1,5,6,10,11) or t.INVENTORY_STATE_ID is null)
        )
 


dbo.Inventory_State AS t ON id.INVENTORY_STATE_ID = t.INVENTORY_STATE_ID
0
 
MosquitoeAuthor Commented:
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
0
 
Lara FEACommented:
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

Open in new window

0
 
MosquitoeAuthor Commented:
The WHERE clause had a condition that I continued to overlooked - this response held the key to correcting the query - Thanks SO much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now