substract value from a union query

  • 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

  • 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?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • i havent yet, will work with today(hoepfully)

    i will keep you posted, thanks for your help

  • 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