Dynamic Totals - SQL Server Forums

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

Dynamic Totals

Author

Topic

capjlp
Starting Member

19 Posts

Posted - 2011-08-26 : 11:32:13

Hey Folks

I am new to SQL and I have used access a lot and done similar reporting. But I am trying to build a rdlc in my asp.net web application. I am creating a view to be used as my data set for the report.

I have a "Value" field and a Data Type Field in my table. We use the one "Value" field to track data this is a Text Field the Data Type field tells me what type of data is in the field.

It can be

Yes/No
Percentage
Integer
Currency

I need to be able to in my view to do some totals using the data type field to either add the percentages together numbers or show me if any of field was a Y for that group.

Example

1 Increase Demand DataTypeInt 1
2 Increase Demand DataTypeInt 2
3 Hire New Person DataTypeY/N N
4 Hire New Person DataTypeY/N Y
5 Complete Job DataType% 10%
6 Complete Job DataType% 15%

Total for Increase Demand is 3
Total for Hire New Person is Y
Total for Complete Job is 25%

Hopefully that makes since. My data types are codes but for readability I just made this small example.

Can I do this in the sql query that's creating the view and how would I do that?

Thanks
CapJLP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-26 : 19:35:28

[code]
;with cte as
(
select Col, DT, Total = sum(case DT
when 'DataTypeInt' then convert(int, Value)
when 'DataTypeY/N' then case when Value = 'Y' then 1 else NULL end
when 'DataType%' then convert(int, replace(Value, '%', ''))
end)
from tbl
group by Col, DT
)
select Col, Total = case DT
when 'DataTypeInt' then convert(varchar(10), Total)
when 'DataTypeY/N' then case when Total > 0 then 'Y' else 'N' end
when 'DataType%' then convert(varchar(10), Total) + '%'
end
from cte
[/code]

KH
[spoiler]Time is always against us[/spoiler]

capjlp
Starting Member

19 Posts

Posted - 2011-08-30 : 11:59:33

quote:

Originally posted by khtan


;with cte as
(
	select	Col, DT, Total = sum(case DT
				when 'DataTypeInt' then convert(int, Value)
				when 'DataTypeY/N' then case when Value = 'Y' then 1 else NULL end
				when 'DataType%'   then convert(int, replace(Value, '%', ''))
				end)
	from	tbl
	group by Col, DT
)
select	Col, Total = case DT
			when 'DataTypeInt' then convert(varchar(10), Total)
			when 'DataTypeY/N' then case when Total > 0 then 'Y' else 'N' end
			when 'DataType%'   then convert(varchar(10), Total) + '%'
			end
from	cte

KH
[spoiler]Time is always against us[/spoiler]

Hey

Thank you for the reply.

It does look great here. I am working on trying to make it work.

Can you tell me what cte is or what that stands for? I hope to use a view to run this select.

capjlp
Starting Member

19 Posts

Posted - 2011-09-01 : 11:01:39

Hi

I got this sort of working.... The issue is sometimes there are Null values for the field types. This cause an issue when the field type is a Y/N cause it will come up with a N when the field should be Null I need it to only set the field to 0 if it is N not sure how to do that. I tired leaving off the else on the '1' field type but that didn't work...
It is also setting all numbered field types to a 4 decimal place holder which sucks... :)

WITH cte AS (SELECT IndicatorID, DataTypeCode, SUM(CASE DataTypeCode WHEN '1' THEN CASE WHEN Value = 'Y' THEN 1 ELSE 0 END
WHEN '2' THEN CONVERT(int,replace(Value, ',', ''))
WHEN '3' THEN CONVERT(int, Value) WHEN '4' THEN CONVERT(int, Value)
WHEN '5' THEN CONVERT(money, Value)
WHEN '6' THEN CONVERT(decimal(1, 0), Value)
WHEN '7' THEN CONVERT(decimal(2, 0), Value)
WHEN '8' THEN CONVERT(decimal(3, 0), Value)
WHEN '9' THEN CONVERT(decimal(4, 0), Value)
WHEN '10' THEN CONVERT(int, replace(Value, '%', '')) END) AS T1Total
FROM Impact.View_BoardPacket
WHERE (Trimester = 1)
GROUP BY IndicatorID, DataTypeCode)
SELECT IndicatorID, CASE DataTypeCode WHEN '1' THEN CASE WHEN T1Total > 0 THEN 'Y' ELSE 'N' END
WHEN '2' THEN CONVERT(varchar(20), T1Total)
WHEN '3' THEN CONVERT(varchar(20), T1Total)
WHEN '4' THEN CONVERT(varchar(20), T1Total)
WHEN '5' THEN CONVERT(varchar(20), T1Total)
WHEN '6' THEN CONVERT(varchar(20), T1Total)
WHEN '7' THEN CONVERT(varchar(20), T1Total)
WHEN '8' THEN CONVERT(varchar(20), T1Total)
WHEN '9' THEN CONVERT(varchar(20), T1Total)
WHEN '10' THEN CONVERT(varchar(20), T1Total) + '%' END AS T1Total
FROM cte AS cte_1

capjlp
Starting Member

19 Posts

Posted - 2011-09-02 : 16:18:05

bump

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-02 : 20:50:23

quote:

Can you tell me what cte is or what that stands for? I hope to use a view to run this select.

CTE is Common Table Expressions

You can create a view using that query

quote:
The issue is sometimes there are Null values for the field types. This cause an issue when the field type is a Y/N cause it will come up with a N when the field should be Null I need it to only set the field to 0 if it is N

so what should the final value ? Maybe you can post few scenario and the expected result

KH
[spoiler]Time is always against us[/spoiler]

capjlp
Starting Member

19 Posts

Posted - 2011-09-06 : 14:39:25

Hey

I got it working find just didn't know what what that stood for. Thank you by the way.

Now if I can just get my numbers to look right.

I also have an issue with my yes / no field since I am doing a if Y 1 else 0 then I am getting 0 or 1 but some my have Null values for this value of course I am getting 0 I would rather get nothing so on the report it would show Y for yes's and N for no's and no data for null fields. Whats happening is my future year reporting is showing up N which is correct since we aren't there yet but this confuses some of the folks looking at the report. I am not sure how to deal with Null values for this Yes/No field type.

For my numbers they are adding up however my values are coming out with 4 decimal values all numbers even my percentages.

So 75% shows up as 75.0000%
My dollar values show up like 909400.0000 i need them to be currency types with 2 decimals or no decimals depending on the data type code field. It converts to $909400.0000 I need it to look like $909,400.00 or $909,400 so adding commas on some the numbers i need to do.

I guess what confuses me is apparently the statement converts a dollar type to a 4 decimal type i guess all types in my matrix i assume. I would have thought it would have went o its a number 5 data type thats a currency field then not compare to the other types...

My query is building so I keep adding types and totals as I am building reports.

Currently My SQL looks like....

WITH cte AS
(SELECT IndicatorID, DataTypeCode,
SUM(CASE DataTypeCode WHEN '1' THEN CASE WHEN Value = 'Y' THEN 1 ELSE 0 END
WHEN '2' THEN CONVERT(int, replace(Value, ',', ''))
WHEN '3' THEN CONVERT(int, Value)
WHEN '4' THEN CONVERT(int, Value)
WHEN '5' THEN CONVERT(money, Value)
WHEN '6' THEN CONVERT(decimal(1, 0), Value)
WHEN '7' THEN CONVERT(decimal(2, 0), Value)
WHEN '8' THEN CONVERT(decimal(3, 0), Value)
WHEN '9' THEN CONVERT(decimal(4, 0), Value)
WHEN '10' THEN CONVERT(int, replace(Value, '%', '')) END) AS T1Total
FROM Impact.View_BoardPacket
WHERE (Trimester = 1)
GROUP BY IndicatorID, DataTypeCode)
SELECT IndicatorID, CASE DataTypeCode WHEN '1' THEN CASE WHEN T1Total > 0 THEN 'Y' ELSE 'N' END
WHEN '2' THEN CONVERT(varchar(20), T1Total)
WHEN '3' THEN CONVERT(varchar(20), T1Total)
WHEN '4' THEN CONVERT(varchar(20), T1Total)
WHEN '5' THEN '$'+ CONVERT(varchar(20), T1Total)
WHEN '6' THEN CONVERT(varchar(20), T1Total)
WHEN '7' THEN CONVERT(varchar(20), T1Total)
WHEN '8' THEN CONVERT(varchar(20), T1Total)
WHEN '9' THEN CONVERT(varchar(20), T1Total)
WHEN '10' THEN CONVERT(varchar(20), T1Total) + '%' END AS T1Total
FROM cte AS cte_1

Kristen
Test

22859 Posts

Posted - 2011-09-06 : 14:51:58

"I am getting 0 or 1 but some my have Null values for this value of course I am getting 0 I would rather get nothing so on the report it would show Y for yes's and N for no's and no data for null fields"


CASE WHEN MyColumn IS NULL THEN '' WHEN MyColumn = 1 THEN 'Yes' WHEN MyColumn = 0 THEN 'No' ELSE '???' END

"My dollar values show up like 909400.0000 i need them to be currency types with 2 decimals"

If your "dollar value" is stored as a Money datatype it will be 4 d.p. by default.

Format it in your Application, SQL Server is the wrong place to format it. To format it in SQL Server you have to convert it to a String datatype, so you have lost all the benefit of it being a structured numeric (money in this case) datatype which your application could benefit from.

If the output is NOT to an application then use

CONVERT(varchar(99), MyColumn, 9999)

where 99 is the width your need and 9999 is the formatting style - see SQL documentation for available types.

capjlp
Starting Member

19 Posts

Posted - 2011-09-06 : 15:04:30

Hello

Thank you for the reply. I am creating a report in asp.net using Microsoft Report Viewer. I am new to it as well and I am not sure how to format it using the data type codes in there. I felt more familiar with SQL than the report viewer. So I was trying to do as much as I could in here. In the report viewer it uses Visual Basic language and its a sub set because I have tried something things and it wont do it.

I also have a post on asp.net under reporting because I wanted to try and do my formatting in there. I assume that its not used that much because I haven't had anyone reply to me in that forum.

Thank you for your help its been great...!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources