August 6, 2013 at 1:50 pm
I am trying to create a date, that I can put in a where statement to filter for the last 3 months of data... but not use today's date but the latest date in the table.
I was thinking along these lines.. but SQL errors out:
;with base as
(
select post_dm
from ztb_forecastable_metrics_hist
group by Post_DM
)
set @lastdate = (select MAX(post_dm) as Max_DM from base)
August 6, 2013 at 2:00 pm
Why are you using a CTE when a normal query could work?
If you give us more information on your query, we might suggest something different, but to solve your problem, you could use something like this.
SELECT @date = DATEADD( MONTH, -3, MAX(post_dm))
FROM ztb_forecastable_metrics_hist
August 7, 2013 at 7:12 am
Luis Cazares (8/6/2013)
Why are you using a CTE when a normal query could work?If you give us more information on your query, we might suggest something different, but to solve your problem, you could use something like this.
SELECT @date = DATEADD( MONTH, -3, MAX(post_dm))
FROM ztb_forecastable_metrics_hist
Because I was thinking by process.. I need to know what the last date, in the table, so I can then get a 3 mo rolling avg. It should be noted that post DM is a date field, but due to the PK can only have one record per month. (yyyymm is part of the PK... called UID)
so you would have..
CREATE TABLE [dbo].[ztb_forecastable_metrics_hist ](
[UID] [varchar](59) NOT NULL,
[Post_DM] [date] NULL,
[Business] [varchar](50) NOT NULL,
[Per_Vol_Fcstable] [numeric](7, 4) NULL,
[Per_Min_Max_Stat_Fcstable] [numeric](7, 4) NULL,
[Per_Min_Max_Stat_Non_Fcstable] [numeric](7, 4) NULL,
[Per_Min_Max_Consen_Fcstable] [numeric](7, 4) NULL,
[Per_Min_Max_consen_Non_Fcstable] [numeric](7, 4) NULL,
[Per_Min_Max_Stat_overall] [numeric](7, 4) NULL,
[Per_Min_Max_consen_Non_overall] [numeric](7, 4) NULL,
CONSTRAINT [PK_metrics_hist] PRIMARY KEY CLUSTERED
(
[UID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SELECT Business
, Avg(Per_Vol_Fcstable)
, Avg(Per_Min_Max_Stat_Fcstable)
, Avg(Per_Min_Max_Stat_Non_Fcstable)
, Avg(Per_Min_Max_Consen_Fcstable)
, Avg(Per_Min_Max_consen_Non_Fcstable)
, Avg(Per_Min_Max_Stat_overall)
, Avg(Per_Min_Max_consen_Non_overall)
FROM ztb_forecastable_metrics_hist
where Post_DM >= (select dateadd(mm, datediff(mm, 0, GETDATE()) - 2, 0))
group by Business
order by Business
August 7, 2013 at 7:26 am
;WITH base AS (
SELECT Max_DM = MAX(post_dm) FROM ztb_forecastable_metrics_hist
)
SELECT
post_dm -- etc
FROM ztb_forecastable_metrics_hist
WHERE post_dm BETWEEN DATEADD(mm,-3,base.Max_DM) AND base.Max_DM
GROUP BY Post_DM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 7, 2013 at 8:33 am
The query gave me a multi-part error on Base.Post_DM
I changed it to the following:
;WITH base AS (
SELECT Max_DM = MAX(post_dm) FROM ztb_forecastable_metrics_hist
)
SELECT
post_dm -- etc
FROM ztb_forecastable_metrics_hist
WHERE post_dm BETWEEN DATEADD(mm,-3,(select Max_DM from base)) AND (select Max_DM from base)
GROUP BY Post_DM
August 7, 2013 at 8:43 am
You could change it like this
;WITH base AS (
SELECT Initial_Date = DATEADD( mm, -3, MAX(post_dm)) ,
Final_Date = MAX(post_dm)
FROM ztb_forecastable_metrics_hist
)
SELECT
post_dm -- etc
FROM ztb_forecastable_metrics_hist ztb
JOIN base ON post_dm BETWEEN base.Initial_Date AND base.Final_Date
August 7, 2013 at 9:20 am
Louis and Chris thanks for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy