DECLARE @StarDate varchar(50),@EndDatevarchar(50)
DECLARE @MC varchar(6),@FAC int
set @StarDate='2019-02-25 08:00:00'
set @EndDate='2019-03-25 07:59:59'
set @MC='2000'
set @FAC=8
with tempb AS (
select convert(varchar(100),p.SaveDate,112) as DayTime ,isnull(sum(p.NetWeight/1000),0) as NetWeight,c.CustomerId
from Base_PoundRecord p
left join Base_Customers c on c.CustomerId=p.CustomerId
where p.CRState = 9 AND (p.SaveDate BETWEEN @StarDate and @EndDate) AND p.FactoryId=@FAC and p.MaterialCode=@MC and c.IsFinancial=1 and c.DeleteMark=1
group by SaveDate,p.NetWeight,c.CustomerId
)
SELECT CustomerId,DayTime,NetWeight FROM tempb
--卡住的位置
PIVOT (sum(NetWeight) as NetWeight FOR DayTime in(这里不知道该如何去构造日期))
查询后得到的结果是
CustomerId NetWeight DayTime
XXXXXXXX 31 2019-02-25 08:15:12
XXXXXXXX 32 2019-02-25 09:45:21
XXXXXXXX 31.2 2019-02-26 10:14:17
XXXXXXXX 33.1 2019-02-26 13:51:11
XXXXXXXX 34.1 2019-02-27 16:43:08
BBBBBBBB 32 2019-02-25 09:45:21
BBBBBBBB 32 2019-02-25 09:45:21
XXXXXXXX 32 2019-02-28 09:45:21
CCCCCCC 32 2019-02-25 09:45:21
CCCCCCC 32 2019-02-25 09:45:21
DDDDDDD 32 2019-02-25 09:45:21
DDDDDDD 32 2019-02-25 09:45:21
BBBBBBB 32 2019-02-26 09:45:21
想要得到的结果是,那一天没有的用0替代
CustomerId 2019-02-25 2019-02-26 2019-02-27 2019-02-28..........2019-03-25
XXXXXXXX 63 64.3 34.1 32 0
BBBBBBBB 64 32 0 0 0
CCCCCCC 64 0 0 0 0
DDDDDDD 64 0 0 0 0
DECLARE @StarDate varchar(50),@EndDatevarchar(50)
DECLARE @MC varchar(6),@FAC int
set @StarDate='2019-02-25 08:00:00'
set @EndDate='2019-03-25 07:59:59'
set @MC='2000'
set @FAC=8
with tempb AS (
select convert(varchar(100),p.SaveDate,112) as DayTime ,isnull(sum(p.NetWeight/1000),0) as NetWeight,c.CustomerId
from Base_PoundRecord p
left join Base_Customers c on c.CustomerId=p.CustomerId
where p.CRState = 9 AND (p.SaveDate BETWEEN @StarDate and @EndDate) AND p.FactoryId=@FAC and p.MaterialCode=@MC and c.IsFinancial=1 and c.DeleteMark=1
group by SaveDate,p.NetWeight,c.CustomerId
)
SELECT CustomerId,DayTime,NetWeight FROM tempb
--卡住的位置
PIVOT (sum(NetWeight) as NetWeight FOR DayTime in(这里不知道该如何去构造日期))
查询后得到的结果是
CustomerId NetWeight DayTime
XXXXXXXX 31 2019-02-25 08:15:12
XXXXXXXX 32 2019-02-25 09:45:21
XXXXXXXX 31.2 2019-02-26 10:14:17
XXXXXXXX 33.1 2019-02-26 13:51:11
XXXXXXXX 34.1 2019-02-27 16:43:08
BBBBBBBB 32 2019-02-25 09:45:21
BBBBBBBB 32 2019-02-25 09:45:21
XXXXXXXX 32 2019-02-28 09:45:21
CCCCCCC 32 2019-02-25 09:45:21
CCCCCCC 32 2019-02-25 09:45:21
DDDDDDD 32 2019-02-25 09:45:21
DDDDDDD 32 2019-02-25 09:45:21
BBBBBBB 32 2019-02-26 09:45:21
想要得到的结果是,那一天没有的用0替代
CustomerId 2019-02-25 2019-02-26 2019-02-27 2019-02-28..........2019-03-25
XXXXXXXX 63 64.3 34.1 32 0
BBBBBBBB 64 32 0 0 0
CCCCCCC 64 0 0 0 0
DDDDDDD 64 0 0 0 0
