SQLserver加个判断

Question

SELECT count(distinct lp.lot_number)as ELzs
FROM (SELECT lp.lot_number,lp.TransDate,lp.WorkorderId ,lp.device_number,
ROW_NUMBER() OVER( PARTITION BY lp.lot_number ORDER BY lp.TransDate)RN FROM Lot_Process lp)lp
WHERE RN=2 and (lp.WorkorderId like'PB%' or lp.WorkorderId like'DB%') and
lp.device_number in('M1-EL1-L1-01','M1-EL1-L1-02')
and lp.TransDate between convert(varchar(11),dateadd(day,-1,getdate()),120)+'08:00:00'
and convert(varchar(11),dateadd(day,-1,getdate()),120)+'19:59:59'

给帮忙加个判断，每天9点之前查询昨天8点到昨天20点，9点之后查询今天8点到今天20点。

Answer 1

两种方案：

1、内嵌判断条件

SELECT COUNT(DISTINCT LP.LOT_NUMBER) AS ELZS
FROM (
         SELECT LP.LOT_NUMBER,
                LP.TRANSDATE,
                LP.WORKORDERID,
                LP.DEVICE_NUMBER,
                ROW_NUMBER()
                        OVER (PARTITION BY LP.LOT_NUMBER
                            ORDER BY LP.TRANSDATE
                            ) RN
         FROM LOT_PROCESS LP
     ) LP
WHERE RN = 2
  AND (LP.WORKORDERID LIKE 'PB%'
    OR LP.WORKORDERID LIKE 'DB%')
  AND LP.DEVICE_NUMBER IN ('M1-EL1-L1-01', 'M1-EL1-L1-02')
  AND LP.TRANSDATE
    BETWEEN CONVERT(VARCHAR(11),
                    DATEADD(DAY, CASE WHEN CONVERT(varchar(100), GETDATE(), 24) < '09:00:00' THEN - 1 ELSE 0 END,
                            GETDATE()), 120) + '08:00:00'
    AND CONVERT(VARCHAR(11), DATEADD(
                                     DAY,
                                     CASE WHEN CONVERT(varchar(100), GETDATE(), 24) < '09:00:00' THEN - 1 ELSE 0 END,
                                     GETDATE()), 120) + '19:59:59';

2、分别判断，然后再UNION ALL

SELECT COUNT(DISTINCT LP.LOT_NUMBER) AS ELZS
FROM (
         SELECT LP.LOT_NUMBER,
                LP.TRANSDATE,
                LP.WORKORDERID,
                LP.DEVICE_NUMBER,
                ROW_NUMBER()
                        OVER (PARTITION BY LP.LOT_NUMBER
                            ORDER BY LP.TRANSDATE
                            ) RN
         FROM LOT_PROCESS LP
     ) LP
WHERE RN = 2
  AND (LP.WORKORDERID LIKE 'PB%'
    OR LP.WORKORDERID LIKE 'DB%')
  AND LP.DEVICE_NUMBER IN ('M1-EL1-L1-01', 'M1-EL1-L1-02')
  AND LP.TRANSDATE
    BETWEEN CONVERT(VARCHAR(11), DATEADD(DAY, - 1, GETDATE()), 120) + '08:00:00'
    AND CONVERT(VARCHAR(11), DATEADD(DAY, -1, GETDATE()), 120) + '19:59:59'
  AND CONVERT(varchar(100), GETDATE(), 24) < '09:00:00'
UNION ALL
SELECT COUNT(DISTINCT LP.LOT_NUMBER) AS ELZS
FROM (
         SELECT LP.LOT_NUMBER,
                LP.TRANSDATE,
                LP.WORKORDERID,
                LP.DEVICE_NUMBER,
                ROW_NUMBER()
                        OVER (PARTITION BY LP.LOT_NUMBER
                            ORDER BY LP.TRANSDATE
                            ) RN
         FROM LOT_PROCESS LP
     ) LP
WHERE RN = 2
  AND (LP.WORKORDERID LIKE 'PB%'
    OR LP.WORKORDERID LIKE 'DB%')
  AND LP.DEVICE_NUMBER IN ('M1-EL1-L1-01', 'M1-EL1-L1-02')
  AND LP.TRANSDATE
    BETWEEN CONVERT(VARCHAR(11), GETDATE(), 120) + '08:00:00'
    AND CONVERT(VARCHAR(11), GETDATE(), 120) + '19:59:59'
  AND CONVERT(varchar(100), GETDATE(), 24) >= '09:00:00';