Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Group by time interval again
Message
 
 
To
31/01/2003 07:49:13
Anderson Girardi
Athenas Automação de Escritório
Porto Alegre, Brazil
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
00747602
Message ID:
00747634
Views:
17
Try
SELECT * FROM dt01
  (SELECT 
	LEFT(CONVERT(char(8), fl,datetime,108),3) + 
		CASE 
			WHEN RIGHT(CONVERT(char(8), fl,datetime,108),5) > '45:00' THEN '45'
			WHEN RIGHT(CONVERT(char(8), fl,datetime,108),5) > '30:00' THEN '30'
			WHEN RIGHT(CONVERT(char(8), fl,datetime,108),5) > '45:00' THEN '15'
			ELSE '00'
		END AS period15
		cp.name, SUM(fl.quantity) quantity
	FROM fluxo fl 
		JOIN ClientesPortas cp 
			ON  cp.idCustomer = dl.idCustomer AND cp.idDoor = fl.idDoor 
	WHERE cp.idCustomer = 1 AND
		fl.datetime >= '1/1/2003' AND fl.datetime < '1/2/2003' 
  ) dt01
  GROUP BY period15, name
  ORDER BY period15, name
First, time is converted in 15 minutes intervals and after that grouping is done on them. To avoid repeating expression for period15 column derived table dt01 is used. It assumes that either data is selected for one day or date itseld is irrelevant.
BTW, it's not good idea to use reserved words, as 'datetime', as column names in your tables.


>Hello Dear friends,
>
>i have two tables with the folow structure,
>
>Fluxo(idCostumer, idDoor, dateTime, quantity)
>clientesPortas(idCustomer, idDoor, name)
>
>I need to create a SQL Statement to Sum the field quantity group by each 15 minutes?
>
>i´m trying to do this
>
>SELECT CONVERT(char(5), (fluxo.dateTime),108) AS Time, clientesPortas.name, SUM(fluxo.quantity) quantity, FLUXO.dateTime
> FROM CLIENTESPORTAS INNER JOIN FLUXO ON CLIENTESPORTAS.idDoor = FLUXO.idDoor
> WHERE CLIENTESPORTAS.idCustomer = 1 AND
> fluxo.dateTime > '1/1/2003' AND
> fluxo.dateTime < '1/2/2003'
> GROUP BY CONVERT(char(5), (fluxo.dateTime),108), clientesPortas.name, FLUXO.dateTime
> ORDER BY time, clientesPortas.name
>
>
>But the result hide some values.
>
>
>Someone can help me?
>
>Thanks
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform