How to filter condition by column?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » How to filter condition by column?

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

How to filter condition by column?

Expand / Collapse

Author

Message

Posted Saturday, September 22, 2012 9:31 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 01, 2012 1:28 AM
Points: 77, Visits: 391

Hi SQL Gurus,

I have a below table with column names ACCOUNT, QTR1, QTR2, QTR3, QTR4

ACCOUNT QTR1 QTR2 QTR3 QTR4
-------------------------------
Opex 20 0 0 0
Sales 30 0 0 0
Costs 55 0 0 0

As you can see from the table above, the table only contains data for quarter 1. How can I perform the sql query where it allows me to filter by quarter?

Example; If I do a filter by quarter 1, the output will appear as per below:

ACCOUNT QTR1
---------------
Opex 20
Sales 30
Costs 55

Thanks in advance!

Post #1363116

J Livingston SQL

Posted Saturday, September 22, 2012 9:49 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 1,456, Visits: 14,281

yingchai (9/22/2012)

Hi SQL Gurus,

I have a below table with column names ACCOUNT, QTR1, QTR2, QTR3, QTR4

ACCOUNT QTR1 QTR2 QTR3 QTR4
-------------------------------
Opex 20 0 0 0
Sales 30 0 0 0
Costs 55 0 0 0

As you can see from the table above, the table only contains data for quarter 1. How can I perform the sql query where it allows me to filter by quarter?

Example; If I do a filter by quarter 1, the output will appear as per below:

ACCOUNT QTR1
---------------
Opex 20
Sales 30
Costs 55

Thanks in advance!

edit : can you please clarify what you are expecting?

given what you have told us so far.....to "filter by quarter" would only mean selecting the relevant column in a "SELECT QTR1 FROM..." statement
...but i am sure you already know this

__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________

Post #1363119

Posted Saturday, September 22, 2012 10:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, September 22, 2012 10:57 AM
Points: 3, Visits: 6

you can use dynamic sql as

declare @Col_List varchar(10)=',QTR1'
Declare @Sql nVarchar(max)='SELECT ACCOUNT'+@Col_List +' FROM TABLE_NAME'
EXEC @Sql

Post #1363125

Posted Saturday, September 22, 2012 11:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

You *can* use dynamic SQL. Whether it's a good idea to do so is another matter...

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1363138

Posted Saturday, September 22, 2012 3:25 PM

SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 19, 2013 1:36 PM
Points: 887, Visits: 2,062

You can also UNIONize quarters and then select from that union using quarter number as filter in a WHERE clause.

SELECT *
FROM (
  SELECT Account, QTR1 AS QuarterValue, 1 AS QuarterNumber FROM ....
  UNION ALL
  SELECT Account, QTR2 AS QuarterValue, 2 AS QuarterNumber FROM ....
  UNION ALL
  ....
)
WHERE QuarterNumber = 2

You can also create procedure passing quater number and then doing IF ... ELSE

Post #1363175

Posted Saturday, September 22, 2012 6:58 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

[quiote] have a below table with column names account_name, qtr1, qtr2, qtr3, qtr4 [/quote]

No, this is a not a table; we have no DLL, no keys, constraints data types or anything. Next the picture you did post is not normalized. Do you know what Normalization is? A quarter is a temporal value; it is not an attribute. Here is a guess at a correct design:

CREATE TABLE Account_Qtr_Totals
(account_name VARCHAR(10) NOT NULL,
report_quarter CHAR(6) NOT NULL
CHECK (report_quarter LIKE '[12][0-9][0-9][0-9]Q[1-4]'),
PRIMARY KEY (account_name, report_quarter),
something_amt DECIMAL (12,2) NOT NULL);

The quarter will
Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
week_in_year SMALLINT NOT NULL, -- SQL Server is not ISO standard
holiday_type SMALLINT NOT NULL
CHECK(holiday_type IN ( ..), --
day_in_year
SMALLINT NOT NULL,
julian_business_day INTEGER NOT NULL,
...);

The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

Derek Dongray came up with a classification of the public holidays and weekends he needed to work with in multiple countries. Heris his list with more added.
1. Fixed date every year.
2. Days relative to Easter.
3. Fixed date but will slide to next Monday if on a weekend
4. Fixed date but slides to Monday if Saturday or Tuesday if Sunday (UK Boxing Day is the only one).
5. Specific day of week after a given date (usually first/last Monday in a month but can be other days, e.g. First Thursday after November 22 = Thanksgiving)
6. Days relative to Greek Orthodox Easter (not always the same as Western Easter)
7. Fixed date in Hijri (Muslim) Calendar - this turns out to only be approximate due to the way the calendar works. An Imam has to see a full moon to begin the cycle and declare it.
8. Days relative to previous Winter Solstice (Chinese holiday of Qing Ming Jie) 9. Civil holidays set by decree, such as a National Day Of Mourning.
10. Fixed date except Saturday slides to Friday, and Sunday slides to Monday.
11. Fixed date, but Tuesday slides to Monday, and Thursday to Friday. (Argentina celebrates October 12, the day Columbus discovered America is a national holiday in Argentina. Except when it's a Tuesday, they back it one day to Monday.

As you can see, some of these are getting a bit esoteric and a bit fuzzy. A calendar table for US Secular holidays can be built from the data at this website, so you will get the three-day weekends:

http://www.smart.net/~mmontes/ushols.html

Time zones with fractional hour displacements:
http://www.timeanddate.com/worldclock/city.html?n=5
http://www.timeanddate.com/worldclock/city.html?n=54
http://www.timeanddate.com/worldclock/city.html?n=176
http://www.timeanddate.com/worldclock/city.html?n=246

But the strange ones are:
http://www.timeanddate.com/worldclock/city.html?n=5
http://www.timeanddate.com/worldclock/city.html?n=63

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL, ...);

INSERT INTO Calendar
VALUES ('2007-04-05', 42),
('2007-04-06', 43), -- good Friday
('2007-04-07', 43),
('2007-04-08', 43), -- Easter Sunday
('2007-04-09', 44),
('2007-04-10', 45); --Tuesday

To compute the business days from Thursday of this week to next
Tuesdays:

SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05',
AND C2.cal_date = '2007-04-10';

A useful idiom is a report period calendar. It gives a name to a range of dates. In your case, try this:

CREATE TABLE Quarter_Periods
(quarter_name VARCHAR(30) NOT NULL PRIMARY KEY,
quarter_start_date DATE NOT NULL,
quarter_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (quarter_start_date <= quarter_end_date),
etc);

The advantage of this quarter naming convention is that it will sort with the ISO-8601 data format required by Standard SQL.

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

Post #1363183

Posted Sunday, September 23, 2012 1:45 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

GilaMonster (9/22/2012)

You *can* use dynamic SQL. Whether it's a good idea to do so is another matter...

Do you have an alternative?

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1363289

Posted Sunday, September 23, 2012 1:47 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

rVadim (9/22/2012)

You can also UNIONize quarters and then select from that union using quarter number as filter in a WHERE clause.

SELECT *
FROM (
  SELECT Account, QTR1 AS QuarterValue, 1 AS QuarterNumber FROM ....
  UNION ALL
  SELECT Account, QTR2 AS QuarterValue, 2 AS QuarterNumber FROM ....
  UNION ALL
  ....
)
WHERE QuarterNumber = 2

You can also create procedure passing quater number and then doing IF ... ELSE

How will any of that produce the desired output?

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1363290

Posted Sunday, September 23, 2012 1:56 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

CELKO (9/22/2012)

[quiote] have a below table with column names account_name, qtr1, qtr2, qtr3, qtr4

No, this is a not a table; we have no DLL, no keys, constraints data types or anything. Next the picture you did post is not normalized. Do you know what Normalization is? A quarter is a temporal value; it is not an attribute. Here is a guess at a correct design:

CREATE TABLE Account_Qtr_Totals
(account_name VARCHAR(10) NOT NULL,
report_quarter CHAR(6) NOT NULL
CHECK (report_quarter LIKE '[12][0-9][0-9][0-9]Q[1-4]'),
PRIMARY KEY (account_name, report_quarter),
something_amt DECIMAL (12,2) NOT NULL);

The quarter will
Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
week_in_year SMALLINT NOT NULL, -- SQL Server is not ISO standard
holiday_type SMALLINT NOT NULL
CHECK(holiday_type IN ( ..), --
day_in_year
SMALLINT NOT NULL,
julian_business_day INTEGER NOT NULL,
...);

The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

Derek Dongray came up with a classification of the public holidays and weekends he needed to work with in multiple countries. Heris his list with more added.
1. Fixed date every year.
2. Days relative to Easter.
3. Fixed date but will slide to next Monday if on a weekend
4. Fixed date but slides to Monday if Saturday or Tuesday if Sunday (UK Boxing Day is the only one).
5. Specific day of week after a given date (usually first/last Monday in a month but can be other days, e.g. First Thursday after November 22 = Thanksgiving)
6. Days relative to Greek Orthodox Easter (not always the same as Western Easter)
7. Fixed date in Hijri (Muslim) Calendar - this turns out to only be approximate due to the way the calendar works. An Imam has to see a full moon to begin the cycle and declare it.
8. Days relative to previous Winter Solstice (Chinese holiday of Qing Ming Jie) 9. Civil holidays set by decree, such as a National Day Of Mourning.
10. Fixed date except Saturday slides to Friday, and Sunday slides to Monday.
11. Fixed date, but Tuesday slides to Monday, and Thursday to Friday. (Argentina celebrates October 12, the day Columbus discovered America is a national holiday in Argentina. Except when it's a Tuesday, they back it one day to Monday.

As you can see, some of these are getting a bit esoteric and a bit fuzzy. A calendar table for US Secular holidays can be built from the data at this website, so you will get the three-day weekends:

http://www.smart.net/~mmontes/ushols.html

Time zones with fractional hour displacements:
http://www.timeanddate.com/worldclock/city.html?n=5
http://www.timeanddate.com/worldclock/city.html?n=54
http://www.timeanddate.com/worldclock/city.html?n=176
http://www.timeanddate.com/worldclock/city.html?n=246

But the strange ones are:
http://www.timeanddate.com/worldclock/city.html?n=5
http://www.timeanddate.com/worldclock/city.html?n=63

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL, ...);

INSERT INTO Calendar
VALUES ('2007-04-05', 42),
('2007-04-06', 43), -- good Friday
('2007-04-07', 43),
('2007-04-08', 43), -- Easter Sunday
('2007-04-09', 44),
('2007-04-10', 45); --Tuesday

To compute the business days from Thursday of this week to next
Tuesdays:

SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = '2007-04-05',
AND C2.cal_date = '2007-04-10';

A useful idiom is a report period calendar. It gives a name to a range of dates. In your case, try this:

CREATE TABLE Quarter_Periods
(quarter_name VARCHAR(30) NOT NULL PRIMARY KEY,
quarter_start_date DATE NOT NULL,
quarter_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (quarter_start_date <= quarter_end_date),
etc);

The advantage of this quarter naming convention is that it will sort with the ISO-8601 data format required by Standard SQL.
[/quote]

While I agree on all fronts with this, Joe, do you have any method to produce the desired output even if you use the good methods you've outlined. Yes, I agree... this should be "done in the app" but sometimes there is no app.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1363291

Posted Sunday, September 23, 2012 2:03 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

Jeff Moden (9/23/2012)

GilaMonster (9/22/2012)

You *can* use dynamic SQL. Whether it's a good idea to do so is another matter...

Do you have an alternative?

Yes.

I'm waiting for the OP to clarify exactly what he wants to see, as Livingstone asked.

Do you have a solution?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1363294

« Prev Topic | Next Topic »

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse