SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to filter condition by column?


How to filter condition by column?

Author
Message
yingchai
yingchai
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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!
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5483 Visits: 35448
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

ssurve
ssurve
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 16
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87986 Visits: 45276
You *can* use dynamic SQL. Whether it's a good idea to do so is another matter...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


rVadim
rVadim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 2306
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86889 Visits: 41104
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86889 Visits: 41104
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86889 Visits: 41104
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87986 Visits: 45276
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search