Return the shortest date within Select

Question

I have the following table:

CREATE TABLE [dbo].[Crm_man](
[id] [int] IDENTITY(1,1) NOT NULL,
[nCRM] [int] NULL,
[cliente_CRM] [int] NULL,
[item_CRM] [int] NULL,
[qnt_CRM] [int] NULL,
[descri_CRM] [varchar](max) NULL,
[descri_NF] [varchar](max) NULL,
[of_CRM] [varchar](50) NULL,
[cod_item_CRM] [varchar](100) NULL,
[obs] [varchar](max) NULL,
[bloq] [int] NULL,
[data] [varchar](max) NULL,
[id_motivo] [varchar](10) NULL,
[id_dev] [int] NULL,
[unid] [varchar](10) NULL,
[statos] [int] NULL,
[cliente_CRM_new] [varchar](400) NULL,
(

And contain the following data:

SET IDENTITY_INSERT [dbo].[Crm_man] ON 
INSERT [dbo].[Crm_man] ([id], [nCRM], [cliente_CRM], [item_CRM], [qnt_CRM], [descri_CRM], [descri_NF], [of_CRM], [cod_item_CRM], [obs], [bloq], [data], [id_motivo], [id_dev], [unid], [statos], [cliente_CRM_new]) VALUES (26, 5668, 855, 1, 1, N'REC. SELO TIPO 209 Ø130MM LRFR2C8', N'', N'', N'5668/1_1', N'', 0, N'12/05/2017 10:55:11', N'6.0.0', 0, N'1', 1, N'')
INSERT [dbo].[Crm_man] ([id], [nCRM], [cliente_CRM], [item_CRM], [qnt_CRM], [descri_CRM], [descri_NF], [of_CRM], [cod_item_CRM], [obs], [bloq], [data], [id_motivo], [id_dev], [unid], [statos], [cliente_CRM_new]) VALUES (27, 5668, 855, 2, 1, N'REC. SELO TIPO 209 Ø110MM LRFR2C8', N'', N'', N'5668/2_1', N'', 0, N'12/05/2017 10:55:11', N'6.0.0', 0, N'1', 1, N'')
INSERT [dbo].[Crm_man] ([id], [nCRM], [cliente_CRM], [item_CRM], [qnt_CRM], [descri_CRM], [descri_NF], [of_CRM], [cod_item_CRM], [obs], [bloq], [data], [id_motivo], [id_dev], [unid], [statos], [cliente_CRM_new]) VALUES (28, 5668, 855, 3, 1, N'REC. SELO TIPO 209 Ø90MM LRFR2C8', N'', N'', N'5668/3_1', N'', 0, N'12/05/2017 10:55:11', N'6.0.0', 0, N'1', 1, N'')
INSERT [dbo].[Crm_man] ([id], [nCRM], [cliente_CRM], [item_CRM], [qnt_CRM], [descri_CRM], [descri_NF], [of_CRM], [cod_item_CRM], [obs], [bloq], [data], [id_motivo], [id_dev], [unid], [statos], [cliente_CRM_new]) VALUES (29, 5668, 855, 4, 1, N'REC. SELO TIPO 215 Ø30MM LRFR2C8/R2C8', N'', N'', N'5668/4_1', N'', 0, N'12/05/2017 10:55:11', N'6.0.0', 0, N'1', 1, N'')
INSERT [dbo].[Crm_man] ([id], [nCRM], [cliente_CRM], [item_CRM], [qnt_CRM], [descri_CRM], [descri_NF], [of_CRM], [cod_item_CRM], [obs], [bloq], [data], [id_motivo], [id_dev], [unid], [statos], [cliente_CRM_new]) VALUES (31, 5669, 55, 1, 1, N'teste', N'', N'', N'5669/1_1', N'teste thomas', 0, N'12/05/2017 12:42:59', N'7.1.0', 0, N'1', 1, N'')
INSERT [dbo].[Crm_man] ([id], [nCRM], [cliente_CRM], [item_CRM], [qnt_CRM], [descri_CRM], [descri_NF], [of_CRM], [cod_item_CRM], [obs], [bloq], [data], [id_motivo], [id_dev], [unid], [statos], [cliente_CRM_new]) VALUES (32, 5669, 55, 2, 1, N'teste 12', N'', N'', N'5669/2_1', N'teste thomas', 0, N'12/05/2017 12:48:14', N'7.1.0', 0, N'1', 1, N'')
SET IDENTITY_INSERT [dbo].[Crm_man] OFF

I want to run the script below:

select a.ncrm AS 'Nº CRM', c.descricao 'STATUS', 
case 
when a.cliente_CRM = 0
then a.cliente_CRM_new
else b.nome_fantasia
end AS CLIENTE,
d.motivo 'MOTIVO', a.data AS 'RECEBIMENTO', a.bloq
from crm_man as a
left join Cliente_man as b
on a.cliente_CRM = b.id_cliente
left join Crm_statos as c
on a.statos = c.id
left join Motivo_crm as d
on a.id_motivo = d.id_comp
group by a.ncrm, b.nome_fantasia, c.descricao, d.motivo, a.data, a.bloq, a.cliente_CRM_new, a.cliente_CRM

As you can see, I’m wearing Group By, then, because of the column data, I can’t group the nCRM = 5669.

So for that, I thought I’d show it in the column data, always the oldest date ref. to each nCRM, so I thought I’d use the following script:

declare @ncrm int;
set @ncrm = ?????

select  a.ncrm AS 'Nº CRM', c.descricao 'STATUS', 
case 
when a.cliente_CRM = 0
then a.cliente_CRM_new
else b.nome_fantasia
end AS CLIENTE,
d.motivo 'MOTIVO', 

(select min(data) from crm_man where nCRM = @ncrm) as Rec_,

a.data AS 'RECEBIMENTO', 
a.bloq
from crm_man as a
left join Cliente_man as b
on a.cliente_CRM = b.id_cliente
left join Crm_statos as c
on a.statos = c.id
left join Motivo_crm as d
on a.id_motivo = d.id_comp
group by a.ncrm, b.nome_fantasia, c.descricao, d.motivo, a.data, a.bloq, a.cliente_CRM_new, a.cliente_CRM

However, how do I define the variable @ncrm for each line of the consultation SELECT?

Answer 1

You can simply change your GROUP BY, removing the column data, and change the SELECT clause by applying the MIN aggregation function. This will ensure that for each nCRM only the older date will be returned.

SELECT a.ncrm 'Nº CRM', 
       c.descricao 'STATUS', 
       CASE  
          WHEN a.cliente_CRM = 0 THEN a.cliente_CRM_new
          ELSE b.nome_fantasia
       END CLIENTE,
       d.motivo 'MOTIVO', 
       MIN(a.data) 'RECEBIMENTO', 
       a.bloq
  FROM crm_man a
  LEFT JOIN Cliente_man b
     ON a.cliente_CRM = b.id_cliente
  LEFT JOIN Crm_statos as c
     ON a.statos = c.id
  LEFT JOIN Motivo_crm as d
     ON a.id_motivo = d.id_comp
  GROUP BY a.ncrm, b.nome_fantasia, c.descricao, d.motivo, a.bloq, a.cliente_CRM_new, a.cliente_CRM

Answer 2

If what you want is to return the lines to Each nCRM with the lowest date, follow a query example

select a.*
  from [Crm_man] a
  join (select x.nCrm, Min(x.Data) as data
          from [Crm_man] x
         group by x.nCrm) b on a.data = b.data
                           and a.nCrm = b.nCrm

I made a subconsultation to group with nCRM and catch the smallest date, you can add the Join’s or Left Join’s and customize the query.