October 12, 2021 at 5:31 am
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2021 at 5:45 am
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
Change is inevitable... Change for the better is not.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply