Adding to a case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding to a case statement

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-09 : 11:09:42

How can I add this to the case statement of the query or just to the query to check...

If fileddate <> filedate then use the min filedate and place in the title field the word 'concurr' for the one record.

I have this query doing it already but it does it for filedates matching.


Select  distinct p.CLMS, p.DOC,
        p.Reg, 
n.RegionAcronym,
        p.FO,
     
    
    
Age = Datediff(day,max(p.filedate), getdate()),
    FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
    DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
    Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end 
From pendingdds p
 join Offices.dbo.OfficeCodes d 
on d.officecode = p.doc
join natdocfile n
on n.doc = p.fo
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc ='s09'
AND ( Datediff(DAY, filedate, Getdate()) > 300 ) 
Group By  p.fo, p.Reg, n.regionacronym,p.DOC, p.CLMS, o.ddsofficecode,
o.mailingaddressstate
order by clms

How can I add to the bold section or add onto the title section to get what I want

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:19:51

which table you get fileddate from?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-09 : 11:22:07

p.pendingdds

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:27:00

Title = case when min(p.Title) <> max(p.Title) then 'Concurr'
when min(p.pendingdds) <> min(filedate ) then min(filedate )
else min(p.Title) end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-09 : 11:32:58

Thanks I tried that but got invlaid column name pendingdds on p.pendingdds. I added this because pendingdds is the table name.


Select  distinct p.CLMS, p.DOC,
		p.Reg, 
n.RegionAcronym,
		p.FO,
	 
	
	
Age = Datediff(day,max(p.filedate), getdate()),
	FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
	--Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
Title = case when min(p.Title) <> max(p.Title) then 'Concurr' 
when min(p.filedate) <> min(p.filedate ) then min(p.filedate)
else min(p.Title) end 

From pendingdds p
 join Offices.dbo.OfficeCodes d 
on d.officecode = p.doc
join natdocfile n
on n.doc = p.fo
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc ='s09'
AND ( Datediff(DAY, filedate, Getdate()) > 300 ) 
Group By  p.fo, p.Reg, n.regionacronym,p.DOC, p.CLMS, o.ddsofficecode,
o.mailingaddressstate
order by clms

and now getting conversion failed when converting datetime from character string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:46:51

Title = case when min(p.Title) <> max(p.Title) then 'Concurr'
when min(p.fileddate) <> min(p.filedate ) then min(p.filedate)
else min(p.Title) end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-09 : 12:03:56

I just tried that but I'm still getting

conversion failed when converting datetime from character string.

When I click on the error message it takes me to the bold section:


Select  distinct p.CLMS, p.DOC,		p.Reg, 
n.RegionAcronym,
		p.FO,
	 
	
	
Age = Datediff(day,max(p.filedate), getdate()),
	FileDate = max(FileDate),
o.mailingaddressstate as DDS,
o.ddsofficecode as DDSCode,
Min(ddsrcpt) AS DDSReceipt,
	DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),
	--Title = case when min(p.Title) <> max(p.Title) then 'Concurr' else min(p.Title) end
Title = case when min(p.Title) <> max(p.Title) then 'Concurr' 
when min(p.filedate) <> min(p.filedate ) then min(p.filedate)
else min(p.Title) end 

From pendingdds p
 join Offices.dbo.OfficeCodes d 
on d.officecode = p.doc
join natdocfile n
on n.doc = p.fo
join offices.dbo.doorsinfo o
on o.officecode = p.fo
where p.doc ='s09'--@doc
AND ( Datediff(DAY, filedate, Getdate()) > 300 ) 
Group By  p.fo, p.Reg, n.regionacronym,p.DOC, p.CLMS, o.ddsofficecode,
o.mailingaddressstate
order by clms

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 12:08:27

all conditions inside case expression should return same datatype values so make it like


...
Title = case when min(p.Title) <> max(p.Title) then 'Concurr' 
when min(p.fileddate) <> min(p.filedate ) then convert(varchar(20),min(p.filedate),121)
else convert(varchar(20),min(p.filedate),121) end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-09 : 12:14:45

Okay almost

I need to call that column concurr when the case matches like when I'm doing it with the first case statement.


Title = case when min(p.Title) <> max(p.Title) then 'Concurr' 
when min(p.filedate) <> max(p.filedate ) then convert(varchar(20),min(p.filedate),121)
else convert(varchar(20),min(p.filedate),121) end

I also changed the second min to max

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-09 : 12:17:52

Tried this but getting incorrect syntax near the keyword 'convert'


Title = case when min(p.Title) <> max(p.Title) then 'Concurr' 
when min(p.filedate) <> max(p.filedate ) then 'concurr' convert(varchar(20),min(p.filedate),121) end
--else convert(varchar(20),min(p.title),121) end

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-09 : 12:24:19

I changed the bold section because it was giving me the filedate inthe else and I need the title instead.


Title = case when min(p.Title) <> max(p.Title) then 'Concurr' 
when min(p.filedate) <> max(p.filedate ) then convert(varchar(20),min(p.filedate),121)else min(p.title) end

I need to add the other 'Conncurr' in front of the convert. I added it and getting incorrect syntax near the keyword 'convert'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 12:29:31

its really surprising that your postcount is about 1000 and still you doesnt know concatenation operator is + in t-sql


..
Title = case when min(p.Title) <> max(p.Title) then 'Concurr' 
when min(p.filedate) <> max(p.filedate ) then 'Concurr' + convert(varchar(20),min(p.filedate),121)else min(p.title) end

suggest you to understand solutions provided and look up in books online for more details on solutions provided

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-09 : 13:00:21

Yes that is it thanks it works. I will look it up and read more as I wasn't thinking on how to do that. I guess old age is sinking in and I can't remember as much. So sorry.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 21:49:53

no problem. It happens to all.
Just make sure you understand the solutions provided so that you can apply for future scenarios

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-03-11 : 08:28:57

Okay thank you I will and thanks again for all of your help and the people on this forum!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources