Help with query - 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 2000 Forums

Transact-SQL (2000)

Help with query

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-10-09 : 14:46:00

Hi,

I need to perform a query that will return the status of packlists.
The query will be something like this:
Select cartonstatus_id from carton C inner join CartonStatusHistory H
on C.carton_id=H.carton_id where packlist_id in (@packlist).
I also have to join other tables in order to get data from other tables.

However, I need to check per packlist if different status_ids are returned. If yes then i have to return the word 'Mixed' else the status_name.
So if packlist_id has statuses 1,2 the status will actually be Mixed.

Not sure what is the best way to write that query.

Thanks for the help.

CREATE TABLE [dbo].[Carton](
	[carton_id] [nvarchar](255) ,
	[packlist_id] [nvarchar](30) ,
	[businessunit_id] [int] NOT NULL,
	[season_id] [int] NOT NULL,
 CONSTRAINT [PK_Carton] PRIMARY KEY CLUSTERED 

CREATE TABLE [dbo].[CartonStatusHistory](
	[cartonstatushistory_id] [int] IDENTITY(1,1) NOT NULL,
	[cartonstatushistory_date] [smalldatetime] NOT NULL CONSTRAINT [DF_CartonStatusHistory_cartonstatushistory_date]  DEFAULT (getdate()),
	[cartonstatus_id] [int] NOT NULL,
	[carton_id] [nvarchar](255) ,
	[user_id] [int] NOT NULL,
	[message_id] [int] NULL,
	[cartonstatushistory_xmlsource_date] [smalldatetime] NULL

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 14:55:08

seems like


Select case when h1. status_count>1 then 'Mixed' else cast( cartonstatus_id as varchar(15)) end from carton C inner join CartonStatusHistory H
on C.carton_id=H.carton_id 
inner join (select carton_id,count(cartonstatus_id) as status_count from CartornStatusHistory group by carton_id)h1
on h1.carton_id=H.carton_id 
where ',' + @packlist +  ',' like '%,' + cast(packlist_id as varchar(20)) + ',%'

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-10-09 : 17:00:17

Thanks, although i have different status for the same packlist it returns the status_ids and not the value Mixed.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources