T SQL help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

T SQL help

Author

Topic

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-03-16 : 15:19:03

I have the following table

Call_ID Order_ID Activity LastUpdateDate
1 100 'Install' 01/01/2007
2 100 'Add' 01/02/2007
3 101 'Install' 02/02/2007
4 105 'Install' 03/05/2007
5 105 'Other' 03/05/2007
6 106 'NEW' 04/05/2007
7 106 'Charge' 04/06/2007

For all orders which has install as an activity, and lastupdate is equl or more than 01/01/2007

find where there is an 'add' activity for that order or no add activity

I want to display results like this

Order_ID Activity Result
100 Install Add
101 Install No add activity
105 Install No add activity

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-17 : 00:44:19

Hi,

you want like this

Declare @Table Table (Call_ID INT, Order_ID INT, Activity VARCHAR(100), LastUpdateDate DATETIME)
INSERT INTO @TABLE
SELECT 1 ,100 ,'Install', '01/01/2007' UNION ALL
SELECT 2 ,100 ,'Add', '01/02/2007 ' UNION ALL
SELECT 3 ,101 ,'Install', '02/02/2007 ' UNION ALL
SELECT 4 ,105 ,'Install', '03/05/2007' UNION ALL
SELECT 5 ,105 ,'Other', '03/05/2007' UNION ALL
SELECT 6 ,106 ,'NEW', '04/05/2007' UNION ALL
SELECT 7 ,106 ,'Charge' ,'04/06/2007'

--SELECT * FROM @TABLE
SELECT Order_ID, Activity , CASE WHEN Activity = 'Install' THEN 'Add' ELSE 'No add activity' END AS Result
FROM @TABLE
WHERE Activity = 'Install' and LastUpdateDate >= '01/01/2007'

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-03-17 : 09:44:56

Rangnath, your query is not *giving* result I want

Here is my version of query but it displays result like this
Order_id activity result
100 Install NULL
100 Install ADD

Query should tell us whether for install there is an add acitivity or not

order_id activity result
100 Install ADD
105 Install No Add

SELECT distinct t.order_id, t.activity, CASE dd.activity
WHEN 'ADD SERIAL #' THEN 'ADD SERIAL #'
END
FROM
(

SELECT distinct order_id,activity
FROM
demand_done
WHERE LastUpdateDT >= '01/01/2007' and activity= 'install'

) AS T LEFT JOIN demand_done dd on t.order_id = dd.order_id

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-03-17 : 10:08:25

I found the query I need sorry sometime I go nuts.. just need a cup of coffee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources