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

Question

Post reply

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

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)