查询最早入库时间数据

查询最早入库时间数据

SELECT TOP 1 入库时间标记 AS 当月入库最早时间,COUNT(*) AS 总数
FROM JW01_DBQD
WHERE YEAR(入库时间标记) = YEAR(GETDATE())
AND MONTH(入库时间标记) = MONTH(GETDATE())
GROUP BY 入库时间标记
ORDER BY 入库时间标记 ASC;

WITH a AS 
(
SELECT * FROM JW01_DBQD WHERE 上联接入方式 = 'Ethernet' 
 AND 入库时间标记 BETWEEN '2024-03-11' AND '2024-03-14' AND LEFT(CAST(激活时间 AS date),7) 
 LIKE LEFT('2024-03-14', 7) + '%'
),
b AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [设备SN] ORDER BY [入库时间标记] ASC) AS 去重参考 FROM a
),
main AS (
SELECT * FROM b WHERE 去重参考=1
)   
select 地市替换=REPLACE(a.地市,'市',''),b.区县,b.网格,a.* from main as a left join 
(select distinct 地市,区县,网格 from JW01_JZB) as b on REPLACE(a.地市,'市','') = b.地市;


WITH a AS(
SELECT 
    jzb.*,
    CASE WHEN DBQD."设备SN" IS NOT NULL THEN '已整治' ELSE '未整治' END AS 匹配情况
FROM JW01_JZB jzb
LEFT JOIN (
    SELECT DISTINCT "设备SN" FROM JW01_DBQD WHERE CAST(入库时间标记 AS date) = '2024-03-14'
) DBQD ON jzb."设备SN" = DBQD."设备SN"
)
SELECT * FROM a




WITH a AS(
SELECT 
    jzb.*,
    CASE WHEN DBQD."设备SN" IS NOT NULL THEN '未整治' ELSE '已整治' END AS 匹配情况
    --CASE WHEN DBQD."设备SN" IS NOT NULL THEN '已整治' ELSE '未整治' END AS 匹配情况
FROM JW01_JZB jzb
LEFT JOIN (
    SELECT "设备SN" FROM JW01_DBQD WHERE CAST(入库时间标记 AS date) = '2024-03-14'
) DBQD ON jzb."设备SN" = DBQD."设备SN"
)
SELECT DISTINCT * FROM a WHERE 匹配情况='已整治';
SELECT 匹配情况,COUNT(*) AS 总数 FROM a GROUP BY 匹配情况;

SELECT 
    CONVERT(date, 入库时间标记) AS 入库日期,
    COUNT(1) AS 总数
FROM JW01_DBQD WHERE CONVERT(date, 入库时间标记) = CONVERT(date, DATEADD(day, -1, GETDATE()))
GROUP BY CONVERT(date, 入库时间标记);
SELECT CONVERT(date,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) AS 当月第一天;
SELECT 入库时间标记 FROM JW01_DBQD
WHERE CONVERT(date, 入库时间标记) = (SELECT MIN(CONVERT(date, 入库时间标记)) FROM JW01_DBQD);

SELECT MIN(入库时间标记) AS 最早入库时间
FROM JW01_DBQD
WHERE CONVERT(date, 入库时间标记) = 
CONVERT(date, DATEADD(day, -1, GETDATE()));
--CONVERT(date, GETDATE());
SELECT TOP 1 入库时间标记
FROM JW01_DBQD
WHERE CONVERT(date, 入库时间标记) = CONVERT(date, GETDATE())
ORDER BY 入库时间标记 ASC;

SELECT TOP 1 入库时间标记 AS 当月入库最早时间,COUNT(*) AS 总数
FROM JW01_DBQD
WHERE YEAR(入库时间标记) = YEAR(GETDATE())
AND MONTH(入库时间标记) = MONTH(GETDATE())
GROUP BY 入库时间标记
ORDER BY 入库时间标记 ASC;

SELECT MIN(入库时间标记) AS 当月入库最早时间,COUNT(*) AS 总数
FROM JW01_DBQD
WHERE CAST(入库时间标记 AS date) LIKE LEFT(CONVERT(date, DATEADD(day, -1, GETDATE())),7)+'%'
GROUP BY 入库时间标记;

SELECT MIN(入库时间标记) AS 当月入库最早时间,COUNT(*) AS 总数 FROM JW01_DBQD
WHERE YEAR(入库时间标记) = YEAR(GETDATE()) AND MONTH(入库时间标记) = MONTH(GETDATE());

SELECT 入库时间标记 AS 当月入库最早时间, COUNT(*) AS 总数 FROM JW01_DBQD
WHERE CAST(入库时间标记 AS DATE) = (
    SELECT MIN(CAST(入库时间标记 AS DATE)) FROM JW01_DBQD
    WHERE 入库时间标记 >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) -- 当月第一天
      AND 入库时间标记 < DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0) -- 下个月第一天
)
GROUP BY 入库时间标记;
SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) AS date) AS 下个月第一天;

SELECT * FROM View_ZNZWZJ_GD_ONLY WHERE "工单标题" LIKE 'FTTR%' AND CAST(boss归档时间 AS date) LIKE '2024-03%'



SELECT MIN(入库时间标记) AS 当月入库最早时间, COUNT(*) AS 总数
FROM JW01_DBQD
WHERE 入库时间标记 >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) -- 当月第一天
  AND 入库时间标记 < DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0); -- 下个月第一天

SELECT 入库时间标记,COUNT(1) AS 总数 FROM JW01_DBQD
WHERE CAST(入库时间标记 AS date) = (GETDATE()-1) GROUP BY 入库时间标记;
SELECT 入库时间标记,COUNT(1) AS 总数 FROM JW01_DBQD
WHERE CAST(入库时间标记 AS date) LIKE LEFT('2024-03-24',7)+'%' GROUP BY 入库时间标记;
-- 查询当月每天入库时间标记并统计总数
SELECT 
    CONVERT(date, 入库时间标记) AS 入库日期,
    COUNT(1) AS 总数
FROM JW01_DBQD WHERE CONVERT(date, 入库时间标记) LIKE LEFT(CONVERT(date, DATEADD(day, -1, GETDATE())),7)+'%'
GROUP BY CONVERT(date, 入库时间标记);

-- 声明一个日期参数
DECLARE @DateParameter DATE
-- 设置日期参数为前一天的日期
SET @DateParameter = DATEADD(day, -1, GETDATE())
-- 执行查询，并使用参数化查询来传递日期参数
SELECT
    CONVERT(date, 入库时间标记) AS 入库日期,
    COUNT(1) AS 总数
FROM JW01_DBQD
WHERE CONVERT(date, 入库时间标记) LIKE LEFT(CONVERT(date, @DateParameter), 7) + '%'
GROUP BY CONVERT(date, 入库时间标记);



SELECT * FROM [4AZHB];
SELECT DISTINCT [4A账号] FROM PDCL;

SELECT COUNT(*) AS 总数 FROM JW01_DBQD WHERE CAST(入库时间标记 AS date) BETWEEN '2024-03-23' AND '2024-03-24'
DELETE JW01_DBQD WHERE CAST(入库时间标记 AS date) BETWEEN '2024-03-23' AND '2024-03-24'

SELECT 入库时间标记 FROM JW01_DBQD WHERE CAST(入库时间标记 AS date) LIKE LEFT('2024-03-14',7)+'%' 
GROUP BY 入库时间标记
WITH a AS 
(
SELECT * FROM JW01_DBQD WHERE 上联接入方式 = 'Ethernet' 
 AND 入库时间标记 BETWEEN '2024-03-11' AND '2024-03-21' AND LEFT(CAST(激活时间 AS date),7) LIKE 
 LEFT('2024-03-21', 7) + '%'
),
b AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [设备SN] ORDER BY [入库时间标记] ASC) AS 去重参考 FROM a
),
main AS (
SELECT * FROM b WHERE 去重参考=1
),
table_a AS(
select 地市替换=REPLACE(a.地市,'市',''),b.区县 AS 匹配区县,b.网格,a.* from main as a left join 
(select distinct 地市,区县,网格 from JW01_JZB) as b on REPLACE(a.地市,'市','') = b.地市
)
SELECT 匹配情况,COUNT(*) AS 总数 FROM (
SELECT *,匹配情况=CASE WHEN b.匹配SN IS NOT NULL THEN '未整治' ELSE '已整治' END 
FROM table_a a LEFT JOIN (SELECT DISTINCT "设备SN" AS 匹配SN FROM JW01_DBQD WHERE 入库时间标记='2024-03-21') b 
ON a."设备SN"=b.匹配SN
) mian GROUP BY 匹配情况


-- WITH a AS 
-- (
--     SELECT * FROM JW01_DBQD 
--     WHERE 上联接入方式 = 'Ethernet' 
--     AND 入库时间标记 BETWEEN '2024-03-11' AND '2024-03-21' 
--     AND LEFT(CAST(激活时间 AS date), 7) LIKE LEFT('2024-03-21', 7) + '%'
-- ),
-- b AS (
--     SELECT *, ROW_NUMBER() OVER (PARTITION BY [设备SN] ORDER BY [入库时间标记] ASC) AS 去重参考 FROM a
-- ),
-- main AS (
--     SELECT * FROM b WHERE 去重参考 = 1
-- ),
-- table_a AS (
--     SELECT 地市替换 = REPLACE(a.地市,'市',''), b.区县 AS 匹配区县, b.网格, a.* 
--     FROM main AS a 
--     LEFT JOIN (SELECT DISTINCT 地市, 区县, 网格 FROM JW01_JZB) AS b 
--     ON REPLACE(a.地市,'市','') = b.地市
-- )
-- SELECT COUNT(*) AS 数据量
-- FROM table_a a 
-- LEFT JOIN (
--     SELECT DISTINCT "设备SN" AS 匹配SN 
--     FROM JW01_DBQD 
--     WHERE 入库时间标记 = '2024-03-21'
-- ) b ON a."设备SN" = b.匹配SN
-- WHERE b.匹配SN IS NULL IS NOT NULL


SELECT TOP 10 * FROM HDCD;
ALTER TABLE HDCD
ADD id INT IDENTITY(1,1) --PRIMARY KEY;

sp_refreshview View_HDCD_ONLY;

SELECT COUNT(*) AS 总数 FROM HDCD WHERE CAST(boss撤单时间 as date) LIKE '2024-02%';
with a as 
(select 地市,区县,区域类型,后端驳回原因,所属网格,所属小区,count(*) 总数 from View_HDCD_ONLY 
where left(cast(boss撤单时间 as date),10)>='2024-02-01' and left(cast(boss撤单时间 as date),10)<='2024-02-29' 
GROUP BY 地市,区县,区域类型,后端驳回原因,所属网格,所属小区)
SELECT * FROM a 

SELECT TOP 1 * FROM JW01_DBQD;