我已经创建了类似的东西,但是我得到了一个错误。
create view _incident_tickets(ddate datetime, dgroup varchar(50))
Select
--incident.actie,
incident.dataanmk as "Aanmaak Datum",
incident.ref_operatorgroup as "Behandelaarsgroep",
incident.ref_status as "Status",
incident.datumaangemeld as "Datum aanmelding"
INTO _incident_tickets
From incident
WHERE incident.dataanmk >= DATEADD(day,-7, GETDATE())
AND incident.ref_operatorgroup in ('Automatisering','Informatisering')
order by incident.dataanmk desc
Select ddate1,[00:00],[01:00],[02:00],[03:00],[04:00],[05:00],[06:00],[07:00],[08:00],[09:00],[10:00],[11:00],[12:00],
[13:00],[14:00],[15:00],[16:00],[17:00],[18:00],[19:00],[20:00],[21:00],[22:00],[23:00]
From
(Select dgroup 'dgroup',cast(Convert(varchar(10),ddate,101)+' '+ convert(varchar(3),ddate,108) +'00' as date) ddate1,convert(varchar(3),ddate,108) +'00' dtime
From _incident_tickets)p
PIVOT
(Count(dgroup) for dtime in([00:00],[01:00],[02:00],[03:00],[04:00],[05:00],[06:00],[07:00],[08:00],[09:00],[10:00],[11:00],[12:00],
[13:00],[14:00],[15:00],[16:00],[17:00],[18:00],[19:00],[20:00],[21:00],[22:00],[23:00])
)
PVT
Drop view _incident_tickets
但我会遇到一个错误:" dateTime"附近的语法不正确。我的查询有什么问题,任何人都可以帮助我解决此查询。
只是添加到 @prfctbydesigns的答案:
- 视图无法更新数据,也无法发出诸如
DROP VIEW
之类的DDL命令 - SQLServer中的变量必须在
@
之前。 -
CREATE VIEW
(和CREATE PROC
)需要使用AS
关键字
引入 - 您不能
SELECT INTO
与现有对象相同的对象(viz_incident_tickets
)
同意 @prfctbydsgn的假设,即由于其参数,该查询应该是存储的proc或表值函数,但我怀疑我仍然有一些工作可以做/debug:
CREATE PROCEDURE _incident_tickets (@ddate datetime, @dgroup varchar(50))
AS
WITH cte_incident_tickets AS
(
SELECT
--incident.actie,
incident.dataanmk as "Aanmaak Datum",
incident.ref_operatorgroup as "Behandelaarsgroep",
incident.ref_status as "Status",
incident.datumaangemeld as "Datum aanmelding"
From incident
WHERE incident.dataanmk >= DATEADD(day,-7, GETDATE())
AND incident.ref_operatorgroup in ('Automatisering','Informatisering')
)
Select ddate1,[00:00],[01:00],[02:00],[03:00],[04:00],[05:00],[06:00],[07:00],[08:00],[09:00],[10:00],[11:00],[12:00],
[13:00],[14:00],[15:00],[16:00],[17:00],[18:00],[19:00],[20:00],[21:00],[22:00],[23:00]
From
(Select
@dgroup 'dgroup',
cast(Convert(varchar(10), @ddate,101)+' '+ convert(varchar(3), @ddate,108) +'00' as date) ddate1,
convert(varchar(3), @ddate,108) +'00' dtime
From cte_incident_tickets
) p
PIVOT
(Count(dgroup) for dtime in([00:00],[01:00],[02:00],[03:00],[04:00],[05:00],[06:00],[07:00],[08:00],[09:00],[10:00],[11:00],[12:00],
[13:00],[14:00],[15:00],[16:00],[17:00],[18:00],[19:00],[20:00],[21:00],[22:00],[23:00])
)
PVT
您无法使用参数创建视图...如果您需要参数,则应创建一个存储过程