September 16, 2016 at 9:43 am
Ok, I put the whole query in here, now I have not done alot of CAST or CASE, so this was my first attempt. What am I missing?
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @StartDate SMALLDATETIME
DECLARE @EndDate SMALLDATETIME
/**********************SET DATES HERE***************************/
SET @StartDate = '2016-08-23 08:00:00.000'
SET @EndDate = '2016-09-16 11:59:59.997'
/***************************************************************/
USE MEDTRAN
SELECT CONVERT(VARCHAR(10), orders.orderreceiveddate, 101) + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), orders.orderreceiveddate, 22), 11)) AS 'Order Received Date',
CONVERT(VARCHAR(10), ORDERS.UdpateTimeStamp, 101) + ' ' + LTRIM(RIGHT(CONVERT(CHAR(20), ORDERS.UdpateTimeStamp, 22), 11)) AS 'Update Time Stamp',
ORDERS.MemberOrderNumber AS 'PO Number', mem1.MemberName AS 'Affiliate', SNP_VENDOR.VendorName AS 'Vendor Name', SLA_CODE_DESCRIPTION.SLACodeKey AS 'SLA Code',
SNP_USER.UserFirstName AS TA,
CAST( CASE
WHEN orders.orderreceiveddate = ORDERS.UdpateTimeStamp
THEN 'NEW'
ELSE 'OLD'
END AS bit) AS 'CALL PO'
FROM ORDERS LEFT OUTER JOIN
ORDER_ACKNOWLEDGEMENT ON ORDERS.OrderId = ORDER_ACKNOWLEDGEMENT.OrderId INNER JOIN
SNP_MEMBER WITH (NOLOCK) ON ORDERS.MemberId = SNP_MEMBER.MemberId LEFT OUTER JOIN
SNP_VENDOR ON ORDERS.VendorId = SNP_VENDOR.VendorId INNER JOIN
SNP_MEMBER AS mem1 WITH (NOLOCK) ON SNP_MEMBER.ParentMemberId = mem1.MemberId LEFT OUTER JOIN
SLA_CODE_DESCRIPTION WITH (NOLOCK) ON ORDERS.SLACodeId = SLA_CODE_DESCRIPTION.SLACodeId INNER JOIN
TIME_ZONE ON SNP_MEMBER.TimeZoneId = TIME_ZONE.TimeZoneId LEFT OUTER JOIN
SNP_USER WITH (NOLOCK) ON ORDERS.UpdateUserId = SNP_USER.UserId
WHERE (ORDERS.OrderReceivedDate >= @StartDate)
AND (ORDERS.OrderReceivedDate < @EndDate)
AND ORDERS.SLACodeId = 10
AND ORDERS.OrderStatusId = 8
GROUP BY ORDERS.OrderReceivedDate, mem1.MemberName, SNP_USER.UserFirstName, SNP_VENDOR.VendorName, ORDERS.MemberOrderNumber, SLA_CODE_DESCRIPTION.SLACodeKey
, ORDERS.UdpateTimeStamp, ORDERS.OrderStatusId
ORDER BY SNP_USER.UserFirstName ASC
September 16, 2016 at 9:52 am
Your CASE expression returns NEW or OLD, and you're casting it to bit, which isn't valid. Change your CASE expression so that it returns 1 or 0, or cast as something else (or not at all).
John
September 16, 2016 at 10:04 am
Thank you! I was looking at that and did not thing of the "bit" as a numeric value. So if I want you use NEW and OLD then END AS " " what? VARCHAR? or CHAR?
September 16, 2016 at 10:07 am
tmiller 90290 (9/16/2016)
Thank you! I was looking at that and did not thing of the "bit" as a numeric value. So if I want you use NEW and OLD then END AS " " what? VARCHAR? or CHAR?
Since "NEW" and "OLD" are both 3-digits wide, I'd put it as CHAR(3). not much value casting it as variable length if the length is always 3.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 16, 2016 at 10:16 am
Got it and I see that now.
Ok, so last bump is:
Two dates are the same (=) then it should be "NEW", then the dates do not equal (=) is should be "OLD". That is not happening, it is just putting "OLD"
Whit that said since I am converting the date/time to add AM/PM is that why?
If so, what is the solution?
September 16, 2016 at 10:32 am
tmiller 90290 (9/16/2016)
Got it and I see that now.Ok, so last bump is:
Two dates are the same (=) then it should be "NEW", then the dates do not equal (=) is should be "OLD". That is not happening, it is just putting "OLD"
Whit that said since I am converting the date/time to add AM/PM is that why?
If so, what is the solution?
It's hard to say without sample data, but you should be using the DATEADD and DATEDIFF functions instead of converting your times to (VAR)CHAR. They're both easier to use and more efficient.
Depending on your needs, another option might be to CAST your DATETIME data to DATE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 16, 2016 at 10:40 am
The date show like this:
08/31/2016 10:30:50 AM
Now where I am not seeing my desired result is in this result
Order Received Date Update Time Stamp
09/15/2016 10:34:24 AM 09/15/2016 4:00:54 PM
This should be "NEW" based on the dates are equal, but since the time stamp is in there and I have to use it, that is how management wants to see it, then I "gut feeling" know this is the "why". Just not sure how to fix it. I will try your suggestion on the date.
September 16, 2016 at 10:45 am
Seems like you have time components in the columns you're comparing.
You're wanting it to just compare the date portion, but it's going to compare the full values of both columns, and the times are different.
To get you're desired results, you'll need to just compare the date components of those columns instead of the full datetime value.
Cheers!
September 16, 2016 at 10:49 am
Jacob Wilkins (9/16/2016)
Seems like you have time components in the columns you're comparing.You're wanting it to just compare the date portion, but it's going to compare the full values of both columns, and the times are different.
To get you're desired results, you'll need to just compare the date components of those columns instead of the full datetime value.
Cheers!
Yes, that is correct, my struggle is how to include that in the CASE.........
Since the desired result in the report is the date and time with AM/PM.
September 16, 2016 at 10:52 am
There are at least a couple ways. Your comparison in the CASE expression could check for equality between the values of those columns cast as DATE, or you could just make the condition a DATEDIFF(dd,...)=0.
Cheers!
September 16, 2016 at 11:37 am
tmiller 90290 (9/16/2016)
Jacob Wilkins (9/16/2016)
Seems like you have time components in the columns you're comparing.You're wanting it to just compare the date portion, but it's going to compare the full values of both columns, and the times are different.
To get you're desired results, you'll need to just compare the date components of those columns instead of the full datetime value.
Cheers!
Yes, that is correct, my struggle is how to include that in the CASE.........
Since the desired result in the report is the date and time with AM/PM.
Manipulations you do inside of a conditional expression do not update those fields. You can cast a DATETIME field to DATE inside of a CASE expression and still output the full DATETIME data.
DECLARE @now DATETIME = GETDATE();
SELECT dt, @now, CASE WHEN CAST(dt AS DATE) = CAST(@now AS DATE) THEN 'NEW' ELSE 'OLD' END
FROM (
VALUES(CAST('20000101 12:34:56' AS DATETIME)), ('20160916 09:50:43')
) d(dt)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply