34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT SUBSTRING(C.ExpiDate, 1, 4) AS S1
,SUBSTRING(C.ExpiDate, 6, 2) AS S2
FROM SPKFK A
JOIN DFWMS..WBT_Product B
ON A.SPID = B.Bindid
AND A.SPBH = B.Pcode
JOIN DFWMS..WBT_Inventory C
ON B.PID = C.PID
JOIN DFWMS..WBT_WHshy D
ON D.SHYID = C.SHYID
JOIN DFWMS..WBT_WHArea E
ON E.WHAID = D.WHAID
AND E.WHID = D.WHID
JOIN DFWMS..WBT_SKU F
ON C.SID = F.SID
AND A.sptm = F.Sbarcode
WHERE A.beactive = '是'
AND E.WHAstate = 1
AND F.Sstate = 1
AND (
A.spbh LIKE '%%'
OR A.spmch LIKE '%%'
OR A.sptm LIKE '%%'
OR A.ZJM LIKE '%%'
OR D.SHYcode LIKE '%201-01-01%'
OR E.WHAname LIKE '%%'
)
SELECT E.WHAname,
D.SHYcode,
A.spbh,
A.tongym,
A.sptm,
shpgg,
A.pizhwh,
A.shengccj,
F.Sunit,
B.Psort,
C.INnum,
C.Batch,
C.INprice,
B.Pstyle,
C.ProdDate,
C.ExpiDate,
CASE
WHEN LEN(C.ExpiDate) >= 7
AND(CONVERT(INT, SUBSTRING(CONVERT(CHAR(19),C.ExpiDate,120), 1, 4)) -YEAR(GETDATE())) * 365 + (
CONVERT(INT, SUBSTRING(CONVERT(CHAR(19),C.ExpiDate,120), 6, 2)) -MONTH(GETDATE())
) * 30 <= 180
AND(CONVERT(INT, SUBSTRING(CONVERT(CHAR(19),C.ExpiDate,120), 1, 4)) -YEAR(GETDATE())) * 365 + (
CONVERT(INT, SUBSTRING(CONVERT(CHAR(19),C.ExpiDate,120), 6, 2)) -MONTH(GETDATE())
) * 30 > 0 THEN '$FF0000'
WHEN LEN(C.ExpiDate) >= 7
AND(CONVERT(INT, SUBSTRING(CONVERT(CHAR(19),C.ExpiDate,120), 1, 4)) -YEAR(GETDATE())) * 365 + (
CONVERT(INT, SUBSTRING(CONVERT(CHAR(19),C.ExpiDate,120), 6, 2)) -MONTH(GETDATE())
) * 30 <= 0 THEN '$0000FF'
ELSE '$000000'
END AS __fcolor
FROM SPKFK A
JOIN DFWMS..WBT_Product B
ON A.SPID = B.Bindid
AND A.SPBH = B.Pcode
JOIN DFWMS..WBT_Inventory C
ON B.PID = C.PID
JOIN DFWMS..WBT_WHshy D
ON D.SHYID = C.SHYID
JOIN DFWMS..WBT_WHArea E
ON E.WHAID = D.WHAID
AND E.WHID = D.WHID
JOIN DFWMS..WBT_SKU F
ON C.SID = F.SID
AND A.sptm = F.Sbarcode
WHERE A.beactive = '是'
AND E.WHAstate = 1
AND F.Sstate = 1
AND (
A.spbh LIKE '%%'
OR A.spmch LIKE '%%'
OR A.sptm LIKE '%%'
OR A.ZJM LIKE '%%'
OR D.SHYcode LIKE '%201-01-01%'
OR E.WHAname LIKE '%%'
)