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