Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Case When Subtotaling on Dynamic data

Author  Topic 

jazlady
Starting Member

7 Posts
Posted - 2011-04-04 : 15:20:57
I have a table that contains the following data:

User Type Count Days
RAC A 203 39
RAC B 301 39
RAC C 139 39
JOE D 60 39
JOE E 45 39
JOE A 75 39

For each USERS I want to add A + B and C + D + E together divided by 39 days. I want A row to contain values for A + B and E to contain the values for C + D + E to provide results like this:

User Type Count Days Count by Days
RAC A 203 39 12.92 (A + B) / 39
RAC B 301 39
RAC C 139 39 3.56 C/39
JOE D 60 39
JOE E 45 39 2.69 (D + E) / 39
JOE A 75 39 1.92 A/39

How can I develop a sql case when statement to do this If my report is grouped by Users and Type?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-04 : 15:31:49
[code]
--User Type Count Days
Declare @t Table (
u varchar(10),
t varchar(10),
c int,
d int
)

Insert Into @t Select 'RAC', 'A', 203, 39
Insert Into @t Select 'RAC', 'B', 301, 39
Insert Into @t Select 'RAC', 'C', 139, 39
Insert Into @t Select 'JOE', 'D', 60, 39
Insert Into @t Select 'JOE', 'E', 45, 39
Insert Into @t Select 'JOE', 'A', 75, 39


Select
A.u,
A.t,
A.c,
A.d,
X = convert(decimal(9,2),sum(
case
when A.t = 'A' and B.t in ('A','B') then B.c
when A.t = 'E' and B.t in ('C','D','E') then B.c
else 0 end
)/39.0)
From @t A
Inner Join @t B
On A.u = B.u
Group By A.u, A.t, A.c, A.d
[/code]

Corey

I Has Returned!!
Go to Top of Page

jazlady
Starting Member

7 Posts
Posted - 2011-04-04 : 15:40:44
Remember my data is random - so it will not alway be the scenario in your case statement... In other words I could have


RAC A
RAC C
RAC D

JOE A
JOE B
JOE E
JOE D
JOE C

NAK B
NAK C
NAK D

JIL A
JIL D
JIL B

So how can I build a check to determine which types I have before going into the case statement below. I was trying to do the case statement without having to join or use any subqueries.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-04 : 15:45:25
Your description of the problem did not match your expected results. You said you wanted the data on the 'E' row.

How do you determine which row should have the data? Why not just return the appropriately summed value for every row? Since you are asking for the data to be in one row, is there some kind of order that is expected?

and why in the world would you say this: "I was trying to do the case statement without having to join or use any subqueries."?



quote:
Originally posted by jazlady

Remember my data is random - so it will not alway be the scenario in your case statement... In other words I could have


RAC A
RAC C
RAC D

JOE A
JOE B
JOE E
JOE D
JOE C

NAK B
NAK C
NAK D

JIL A
JIL D
JIL B

So how can I build a check to determine which types I have before going into the case statement below. I was trying to do the case statement without having to join or use any subqueries.



Corey

I Has Returned!!
Go to Top of Page

jazlady
Starting Member

7 Posts
Posted - 2011-04-04 : 15:54:16
I also need to say "included with E" where C & D & E are summed together. And I need to say "included with A" where A & B are summed together only.

I am not sure how to pose this question I just want my results from the calculation results to return for one row not for all the rows.

Hopes this help.

Order by User and Type

I didn't want to use any more joins or subqueries as I already have a lot of joins to gather this data now. I am using a view to display this data.



quote:
Originally posted by Seventhnight

Your description of the problem did not match your expected results. You said you wanted the data on the 'E' row.

How do you determine which row should have the data? Why not just return the appropriately summed value for every row? Since you are asking for the data to be in one row, is there some kind of order that is expected?

and why in the world would you say this: "I was trying to do the case statement without having to join or use any subqueries."?



quote:
Originally posted by jazlady

Remember my data is random - so it will not alway be the scenario in your case statement... In other words I could have


RAC A
RAC C
RAC D

JOE A
JOE B
JOE E
JOE D
JOE C

NAK B
NAK C
NAK D

JIL A
JIL D
JIL B

So how can I build a check to determine which types I have before going into the case statement below. I was trying to do the case statement without having to join or use any subqueries.



Corey

I Has Returned!!
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-04 : 16:00:42
So you still aren't addressing the confusion...

NAK B
NAK C
NAK D


For Nak, there is no 'A' and no 'E'... so now what?

quote:

I am not sure how to pose this question I just want my results from the calculation results to return for one row not for all the rows.


^ this is not a reason... this is a 'want'. Why do you want this... there is no reason from a data perspective to want this. Most likely, you are trying to make you're data look like your 'interface'. Do display work in the interface... not in SQL.


Corey

I Has Returned!!
Go to Top of Page

jazlady
Starting Member

7 Posts
Posted - 2011-04-04 : 16:07:35
Since there is no A or E then you should see the data as normal. the only time the data will but subtotal is when you have the combination of A + B or C + D + E

USER TYPE Count Days Count by Days
NAK B 290 39 7.44
NAK C 112 39 2.87
NAK D 85 39 2.18
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-04 : 16:50:09
First this...


quote:
Originally posted by jazlady


For each USERS I want to add A + B and C + D + E together divided by 39 days. I want A row to contain values for A + B and E to contain the values for C + D + E to provide results like this:

User Type Count Days Count by Days
RAC A 203 39 12.92 (A + B) / 39
RAC B 301 39
RAC C 139 39 3.56 C/39
JOE D 60 39
JOE E 45 39 2.69 (D + E) / 39
JOE A 75 39 1.92 A/39


notice the bit in Red does not fit your description...

and now...

quote:
Originally posted by jazlady

Since there is no A or E then you should see the data as normal. the only time the data will but subtotal is when you have the combination of A + B or C + D + E

USER TYPE Count Days Count by Days
NAK B 290 39 7.44
NAK C 112 39 2.87
NAK D 85 39 2.18



Again, you restate your original description with data that doesn't represent the stated description...


I give up until you can come to a clearer description/example.

Good luck!

Corey

I Has Returned!!
Go to Top of Page

jazlady
Starting Member

7 Posts
Posted - 2011-04-04 : 17:08:52
The perfect A & B and C & D & E will not be present all the time.

I was trying to demonstrate random data on my table. sorry to confuse you.

I have multiple scenarios and I am trying to build a case statement to handle all scenarios.

Is it possible to do grouping/totaling like this?


Users Type Count Days Count by Days Comments to understand what I am trying to accomplish
JOE A 203 39 5.205128205 203+128/39
JOE B 128 39 Included with A
JOE C 46 39 1.179487179 46/39

NAK B 594 39 15.23076923 593/39
NAK C 645 39 16.53846154 645/39
NAK D 241 39 6.179487179 241/39

RAC C 123 39 Included with E 123 + 436 + 78 / 39
RAC D 436 39 Included with E
RAC E 78 39 2

SUE A 783 39 20.07692308 783 + 456 / 39
SUE B 456 39 Included with A

TIM E 231 39 5.923076923 123 + 231 / 39
TIM D 123 39 Included with E
TIM B 111 39 2.846153846 111/39

MAR A 357 39 377.4615385 357 + 798 / 39
MAR C 244 39 Included with E
MAR D 456 39 Included with E
MAR E 876 39 22.46153846 244+456+876/39
MAR B 798 39 Included with A







quote:
Originally posted by Seventhnight

First this...


quote:
Originally posted by jazlady


For each USERS I want to add A + B and C + D + E together divided by 39 days. I want A row to contain values for A + B and E to contain the values for C + D + E to provide results like this:

User Type Count Days Count by Days
RAC A 203 39 12.92 (A + B) / 39
RAC B 301 39
RAC C 139 39 3.56 C/39
JOE D 60 39
JOE E 45 39 2.69 (D + E) / 39
JOE A 75 39 1.92 A/39


notice the bit in Red does not fit your description...

and now...

quote:
Originally posted by jazlady

Since there is no A or E then you should see the data as normal. the only time the data will but subtotal is when you have the combination of A + B or C + D + E

USER TYPE Count Days Count by Days
NAK B 290 39 7.44
NAK C 112 39 2.87
NAK D 85 39 2.18



Again, you restate your original description with data that doesn't represent the stated description...


I give up until you can come to a clearer description/example.

Good luck!

Corey

I Has Returned!!
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-05 : 07:29:08
Ok... one last shot:



Declare @t Table (
	u varchar(10),
	t varchar(10),
	c int,
	d int
)

Insert Into @t Select 'JOE', 'A', 203, 39 --5.205128205 203+128/39
Insert Into @t Select 'JOE', 'B', 128, 39 --Included with A 
Insert Into @t Select 'JOE', 'C',  46, 39 --1.179487179 46/39
Insert Into @t Select 'NAK', 'B', 594, 39 --15.23076923 593/39
Insert Into @t Select 'NAK', 'C', 645, 39 --16.53846154 645/39
Insert Into @t Select 'NAK', 'D', 241, 39 --6.179487179 241/39
Insert Into @t Select 'RAC', 'C', 123, 39 --Included with E 123 + 436 + 78 / 39
Insert Into @t Select 'RAC', 'D', 436, 39 --Included with E 
Insert Into @t Select 'RAC', 'E',  78, 39 --2 
Insert Into @t Select 'SUE', 'A', 783, 39 --20.07692308 783 + 456 / 39
Insert Into @t Select 'SUE', 'B', 456, 39 --Included with A 
Insert Into @t Select 'TIM', 'E', 231, 39 --5.923076923 123 + 231 / 39
Insert Into @t Select 'TIM', 'D', 123, 39 --Included with E 
Insert Into @t Select 'TIM', 'B', 111, 39 --2.846153846 111/39
Insert Into @t Select 'MAR', 'A', 357, 39 --377.4615385 357 + 798 / 39
Insert Into @t Select 'MAR', 'C', 244, 39 --Included with E 
Insert Into @t Select 'MAR', 'D', 456, 39 --Included with E 
Insert Into @t Select 'MAR', 'E', 876, 39 --22.46153846 244+456+876/39
Insert Into @t Select 'MAR', 'B', 798, 39 --Included with A 


; with cte1(u,t,c,d,r)
As (
	Select u, t, c, d,  
		r = Row_Number()Over(
			Partition By u, case when t in ('A','B') then 1 when t in ('C','D','E') then 2 else 0 end 
			Order By case t when 'A' then 1 when 'B' then 2 when 'C' then 3 when 'D' then 2 when 'E' then 1 else 0 end)
	From @t
)

, cte(u,t,c,d,r)
As (
	Select Distinct
		A.u, A.t, A.c, A.d, 
		r = case when B.t is null then 1 else A.r end
	From cte1 A
	Left Join cte1 B
	On A.u = B.u
	and (
		(A.t in ('A','B') and B.t = 'A')
		or 
		(A.t in ('C','D','E') and B.t = 'E')
	)
)

Select 
	A.u,
	A.t,
	A.c,
	A.d,
	X = convert(decimal(9,2),sum(
		case 
			when A.r = 1 and A.t = 'A' and B.t in ('A','B') then B.c 
			when A.r = 1 and A.t = 'E' and B.t in ('C','D','E') then B.c 
			when A.r = 1 and A.t = B.t then B.c 
			else 0 end
		)/39.0)
From cte A
Inner Join cte B
On A.u = B.u
Group By A.u, A.t, A.c, A.d
Order By A.u, A.t


Corey

I Has Returned!!
Go to Top of Page

jazlady
Starting Member

7 Posts
Posted - 2011-04-05 : 10:27:01
I also need to include the words "Included with A" where B and "Included with E" where C and D is.

I got an error with ; with and Row_Number() in SQL SERVER 2008 Can you advise? Is there a simplier way to do this?

quote:
Originally posted by Seventhnight

Ok... one last shot:



Declare @t Table (
	u varchar(10),
	t varchar(10),
	c int,
	d int
)

Insert Into @t Select 'JOE', 'A', 203, 39 --5.205128205 203+128/39
Insert Into @t Select 'JOE', 'B', 128, 39 --Included with A 
Insert Into @t Select 'JOE', 'C',  46, 39 --1.179487179 46/39
Insert Into @t Select 'NAK', 'B', 594, 39 --15.23076923 593/39
Insert Into @t Select 'NAK', 'C', 645, 39 --16.53846154 645/39
Insert Into @t Select 'NAK', 'D', 241, 39 --6.179487179 241/39
Insert Into @t Select 'RAC', 'C', 123, 39 --Included with E 123 + 436 + 78 / 39
Insert Into @t Select 'RAC', 'D', 436, 39 --Included with E 
Insert Into @t Select 'RAC', 'E',  78, 39 --2 
Insert Into @t Select 'SUE', 'A', 783, 39 --20.07692308 783 + 456 / 39
Insert Into @t Select 'SUE', 'B', 456, 39 --Included with A 
Insert Into @t Select 'TIM', 'E', 231, 39 --5.923076923 123 + 231 / 39
Insert Into @t Select 'TIM', 'D', 123, 39 --Included with E 
Insert Into @t Select 'TIM', 'B', 111, 39 --2.846153846 111/39
Insert Into @t Select 'MAR', 'A', 357, 39 --377.4615385 357 + 798 / 39
Insert Into @t Select 'MAR', 'C', 244, 39 --Included with E 
Insert Into @t Select 'MAR', 'D', 456, 39 --Included with E 
Insert Into @t Select 'MAR', 'E', 876, 39 --22.46153846 244+456+876/39
Insert Into @t Select 'MAR', 'B', 798, 39 --Included with A 


; with cte1(u,t,c,d,r)
As (
	Select u, t, c, d,  
		r = Row_Number()Over(
			Partition By u, case when t in ('A','B') then 1 when t in ('C','D','E') then 2 else 0 end 
			Order By case t when 'A' then 1 when 'B' then 2 when 'C' then 3 when 'D' then 2 when 'E' then 1 else 0 end)
	From @t
)

, cte(u,t,c,d,r)
As (
	Select Distinct
		A.u, A.t, A.c, A.d, 
		r = case when B.t is null then 1 else A.r end
	From cte1 A
	Left Join cte1 B
	On A.u = B.u
	and (
		(A.t in ('A','B') and B.t = 'A')
		or 
		(A.t in ('C','D','E') and B.t = 'E')
	)
)

Select 
	A.u,
	A.t,
	A.c,
	A.d,
	X = convert(decimal(9,2),sum(
		case 
			when A.r = 1 and A.t = 'A' and B.t in ('A','B') then B.c 
			when A.r = 1 and A.t = 'E' and B.t in ('C','D','E') then B.c 
			when A.r = 1 and A.t = B.t then B.c 
			else 0 end
		)/39.0)
From cte A
Inner Join cte B
On A.u = B.u
Group By A.u, A.t, A.c, A.d
Order By A.u, A.t


Corey

I Has Returned!!
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2011-04-05 : 11:07:18
The text should really be done in the presentation layer, especially since the column is not a text column at the moment, its numeric.

You could convert the column to text after the aggregate if you must i suppose:


X = isnull(convert(varchar,convert(decimal(9,2),sum(
		case 
			when A.r = 1 and A.t = 'A' and B.t in ('A','B') then B.c 
			when A.r = 1 and A.t = 'E' and B.t in ('C','D','E') then B.c 
			when A.r = 1 and A.t = B.t then B.c 
			else null end
		)/39.0)),case when A.t = 'B' then 'Included with A' when A.t in ('B','C') then 'Included with E' else null end)



As for the 'Is there a simpler way'... yes. Don't do it at all. The results you are trying to squeeze out of this query are not really reasonable. Much of summary/decision making/display generating work that is being done should be done in the presentation layer (ie. report, website, win app, etc)

quote:
Originally posted by jazlady

I also need to include the words "Included with A" where B and "Included with E" where C and D is.

I got an error with ; with and Row_Number() in SQL SERVER 2008 Can you advise? Is there a simplier way to do this?

...


Corey

I Has Returned!!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site