question

arkiboys avatar image
0 Votes"
arkiboys asked arkiboys commented

select -group

Hello,
I would like to carry out rules on a sample table as below.
Can you see how to return the final result please?
Thank you

 create table #tblMain
 (
 V_Number varchar(50),m_name varchar(50),party varchar(50),m_shipment varchar(50),[Name] varchar(50)
 ,estimated date,actual date,ml_name varchar(50),Doc_Name varchar(50),[weight] float
 )
    
 insert into #tblMain (V_Number,m_name,party,m_shipment,[Name],estimated,actual,ml_name,Doc_Name,[weight])
 values ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','t DateName','7/1/2021','7/1/2021','destination','uk',33.2),
  ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','t DateName','7/1/2021','7/1/2021','source','france',33.2),
  ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','l DateName','7/1/2021','7/1/2021','source','uk',33.2),
  ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','l DateName','7/1/2021','7/1/2021','destination','scotland',33.2),
  ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','v DateName','7/30/2021',null,'source','scotland',33.2),
  ('V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','v DateName','7/30/2021',null,'destination','belgium',33.2),
  ('V_98xx','company999','partyNamexxx','SHIP_xyz','t DateName','7/1/2021','7/2/2021','destination','uk',40.12),
  ('V_98xx','company999','partyNamexxx','SHIP_xyz','t DateName','7/1/2021','7/2/2021','source','france',40.12),
  ('V_98xx','company999','partyNamexxx','SHIP_xyz','l DateName','7/1/2021','7/2/2021','source','uk',40.12),
  ('V_98xx','company999','partyNamexxx','SHIP_xyz','l DateName','7/1/2021','7/2/2021','destination','scotland',40.12),
  ('V_98xx','company999','partyNamexxx','SHIP_xyz','v DateName','7/30/2021','01/8/2021','destination','belgium',40.12),
  ('V_98xx','company999','partyNamexxx','SHIP_xyz','v DateName','7/30/2021','01/8/2021','source','scotland',40.12)
    
 select * from #tblMain
 --drop table #tblMain
 /*
 select V_Number, m_name from the #tblMain where [RULE as below] group by V_Number, m_name
    
 check rule:
 if
 [actual] date column of (t DateName) row values in [Name] column is not null
 and [actual] date column of (l DateName) row values is not null
 and [actual] date column of (v DateName) rows values is not null
 and weight is not null
    
 --The above rule should return:
 V_Number, m_name
 ----------------
 V_98xx companyNamexyz
 */
sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered arkiboys commented

It would have helped to have some sample data where my previous query gave the wrong result. But here is my next guess:

SELECT A.V_Number, A.m_name
 FROM   (SELECT DISTINCT V_Number, m_name FROM #tblMain) AS A
 WHERE  EXISTS (SELECT *
                FROM   #tblMain B
                WHERE  B.V_Number = A.V_Number
                  AND  B.m_name   = A.m_name
                  AND  B.actual IS NOT NULL 
                  AND  B.Name = 't DateName')
   AND  EXISTS (SELECT *
                FROM   #tblMain B
                WHERE  B.V_Number = A.V_Number
                  AND  B.m_name   = A.m_name
                  AND  B.actual IS NULL 
                  AND  B.Name IN ('v DateName', 'l DateName'))

I note that in this round of the game the column weight has fallen out of the rules.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

this is what I have. what do you think?
In addition, the weight is still there.

select
distinct
--v1.V_Number, v1.m_name, v1.actual, v1.ml_name, v1.Name, v1.Doc_Name,
v2.V_Number, v2.m_name, v2.actual, v2.ml_name, v2.Name, v2.Doc_Name, v3.V_Number
from
#tblMain as v1
inner join #tblMain as v2 on v1.V_Number = v2.V_Number and v1.m_name = v2.m_name
inner join #tblMain as v3 on v1.V_Number = v3.V_Number and v1.m_name = v3.m_name
where
(v1.Name = 't DateName' and v1.actual is not null)
and
(
(v2.Name = 'v DateName' and v2.actual is null)
OR (v3.Name = 'l DateName' and v3.actual is null)
)

0 Votes 0 ·

What I think? Well, in your original post you only wanted to columns back, here you return a lot more.

Anyway, if your query returns the correct result on your full data set, I guess you are happy. And if it does not, maybe my query does?

And if it does not, I think we need more sample data with more groups. Unfortunately, you explanation of the rules is someone terse, and difficult to understand. Bear in mind that we have no knowledge of the actual business problem you are trying to use. So it becomes a bit of a guessing game.

I don't think our queries are that much different, though.

0 Votes 0 ·
arkiboys avatar image arkiboys ErlandSommarskog ·

Your query helped and I converted it to below.
They both give the same results.
Which one do you prefer and why?
Thank you

select
distinct t1.V_Number, t1.m_name
from
#tblMain as t1
inner join #tblMain as t2 on t1.V_Number = t2.V_Number and t1.m_name = t2.m_name
where
(t1.actual is not null and t1.[Name] = 't DateName')
and (t2.actual is null and t2.[Name] in ('l DateName', 'v DateName'))

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered arkiboys edited

I can't say that I understand the rule fully, but I tried to read between the lines. Maybe this is what you are looking for:

SELECT A.V_Number, A.m_name
FROM   (SELECT DISTINCT V_Number, m_name FROM #tblMain) AS A
WHERE  EXISTS (SELECT *
                FROM   #tblMain B
                WHERE  B.V_Number = A.V_Number
                  AND  B.m_name   = A.m_name
                  AND (B.actual IS NULL OR
                       B.Name = 'v DateName' AND weight IS NULL))
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

is it not best to use self join instead?
Thanks

0 Votes 0 ·

As I said, I had to guess a bit, but for my understanding of the problem, I think this is most natural way of writing the query. Well, if there is a table that defines these (V_number, m_name) combos, I would rather use that table.

I guess your actual data set is larger. You need to try it and see if it meets your needs. If it does not, please give more examples, and explain what should be different.

0 Votes 0 ·
arkiboys avatar image arkiboys ErlandSommarskog ·

further details for better explanations in-case you may want to update your query accordingly:
the result should return as follows:
In the example, these are rows 12 and 13

 V_Number,m_name,party,m_shipment,[Name],estimated,actual,ml_name,Doc_Name,[weight]
 -------------------------------------------------------------------------------------
 'V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','v DateName','7/30/2021',null,'source','scotland',33.2
 'V_98xx','companyNamexyz','partyNamexxx','SHIP_xyz','v DateName','7/30/2021',null,'destination','belgium',33.2


Explanation:


This is because, these are the rows for the groups (V_Number, m_name)
which have the [actual] populated for the [Name] column with value (t DateName) populated
and
(
the [actual] column not populated for the [Name] column with value (v DateName) OR the [actual] column not populated for the [Name] column with value (l DateName)
)
and weight is not null

0 Votes 0 ·