Mainly, in a where clause, how do you use two cases correctly for the same thing from two different spots?
Spot1 the table on hand
spot2 the table you join
one set of transactions gets the machine Id as a number.
Machines 1-18 for example.
So I use inner join with a table of names to get the line names.
This one gets me my rejects numbers
production numbers are null in these entries
one set of transactions just gets the line names.
This one gets me my production numbers.
rejects are null in these entries
my code is something like:
Select subq.Line,year_month, rejects, subq.highOutfeed-subq.lowOutfeed as Outputs
from(
Select min(t_stamp) as earliest, Format(t_stamp,'yyyy-MMM') as year_month,
min(Outfeed) as lowOutfeed, max(Outfeed) as highOutfeed, LineNamesTable.Line
from Tablewithrejects
inner join LineNamesTable on LineNamesTable.Machine=Tablewithrejects.Machine
Where (t_stamp>DATEADD(month, -6,EOMONTH( GETDATE()))) and (:machine=-1 or
LineNamesTable.Line = Case when :machine=1 then 'machine-1'
when :machine=2 then 'machine-2'
else null end
or
Tablewithrejects.Line=Case when :machine=1 then 'machine1'
when :machine=2 then 'machine2'
else null end)
group by Format(t_stamp, 'yyyy-MMM'), LineNamesTable.Line, Tablewithrejects.Line
) subq
order by subq.Line, earliest
so as I wrote this I see that I have to resolve this line for subq.
The query is not throwing an error. I get the data from the rejects, but not the production.
Not sure if Iām fully understanding, but what about trying
Where (t_stamp>DATEADD(month, -6,EOMONTH( GETDATE()))) and (:machine=-1 or
LineNamesTable.Line = āmachine-ā + cast(machine as varchar(1))
or
Tablewithrejects.Line= āmachineā + cast(machine as varchar(1))
group by Format(t_stamp, āyyyy-MMMā), LineNamesTable.Line, Tablewithrejects.Line
) subq
in my where clause, I have code > 1100, but when I collect my production information, my codes are below 1100, so I need some way to both isolate to those codes to tally my rejects, but still pick up the production values
Charis, that is a good idea. Unfortunately, I had presented generic names, but in reality the names are more categorical in nature. I would have used your recommendation for sure.
I am not sure my cases are written correctly.
When I say āelse null endā is that correct for each? or is one ending and then the other is skipped?
And is there some subquery way to get the production values from rows with codes under 1100, but not count the code events from the codes under 1100?
When I say āelse null endā is that correct for each? or is one ending and then the other is skipped?
I am still not reading these other codes in my query.
I added or Code=9000 to my where clause
I expected to see the values show, but they donāt
@zacharyw.larson see @pturmel above post for clarification. Well it is generally all the āsameā thing, they do have differences and methods of doing stuff can vary. If you let us know specifically which one you are using, it can better help guide an answer, best practice, or relevant docs how to properly code it.
When I say āelse null endā is that correct for each? or is one ending and then the other is skipped?
I am still not reading these other codes in my query.
I added or Code=9000 to my where clause
I expected to see the values show, but they donāt
I put left join and also full join
both show me the production values, but the lines just say null. I had expected my new transaction names to work. Not sure what happened. Maybe I can tinker with it.
I am going to modify my table with the names to include both subsets (with dash and without dash) and the number for the machines.
Maybe then I can get it to display the name from that table for both conversions.
I donāt know that I can be helped much directly on the issue I am having with the naming, but at least if you could tell me the correct ms sql syntax for two cases with an or in the where clause, I would appreciate it.
is this okay syntax in MS SQL for two cases orād in the where clause?
I donāt know the specific version. Whatever version W3schools is using has been working so far.
where
(:machine=-1 or
LineNamesTable.Line = Case when :machine=1 then 'machine-1'
when :machine=2 then 'machine-2'
else null end
or
Tablewithrejects.Line=Case when :machine=1 then 'machine1'
when :machine=2 then 'machine2'
else null end)
The issue has changed. The cases are working. I have the data collected into my table, but they have different names.
One is from my conversion table from number to table name.
One is from the table on hand.
And this is more convoluted because one set even splits a machine in half from the PLC.
I think I need to make another conversion table.
I like half the names in the one set, and half the names in the other set.
I will work on this tomorrow.
I like the names with the dashes, but I donāt want the machines by parts so I need to total those which means converting in tables and grouping by the converted name I think.
You should consider not using names for joining. Just make a lookup table that has a numeric machine ID and the various static information about it, like its name(s). Use the numeric machine ID in all the of the tables that actually record your data.
agreed, the best way is to have avoided this mess to begin with haha
Is it possible to do two different joins with one lookup table matching two different columns to get one set of names? I am attempting it.
I think I have two parts to one machine from the plc though, and I donāt think it will tally them. Maybe it will if I group it right. I am not sure.
edit: I found a resource
This was really helpful for situations of joining multiple times to the same table and it has some other unusual joins.
yet, when they double join, they select two different columns, each from a different relabel of the lookup table. Maybe I do that, then a coalesce/concat?
Or maybe I need a select case
it finally hit me
I had half my table correct for rejects
half my table correct for production
I just need to make two subquery tables, and join them on the date format column and the converted names
