统计常用SQL

--分割函数1
ALTER FUNCTION [dbo].[ttt]
(
@str NVARCHAR(MAX),
@char CHAR(1),
@Index INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
IF @char IS NULL OR @str IS NULL OR @Index IS NULL OR LEN(@char)<1 OR @Index<1 OR LEN(@str)<1
RETURN @str
DECLARE @i INT=-1
DECLARE @t INT=0
WHILE @i=-1 OR @i>0
BEGIN
SET @i=CHARINDEX(@char,@str)
SET @t=@t+1

IF @t=@Index
BEGIN
IF @i>0
RETURN SUBSTRING(@str,0,@i)
ELSE
RETURN @str
END
SET @str=SUBSTRING(@str,@i+1,LEN(@str)-@i)
END

RETURN ''
END

--分割函数2
--SELECT [dbo].LastStr('fgfdgfdsg','f')
ALTER FUNCTION [dbo].[LastStr]
(
-- Add the parameters for the function here
@Str VARCHAR(MAX),
@splitStr VARCHAR(2)
)
RETURNS CHAR(10)
AS
BEGIN
DECLARE @v_temp NVARCHAR(50)
DECLARE @TempS TABLE(seq INT IDENTITY,id VARCHAR(500))

WHILE(CHARINDEX(@splitStr,@Str)<>0)
BEGIN
SET @v_temp=(SUBSTRING(@Str,1,CHARINDEX(@splitStr,@Str)-1))
IF ISNULL(@v_temp,'N/A')!='N/A' AND ISNULL(@v_temp,'N/A')<>''
BEGIN
INSERT @TempS(id) VALUES (@v_temp)
END
SET @Str = STUFF(@Str,1,CHARINDEX(@splitStr,@Str),'')
END
IF ISNULL(@Str,'N/A')!='N/A' AND ISNULL(@Str,'N/A')<>''
BEGIN
INSERT @TempS(id) VALUES (@Str)

END

SELECT @v_temp =id FROM @TempS WHERE seq =( SELECT MAX(seq) FROM @TempS )

RETURN @v_temp
END

--白夜
alter PROCEDURE [dbo].[txn_GetShiftId]
@DateTime DATETIME=NULL,
@ShiftId CHAR(12) OUTPUT--,
--@ShiftStartTime DATETIME OUTPUT
AS
BEGIN

DECLARE @TimeNow NVARCHAR(6)--=REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
DECLARE @dateNow DATETIME=ISNULL(@DateTime,GETDATE())

SET @TimeNow=RIGHT('000'+CONVERT(NVARCHAR(5),DATEPART(HOUR,@dateNow)),2)
SET @TimeNow=@TimeNow+RIGHT('000'+CONVERT(NVARCHAR(5),DATEPART(MINUTE,@dateNow)),2)
SET @TimeNow=@TimeNow+RIGHT('000'+CONVERT(NVARCHAR(5),DATEPART(SECOND,@dateNow)),2)

IF @TimeNow BETWEEN '080000' AND '200000' -- 白班
BEGIN
SET @ShiftId='SHF10000000C'
END
ELSE --夜班
BEGIN
SET @ShiftId='SHF10000000D'
END
END
GO

 

--计算时间戳
DECLARE @PlanDate DATE
DECLARE @NowTime DATETIME --当前时间
DECLARE @TimeSlice INT=8
DECLARE @MaxTimeSlice INT --最大的时间段
DECLARE @CurrTimeSlice INT

SET @NowTime=GETDATE()
SET @PlanDate=@NowTime

DECLARE @ShiftId CHAR(12)
SET @ShiftId='SHF10000000D'
IF OBJECT_ID('tempdb..#time1') IS NOT NULL DROP TABLE #time1
CREATE TABLE #time1(
CreateDate DATE,
TimeSlice int,
TimeQuantum NVARCHAR(50)
)
IF DATEPART(HOUR,@NowTime)>=0 AND DATEPART(HOUR,@NowTime)< 8
BEGIN
SET @PlanDate=DATEADD(DAY,-1,@NowTime)--夜班1点到8点必须减一天
SET @TimeSlice=20 --起始时间段
END

IF DATEPART(HOUR,@NowTime)>=20 AND DATEPART(HOUR,@NowTime)<= 24
BEGIN
SET @TimeSlice=20 --起始时间段
END

SET @CurrTimeSlice = DATEPART(HOUR,@NowTime) --add by zhangch 20200329
DECLARE @iData DATE
DECLARE @iTime INT
SET @iTime=0
WHILE @iTime<24
BEGIN
IF (@ShiftId='SHF10000000C' AND @iTime>=8 AND @iTime<20)OR (@ShiftId='SHF10000000D' AND (@iTime<8 or @iTime>=20))
BEGIN
IF @iTime<8
BEGIN
SET @iData=DATEADD(DAY,1,@NowTime)
END
ELSE
BEGIN
SET @iData=@NowTime
END
INSERT INTO #time1(CreateDate,TimeSlice,TimeQuantum)
SELECT @iData,@iTime+1,RIGHT('0'+CONVERT(NVARCHAR(2),@iTime),2)+':00-'+RIGHT('0'+CONVERT(NVARCHAR(2),(@iTime+1)),2)+':00'
END
SET @iTime=@iTime+1
END

IF @ShiftId='SHF10000000D' AND @CurrTimeSlice<8
BEGIN
UPDATE #time1 SET CreateDate = CONVERT ( DATE , CONVERT (DATETIME , CreateDate) -1 )
END

SELECT *FROM #time1 ORDER BY CreateDate, TimeSlice

--行转列
IF OBJECT_ID('tempdb..#T4') IS NOT NULL DROP TABLE #T4
select CreateDate,TimeSlice,InQty,OutQty,CASE WHEN (InQty-OutQty)<0THEN 0 ELSE (InQty-OutQty) end MakingQty
INTO #T4
FROM(SELECT CreateDate,TimeSlice,SpecificationId,Qty FROM #T3 ) test PIVOT(MAX(Qty) for
SpecificationId in(InQty,OutQty)) pvt

--累计数
SELECT CreateDate,TimeSlice,ISNULL(InQty,0),ISNULL(OutQty,0),
CASE when InQty-OutQty<0 THEN 0 ELSE InQty-OutQty end ,
(SELECT SUM(ISNULL(b.InQty,0)) FROM #T5 b WHERE b.id <= a.id) SumInQty,
(SELECT SUM(ISNULL(c.OutQty,0)) FROM #T5 c WHERE c.id <= a.id) SumOutQty,
(SELECT SUM(ISNULL(c.TargetQty,0)) FROM #T5 c WHERE c.id <= a.id) SUMUPH,
ISNULL(MakingQty,0) SumMakingQty
FROM #T5 a ORDER BY id ASC

--计算百分比
SELECT TimeSlice, isnull(SUMUPH,0) as SUMUPH,ISNULL(SumOutQty,0) AS OutQty, case bRate when null then
CONVERT(NVARCHAR(50),convert(numeric(18,2),CONVERT(INT,SumOutQty )*100.0/ case SUMUPH when 0 then 1 else SUMUPH end))
else bRate end AS aRate fROM #table_mxhj_y