CASE WHEN with subquery - 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)

CASE WHEN with subquery

Author

Topic

netsports
Starting Member

11 Posts

Posted - 2005-08-10 : 12:13:09

I'm trying to create an aliased field on the fly in my sql string to use later in my datagrid, but having a tuff time coordinating the right CASE WHEN together with a subquery:

amtA = (select b.salesamount from b WHERE b.BoardDate = '" + day1 + "')

CASE WHEN b.salesamount > 0 THEN 'amtA' ELSE NULL END ,

the above will not give me an error, but it displays a blank datagrid when posting a date from a calendar to view a datagrid with information for that particular date.
What I want to accomplish is making amtA an aliased field - the salesamount from the day before. the user will click on a calendar date from the page before and view data for that date, but 'amtA' will be the salesamount from the previous day (already configured in c#:
DateTime day1;
day1 = requestedday.AddDays( -1);)

and i will want to display 'amtA' in a column in my datagrid (eventually will do a sum with day2, day3, etc for a weekly total).
Just wondering the best way to parse yesterday's 'amtA' and use it as an aliased field name
thanks in advance
netsports

kristianwhittick
Starting Member

12 Posts

Posted - 2006-06-07 : 05:01:38

Try this
Kris

DECLARE @amtA NUMERIC;
DECLARE @yest varchar(12);

SET @yest = CONVERT( varchar(12), DATEADD (day , -1, getDate()), 102);

SELECT @amtA = b.salesamount
FROM b
WHERE CONVERT( varchar(12), b.BoardDate , 102) = @yest;

IF (@amtA <= 0)
BEGIN
SET @amtA = NULL;
END;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources