Return the shortest date within the Select

Question

Return the shortest date within the Select

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

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 I have 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 using Group By , so, because of the data column, I can not cluster the nCRM = 5669.

So, for this, I thought of displaying in the column data , always the oldest date ref. every 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 set the variable @ncrm to each line of the query SELECT ?

sql-server query select

asked by anonymous 12.05.2017 / 18:59

2 answers

0

If you want to return the rows to each nCRM with the shortest 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 subquery to group with nCRM and get the shortest date, you can add Join's or Left Join's and customize the query.

12.05.2017 / 20:07

Recreate HTML elements from one Div to another Div by ClassName ASP.NET - Access DropDownList item attribute in Code Behind

score 1 · Accepted Answer

You can simply change your GROUP BY by removing the data column, and change the SELECT clause by applying the MIN aggregation function. This will ensure that for each nCRM only the oldest 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