>>Smokin' !
>>
>>In real life my table has lots of records so I need to filter the select with some parameters in a WHERE clause. I tried this and it works great.
>>
>>FROM cte WHERE (LocID='N-18' AND Classification = 2)
>>but this does not
>>
>>FROM cte WHERE (LocID=@LocID AND Classification = @Class)
>>
>>I note that you used a parameter for @Today. So why does mine not work and yours does?
>
>In the view you can not use parameters, it will need to be a table defined function then. However, I see no reason of this code not working with parameters while working with values directly. If you post your code and structure of your real table I will probably be able to tell you more.
Sure thing. Here is my real table.
USE [TrafficCounts]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClassCounts](
[KeyID] [int] IDENTITY(1,1) NOT NULL,
[LocID] [varchar](50) NOT NULL,
[CountDate] [date] NOT NULL,
[Classification] [int] NOT NULL,
[P1] [int] NOT NULL,
[P2] [int] NOT NULL,
[P3] [int] NOT NULL,
[P4] [int] NOT NULL,
[P5] [int] NOT NULL,
[P6] [int] NOT NULL,
[P7] [int] NOT NULL,
[P8] [int] NOT NULL,
[P9] [int] NOT NULL,
[P10] [int] NOT NULL,
[P11] [int] NOT NULL,
[P12] [int] NOT NULL,
[P13] [int] NOT NULL,
[P14] [int] NOT NULL,
[P15] [int] NOT NULL,
[P16] [int] NOT NULL,
[P17] [int] NOT NULL,
[P18] [int] NOT NULL,
[P19] [int] NOT NULL,
[P20] [int] NOT NULL,
[P21] [int] NOT NULL,
[P22] [int] NOT NULL,
[P23] [int] NOT NULL,
[P24] [int] NOT NULL,
[P25] [int] NOT NULL,
[P26] [int] NOT NULL,
[P27] [int] NOT NULL,
[P28] [int] NOT NULL,
[P29] [int] NOT NULL,
[P30] [int] NOT NULL,
[P31] [int] NOT NULL,
[P32] [int] NOT NULL,
[P33] [int] NOT NULL,
[P34] [int] NOT NULL,
[P35] [int] NOT NULL,
[P36] [int] NOT NULL,
[P37] [int] NOT NULL,
[P38] [int] NOT NULL,
[P39] [int] NOT NULL,
[P40] [int] NOT NULL,
[P41] [int] NOT NULL,
[P42] [int] NOT NULL,
[P43] [int] NOT NULL,
[P44] [int] NOT NULL,
[P45] [int] NOT NULL,
[P46] [int] NOT NULL,
[P47] [int] NOT NULL,
[P48] [int] NOT NULL,
[P49] [int] NOT NULL,
[P50] [int] NOT NULL,
[P51] [int] NOT NULL,
[P52] [int] NOT NULL,
[P53] [int] NOT NULL,
[P54] [int] NOT NULL,
[P55] [int] NOT NULL,
[P56] [int] NOT NULL,
[P57] [int] NOT NULL,
[P58] [int] NOT NULL,
[P59] [int] NOT NULL,
[P60] [int] NOT NULL,
[P61] [int] NOT NULL,
[P62] [int] NOT NULL,
[P63] [int] NOT NULL,
[P64] [int] NOT NULL,
[P65] [int] NOT NULL,
[P66] [int] NOT NULL,
[P67] [int] NOT NULL,
[P68] [int] NOT NULL,
[P69] [int] NOT NULL,
[P70] [int] NOT NULL,
[P71] [int] NOT NULL,
[P72] [int] NOT NULL,
[P73] [int] NOT NULL,
[P74] [int] NOT NULL,
[P75] [int] NOT NULL,
[P76] [int] NOT NULL,
[P77] [int] NOT NULL,
[P78] [int] NOT NULL,
[P79] [int] NOT NULL,
[P80] [int] NOT NULL,
[P81] [int] NOT NULL,
[P82] [int] NOT NULL,
[P83] [int] NOT NULL,
[P84] [int] NOT NULL,
[P85] [int] NOT NULL,
[P86] [int] NOT NULL,
[P87] [int] NOT NULL,
[P88] [int] NOT NULL,
[P89] [int] NOT NULL,
[P90] [int] NOT NULL,
[P91] [int] NOT NULL,
[P92] [int] NOT NULL,
[P93] [int] NOT NULL,
[P94] [int] NOT NULL,
[P95] [int] NOT NULL,
[P96] [int] NOT NULL,
[P101] [int] NOT NULL,
[P102] [int] NOT NULL,
[P103] [int] NOT NULL,
[P104] [int] NOT NULL,
[P105] [int] NOT NULL,
[P106] [int] NOT NULL,
[P107] [int] NOT NULL,
[P108] [int] NOT NULL,
[P109] [int] NOT NULL,
[P110] [int] NOT NULL,
[P111] [int] NOT NULL,
[P112] [int] NOT NULL,
[P113] [int] NOT NULL,
[P114] [int] NOT NULL,
[P115] [int] NOT NULL,
[P116] [int] NOT NULL,
[P117] [int] NOT NULL,
[P118] [int] NOT NULL,
[P119] [int] NOT NULL,
[P120] [int] NOT NULL,
[P121] [int] NOT NULL,
[P122] [int] NOT NULL,
[P123] [int] NOT NULL,
[P124] [int] NOT NULL,
[P125] [int] NOT NULL,
[P126] [int] NOT NULL,
[P127] [int] NOT NULL,
[P128] [int] NOT NULL,
[P129] [int] NOT NULL,
[P130] [int] NOT NULL,
[P131] [int] NOT NULL,
[P132] [int] NOT NULL,
[P133] [int] NOT NULL,
[P134] [int] NOT NULL,
[P135] [int] NOT NULL,
[P136] [int] NOT NULL,
[P137] [int] NOT NULL,
[P138] [int] NOT NULL,
[P139] [int] NOT NULL,
[P140] [int] NOT NULL,
[P141] [int] NOT NULL,
[P142] [int] NOT NULL,
[P143] [int] NOT NULL,
[P144] [int] NOT NULL,
[P145] [int] NOT NULL,
[P146] [int] NOT NULL,
[P147] [int] NOT NULL,
[P148] [int] NOT NULL,
[P149] [int] NOT NULL,
[P150] [int] NOT NULL,
[P151] [int] NOT NULL,
[P152] [int] NOT NULL,
[P153] [int] NOT NULL,
[P154] [int] NOT NULL,
[P155] [int] NOT NULL,
[P156] [int] NOT NULL,
[P157] [int] NOT NULL,
[P158] [int] NOT NULL,
[P159] [int] NOT NULL,
[P160] [int] NOT NULL,
[P161] [int] NOT NULL,
[P162] [int] NOT NULL,
[P163] [int] NOT NULL,
[P164] [int] NOT NULL,
[P165] [int] NOT NULL,
[P166] [int] NOT NULL,
[P167] [int] NOT NULL,
[P168] [int] NOT NULL,
[P169] [int] NOT NULL,
[P170] [int] NOT NULL,
[P171] [int] NOT NULL,
[P172] [int] NOT NULL,
[P173] [int] NOT NULL,
[P174] [int] NOT NULL,
[P175] [int] NOT NULL,
[P176] [int] NOT NULL,
[P177] [int] NOT NULL,
[P178] [int] NOT NULL,
[P179] [int] NOT NULL,
[P180] [int] NOT NULL,
[P181] [int] NOT NULL,
[P182] [int] NOT NULL,
[P183] [int] NOT NULL,
[P184] [int] NOT NULL,
[P185] [int] NOT NULL,
[P186] [int] NOT NULL,
[P187] [int] NOT NULL,
[P188] [int] NOT NULL,
[P189] [int] NOT NULL,
[P190] [int] NOT NULL,
[P191] [int] NOT NULL,
[P192] [int] NOT NULL,
[P193] [int] NOT NULL,
[P194] [int] NOT NULL,
[P195] [int] NOT NULL,
[P196] [int] NOT NULL,
[Total] [int] NOT NULL,
[SourceFile] [varchar](100) NULL,
[count_id] [int] NULL,
[counted_by] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
And here is your code after I modified it.
DECLARE @LocID char = 'N-18'
DECLARE @Class integer = 2
DECLARE @Today DATETIME = cast(CURRENT_TIMESTAMP AS DATE);;
WITH cte
AS (
SELECT *
FROM ClassCounts
UNPIVOT(TrafficCount FOR ColumnName IN (
[P1],[P2],[P3],[P4],[P5],[P6],[P7],[P8],[P9],[P10],
[P11],[P12],
[P13],[P14],[P15],[P16],[P17],[P18],[P19],[P20],
[P21],[P22],[P23],[P24],[P25],[P26],[P27],[P28],[P29],[P30],
[P31],[P32],[P33],[P34],[P35],[P36],[P37],[P38],[P39],[P40],
[P41],[P42],[P43],[P44],[P45],[P46],[P47],[P48],[P49],[P50],
[P51],[P52],[P53],[P54],[P55],[P56],[P57],[P58],[P59],[P60],
[P61],[P62],[P63],[P64],[P65],[P66],[P67],[P68],[P69],[P70],
[P71],[P72],[P73],[P74],[P75],[P76],[P77],[P78],[P79],[P80],
[P81],[P82],[P83],[P84],[P85],[P86],[P87],[P88],[P89],[P90],
[P91],[P92],[P93],[P94],[P95],[P96]
)) unpvt
)
,cte2
AS (
SELECT *
,(CAST(SUBSTRING(ColumnName, 2, LEN(ColumnName)) AS INT) - 1) / 4 + 1 AS [Hour]
,(CAST(SUBSTRING(ColumnName, 2, LEN(ColumnName)) AS INT) - 1) % 4 * 15 AS [Minutes]
FROM cte WHERE (LocID=@LocID AND Classification = @Class)
)
,cte3
AS (
SELECT TrafficCount
,dateadd(minute, [Minutes], dateadd(hour, [hour] - 1, @Today)) AS StartTime
FROM cte2
)
SELECT TOP (1)
WITH TIES TrafficCount AS IntervalStartCount
,TotalCount
,StartTime
,EndTime
FROM cte3 c
CROSS APPLY (
SELECT SUM(TrafficCount) AS TotalCount
,DATEADD(minute, 15, MAX(c2.StartTime)) AS EndTime
FROM cte3 c2
WHERE c2.StartTime >= c.StartTime
AND c2.StartTime < dateadd(hour, 1, c.StartTime)
) X
ORDER BY TotalCount DESC;
It runs OK but produces no result (empty table). Whereas if I use the absolute values instead of the parameters, I get a good result.
