September 7, 2010 at 7:45 am
i use the following to capture the previous days values, as well as, current day.
i would like to know how do i substract a value in the previous day from the current day then use the retrieved value as a field in a report.
would i put the formula in the select distinct to portion?
(S1.ON_ORD_1 - S2.ON_ORD_1_2) as total_diff
would that work????
SELECT DISTINCT
S1.STYLE,
S1.COLOR,
S1.replication_date,
S1.SEASON,
S1.ON_ORD_1,
S1.ON_ORD_2,
S1.ON_ORD_3,
S1.ON_ORD_4,
S1.ON_ORD_5,
S1.ON_ORD_6,
S1.ON_ORD_7,
S1.ON_ORD_8,
S1.ON_ORD_9,
S1.ON_HND_1,
S1.ON_HND_2,
S1.ON_HND_3,
S1.ON_HND_4,
S1.ON_HND_5,
S1.ON_HND_6,
S1.ON_HND_7,
S1.ON_HND_8,
S1.ON_HND_9,
S2.STYLE_2,
S2.COLOR_2,
S2.replication_date_2,
S2.SEASON_2,
S2.ON_ORD_1_2,
S2.ON_ORD_2_2,
S2.ON_ORD_3_2,
S2.ON_ORD_4_2,
S2.ON_ORD_5_2,
S2.ON_ORD_6_2,
S2.ON_ORD_7_2,
S2.ON_ORD_8_2,
S2.ON_ORD_9_2,
S2.ON_HND_1_2,
S2.ON_HND_2_2,
S2.ON_HND_3_2,
S2.ON_HND_4_2,
S2.ON_HND_5_2,
S2.ON_HND_6_2,
S2.ON_HND_7_2,
S2.ON_HND_8_2,
S2.ON_HND_9_2
FROM (/*FOR CURRENT DAY*/
SELECT DISTINCT
SYSTYL AS STYLE,
SYCLNM AS COLOR,
CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101)) as replication_date,
SYSEA AS SEASON,
SYASZ1 as ON_ORD_1,
SYASZ2 as ON_ORD_2,
SYASZ3 as ON_ORD_3,
SYASZ4 as ON_ORD_4,
SYASZ5 as ON_ORD_5,
SYASZ6 as ON_ORD_6,
SYASZ7 as ON_ORD_7,
SYASZ8 as ON_ORD_8,
SYASZ9 as ON_ORD_9,
SYFSZ1 AS ON_HND_1,
SYFSZ2 AS ON_HND_2,
SYFSZ3 AS ON_HND_3,
SYFSZ4 AS ON_HND_4,
SYFSZ5 AS ON_HND_5,
SYFSZ6 AS ON_HND_6,
SYFSZ7 AS ON_HND_7,
SYFSZ8 AS ON_HND_8,
SYFSZ9 AS ON_HND_9
FROM
dbo.STYLEMST_HISTORY
where CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101))=CONVERT(datetime,CONVERT(char(10),getdate(),101))
)S1
LEFT OUTER JOIN
(
/* For previous day */
SELECT DISTINCT
SYSTYL AS STYLE_2,
SYCLNM AS COLOR_2,
CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101)) as replication_date_2,
SYSEA AS SEASON_2,
SYASZ1 as ON_ORD_1_2,
SYASZ2 as ON_ORD_2_2,
SYASZ3 as ON_ORD_3_2,
SYASZ4 as ON_ORD_4_2,
SYASZ5 as ON_ORD_5_2,
SYASZ6 as ON_ORD_6_2,
SYASZ7 as ON_ORD_7_2,
SYASZ8 as ON_ORD_8_2,
SYASZ9 as ON_ORD_9_2,
SYFSZ1 AS ON_HND_1_2,
SYFSZ2 AS ON_HND_2_2,
SYFSZ3 AS ON_HND_3_2,
SYFSZ4 AS ON_HND_4_2,
SYFSZ5 AS ON_HND_5_2,
SYFSZ6 AS ON_HND_6_2,
SYFSZ7 AS ON_HND_7_2,
SYFSZ8 AS ON_HND_8_2,
SYFSZ9 AS ON_HND_9_2,
CONVERT(datetime,CONVERT(char(10),getdate()-1,101)) as previous_day_2
FROM
dbo.STYLEMST_HISTORY
WHERE CONVERT(datetime,CONVERT(char(10),REPLICATION_DATE,101))=CONVERT(datetime,CONVERT(char(10),getdate()-1,101))
)S2
ON S1.STYLE =S2.STYLE_2
AND S1.color = S2.COLOR_2
AND S1.season = S2.SEASON_2
WHERE
ON_ORD_1 = ON_ORD_1_2 and ON_ORD_1 <>0 and ON_ORD_1_2 <>0 OR
ON_ORD_2 = ON_ORD_2_2 and ON_ORD_2 <>0 and ON_ORD_2_2 <>0 OR
ON_ORD_3 = ON_ORD_3_2 and ON_ORD_3 <>0 and ON_ORD_3_2 <>0 OR
ON_ORD_4 = ON_ORD_4_2 and ON_ORD_4 <>0 and ON_ORD_4_2 <>0 OR
ON_ORD_5 = ON_ORD_5_2 and ON_ORD_5 <>0 and ON_ORD_5_2 <>0 OR
ON_ORD_6 = ON_ORD_6_2 and ON_ORD_6 <>0 and ON_ORD_6_2 <>0 OR
ON_ORD_7 = ON_ORD_7_2 and ON_ORD_7 <>0 and ON_ORD_7_2 <>0 OR
ON_ORD_8 = ON_ORD_8_2 and ON_ORD_8 <>0 and ON_ORD_8_2 <>0 OR
ON_ORD_9 = ON_ORD_9_2 and ON_ORD_9 <>0 and ON_ORD_9_2 <>0 OR
ON_HND_1 = ON_HND_1_2 and ON_HND_1<> 0 and ON_HND_1_2 <>0 or
ON_HND_2 = ON_HND_2_2 and ON_HND_2<> 0 and ON_HND_2_2 <>0 or
ON_HND_3 = ON_HND_3_2 and ON_HND_3<> 0 and ON_HND_3_2 <>0 or
ON_HND_4 = ON_HND_4_2 and ON_HND_4<> 0 and ON_HND_4_2 <>0 or
ON_HND_5 = ON_HND_5_2 and ON_HND_5<> 0 and ON_HND_5_2 <>0 or
ON_HND_6 = ON_HND_6_2 and ON_HND_6<> 0 and ON_HND_6_2 <>0 or
ON_HND_7 = ON_HND_7_2 and ON_HND_7<> 0 and ON_HND_7_2 <>0 or
ON_HND_8 = ON_HND_8_2 and ON_HND_8<> 0 and ON_HND_8_2 <>0 or
ON_HND_9 = ON_HND_9_2 and ON_HND_9<> 0 and ON_HND_9_2 <>0
ORDER BY S1.STYLE
September 7, 2010 at 7:54 am
Hi Sharon
I think this query can be simplified quite a bit. First though, a couple of questions. Is the DISTINCT necessary, and if so why?
What datatype is the column REPLICATION_DATE?
What rows do you need in your report?
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
September 7, 2010 at 8:14 am
-- do your date arithmetic up front
DECLARE @StartToday DATETIME
SET @StartToday = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
SELECT @StartToday
-- no more DISTINCT: which row would you pick from the dupes?
-- If there really are dupes, what are the rules for selecting one row over another?
SELECT
STYLE = t.SYSTYL,
COLOR = t.SYCLNM,
Today = @StartToday,
Yesterday = DATEADD(dd, -1, @StartToday),
SEASON = t.SYSEA,
ON_ORD_1 = t.SYASZ1 - y.SYASZ1, -- today minus yesterday
ON_ORD_9 = t.SYASZ9 - y.SYASZ9,
ON_HND_1 = t.SYFSZ1 - y.SYFSZ1,
ON_HND_9 = t.SYFSZ9 - y.SYFSZ9
FROM dbo.STYLEMST_HISTORY t
LEFT JOIN dbo.STYLEMST_HISTORY y
ON y.SYSTYL = t.SYSTYL
AND y.SYCLNM = t.SYCLNM
AND y.SYSEA = t.SYSEA
AND y.REPLICATION_DATE >= DATEADD(dd, -1, @StartToday) AND y.REPLICATION_DATE < @StartToday
-- no functions around REPLICATION_DATE means it will be SARGable - an appropriate index will speed it up
WHERE t.REPLICATION_DATE >= @StartToday AND t.REPLICATION_DATE < DATEADD(dd, +1, @StartToday)
ORDER BY t.SYSTYL
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
September 7, 2010 at 10:55 am
when the sp was created, distinct was neccessary. table has been cleaned up since.
i use all the fields in the report,
replication date is a date field when the data was added to the table
September 8, 2010 at 2:06 am
sharonmtowler (9/7/2010)
when the sp was created, distinct was neccessary. table has been cleaned up since.i use all the fields in the report,
replication date is a date field when the data was added to the table
Thanks Sharon.
I've taken out DISTINCT because it has a cost.
Is replication date a DATE or DATETIME?
Did you run the code I posted? Any questions?
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
September 8, 2010 at 7:00 am
i havent yet, will work with today(hoepfully)
i will keep you posted, thanks for your help
September 8, 2010 at 7:01 am
replication date is a date field
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply