Click here to monitor SSC
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 Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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!
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3445 Visits: 32990
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 (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47171 Visits: 44346
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: 1054 Visits: 2299
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44962 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44962 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44962 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47171 Visits: 44346
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