Query tweak

Question

Post reply

Query tweak

VSSGeorge

SSCrazy Eights

Points: 8146

January 2, 2020 at 7:37 am

#3711688

Is there any way to tweak the following query? Thanks

; WITH CTE AS
(
SELECT DATEPART(QUARTER, GETDATE()) as 'Quarter', DATEPART(YEAR, GETDATE()) as 'Year'
)

select Quarter,Year from CTE where Quarter IN (SELECT Quater from Quaters) AND Year IN (SELECT Year from Quaters)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24279 More actions · Answer 1

Depending on your indexes, this may help

; WITH CTE AS
(
SELECT DATEPART(QUARTER, GETDATE()) as 'Quarter', DATEPART(YEAR, GETDATE()) as 'Year'
)
SELECT CTE.Quarter, CTE.Year
FROM CTE
INNER JOIN Quaters as Q
   ON CTE.Quarter = Q.Quater
  AND CTE.Year = Q.Year

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24279 More actions · Answer 2

You could also do it without the CTE

SELECT Q.Quater as 'Quarter', Q.Year as 'Year'
FROM Quaters as Q
WHERE Q.Year   = DATEPART(YEAR, GETDATE())
  AND Q.Quater = DATEPART(QUARTER, GETDATE())

How to post data/code on a forum to get the best help.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

Check the execution plans, but I'll bet the second choice there performs better.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan Szeto SSC Enthusiast Points: 120 More actions · Answer 4

If you don't actually need results from the QUARTER table, an EXISTS subquery can actually be a little bit faster.

WITH CTE AS (
    SELECT DATEPART(QUARTER, GETDATE()) as 'Quarter', DATEPART(YEAR, GETDATE()) as 'Year'
)
SELECT
    CTE.Quarter,
    CTE.Year
FROM CTE
WHERE EXISTS (
SELECT 1 FROM Quaters as Q
WHERE
   CTE.Quarter = Q.Quater
   AND CTE.Year = Q.Year
);

This is especially true is the combination of Quater and Year in the table is not unique.

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 5

Instead of treating dates and time as if you were still writing 1960s COBOL and have to use strings, why not take a calendar table approach? SQL would call quarter an interval data type, which models a unit of temporal measure. 1 Possible Way of doing this is to give this interval a name that looks like '2020Q1' when you display it. You can put any given calendar date into its proper quarter of the symbol between predicate.

you need a simple lookup table to do this:

CREATE TABLE Foobar_Quarters

(quarter_name CHAR(6) NOT NULL PRIMARY KEY

CHECK (quarter_name LIKE '[12][09][09][09]Q[1-4]',

quarter_start_date DATE NOT NULL,

quarter_end_date DATE NOT NULL

CHECK (quarter_start_date quarter_end_date));

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

VSSGeorge wrote:

Is there any way to tweak the following query? Thanks

; WITH CTE AS
(
SELECT DATEPART(QUARTER, GETDATE()) as 'Quarter', DATEPART(YEAR, GETDATE()) as 'Year'
)

select Quarter,Year from CTE where Quarter IN (SELECT Quater from Quaters) AND Year IN (SELECT Year from Quaters)

What is the structure of the "Quaters" table???

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

I think we need clarification on what the desired output of this query is. To me, the whole Quaters table seems unnecessary if all you are returning in your results set is the Quarter and Year of the current date.

Is there some kind of data validation that is occurring by checking the Quaters table? If so, then you definitely don't want to use separate queries to validate the Quarter and Year, as a Year could have rows for up to 4 quarters in it.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Chris Harshman wrote:

I think we need clarification on what the desired output of this query is. To me, the whole Quaters table seems unnecessary if all you are returning in your results set is the Quarter and Year of the current date.
Is there some kind of data validation that is occurring by checking the Quaters table. If so, then you definitely don't want to use separate queries to validate the Quarter and Year, as a Year could have rows for up to 4 quarters in it.

This is exactly why I'm asking what's in it. It seems totally unnecessary to even look at it unless, as you say, it's somehow being used as validation of the presence of data OR we haven't been given all the information about what the desired return is supposed to be.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)