34,590
社区成员
发帖
与我相关
我的任务
分享
--创建表与数据的sql语句如下,想得到的结果如下图:
项目 区域 开始日期 结束日期 天数
项目1 市外 2018/1/1 2018/1/9 10
项目1 市外 2018/1/25 2018/1/27 3
项目2 市外 2018/1/14 2018/1/16 3
项目3 市外 2018/1/28 2018/1/31 4
---- 请高手们指点指点。
CREATE TABLE [dbo].[Table_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Days] [datetime] NULL,
[XM] [nvarchar](50) NULL,
[QY] [nvarchar](50) NULL
) ON [PRIMARY]
GO
DECLARE @DayTime DATETIME, @DayTime2 datetime
SET @DayTime='2018-01-01'
SET @DayTime2='2018-01-31'
WHILE @DayTime<=@DayTime2
BEGIN
INSERT INTO Table_A([Days],[XM],[QY]) VALUES (@DayTime,'','')
SET @DayTime=DATEADD(DAY,1,@DayTime)
END
UPDATE Table_A SET [XM]='项目1',[QY]='市外' WHERE [Days] BETWEEN '2018-01-01' AND '2018-01-09'
UPDATE Table_A SET [XM]='项目2',[QY]='市内' WHERE [Days] BETWEEN '2018-01-10' AND '2018-01-13'
UPDATE Table_A SET [XM]='项目2',[QY]='市外' WHERE [Days] BETWEEN '2018-01-14' AND '2018-01-16'
UPDATE Table_A SET [XM]='项目3',[QY]='市内' WHERE [Days] BETWEEN '2018-01-17' AND '2018-01-20'
UPDATE Table_A SET [XM]='项目1',[QY]='市内' WHERE [Days] BETWEEN '2018-01-21' AND '2018-01-24'
UPDATE Table_A SET [XM]='项目1',[QY]='市外' WHERE [Days] BETWEEN '2018-01-25' AND '2018-01-27'
UPDATE Table_A SET [XM]='项目3',[QY]='市外' WHERE [Days] BETWEEN '2018-01-28' AND '2018-01-31'
SELECT * FROM Table_A
SELECT XM,
QY,
rn,
MIN(Days) AS 开始日期,
MAX(Days) AS 结束日期,
DATEDIFF(DAY,MIN(Days),MAX(Days))+1 AS 天数
FROM
(
SELECT *,
ID - ROW_NUMBER() OVER (PARTITION BY XM ORDER BY Days) AS rn
FROM Table_A
WHERE QY = '市外'
) t
GROUP BY XM,
QY,
rn
ORDER BY t.XM;
SELECT XM,
QY,
MIN(RQ) AS 开始日期,
MAX(RQ) AS 结束日期,
COUNT(RQ)*0.5 as 半天数,
COUNT(DISTINCT(RQ)) AS 天数
FROM
table_c
WHERE qy='市外'
GROUP BY XM,
QY
ORDER BY XM;
with cte_1
as
(select XM,QY,CONVERT(DATE,RQ) AS rq_new from Table_C
group by XM,QY,CONVERT(DATE,RQ))
select XM,QY,MIN(rq_new) as start_date,MAX(rq_new) as end_date,count(1) as amount
from
(select *,
ROW_NUMBER() over (partition by XM,QY order by rq_new) as rn_1,
DATEDIFF(DAY,'1900-01-01',rq_new) as rn_2
from cte_1) as A
group by XM,QY,rn_2-rn_1
order by xm,start_date
CREATE TABLE [dbo].[Table_C](
[XM] [nvarchar](50) NULL,
[QY] [nvarchar](50) NOT NULL,
[RQ] [datetime] NOT NULL,
[QJ] [varchar](4) NOT NULL
) ON [PRIMARY]
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','公司','2018-03-02 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','公司','2018-03-02 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','公司','2018-03-03 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市内','2018-03-03 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市内','2018-03-05 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市内','2018-03-05 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','公司','2018-03-06 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','公司','2018-03-06 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目3','公司','2018-03-07 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目3','公司','2018-03-07 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目3','市内','2018-03-08 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目3','市内','2018-03-08 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目3','市外','2018-03-09 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目3','市外','2018-03-09 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','市外','2018-03-10 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','市外','2018-03-10 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','市外','2018-03-12 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','市外','2018-03-12 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','市外','2018-03-13 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','市外','2018-03-13 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','省外','2018-03-14 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目1','省外','2018-03-14 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','省外','2018-03-15 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','省外','2018-03-15 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','省外','2018-03-16 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','省外','2018-03-16 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市外','2018-03-17 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市外','2018-03-17 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-03-19 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-03-19 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-03-20 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-03-20 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','省外','2018-03-21 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','省外','2018-03-21 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','省外','2018-03-22 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','省外','2018-03-22 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','省外','2018-03-23 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市外','2018-03-23 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市外','2018-03-24 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','公司','2018-03-24 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','公司','2018-03-26 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市外','2018-03-26 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市外','2018-03-27 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目2','市内','2018-03-27 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市内','2018-03-28 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市内','2018-03-28 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市内','2018-03-29 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市内','2018-03-29 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-03-31 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-03-31 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-04-01 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-04-01 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市外','2018-04-02 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市内','2018-04-02 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市内','2018-04-03 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目4','市内','2018-04-03 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目5','市内','2018-04-04 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目5','市内','2018-04-04 00:00:00.000','下午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目5','市内','2018-04-05 00:00:00.000','上午')
insert into Table_C (XM,QY,RQ,QJ) Values ('项目5','市内','2018-04-05 00:00:00.000','下午')
-- 查询后结果如下:
项目 区域 开始日期 结束日期 天数
项目1 公司内部 2018-03-02 2018-03-02 1
项目1 市外 2018-03-10 2018-03-13 4
项目1 省外 2018-03-14 2018-03-14 1
项目2 市内 2018-03-03 2018-03-03 1
项目2 市内 2018-03-05 2018-03-05 1
项目2 公司内部 2018-03-06 2018-03-07 2
项目2 省外 2018-03-15 2018-03-16 2
项目2 市外 2018-03-17 2018-03-17 1
项目2 市外 2018-03-23 2018-03-24 2
项目2 公司内部 2018-03-24 2018-03-26 3
项目2 市外 2018-03-26 2018-03-27 2
项目2 市内 2018-03-27 2018-03-27 1
项目3 公司内部 2018-03-07 2018-03-07 1
项目3 市内 2018-03-08 2018-03-08 1
项目3 市外 2018-03-09 2018-03-09 1
项目4 市外 2018-03-19 2018-03-20 2
项目4 省外 2018-03-21 2018-03-23 3
项目4 市内 2018-03-28 2018-03-29 2
项目4 市外 2018-03-31 2018-04-02 3
项目4 市内 2018-04-02 2018-04-03 2
--- 解释一下原始数据表结构哈:
这是一张工时表 每天的工时分为上午和下午两类,公司内部 表示是在公司工作,不算出差,市内 表示是在市内出差 市内出差呢 市外 表示市外出差,
省外则表示在省外出差,因为出差标准不一样 所以要分开计算;
有可能一天之内 上午在公司工作 下午出差了 则要体现出这一天的两条记录 则 这一天 既有市内也有在公司工作的数据。
请高手指点。。。。
SELECT XM,
QY,
rn,
MIN(Days) AS 开始日期,
MAX(Days) AS 结束日期,
DATEDIFF(DAY,MIN(Days),MAX(Days))+1 AS 天数
FROM
(
SELECT *,
DATEDIFF(DAY,'2018-01-01',days)-ROW_NUMBER() OVER (PARTITION BY XM ORDER BY Days) AS rn
FROM Table_A
WHERE QY = '市外'
) t
GROUP BY XM,
QY,
rn
ORDER BY t.XM;
--创建表与数据的sql语句如下,想得到的结果如下图:
项目 区域 开始日期 结束日期 天数
项目1 市外 2018/1/1 2018/1/9 10
项目1 市外 2018/1/24 2018/1/26 3
项目2 市外 2018/1/14 2018/1/16 3
项目3 市外 2018/1/27 2018/1/27 1
项目3 市外 2018/1/29 2018/1/31 3
---- 请高手们指点指点。
DROP TABLE [Table_A];
CREATE TABLE [dbo].[Table_A](
[Days] [datetime] NULL,
[XM] [nvarchar](50) NULL,
[QY] [nvarchar](50) NULL
) ON [PRIMARY]
GO
DECLARE @DayTime DATETIME, @DayTime2 datetime
SET @DayTime='2018-01-01'
SET @DayTime2='2018-01-31'
WHILE @DayTime<=@DayTime2
BEGIN
INSERT INTO Table_A([Days],[XM],[QY]) VALUES (@DayTime,'','')
SET @DayTime=DATEADD(DAY,1,@DayTime)
END
UPDATE Table_A SET [XM]='项目1',[QY]='市外' WHERE [Days] BETWEEN '2018-01-01' AND '2018-01-09'
UPDATE Table_A SET [XM]='项目2',[QY]='市内' WHERE [Days] BETWEEN '2018-01-10' AND '2018-01-13'
UPDATE Table_A SET [XM]='项目2',[QY]='市外' WHERE [Days] BETWEEN '2018-01-14' AND '2018-01-16'
UPDATE Table_A SET [XM]='项目3',[QY]='市内' WHERE [Days] BETWEEN '2018-01-17' AND '2018-01-20'
UPDATE Table_A SET [XM]='项目1',[QY]='市内' WHERE [Days] BETWEEN '2018-01-21' AND '2018-01-23'
UPDATE Table_A SET [XM]='项目1',[QY]='市外' WHERE [Days] BETWEEN '2018-01-24' AND '2018-01-26'
UPDATE Table_A SET [XM]='项目3',[QY]='市外' WHERE [Days] BETWEEN '2018-01-27' AND '2018-01-27'
UPDATE Table_A SET [XM]='项目3',[QY]='市外' WHERE [Days] BETWEEN '2018-01-29' AND '2018-01-31'
DELETE FROM Table_A WHERE [Days] = '2018-01-28'
SELECT * FROM Table_A