September 22, 2012 at 9:31 am
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!
September 22, 2012 at 9:49 am
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
September 22, 2012 at 10:18 am
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
September 22, 2012 at 11:03 am
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
September 22, 2012 at 3:25 pm
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
--Vadim R.
September 23, 2012 at 1:45 pm
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
Change is inevitable... Change for the better is not.
September 23, 2012 at 1:47 pm
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
Change is inevitable... Change for the better is not.
September 23, 2012 at 1:56 pm
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
Change is inevitable... Change for the better is not.
September 23, 2012 at 2:03 pm
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
September 23, 2012 at 2:10 pm
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!
First of all, Joe Celko is absolutely correct. If you're storing data in a table like you've depicted, you're in for a world of hurt in the future. It would be much better to store the data in a table as he's portrayed it and then do a dynamic CROSS TAB.The question I have is... what do you want to display if ALL quarters contain "0"?
If fixing the table is beyond your control, then let me ask... what do you want to display if even QTR1 has all zeros? What about if just one of the items has all zeros for all quarters?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2012 at 7:08 pm
Jeff Moden (9/23/2012)
How will any of that produce the desired output?
What is the desired output? I thought it's Account# and Quarter value next to it, No?
--Vadim R.
September 24, 2012 at 12:30 am
Just having a little bit of fun here as the requirements are clearly unclear. 😀
DECLARE @Accts TABLE
(ACCOUNT VARCHAR(10), QTR1 INT, QTR2 INT, QTR3 INT, QTR4 INT)
DECLARE @Filter VARCHAR(4) = 'Q1'
INSERT INTO @Accts
SELECT 'Opex',20, 0, 0, 0
UNION ALL SELECT 'Sales',30, 0, 0, 0
UNION ALL SELECT 'Costs',55, 0, 0, 0
SELECT ACCOUNT, Qtr
FROM @Accts
CROSS APPLY (
VALUES ('Q1', QTR1), ('Q2', QTR2), ('Q3', QTR3), ('Q4', QTR4)) a(Q, QTR)
WHERE Q = @Filter
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply