[Resolvido] Select Diario

Marcos.Ferreira · Maio 16, 2011

Boa tarde... estou com o seguinte problema, possuo uma tabela que armazena os eventos do usuário. um determinado evento faço um count, separando pelo periodo de acesso, café, almoço e janta. consigo trazer totalizado por usário, mas não consigo agrupar pelo usuário e totalizado pelo dia. segue exemplo de como preciso dos registros.

DIA        EMPRESA CAFÉ    ALMOÇO  JANTAR
01/13/2011  NULL   83879   122281   113747
01/14/2011  NULL   83879   122281   113747
01/15/2011  NULL   83879   122281   113747
01/18/2011  NULL   83879   122281   113747
01/19/2011  NULL   83879   122281   113747
01/20/2011  NULL   83879   122281   113747
01/21/2011  NULL   83879   122281   113747
01/22/2011  NULL   83879   122281   113747
01/25/2011  NULL   83879   122281   113747
01/26/2011  NULL   83879   122281   113747
01/31/2011  NULL   83879   122281   113747

abaixo minha consulta:

DECLARE @Inicio datetime,
@Fim datetime;

SET @Inicio = '01-01-2011'
SET @Fim = '14-01-2011'


SELECT CONVERT(varchar, EventDateTime, 101), CompanyName,

(SELECT Count(EventDateTime) FROM DB_Event..Events 
WHERE DB_Event..Events.EventID = 73
AND DATEADD(mi, GMTOFFSet, EventDateTime) BETWEEN @Inicio AND @Fim
AND (SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '05'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '06'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '07'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '08')) 
AS Cafe,

(SELECT Count(EventDateTime) FROM DB_Event..Events 
WHERE DB_Event..Events.EventID = 73
AND DATEADD(mi, GMTOFFSet, EventDateTime) BETWEEN @Inicio AND @Fim
AND (SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '10'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '11'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '12'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '13'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '14')) 
AS Almoco,

(SELECT Count(EventDateTime) FROM DB_Event..Events 
WHERE DB_Event..Events.EventID = 73
AND DATEADD(mi, GMTOFFSet, EventDateTime) BETWEEN @Inicio AND @Fim
AND (SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '18'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '19'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '20'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '21'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '22'
OR SUBSTRING(CONVERT(varchar(50), DATEADD(mi, GMTOFFSet, EventDateTime), 120),12,2) = '23')) 
AS Jantar


FROM DB_Evnt..Events
JOIN DB..CMain ON DB..CMain.CHID = Events.CHID
LEFT JOIN DB..CCompanies ON DB..CCompanies.CompanyID = DB..CMain.CompanyID
GROUP BY CONVERT(varchar, EventDateTime, 101), CompanyName
ORDER BY CompanyName, CONVERT(varchar, EventDateTime, 101)

Se alguem puder me ajudar agradeço.

supernoi · Maio 16, 2011

Group By não resolve não?

os usuários são 83879, 122281 e 113747?

Fiquei meio confuso do que voce quer responder com o select.

advaldomesquita · Maio 16, 2011

Group By não resolve não?

os usuários são 83879, 122281 e 113747?

Fiquei meio confuso do que voce quer responder com o select.

o que ele quer é esse resultado:

DIA        EMPRESA CAFÉ    ALMOÇO  JANTAR
01/13/2011  NULL   83879   122281   113747
01/14/2011  NULL   83879   122281   113747
01/15/2011  NULL   83879   122281   113747
01/18/2011  NULL   83879   122281   113747
01/19/2011  NULL   83879   122281   113747
01/20/2011  NULL   83879   122281   113747
01/21/2011  NULL   83879   122281   113747
01/22/2011  NULL   83879   122281   113747
01/25/2011  NULL   83879   122281   113747
01/26/2011  NULL   83879   122281   113747
01/31/2011  NULL   83879   122281   113747

:P

Marcos,

Qual a estrutura das tabelas envolvidas? Qual a versão do SQL?

E precisa msm ter tanto sub assim? pq nao usa um while com variaveis ou case?

Abçs

Marcos.Ferreira · Maio 16, 2011

Group By não resolve não?

os usuários são 83879, 122281 e 113747?

Fiquei meio confuso do que voce quer responder com o select.

o que ele quer é esse resultado:
DIA        EMPRESA CAFÉ    ALMOÇO  JANTAR
01/13/2011  NULL   83879   122281   113747
01/14/2011  NULL   83879   122281   113747
01/15/2011  NULL   83879   122281   113747
01/18/2011  NULL   83879   122281   113747
01/19/2011  NULL   83879   122281   113747
01/20/2011  NULL   83879   122281   113747
01/21/2011  NULL   83879   122281   113747
01/22/2011  NULL   83879   122281   113747
01/25/2011  NULL   83879   122281   113747
01/26/2011  NULL   83879   122281   113747
01/31/2011  NULL   83879   122281   113747
:P

Marcos,

Qual a estrutura das tabelas envolvidas? Qual a versão do SQL?

E precisa msm ter tanto sub assim? pq nao usa um while com variaveis ou case?

Abçs

a estrutura da consulta é esta apresentada, o primeiro dia que é mostrado a quantidade de cafés, almoços e jantas estão corretos, porem quando passa para o outro dia a contagem se repete. as subs foram a unica forma que encontrei que chegasse ao resultado. usando while e case não sei fazer.

na tabela uso apenas o ID da empresa pra contar, onde acesso tipo 73, e com os JOIN trago as outras informações, tipo, empresa X no dia 1 teve 83879 acessos no café, e no dia 2 deveria vir diferente, pois não foi a mesma quantia, e assim sucessivamente para almoço e janta.

supernoi · Maio 16, 2011

Isso não já resolve nao?

SELECT DIA, EMPRESA, SUM(CAFE), SUM(ALMOCO), SUM(JANTAR)

FROM DB_Event..Events

--WHERE <SUAS CONDICOES>

GROUP BY DIA, EMPRESA

Marcos.Ferreira · Maio 16, 2011

Isso não já resolve nao?

SELECT DIA, EMPRESA, SUM(CAFE), SUM(ALMOCO), SUM(JANTAR)

FROM DB_Event..Events

--WHERE <SUAS CONDICOES>

GROUP BY DIA, EMPRESA

supernoi, a questão não fica simples assim, não tenho a coluna cafe, almoço e janta, as subs na consulta é pra determinar a faixa de horarios. digamos no dia 1, tenho 50000 registros. Sendo café, almoço e janta, o que determina é a faixa de horario nas subs.

Motta · Maio 16, 2011

Desculpe se estou perguntando besteira mas não seria só incluir o usuário na query ?

Marcos.Ferreira · Maio 16, 2011

Desculpe se estou perguntando besteira mas não seria só incluir o usuário na query ?

Acho que me espressei mal, na questão de separar por empresa.. usuário ou afins isso consigo fazer, não consigo é separar os resultados dos respectivos dias. sem que haja repetição, como no exemplo que mostrei.

Motta · Maio 16, 2011

Você quer assim :

DIA        EMPRESA CAFÉ    ALMOÇO  JANTAR
01/13/2011  NULL   83879   122281   113747
01/14/2011  NULL   83879   122281   113747
01/15/2011  NULL   83879   122281   113747
01/18/2011  NULL   83879   122281   113747
01/19/2011  NULL   83879   122281   113747
01/20/2011  NULL   83879   122281   113747
01/21/2011  NULL   83879   122281   113747
01/22/2011  NULL   83879   122281   113747
01/25/2011  NULL   83879   122281   113747
01/26/2011  NULL   83879   122281   113747
01/31/2011  NULL   83879   122281   113747

Mas como eles saem ?

Marcos.Ferreira · Maio 17, 2011

Você quer assim :

DIA        EMPRESA CAFÉ    ALMOÇO  JANTAR
01/13/2011  NULL   83879   122281   113747
01/14/2011  NULL   83879   122281   113747
01/15/2011  NULL   83879   122281   113747
01/18/2011  NULL   83879   122281   113747
01/19/2011  NULL   83879   122281   113747
01/20/2011  NULL   83879   122281   113747
01/21/2011  NULL   83879   122281   113747
01/22/2011  NULL   83879   122281   113747
01/25/2011  NULL   83879   122281   113747
01/26/2011  NULL   83879   122281   113747
01/31/2011  NULL   83879   122281   113747

Mas como eles saem ?

a estrutura da tabela é a seguinte, onde faço count do CHID

ServerDateTime	        EventDateTime	        GMTOffset      EventType	EventHWID	ControllerID	ControllerIPAddress	ClearCode	CardNumber	FacilityCode	CHID	CHType	FirstName	LastName	IdNumber	ZoneID	
2010-11-27 16:33:14.663	2010-11-27 16:33:14.663	-240	       1	         10013  	0       	10.10.240.14   	         7035314|0	7035314	        0	        659	2	NULL    	NULL    	NULL    	NULL

Motta · Maio 17, 2011

Na realidade eu gostaria de ver o que está saindo do sql, fica mais fácil ver a solução.

Marcos.Ferreira · Maio 17, 2011

Na realidade eu gostaria de ver o que está saindo do sql, fica mais fácil ver a solução.

exatamente o que postei.

DIA        EMPRESA CAFÉ    ALMOÇO  JANTAR
01/13/2011  NULL   83879   122281   113747
01/14/2011  NULL   83879   122281   113747
01/15/2011  NULL   83879   122281   113747
01/18/2011  NULL   83879   122281   113747
01/19/2011  NULL   83879   122281   113747
01/20/2011  NULL   83879   122281   113747
01/21/2011  NULL   83879   122281   113747
01/22/2011  NULL   83879   122281   113747
01/25/2011  NULL   83879   122281   113747
01/26/2011  NULL   83879   122281   113747
01/31/2011  NULL   83879   122281   113747

Motta · Maio 17, 2011

Desculpe-me, não estou entendendo pois pelo post #1 entendi que desta forma era como você precisava.

Marcos.Ferreira · Maio 17, 2011

Desculpe-me, não estou entendendo pois pelo post #1 entendi que desta forma era como você precisava.

se puder reparar no resultado da consulta, a primeira linha

01/13/2011  NULL   83879   122281   113747

e na segunda linha os valores se repetem, isso não deve ocorrer, pois seguramente a quantidade de registros é diferente.

supernoi · Maio 17, 2011

tenta ae, só aptar para o seu caso.

Ve se atende a sua necessidade.

select data, empresa,

count(case when CONVERT(char(2), hora, 8) between 5 and 8 then empresa end) as cafe,

count(case when CONVERT(char(2), hora, 8) between 10 and 14 then empresa end) as almoco,

count(case when CONVERT(char(2), hora, 8) between 18 and 23 then empresa end) as jantar

from teste01

where

CONVERT(char(2), hora, 8) between 05 and 08

or CONVERT(char(2), hora, 8) between 10 and 14

or CONVERT(char(2), hora, 8) between 18 and 23

group by data, empresa

advaldomesquita · Maio 17, 2011

você esta agrupando por data

CONVERT(varchar, EventDateTime, 101),

por isso esta se repetindo.

Voce precisa separar por datas?

Marcos.Ferreira · Maio 17, 2011

você esta agrupando por data

CONVERT(varchar, EventDateTime, 101),

por isso esta se repetindo.

Voce precisa separar por datas?

Isso mesmo A.Jr.

tenta ae, só aptar para o seu caso.

Ve se atende a sua necessidade.

select data, empresa,

count(case when CONVERT(char(2), hora, 8) between 5 and 8 then empresa end) as cafe,

count(case when CONVERT(char(2), hora, 8) between 10 and 14 then empresa end) as almoco,

count(case when CONVERT(char(2), hora, 8) between 18 and 23 then empresa end) as jantar

from teste01

where

CONVERT(char(2), hora, 8) between 05 and 08

or CONVERT(char(2), hora, 8) between 10 and 14

or CONVERT(char(2), hora, 8) between 18 and 23

group by data, empresa

O resultado saiu da seguinte forma. Não é bem isso que deveria sair.. os quantitativos são muito maiores.

Data      	CompanyID	cafe	almoco	jantar
2005-01-23	69      	0	1	0
2005-01-23	69      	0	1	0
2005-01-23	69      	0	1	0
2006-09-09	264     	0	1	0
2006-09-09	264     	0	1	0
2006-09-09	264     	0	1	0
2006-09-09	69      	0	1	0
2006-09-09	264     	0	1	0

Motta · Maio 17, 2011

Em Oracle seria algo assim, creio eu.

select to_char(EventDateTime,'dd/mm/yyyy') data,	CompanyName,
   sum(case when to_number(to_char(EventDateTime,'hh')) between 5 and 8 then 1 else 0 end) cafe,
   sum(case when to_number(to_char(EventDateTime,'hh')) between 10 and 14 then 1 else 0 end) almoco,
   sum(case when to_number(to_char(EventDateTime,'hh')) between 18 and 23 then 1 else 0 end) jantar
from DB_Evnt..Events
JOIN DB..CMain ON DB..CMain.CHID = Events.CHID
LEFT JOIN DB..CCompanies ON DB..CCompanies.CompanyID = DB..CMain.CompanyID
group by to_char(EventDateTime,'dd/mm/yyyy'),CompanyName

Falta só traduzir os to_char e to_number.

supernoi · Maio 17, 2011

Cola ae como ficou quando voce inseriu o restante dos parâmetros...

advaldomesquita · Maio 17, 2011

@Motta, se traduzir, o resultado ia ser o mesmo do postado pelo @supernoi.

Esta faltando algum pametro que o @marcos esta esquecendo.... falta algo na estrutura... é o que parece....

Arquivado

[Resolvido] Select Diario

Recommended Posts

Marcos.Ferreira 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

supernoi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

Marcos.Ferreira 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

supernoi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Marcos.Ferreira 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

Marcos.Ferreira 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

Marcos.Ferreira 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

Marcos.Ferreira 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

Marcos.Ferreira 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

supernoi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93

Compartilhar este post

Link para o post

Compartilhar em outros sites

Marcos.Ferreira 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

supernoi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

advaldomesquita 93