how to get quantity from yesterday 6pm till today 6pm

  • uahmed90 wrote:

    Hi Zidar,

    Here's the create table code and attached is a sample output. Hopefully this helps!

    CREATE TABLE [dbo].[FactBP](
    [CREATE_DATE] [date] NULL,
    [CREATE_TIME] [varchar](8) NULL,
    [ADJ_REQ_SHIP] [datetime] NULL,
    [RELEASED_DATE] [date] NULL,
    [RELEASED_TIME] [varchar](8) NULL,
    [ORDER_QTY] [numeric](5, 0) NULL,
    [RELEASED_QTY] [numeric](7, 0) NULL,
    [SHIP_QTY] [numeric](7, 0) NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL
    )

    Heh... UGH!  Spreadsheet! 😀

    Do yourself and the people that are trying to help you a big favor.  Post the data in a "Readily Consumable" format.  See the first link in my signature line below for all the "WHY" and some of the "HOW".  Then see the attached text file for a slightly different way to provide such data.

     

    Attachments:
    You must be logged in to view attached files.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • uahmed90 wrote:

    Hi,

    I am looking to create a case statement that gets different quantities. For example, I would like to see how many orders were created between 6pm yesterday till 6pm today, how much created between 3pm yesterday till 3pm today, and how much was dispatched between 3pm yesterday till 3pm today. I created this case statement, but It doesn't seem correct. The 6-6pm quantities are not correct, but the 3pm seem to be correct. I'm using 5 columns, create date, create time, dispatch date, dispatch time and qty. I have the following case statment:

    SELECT 
    CREATE_DATE
    , CREATE_TIME
    , DISPATCH_DATE
    , DISPATCH_TIME
    , CASE
    WHEN DATEDIFF(DAY,CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME),ADJ_REQ_SHIP) = 1
    THEN

    CASE

    WHEN CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME) BETWEEN
    dateadd(hh, 15, dateadd(dd, -1, convert(datetime, convert(char(12),CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
    AND dateadd(ms, -3, dateadd(hh, 15, convert(datetime, convert(char(12), CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
    THEN 'SMALLER HOUR WINDOW'

    WHEN CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME) BETWEEN
    dateadd(hh, 18, dateadd(dd, -1, convert(datetime, convert(char(12),CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
    AND dateadd(ms, -3, dateadd(hh, 18, convert(datetime, convert(char(12), CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
    THEN 'NORMAL HOUR WINDOW'

    WHEN CAST(CONCAT(RELEASED_DATE, ' ', RELEASED_TIME) AS DATETIME) BETWEEN
    dateadd(hh, 15, dateadd(dd, -1, convert(datetime, convert(char(12),CAST(CONCAT(RELEASED_DATE, ' ', RELEASED_TIME) AS DATETIME)))))
    AND dateadd(ms, -3, dateadd(hh, 15, convert(datetime, convert(char(12), CAST(CONCAT(RELEASED_DATE, ' ', RELEASED_TIME) AS DATETIME)))))
    THEN 'AGREED UPON WINDOW'
    END
    END AS 'WINDOW'

    I have to admit, I confused.  The accepted answer looks nothing like what you posted.  What is the purpose of the "Window" column in your code above?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply