Case/when and how to use exactly the query in cte

Question

1.00/5 (1 vote)

See more:

--There are two tables

--select * from request

hub_id	totalrequest
9	45
20	55
136	75

XML

select * from route_header
<pre>
action1 routid  hub_id  occ
p        1       9       4
p        2       9       3
p        3       9       2
d        4       9       4
d        5       9       1
p        6       20      3
p        7       20      3
p        8       20      4
p        9       20      4
d       10       20      5
p       11      136      2
p       12      136      1
d       13      136      5
d       14      136      3
d       15      136      4
</pre>

---------------------o/p which i want ---------------------------

hub  totalrequest    totalroutes totalemployee Realoccupancy  pickcount  dropcount
9	   45	         5	      14	      2          3           2
20	   55	         5	      19	      3          4           1
136	   75	         5	      15	      3          2           3

----------------------------------------------------------------

-- I want to do it in a set based way without using cursor/while loop.

--what i have tried so far

SQL

;with cte(hub,totalrequest,totalroutes,totalemployee) as
(
select
rd.hub_id,
r.totalrequest,
count(rd.routid) as Totalroutes
,sum(occ) as totalemployee
from request r
join route_header rd on r.hub_id=rd.hub_id
group by
rd.hub_id,
r.totalrequest
),
cte1 as
(
select hub,
totalrequest,
totalroutes,
totalemployee,
floor(totalemployee/totalroutes) as Realoccupancy from  cte
)
select * from cte1

-----------------------------------------------------------------------------
--where i stuck is (till realoccupancy everything is fine) 
-->>but pickcount and dropcount i am unable to do it

--i was trying to use case when ...but failed to do it.
--just after this statement in cte
,sum(occ) as totalemployee

i have to write the logic to get the pickcount and dropcount in cte...and then i will fetch in cte1.

--tell me the way

-------------------------------------------------------------------------------
if you have other solution also help me.

Posted 23-Dec-13 8:49am

anurag19289

Updated 23-Dec-13 8:51am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-12-23T12:30:00

Solution1 by Christian Graus is very good. In addition to it i'd like to provide another way - using subqueries. Have a look at example:

SQL

CREATE TABLE #request (hub_id  INT, totalrequest INT)

INSERT INTO #request (hub_id, totalrequest)
VALUES(9, 45),
(20, 55),
(136, 75)

CREATE TABLE #route_header (action1 VARCHAR(30), routid INT,  hub_id  INT, occ INT)

INSERT INTO #route_header (action1, routid , hub_id,  occ)
VALUES('p', 1, 9, 4),
('p', 2, 9, 3),
('p', 3, 9, 2),
('d', 4, 9, 4),
('d', 5, 9, 1),
('p', 6, 20, 3),
('p', 7, 20, 3),
('p', 8, 20, 4),
('p', 9, 20, 4),
('d', 10, 20, 5),
('p', 11, 136, 2),
('p', 12, 136, 1),
('d', 13, 136, 5),
('d', 14, 136, 3),
('d', 15, 136, 4)

SELECT t1.hub_id, t1.totalrequest , t1.Totalroutes, t1.TotalEmployee, FLOOR(t1.TotalEmployee / t1.Totalroutes) AS RealOccupancy,
    (SELECT COUNT(action1) FROM #route_header WHERE action1 = 'p' AND hub_id = t1.hub_id) AS PickCount,
    (SELECT COUNT(action1) FROM #route_header WHERE action1 = 'd' AND hub_id = t1.hub_id) AS DropCount
FROM (
    SELECT r.hub_id, r.totalrequest, COUNT(rh.routid) AS Totalroutes, SUM(rh.occ) AS TotalEmployee
    FROM #request AS r INNER JOIN  #route_header AS rh ON r.hub_id = rh.hub_id
    GROUP BY r.hub_id, r.totalrequest
    ) AS t1


DROP TABLE #request
DROP TABLE #route_header

Result (as expected):

hub_id  to.req. to.rou. to.emp. Re.Occ. PickCo. DropCo.
9	45	5	14	2	3	2
20	55	5	19	3	4	1
136	75	5	15	3	2	3

Christian Graus · Accepted Answer · 2013-12-23T10:14:00

Create a CTE for each group you want. Creating one select that tries to group on three things is too hard. I would also create CTEs that don't reference each other, and just join the resultant tables in my final SQL, if I could. It's just more readable that way. Create SQL to create your list of scalar values, and SQL to get your three calculated values. Then join all four tables in your final SQL statement to get your result.

Because you gave us your data, I was able to create your tables and a solution:

SQL

;with root(hubid,totalrequest,totalroutes,totalemployee) as
(
select
rd.hub_id,
r.totalrequest,
count(rd.routid) as Totalroutes
,sum(occ) as totalemployee
from request r
join route_header rd on r.hub_id=rd.hub_id
group by
rd.hub_id,
r.totalrequest
),
totals(hubId, pickCount,dropCount) as
(
select hub_id,
sum(CASE WHEN action1 = 'p' THEN 1 ELSE 0 end) AS pickcount,
sum(CASE WHEN action1 = 'd' THEN 1 ELSE 0 end) AS dropcount
FROM route_header
GROUP BY hub_id
)

select r.*, floor(totalemployee/totalroutes) as Realoccupancy, pickCount, dropCount from root r INNER JOIN totals t ON r.hubid = t.hubid

Note that I made each CTE responsible for one part of the solution and I moved some of the calculation to the final SQL. My goal was for each part to have a clear task, and for the whole to be easily maintained.

Case/when and how to use exactly the query in cte

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0