Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to filter condition by column? Expand / Collapse
Author
Message
Posted Saturday, September 22, 2012 9:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:44 PM
Points: 79, Visits: 467
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
Posted Saturday, September 22, 2012 9:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 1,943, Visits: 20,176
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
and remember....every day is a school day
Post #1363119
Posted Saturday, September 22, 2012 10:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 1:39 AM
Points: 79, Visits: 14
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-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:24 AM
Points: 994, Visits: 2,232
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 1,945, Visits: 3,180
[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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363289
Posted Sunday, September 23, 2012 1:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363290
Posted Sunday, September 23, 2012 1:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363291
Posted Sunday, September 23, 2012 2:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 12»»

Permissions Expand / Collapse