Agrupar datas iguais na mesma linha

[fabithomazelli 0]

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 exemploSelect 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 12007-07-19 00:00:00.000 NULL 1 12007-07-19 00:00:00.000 3 NULL 32007-07-18 00:00:00.000 NULL 1 12007-07-18 00:00:00.000 1 NULL 12007-07-16 00:00:00.000 NULL 1 1e eu gostaria que apresentasse:data ComCupom SemCupom ------------------------------------------------------ ----------- ----------- ----------- 2007-07-24 00:00:00.000 1 0 12007-07-19 00:00:00.000 3 1 42007-07-18 00:00:00.000 1 1 22007-07-16 00:00:00.000 0 1 1Script da tabelaif 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_CLIENTESGOif 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_CLIENTESGOif 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_FIDELIDADEGOif 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_CLIENTESGOif 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_DEPENDENTESGOif 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_HISTORICOGOif 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_CLIENTESGOif 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_CLIENTESGOif 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_CLIENTESGOif 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_CLIENTESGOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CLIENTES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[CLIENTES]GOCREATE 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

[eriva_br 7]

tenta colocar o comando ISNULL nos campos que estão retornando null para retornar 0 (zero) e o DISTINCT na consulta principal

 

t+