我想创建一条语句，但我无法成功完成此操作。您能看看吗，让我知道我需要做些什么才能完成此操作。我的问题由于如何在我的查询中添加这两部分。
我想查看每个DRV1NUM的订单的最近30天。如果该司机工作了0天，请说“不活跃”。如果驾驶员工作超过15天，则“全职”且少于15天为“部分时间”。
在这一部分中，我想查看订单的最后30天，并将DRIVERNUM的left（4）与整个DRIVERNUM进行比较。在某些情况下，我们的驱动程序的第4个字母后面有第5个字母。我想查看订单的最后30天，如果在查看所有字符时left（4）DRV1NUM有多个DRV1NUM，则说'掌握'
SELECT DISTINCT DRVLICNUM,DOB,COUNTRY,CREDITLIMIT,DRIVERNUM=LEFT(DRIVERNUM,4),
SSN,D.VEHICLE,PHN1,DRVLICST,HOST,VEHICLE_MC,
VEHICLE_DOT,BACK_APPROVED=CASE WHEN PROBDATE IS NOT NULL THEN 'YES' ELSE 'NO' END
-- CASE WHEN COUNT(DISTINCT O.DROPDATE)=0 IN LAST 30 DAYS THEN 'NOT ACTIVE' WHEN COUNT(DISTINCT O.DROPDATE)>15 IN LAST 30 DAYS THEN 'FULL TIME' WHEN COUNT(DISTINCT O.DROPDATE)>=1 AND <=15 THEN 'PART TIME' IN LAST 30 DAYS ELSE NULL AS DAYSWORKED,
-- --CASE WHEN COUNT(DISTINCT O.DRV1NUM OF LEFT(DRIVERNUM,4 )>0 IN LAST 30 DAYS OF ORDERS>1 THEN 'MASTER IC' ELSE NULL AS MASTER
/* ABOVE TWO STATEMENT I WANT TO ADD */
FROM DRIVER D
FULL OUTER JOIN orde_ O ON O.DRV1ID=D.DRIVERID
AND ISNUMERIC(DRIVERNUM)=1 and DRIVERNUM NOT IN ('1010')
我想要的预期输出
DRVLICNUM Employee DOB COUNTRY ACTIVESTATUS
---------------------------------------------------------
055243324 CONTRACTOR 1985-04-13 ATLANTA FULL TIME
ActiveStatus处于活动状态，因为驱动程序在过去15天内工作了15天以上，如果少于15天则为“兼职”
我无权访问您共享的Google驱动器链接。
但是，您将必须使用CTE（公用表表达式）来获取天数，然后使用它来计算ActiveStatus列的值。
尝试使用以下代码：
;
WITH CTE1
AS(
SELECT D.DRIVERID,
COUNT(DISTINCT O.DROPDATE) AS DayCount
FROM DRIVER AS D
LEFT JOIN ORDER AS O ON D.DRIVERID=O.DRV1ID AND O.DROPDATE BETWEEN CONVERT(DATE,DATEADD(DAY,-30,GETDATE())) AND GETDATE()
WHERE ISNUMERIC(D.DRIVERNUM)=1 AND D.DRIVERNUM NOT IN ('1010')
GROUP BY D.DRIVERID
)
SELECT DRVLICNUM,DOB,COUNTRY,CREDITLIMI,...
CASE
WHEN DayCount=0 THEN 'NOT ACTIVE'
WHEN DayCount<=15 THEN 'PART TIME'
WHEN DayCount>=30 THEN 'FULL TIME'
END AS ACTIVESTATUS
FROM CTE1 AS C
JOIN DRIVER AS D ON C.DRIVERID=D.DRIVERID
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句