Use Parameters as field Name - 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

Use Parameters as field Name

Author

Topic

Zabman
Starting Member

1 Post

Posted - 2011-07-26 : 20:45:30

Hi,

I am new to SQL queries so apologies if this is a very basic question. I have tried searching the answer but haven't been able to get a working solution yet.

My Query is as follows:

SELECT
	(SELECT C.NAME_ FROM CRS.CUSTOMER C WHERE month1."Customer" = C.G_U_I_D) as "Name",
	month1."Revenue" as "1st",
	month2."Revenue" as "2nd",
	(month1."Revenue"/month2."Revenue") as "% change"
FROM
(SELECT
	RM.CUSTOMER__P_K as "Customer",
	RM.MONTH_ as "Month",
	SUM(RM.AMOUNT) as "Revenue"
FROM
	CRS.REVENUE_MONTH RM
WHERE RM.MONTH_ = $(Monthp1)
	and RM.AMOUNT >= 1200
GROUP BY RM.CUSTOMER__P_K,
	RM.MONTH_) month1
	FULL OUTER JOIN 
(SELECT
	RM.CUSTOMER__P_K as "Customer",
	RM.MONTH_ as "Month",
	SUM(RM.AMOUNT) as "Revenue"
FROM
	CRS.REVENUE_MONTH RM
WHERE RM.MONTH_ = $(Monthp2)
	and RM.AMOUNT >= 1200
GROUP BY RM.CUSTOMER__P_K,
	RM.MONTH_) month2
	ON month1."Customer" = month2."Customer"
WHERE month1."Revenue"/month2."Revenue" <= 0.95 OR month1."Revenue"/month2."Revenue" >= 1.0526

I am attempting to have the output read what the paramater is ie

month1."Revenue" as "1st", - would display as whatever $(Monthp1) is
month2."Revenue" as "2nd", - would display as whatever $(Monthp2) is

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-26 : 20:54:15

Only possible with Dynamic SQL

Take a look at http://www.sommarskog.se/dynamic_sql.html

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

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-27 : 00:00:34

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

>> I am new to SQL queries so apologies if this is a very basic question. I have tried searching the answer but haven't been able to get a working solution yet. <<

It is a good question, but your code is awful. Really, really awful. The data element names are all wrong (singular table names, reserved words for columns, etc A query without DDL is impossible to answer correctly.

We name things for what they are in particular. The name is permanent and constant. There is no generic id, name or amount in RDBMS. GUIDs cannot attributes of entities. The underscore is never 5the first or last character of a data element name. Stupid things like “PK” affixes tell us how something is used in one place, not what it is.

We never format reports in the database. In a tared architecture, we pass along the data to the front end the front end does the presentation. This is not just SQL; it is any tiered architecture. That is where we compute percentage, etc.

Scalar sub-queries in a SELECT list are almost impossible to optimize. You are writing 1950's monolithic COBOL, using SQL at a cost of 2-3 orders of magnitude in performance.

A month is a unit of temporal measurement, a value of an attribute. It is no more a valid column than “Ten Liters” would be. The $ is not a legal function or even a valid character in SQL.

SQL is a data language,. We use look up table s and te4h classic idiom for this is a report period table by months

CREATE TABLE Report_Periods
(report_period_name CHAR(10) NOT NULL PRIMARY KEY,
report_period_seq NOT NULL UNIQUE,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date < period_end_date));

The basic query, CTE or VIEW is:

CREATE VIEW Customer_Monthly_Revenue_Totals
(customer_name, report_period_name, report_period_seq, month_revenue_tot)
AS
SELECT C.customer_name, P.report_period_name, P.report_period_seq,
SUM(R.posting_amt)
FROM CRS.Customers AS C,
Report_Periods AS P,
Something_Revenues AS R
WHERE R.customer_id = C.customer_id
AND R.posting_date
BETWEEN P.period_start_date AND P.period_end_date
GROUP BY C.customer_name, P.report_period_name, report_period_seq;

Besides not posting any DDL, you did not post any specs. If your boss makes you code from documents like this, quit. Unless he pays you a lot of money. There is something about months with over $1200 in revenue and a delta between 0.95 and 1.0526; would you like to tell us?

Her is a basic delta query:

SELECT CM1.customer_name,
CM1.report_period_name, CM2.report_period_name,
(CM1.month_revenue_tot – CM2.month_revenue_tot) AS revenue_delta
FROM Customer_Monthly_Revenue_Totals AS CM1,
Customer_Monthly_Revenue_Totals AS CM2
WHERE CM1.customer_name = CM2.customer_name
AND CM1.report_period_seq+1 = CM2.report_period_seq;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources