Click here to Skip to main content
14,267,388 members
Rate this:
Please Sign up or sign in to vote.
See more:
below is my temp table

Amount	       CommonId	  Type	 fromORto
-67852.46	6277	  NULL	   FROM
67852.46	6277	  NULL	   TO
-12000	        7010	  NULL	   FROM
6000	        7010	  NULL	   TO
6000	        7010	  NULL	   TO
5000	        1002	  NULL	   TO
-2500	        1002	  NULL	   FROM
-2500	        1002	  NULL	   FROM


Below is my expected output

Amount   	CommonId	Type	       fromORto
-67852.46	6277	        One to One	FROM
67852.46	6277	        One to One	TO
-12000	        7010	        One to Many	FROM
6000	        7010	        One to Many	TO
6000	        7010	        One to Many	TO
5000	        1002	        Many to One	TO
-2500	        1002	        Many to One	FROM
-2500	        1002	        Many to One	FROM


What I have tried:

Can some one please help me to build update query which will update column type as per expected output. I tried as below but didn't work

SELECT    
o.CommonID,
CASE
    WHEN count(*) = 2 THEN 'ONETONE'
    WHEN count(*) > 2 --and o.Amount < 0 THEN 'One To Many'
    WHEN count(*) > 2 --and o.Amount > 0 THEN 'Many To One'
END priority1
FROM @tempdata o
GROUP BY o.CommonID;
Posted
Updated 8-Aug-19 2:17am
v8
Comments
OriginalGriff 8-Aug-19 5:21am
   
What have you tried?
Where are you stuck?
What help do you need?
shashikant86 8-Aug-19 5:26am
   
I want help in writing sql update query which will update column "Type" data as per expected output. I tried something like below but wont work for me

SELECT
o.CommonID,

CASE
WHEN count(*) = 2
THEN 'ONETONE'
WHEN count(*) > 2 --and o.Amount < 0
THEN 'One To Many'
WHEN count(*) > 2 --and o.Amount > 0
THEN 'Many To One'

END priority1
FROM
@tempdata o


GROUP BY
o.CommonID;
Rate this:
Please Sign up or sign in to vote.

Solution 1

You need to break this down into 2 steps.
1. Work out what the Type is going to be for each CommonId - you can do this by counting the number of FROM and the number of TWO. I did this with a PIVOT like this:
select CommonId, ISNULL([FROM],0) AS [From], ISNULL([TO],0) AS [To]
from
(
    select CommonId, fromORto, COUNT(*) as cnt
    FROM @tempdata
    GROUP BY CommonId, fromORto
) src
PIVOT
(
    SUM(cnt) for fromORto IN ([FROM], [TO])
) pvt
which gave me the results
CommonId	From	To
1002		2	1
6277		1	1
7010		1	2
You can put those results into another temporary table or use a Common Table Expression. I chose to use a CTE

2. Now you can plug those numbers into your case statement but you also need to join to your original table to get the other details … something like this:
;with cte as 
(
	select CommonId, ISNULL([FROM],0) AS [From], ISNULL([TO],0) AS [To]
	from
	(
		select CommonId, fromORto, COUNT(*) as cnt
		FROM @tempdata
		GROUP BY CommonId, fromORto
	) src
	PIVOT
	(
		SUM(cnt) for fromORto IN ([FROM], [TO])
	) pvt
)
select B.Amount, B.CommonId,
CASE WHEN A.[From] = 1 AND A.[To] = 1 THEN 'One to One'
	 WHEN A.[From] = 1 AND A.[To] > 1 THEN 'One to Many'
	 WHEN A.[From] > 1 AND A.[To] = 1 THEN 'Many to One'
	 ELSE 'Error in Data' 
END priority1 	 
, B.fromORto
FROM cte A
INNER JOIN @tempdata B ON A.CommonId = B.CommonId
which gave me these results
Amount		CommonId priority1	fromORto
5000.00		1002	 Many to One	TO
-2500.00	1002	 Many to One	FROM
-2500.00	1002	 Many to One	FROM
-67852.46	6277	 One to One	FROM
67852.46	6277	 One to One	TO
-12000.00	7010	 One to Many	FROM
6000.00		7010	 One to Many	TO
6000.00		7010	 One to Many	TO
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Do the GROUP BY twice to give you counts, and then JOIN onto those results:
SELECT m.CommonID, 
       CASE WHEN tos.NoTo = 1 AND froms.NoFrom = 1 THEN 'One to one'
            WHEN tos.NoTo > froms.NoFrom  THEN 'Many to one'
            ELSE 'One to many'
       END
FROM MyTable m
JOIN (SELECT    
        CommonID,
        COUNT(FromOrTo) AS NoTo
        FROM MyTable
        WHERE FromOrTo = 'TO'
        GROUP BY CommonID) tos
ON tos.CommonID = m.CommonID
JOIN (SELECT    
        CommonID,
        COUNT(FromOrTo) AS NoFrom
        FROM MyTable
        WHERE FromOrTo = 'FROM'
        GROUP BY CommonID) froms
ON froms.CommonID = m.CommonID
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100