Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Group by time interval again
Message
 
 
À
31/01/2003 07:49:13
Anderson Girardi
Athenas Automação de Escritório
Porto Alegre, Brésil
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
00747602
Message ID:
00747634
Vues:
18
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--
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform