having trouble with a sql SP statement

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

SQL Server 2008 Forums

Transact-SQL (2008)

having trouble with a sql SP statement

Author

Topic

cojimarmiami
Starting Member

6 Posts

Posted - 2014-03-28 : 10:44:12

I have 3 records grouped by ticket number(it is a field in my table), in 2 of them I have the same stdesc but in the other one I have a description that contains FREIGHT' word. If sttot field >0 and stdesc contains FREIGHT' word, I would like to get the other description else just stdesc

I am trying to add this

stdesc=max(case when(sttot >0 and stdesc like '%FREIGHT') then (stdesc = stdesc is not like '%FREIGHT') else stdesc end),

to a sql statement and sql gave me the following message

Incorrect syntax near '='.

Can someone help me please?

cojimarmiami
Starting Member

6 Posts

Posted - 2014-03-28 : 11:11:32

for example, if I have the below data

sttckt sttot stdescr

123 0 coca cola

123 20 freight

123 0 coca cola

124 10 pepsi

124 0 freight

124 0 pepsi

I want the case returns coca cola for 123 and pepsi for 124

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-31 : 18:08:33

Please use the below code..


DECLARE @Input TABLE(sttckt INT, sttot INT, stdescr VARCHAR(20))
INSERT INTO @Input VALUES (123, 0, 'coca cola'),(123, 20, 'freight'),(123, 0, 'coca cola'), (124, 10, 'pepsi'), (124, 0, 'freight'), (124, 0, 'pepsi')
,(125, 1, 'abcd')
SELECT DISTINCT sttckt, stdescr 
FROM @Input t1
WHERE stdescr <> 'freight'
AND EXISTS (SELECT 1 FROM @Input t2 WHERE t2.sttckt = t1.sttckt AND t2.stdescr = 'Freight')

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources