[Resolved] Select with case - 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)

[Resolved] Select with case

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-04-16 : 10:35:07

I am connecting to an Advantage data base using linked server:

select * from openquery(ADS_RGWP_SERVER, 'select max(shift_started), sum(qty_shipped_today) from salestkt')

I need to select latest / most recent "shift started" date/time (which I think I am doing) and also need to split up "qty shipped today" in 2 buckets as:
bucket 1 if cust_type = "Inter-Company" and
bucket 2 if cust_type = "Charge"

Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-16 : 10:43:32

I don't know anything about Advantage data base but in T-SQL:
sum(case when cust_type='Inter-Company' then qty_shipped_today else 0 end) as bucket_1
sum(case when cust_type='Charge' then qty_shipped_today else 0 end) as bucket_2

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-04-16 : 12:06:52

webfred, this syntax works

select * from openquery(ADS_RGWP_SERVER, 'select
                                             max(shift_started) as shifttime,
                                             sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty,
                                             sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty
                                          from salestkt')

Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-18 : 12:59:11

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources