Agrupar Datas Iguais Na Mesma Linha

[fabithomazelli]

Pessoal preciso agrupar datas em uma mesma linha vejam o select abaixo e a estrutura da tabela pra quem quiser testar ai:

Eu preciso na mesma linha conter o seguinte exemplo

Select convert(datetime, cast(clie_data_inclusao as nvarchar(11))) as data, 
(select count(distinct CLIE_PK_ID) where clie_mora_proximo = 1) as ComCupom, 
(select count(distinct CLIE_PK_ID) where clie_mora_proximo = 0) as SemCupom 
from clientes group by convert(datetime,cast(clie_data_inclusao as nvarchar(11))), clie_mora_proximo 
order by convert(datetime,cast(clie_data_inclusao as nvarchar(11)))desc, clie_mora_proximo

ele me apresenta o seguinte resultado:

data ComCupom SemCupom ------------------------------------------------------ ----------- ----------- ----------- 2007-07-24 00:00:00.000 1 NULL 1 2007-07-19 00:00:00.000 NULL 1 1 2007-07-19 00:00:00.000 3 NULL 3 2007-07-18 00:00:00.000 NULL 1 1 2007-07-18 00:00:00.000 1 NULL 1 2007-07-16 00:00:00.000 NULL 1 1

e eu gostaria que apresentasse:

data ComCupom SemCupom ------------------------------------------------------ ----------- ----------- ----------- 2007-07-24 00:00:00.000 1 0 1 2007-07-19 00:00:00.000 3 1 4 2007-07-18 00:00:00.000 1 1 2 2007-07-16 00:00:00.000 0 1 1

Script da tabela

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ACOMPANHAMENTO_CLIENTES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ACOMPANHAMENTO] DROP CONSTRAINT FK_ACOMPANHAMENTO_CLIENTES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CARRINHO_CLIENTES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CARRINHO] DROP CONSTRAINT FK_CARRINHO_CLIENTES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CLIENTES_CARTAO_FIDELIDADE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CARTAO_FIDELIDADE] DROP CONSTRAINT FK_CLIENTES_CARTAO_FIDELIDADE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CLIENTES_POR_LISTA_CLIENTES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CLIENTES_POR_LISTA] DROP CONSTRAINT FK_CLIENTES_POR_LISTA_CLIENTES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CLIENTES_DEPENDENTES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DEPENDENTES] DROP CONSTRAINT FK_CLIENTES_DEPENDENTES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CLIENTES_HISTORICO]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[HISTORICO] DROP CONSTRAINT FK_CLIENTES_HISTORICO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MOVIMENTO_VIVO_ABC_CLIENTES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MOVIMENTO_VIVO_ABC] DROP CONSTRAINT FK_MOVIMENTO_VIVO_ABC_CLIENTES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_NATAL_SAIDA_CLIENTES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[NATAL_SAIDA] DROP CONSTRAINT FK_NATAL_SAIDA_CLIENTES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_NATAL_SALDO_CLIENTES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[NATAL_SALDO] DROP CONSTRAINT FK_NATAL_SALDO_CLIENTES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_NOTAS_FISCAL_CLIENTES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[NOTAS_FISCAL] DROP CONSTRAINT FK_NOTAS_FISCAL_CLIENTES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CLIENTES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CLIENTES]
GO

CREATE TABLE [dbo].[CLIENTES] (
	[CLIE_PK_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[CLIE_PESSOA] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_IX_NOME_FANTASIA] [nvarchar] (45) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_RAZAO_SOCIAL] [nvarchar] (45) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_IX_CNPJ] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_IE] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_IX_NOME] [nvarchar] (60) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_SEXO] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_ESCOLARIDADE] [nvarchar] (45) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_ESTADO_CIVIL] [nvarchar] (45) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_IX_CPF] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_IX_RG] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_DATA_NASCIMENTO] [datetime] NULL ,
	[CLIE_ENDERECO] [nvarchar] (70) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_COMPLEMENTO] [nvarchar] (45) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_APARTAMENTO] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_BLOCO] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_NUMERO] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_BAIRRO] [nvarchar] (45) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_BAIRRO_INFORMADO] [nvarchar] (45) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_CIDADE] [nvarchar] (45) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_UF] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_CEP] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_DDD] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_TELEFONE_1] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_TELEFONE_2] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_TELEFONE_3] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_CELULAR] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_IX_EMAIL] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_PASSWORD] [nvarchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[CLIE_INFORMACAO] [bit] NOT NULL ,
	[CLIE_DATA_INCLUSAO] [datetime] NOT NULL ,
	[CLIE_DATA_ALTERACAO] [datetime] NULL ,
	[CLIE_FK_PROF_ID] [int] NULL ,
	[CLIE_ATIVO] [bit] NOT NULL ,
	[CLIE_BONUS] [float] NULL ,
	[CLIE_TRABALHA_PROXIMO] [bit] NOT NULL ,
	[CLIE_MORA_PROXIMO] [bit] NOT NULL 
) ON [PRIMARY]
GO

[Guest Paulo Andrade]

fabithomazelli, beleza.

Em seu group by

group by convert(datetime,cast(clie_data_inclusao as nvarchar(11))), clie_mora_proximo

troca por group by(data).

falou.