Using Between in Having Clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using Between in Having Clause

Author

Topic

GaryE
Starting Member

1 Post

Posted - 2011-02-02 : 15:46:35

Hello!
I am trying to return a set of values based on the current year GL Week.
I am looking at the MAX GL week and then want to subtract X number of weeks from "MAX(gl_week)" to return a list of weeks.
The format is: "201130" (that equals the current year * 100 + the gl_week.) @PROMOWKS is the number of weeks to subtract.

DECLARE @PROMOWKS int
SET @PROMOWKS= '5'

SELECT DISTINCT (DatePart(YYYY,GETDATE())*100) + (gl_week) AS WeekDate

FROM pfg_dm.dbo.dim_opco_time_tbl ot
WHERE gl_year=DatePart(yyyy,Getdate())
AND week_end_flag = 'Y'
GROUP BY ot.gl_week
HAVING gl_week between (max(ot.gl_week)-@PROMOWKS) AND (max(ot.gl_week))

My output is currently starting with week 01 and it should start with week 15. (max(gl_week) which is currently 30. @PROMOWKS= '5' so i should be starting at 201115.)

201101
201102
201103
201104
201105
201106
201107
201108
201109
201110
201111
201112
201113
201114
201115
201116
201117
201118
201119
201120
201121
201122
201123
201124
201125
201126
201127
201128
201129
201130

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-02 : 16:07:40

select TOP(@PROMOWKS) ....
ORDER BY gl_Week desc

N 56°04'39.26"
E 12°55'05.63"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-03 : 13:51:24

Why not put the GL calendar in a look-up table with the start and end dates of each GL week? It would allow the use of an index. UOu can also juliainize thwe weeks to do math on them.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources