| Author |
 Topic  |
|
|
Villanuev
Constraint Violating Yak Guru
315 Posts |
 Posted - 06/04/2012 : 02:20:15
|
Hi Forumer's
I have a query that will check the records from other table.
I could not figure to incorporate all my requirements.
Need your help guys. Thanks.
[Code]
--Requirements
1. if the ESN record in Data1 have only operation_type 4 and 5, the remarks should be 'Cores'
2. if the ESNs found no records from the table the remarks should be 'No records'
3. if the ESN have the operation type 2 the remarks is 'Not yet returned'
---Data
Create table #Sample
(ESN nvarchar(35))
Insert #Sample (ESN) Values ('268435459404494586')
Insert #Sample (ESN) Values ('268435459404494781')
Insert #Sample (ESN) Values ('268435459404507597')
Create table #Data1
(ESN nvarchar(35), operation_type int, rma_type int)
Insert #Data1 (ESN,operation_type, rma_type) Values ('268435459404494586',4, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',5, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',1, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',3, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',52,NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',2, NULL)
--Query
Select s1.ESN,
d1.rma_type,
d1.operation_type,
Case When d1.operation_type=2 Then ' Not yet returned '
When d1.operation_type= Then ' No Records'
When d1.operation_type=4 OR d1.operation_type=5 Then ' Cores'
End As 'Remarks'
from #Sample s1
Left Outer Join #Data1 d1 --not yet returned
On d1.ESN = s1.ESN and d1.operation_type = 2
Left Outer Join #Data1 d2 -- no recounrds found
On d2.ESN = s1.ESN
Group by s1.ESN, d1.rma_type, d1.operation_type
--Result
ESN |Remarks
---------------------------------------
268435459404494586| Cores
268435459404494781| No records
268435459404507597| Not yet Returned
Drop table #Data1
Drop table #Sample[/Code]
Thank you in Advance.
JOV |
Edited by - Villanuev on 06/04/2012 02:29:20
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/04/2012 : 04:46:25
|
You can avoid all those Joins by using one single Join as follows:
Select Distinct * From
(Select a.ESN,
(Case When b.operation_type IN (4,5) Then 'Cores'
When a.ESN Not IN (Select ESN From #Data1) Then 'No Records'
When b.operation_type = 2 then 'Not Yet Returned'
Else ''
End) As Remarks
From #Sample As a
Left JOIN #Data1 As b ON a.ESN = b.ESN) As c
Where Remarks <> ''
That might improve Performance of the query.
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
 |
|
|
Villanuev
Constraint Violating Yak Guru
315 Posts |
Posted - 06/04/2012 : 05:09:41
|
Hi Vinu. Thank you for your reply.
I will add some scenario for the "Cores" Remakrs.
my Previous post that the cores it should be operation type 4 & 5 (no other transaction in ESN)
what if i have other operation type asie from 4 & 5 from the ESNs.
Please take a look my sample data.
Btw, i place additional condition rma_type=5 and operation_type in (4,5)
but the result i got i have duplication ESNs with different remarks. it should be only one ESNs.
the correct result should be.
ESN|Remarks
------------------------------------
268435459404494586 Not Yet Returned
268435459404494781 No Records
268435459404507597 Not Yet Returned
[code]
--Data
Create table #Sample
(ESN nvarchar(35))
Insert #Sample (ESN) Values ('268435459404494586')
Insert #Sample (ESN) Values ('268435459404494781')
Insert #Sample (ESN) Values ('268435459404507597')
Drop table #Data1
Create table #Data1
(ESN nvarchar(35), operation_type int, rma_type int)
Insert #Data1 (ESN,operation_type, rma_type) Values ('268435459404494586',4, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',5, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',1, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',2, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',3, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',1, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',3, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',52,NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',2, NULL)
Select Distinct * From
(Select a.ESN,
(Case When b.rma_type=5 and b.operation_type IN (4,5) Then 'Cores'
When a.ESN Not IN (Select ESN From #Data1) Then 'No Records'
When b.operation_type = 2 then 'Not Yet Returned'
Else ''
End) As Remarks
From #Sample As a
Left JOIN #Data1 As b ON a.ESN = b.ESN) As c
Where Remarks <> ''
|
Edited by - Villanuev on 06/04/2012 05:10:48 |
|
|
|
mani_12345
Starting Member
India
35 Posts |
Posted - 06/04/2012 : 05:15:48
|
try this ...
with cte_name as (
select distinct coalesce(s1.esn,d1.esn )"ESN",
case when d1 .operation_type = 4 OR d1 .operation_type =5 then 'cores'
when d1.operation_type =2 then 'not yet returned '
when d1.operation_type IS null then 'no record '
end "output"
from #Sample s1 full outer join #Data1 d1 on s1.ESN =d1.ESN
)
select * from cte_name where output is not null |
|
|
|
Villanuev
Constraint Violating Yak Guru
315 Posts |
Posted - 06/04/2012 : 05:27:35
|
Thank for your reply.
I make some adjustment in the script and add the rma_type =5
but the result i got duplicate ESN. it should be display one ESNs.
ith cte_name as (
select distinct coalesce(s1.esn,d1.esn )"ESN",
case when d1 .operation_type = 4 OR d1 .operation_type =5 and d1.rma_type=5 then 'cores'
when d1.operation_type =2 then 'not yet returned '
when d1.operation_type IS null then 'no record '
end "output"
from #Sample s1 full outer join #Data1 d1 on s1.ESN =d1.ESN
)
select * from cte_name where output is not null
ESN|Output
---------------------------
268435459404494586 cores
268435459404494586 not yet returned
268435459404494781 no record
268435459404507597 not yet returned me where output is not null
Correct result: since i have the operation type 4,5,1,2,3 for this ESN "268435459404494586"
the result will be "not yet returned" . if the operation type is 4,5 other than that the result will be "cores"
--------------
ESN|Output
----------------
268435459404494586 not yet returned
268435459404494781 no record
268435459404507597 not yet returned me where output is not null
|
|
|
|
mani_12345
Starting Member
India
35 Posts |
Posted - 06/04/2012 : 05:36:58
|
i guess u r doing something wrong i changed it too and getting same ans
with cte_name as (
select distinct coalesce(s1.esn,d1.esn )"ESN",
case when d1 .operation_type = 4 OR d1 .operation_type =5
and d1.rma_type =5 then 'cores'
when d1.operation_type =2 then 'not yet returned '
when d1.operation_type IS null then 'no record '
end "output"
from #Sample s1 full outer join #Data1 d1 on s1.ESN =d1.ESN
)
select * from cte_name where output is not null
|
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/04/2012 : 05:53:15
|
I have done nothing but simply put the following 3 business requirements into a query:
1. if the ESN record in Data1 have only operation_type 4 and 5, the remarks should be 'Cores'
2. if the ESNs found no records from the table the remarks should be 'No records'
3. if the ESN have the operation type 2 the remarks is 'Not yet returned'
The query works perfectly with the requirements.
If the result set shows a duplicate ESN that is because there is more than one Operation type Value for an ESN.
If you need a different Result Set then you need to re-assess your business requirement.
The query can be modified according to your business requirement so you need to be sure what you want.
The Duplicate ESN that you see is because you added an ESN in the #Data1 table with Operation type '2'.
According to your third requirement that ESN is being Selected.
Query is working exactly as per your requirements.
if you need a different result set then the requirements will have ti change.
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
|
mani_12345
Starting Member
India
35 Posts |
Posted - 06/04/2012 : 06:12:08
|
| ya offcourse u have to change logic accrdng to the requrment .. |
|
|
|
Villanuev
Constraint Violating Yak Guru
315 Posts |
Posted - 06/04/2012 : 08:45:59
|
Hi Vinu, thanks for the reply.
Yes, your query is working, but i did not give the clear requirements.
please see my new data.
1.if the ESN record in Data1 have only operation_type 4 and 5 and rma_type=5, the remarks should be 'Cores'---i found out that there are esn have op type 1,2,3,4,5., i would like to get only the ESN
who have the transaction of op type is 4 and 5 only.
2. if the ESNs found no records from the table the remarks should be 'No records'
3. if the ESN have the operation type 2 the remarks is 'Not yet returned'-- even if there are operation type 1,3,2,4,5 these will reflect as 'not yet return'
data:
Create table #Sample
(ESN nvarchar(35))
Insert #Sample (ESN) Values ('268435459404494586') -–this will fall as ‘Not yet return’
Insert #Sample (ESN) Values ('268435459404507597') --this will fall as ‘Cores’
Insert #Sample (ESN) Values ('268435459404494781') --no records..
Create table #Data1
(ESN nvarchar(35), operation_type int, rma_type int)
Insert #Data1 (ESN,operation_type, rma_type) Values ('268435459404494586',4, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',5, 5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',1, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',2, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404494586',3, NULL)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',5,5)
Insert #Data1 (ESN,operation_type,rma_type) Values ('268435459404507597',4,5)
|
Edited by - Villanuev on 06/04/2012 08:47:51 |
|
|
| |
Topic |
|
|
|
Simplify my Query using the Join statement
