February 7, 2015 at 9:46 am
Hi,
Below is the test data and my query. Basically i am trying to get the File records along with if any error messages occurred. I am getting the output as expected but the message column has "," at the front and sometime at the end of the column. How do i avoid that.
Also is there any better way to write this query? Any suggestion or correction please
declare @Files table (ID int identity(1,1) primary key,Filename varchar(50),Date_Created datetime)
insert into @Files (Filename,Date_Created)
select 'File1',GETDATE()-1 union all
select 'File2',GETDATE()-1 union all
select 'File3',GETDATE()-1 union all
select 'File4',GETDATE()-1 union all
select 'File5',GETDATE()-4 union all
select 'File6',getdate()-4;
declare @FilesLog table (IDLog int identity(1,1) primary key,
ID int,Message varchar(4000), Date_Created datetime);
insert into @FilesLog (ID,Message,Date_Created)
select 1,'',GETDATE()-1 union all
select 1,'Error:InvalidFile',GETDATE()-1 union all
select 1,'Error: Email is not valid',GETDATE()-1 union all
select 2,'Error:InvalidFile',GETDATE()-1 union all
select 2,'Error: Age is not valid',GETDATE()-1 union all
select 2,'Error: Salary is not valid',getdate()-1 union all
select 3,'',getdate()-3 union all
select 3,'',getdate()-4 ;
With CTE as (
SELECT id, message = STUFF((SELECT N', ' + message
FROM @FilesLog AS p2
WHERE p2.ID = p.id
FOR XML PATH(N'')), 1, 2, N'')
FROM @FilesLog AS p
where DATEDIFF(DD,GETDATE(),p.Date_Created) = -1 and Message is not null
GROUP BY id)
select F.ID,F.Filename,F.Date_Created,
case when message >'' then message
else 'no Issues' end as [Message] from
@Files F left join CTE C
on(F.ID = C.ID) where DATEDIFF(DD,GETDATE(),F.Date_Created) = -1 ;
February 7, 2015 at 10:02 am
Quick thought, add a case for the zero length message entries, otherwise you solution is fine.
😎
USE tempdb;
GO
SET NOCOUNT ON;
declare @Files table (ID int identity(1,1) primary key,Filename varchar(50),Date_Created datetime)
insert into @Files (Filename,Date_Created)
select 'File1',GETDATE()-1 union all
select 'File2',GETDATE()-1 union all
select 'File3',GETDATE()-1 union all
select 'File4',GETDATE()-1 union all
select 'File5',GETDATE()-4 union all
select 'File6',getdate()-4;
declare @FilesLog table (IDLog int identity(1,1) primary key,
ID int,Message varchar(4000), Date_Created datetime);
insert into @FilesLog (ID,Message,Date_Created)
select 1,'',GETDATE()-1 union all
select 1,'Error:InvalidFile',GETDATE()-1 union all
select 1,'Error: Email is not valid',GETDATE()-1 union all
select 2,'Error:InvalidFile',GETDATE()-1 union all
select 2,'Error: Age is not valid',GETDATE()-1 union all
select 2,'Error: Salary is not valid',getdate()-1 union all
select 3,'',getdate()-3 union all
select 3,'',getdate()-4 ;
;With CTE as (
SELECT id, message = STUFF((SELECT CASE WHEN LEN(message) > 0 THEN N', ' + message ELSE '' END
FROM @FilesLog AS p2
WHERE p2.ID = p.id
FOR XML PATH(N'')), 1, 2, N'')
FROM @FilesLog AS p
where DATEDIFF(DD,GETDATE(),p.Date_Created) = -1 and Message is not null
GROUP BY id)
select F.ID,F.Filename,F.Date_Created,
case when message >'' then message
else 'no Issues' end as [Message] from
@Files F left join CTE C
on(F.ID = C.ID) where DATEDIFF(DD,GETDATE(),F.Date_Created) = -1 ;
Results
ID Filename Date_Created Message
--- --------- ----------------------- -----------------------------------------------------------------------
1 File1 2015-02-06 17:03:38.627 Error:InvalidFile, Error: Email is not valid
2 File2 2015-02-06 17:03:38.627 Error:InvalidFile, Error: Age is not valid, Error: Salary is not valid
3 File3 2015-02-06 17:03:38.627 no Issues
4 File4 2015-02-06 17:03:38.627 no Issues
February 7, 2015 at 11:12 am
Thanks Eirik
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply