Need help converting a select query into a case statement

I have the following query:

(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')

I need to convert this into a case statement.
I tried various ways but did not get the result that I was after

Thanks,
LauraConvert a SELECT statement into a CASE statement?

I haven't a clue what you are intending to do.|||This is the query that I am dealing with:

SELECT PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice AS InvoiceNumber,
CASE WHEN (AR.InvoiceDate = '')
THEN '0'
ELSE DATEDIFF(day, AR.InvoiceDate, ********* need to insert case statement to find the end of the period ************** ) END AS DaysOld,
SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount WHEN LedgerAR.TransType = 'CR' AND
LedgerAR.SubType = 'T' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) AS InvoiceBalance,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'I' THEN - 1 * LedgerAR.Amount ELSE 0 END) AS Interest,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'R' THEN LedgerAR.Amount ELSE 0 END) AS Retainage,
SUM(CASE WHEN (LedgerAR.WBS2 <> '9001' AND AR.Period = '200408') THEN - 1 * LedgerAR.Amount ELSE 0 END) AS total
FROM PR LEFT OUTER JOIN
AR ON AR.WBS1 = PR.WBS1 AND PR.WBS2 = '' AND PR.WBS3 = '' LEFT JOIN
LedgerAR ON AR.WBS1 = LedgerAR.WBS1 AND AR.WBS2 = LedgerAR.WBS2 AND AR.WBS3 = LedgerAR.WBS3 AND
AR.Invoice = LedgerAR.Invoice LEFT JOIN
LD ON PR.WBS1 = LD.WBS1 AND PR.WBS2 = LD.WBS2 AND PR.WBS3 = LD.WBS3 LEFT JOIN
CFGDates ON CFGDates.Period = AR.Period
WHERE (LedgerAR.TransType = 'IN') AND (LedgerAR.SubType <> ' X ' OR
LedgerAR.SubType IS NULL) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND (LedgerAR.Amount <> 0) AND
(AR.PaidPeriod > 200408) AND (PR.WBS1 = '001-298') OR
(LedgerAR.TransType = ' CR ') AND (LedgerAR.SubType IN (' R ', ' T ')) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND
(LedgerAR.Amount <> 0) AND (PR.WBS1 = '001 - 298 ')
GROUP BY PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice, Ar.Period, CFGDates.AccountPdEnd
HAVING SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) <> 0

Within the DateDiff function I need to find the end of the period using a case statment such as

Case When AR.Period='200408' Then Min(CFGDates.AccountPdEnd) Else '0' End As EndPeriod

The problem is that I am having a hard time integrating this into the top query. By using the case statement that I wrote above, I get an error stating that there is a problem near 'As' . I am wondering if there is a way to perform a case statement within a case statement.

Hope that clears things up.

Thanks,
Laura|||that's right, you cannot assign an alias to a value if that value is inside a function

just drop the As EndPeriod

oh, and note that you are looking for the difference in days between AR.InvoiceDate and the value of this case expression, so make sure not to use quotes around the zero if indeed you want december 31, 1899, which seems rather unlikely, but that is, i think, the date that corresponds to zero|||Here is a CASE statement that incorporates your logic:

CASE WHEN (AR.InvoiceDate = '') THEN '0'
WHEN AR.Period='200408' THEN DATEDIFF(day, AR.InvoiceDate, Min(CFGDates.AccountPdEnd))
ELSE DATEDIFF(day, AR.InvoiceDate, 0)
END AS DaysOld

...but whether it will integrate with the rest of your SQL statement, I do not know.|||blindman, that's the same as the nested version (you are allowed to nest CASEs, you know)

that date equal to an empty string gives me the creeps

and the quotes around the zero don't make me feel too hot either

also, i just noticed, CFGDates.AccountPdEnd is in the GROUP BY, so why even bother finding the MIN for it in the DATEDIFF, eh|||Oh, I certainly didn't mean to imply that was ALL that was wrong with her code.

What gives me the heebie-jeebies about it is all the hard-coded values.

need help adding onto my stored procedure

calculate age:
CREATE PROCEDURE selectage
AS
SELECTAge = datediff(yy, birthday, getdate()) +
case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else -1
end
FROM nopay
my ? is how do fix this problem:
I add in
WHERE (((Age)>= @.age1 And (Age)<= @.age2))
and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?

Unfortunately SQL won't handle it that way. One approach is to use your calculation in the WHERE column:

WHERE

    datediff(yy, birthday, getdate()) +

        case

            when datepart(dy, birthday) <= datepart(dy, getdate()) then 0

        else

             -1

        end BETWEEN @.age1 And @.age2)

Alternately, you could do this:

SELECT

    age

FROM

(SELECT

   Age = datediff(yy, birthday, getdate()) +

        case

            when datepart(dy, birthday) <= datepart(dy, getdate()) then 0

        else

            -1

        end

FROM

    nopay) AS subquery

WHERE Age BETWEEN @.age1 And @.age2

Also, I would suggest writing a user-defined function (UDF) tocalculate the age. This will keep your code cleaner and is thesort of thing you will likely need to reuse. For example:

SELECT

   dbo.fnAge(birthday) AS age

FROM

    nopay

WHERE

     dbo.fnAge(birthday)BETWEEN @.age1 And @.age2)

CREATE FUNCTION age (@.indate datetime) -- note: untested!!

RETURNS int

AS

BEGIN

    DECLARE @.age int

    SELECT @.age = datediff(yy, @.indate, getdate()) +

        CASE

            WHEN datepart(dy, @.indate) <= datepart(dy, getdate()) then 0

        ELSE

            -1

        END

    RETURN @.age

END

|||

Can I kiss you?

One more thing I tried to add onto the where and it gave me an error about the comma

WHERE Age BETWEEN @.age1 And @.age2, breed = @.breed

i also did

WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

than it complained about input was not in correct form

Dim paramBreed As SqlClient.SqlParameter
paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar, breed.SelectedValue)
paramBreed.Value = breed.SelectedValue
objCM.Parameters.Add(paramBreed)

CREATE PROCEDURE selectage
@.age1 int,
@.age2 int,
@.breed nvarchar
AS
SELECT
age, username, breed

FROM
(SELECT username, breed,
Age = datediff(yy, birthday, getdate()) +
case
when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else
-1
end
FROM
nopay) AS subquery
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

InvalidCastException: Cast from string "Dalmation" to type 'Integer' is not valid.]
if i change that statement to

paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar)

the program will execute but than it wont display as if the breed = @.breed was incorrect

|||Don't get excited, it's only SQL after all!
If that's an exact copy-and-paste of your WHERE clause, you need a space between the @.breed and the AND:
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2
(You should use the UDF. This is the sort of thing they were made for.)
|||

my mistake, no the syntax checks fine, just the asp gives that error

i tried writing

SELECT

   dbo.fnAge(birthday) AS age

FROM

and i dont understand dbo.fnAge

do i delete create stored procedure?

|||In your stored procedure, give a length to the @.breed nvarchar parameter
@.breed nvarchar(100)
And in your code, try this line instead for creating the parameter:
paramBreed = New SqlClient.SqlParameter("@.breed", SqlDbType.NVarChar, 100)
(Be sure to use the same length that corresponds to your database column.)
|||

Big thanks,

CREATE PROCEDURE selectage
@.age1 int,
@.age2 int,
@.breed nvarchar
AS
SELECT
age, username, breed

FROM
(SELECT username, breed,
Age = datediff(yy, birthday, getdate()) +
case
when datepart(dy, birthday) <= datepart(dy, getdate()) then 0
else
-1
end
FROM
nopay WHEREfirst.username = second.username) AS subquery
WHERE breed = @.breed AND Age BETWEEN @.age1 And @.age2

i figured out how to whatever you call it when u have same feilds and eliminate unwanted data

sql

need help : SQL Server and Clear Case

Environment

1)We are using VBA,MS Access 2000/2003 and SQL Server 2000 in our
project.

2)We need to use Rational clear case as the SCM tool.

Our Work

Our work primarily falls in developing SQL Server stored procedures,
functions and few VisualBasicForAccess(VBA) code.

Technical Help sought

1) Does SQL Server 2000 provide any inbuilt capabilities of Software
Configuration Management(SCM)?Not that I am aware of other than
extending it with other DM management tools like BMC,A&G,VERITAS etc.

2)If not, Is there any plug-in available in SQL Server environment that
can be installed on SQL Server so that any code developed [Stored
procedures/functions etc] can be managed in Clearcase.[I am looking for
Eclipse plugin's kind of plugins which would enable you to check in and
check out from Eclipse after the initial configuration is done on
Eclipse]?

3)If plugins are not available then the option we have is, manual and
external check in and check out the code bits in to Clearcase.Can some
one point me to concise and relevant user manual on this.The MSSQL 2000 client tools have no direct SCM integration (the 2005
ones do), although QA allows you to run batch files with parameters
such as the currently open file, so you could develop some basic
scripts yourself. Visual Studio might be an option if you're already
using it, but personally I just check out a file (from VSS) and start
editing it in QA. I have no idea how ClearCase works, so I can't really
comment on that tool specifically.

Simon|||(db2sysc@.gmail.com) writes:
> 2)If not, Is there any plug-in available in SQL Server environment that
> can be installed on SQL Server so that any code developed [Stored
> procedures/functions etc] can be managed in Clearcase.[I am looking for
> Eclipse plugin's kind of plugins which would enable you to check in and
> check out from Eclipse after the initial configuration is done on
> Eclipse]?

I have no experience of ClearCase, but stored procedures etc are no
different from everything else you have under source control. It's
just files. Some people think they are database objects, but that
is the "binary" representation, so to speak.

Sure, some people think it's nice to check out a file from the
version-control system directly in the tool. I for my part, prefer
to access the CM tool directly (in my case SourceSafe), so I know what
is going on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Need Data in Title Case

Hey Friends...

In database I have description field in Uppar case but I want to display it in Title Case

Like :

In database I have description like : TAKE BACK CONTROL OF YOUR CHANGE

But in report I want like : Take Back Control Of Your Change

and I am using crystal report 8.5 so can anybody help me please

Thanks

-Jayesh Mendpara
jmendpara@.gmail.comDoes 8.5 have the ProperCase function? If not I guess you'll have to code it yourself in a formula.|||No,

in Crystal report 8.5 ProperCase function doesn't support

Need a suggestion on how to revise this query

SELECT WBS2, (SUM(CASE WHEN wbs2 = '9001' THEN amount * - 1 END)) AS reimb
FROM LedgerAR
WHERE (WBS1 = '001-298') AND (WBS2 <> 'zzz') AND (WBS3 <> 'zzz') AND transtype = 'in'
GROUP BY wbs2

this is what the output looks like:

wbs2 amount
0141 0
1217 0
1218 0
1219 0
1222 0
1223 0
9001 3271.02

Id there a way to specify where I want this exact amount to display without having to a subquery. I would ideally like to take the 3271.02 and put it where wbs2 = '1217'

Any suggestions?

Thank You.You mean you want to reorder the results to that

9001, 3271.02

is in the second row?

need a small help on SQL Query.

guys i need a small help on SQL Query.

I want to write a SELECT query which gives me CASE SENSITIVE search result. for ex: if a users original password in db is 'TOM' and user while logging in enters his password as 'tom', he should not be able to login ..But if he enters the password as 'TOM' he is permitted to log in .. How do i check this using a SQL Select Query ?
Any idea on this would be greatly appreciated..

NimeshUsing the PUBS database as an example, I have SQL Server installed as case insensitive. I can write a query to return the Author 'Green' from the AUTHORS table as:

select *
from authors
where au_lname = 'green'

Which would return 1 record even if the lastname is 'Green' with a capital letter. However if I want to enforce case sensitive I would convert the values to VARBINARY. The following would not return a record:

select *
from authors
where convert(varbinary,au_lname) = convert(varbinary,'green')

However this would return 1 record:

select *
from authors
where convert(varbinary,au_lname) = convert(varbinary,'Green')

Need 2nd set of eyes on a CASE statement

I am going batty looking at this! I keep getting ORA-00907:Missing Right parenthesis when I have the 2nd case statement in this SQL (in bold). If I remove that line, it works just fine. As I don't see any left ( w/o a right ), I have no idea what the real issue is. (this is against a Oracle DB)

select rownum as IndexNum,
lm.hostlocid "Loc. Code",
pm.hostpartid "Comcode",
pm.partname "Description",
pc.hostplannercodeid "Plnr Code",
pc.codedescription "PC Descr",
sa.ohneworig "On Hand New Orig",
sa.ohfixedorig "On Hand Fixed Orig",
sa.ohbadorig "On Hand Bad Orig",
sa.onorderorig "On Order Orig",
sa.backorderorig "Backorder Orig",
sa.inrepairorig "In Repair Orig",
sa.inreturnorig "In Return Orig",
sa.allocatedorig "Allocated Orig",
sa.amountcustom1 "Amt Cus 1",
sa.amountcustom2 "Amt Cus 2",
sa.amountcustom3 "Amt Cus 3",
sa.amountcustom4 "Amt Cus 4",
sa.amountcustom5 "Amt Cus 5",
upper(ip.isaslitem) "Is ASL Item",
upper(ip.aslstatus) "ASL Status",
ip.stockmax "Stock Max",
ip.rop "Reorder Point",
pm.price "Std Cost",
fd.ForecastAmount,
to_char(fd.ForecastSliceDate,'MM-YYYY') "Current Period",
case when (sa.ohfixedorig + sa.ohneworig)=0 or fd.ForecastAmount=0 then null else round(((sa.ohfixedorig + sa.ohneworig)/(fd.ForecastAmount/30))) end Out_Of_Stock_Days,
pcd.ChainParentID,
pcd.RelationType,
case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type
from
stock_amount sa,
part_master pm,
loc_master lm,
loc_type lt,
stock_level ip,
planner_codes pc,
Forecasted_data fd,
part_chain_details pcd
where
sa.partid=pm.partid and
sa.locid=lm.locid and
lm.loctypeid=lt.loctypeid and
ip.partid=sa.partid and
pm.plannercodeid=pc.PLANNERCODEID and
ip.locid=sa.locid and
fd.partid = sa.partid and
fd.locid = sa.locid and
lm.hostlocid = 'B014' and
pcd.partid (+) = pm.partid and
(pc.hostplannercodeid = 'C50' or pc.hostplannercodeid = 'C51' or pc.hostplannercodeid = 'C52' or pc.hostplannercodeid = 'ZZ5') and
fd.ForecastSliceDate >ADD_MONTHS(SYSDATE,-1) and
fd.ForecastSliceDate<ADD_MONTHS(SYSDATE,0)

Any help showing the evil of my ways would be greatly appreciated!case when pcd.relationtype = 1 then (N'Alt') else (N'Rep') end Chain_Type

Your problem, is that both (N'Alt') and ('N'Rep') are invalid. The syntax to append text to a column is, (column||' text');|||try it without the Ns

Edit: there's no concatenation; ChainType is a column alias|||Thanks R123456 and R937.

For some reason I had in my head that when using a case statement to output a text value I had to preced the 'text' with (N'my text').

No I was not trying to append to a column! Big OOPS!

I decided to try a DECODE statement that works. (I also was not taking into account nulls!)

With decode( pcd.relationtype,1,'Alt',0,'Rep','') as Chain_Type, the output is what I expect.

Hopefully I can be back to the dynamic SQL issue I posted earler this week!

Thanks for the slap in the face to wake me up! :-)

Near exact matches, help needed

I'm currently working on a match function to compare two char based columns in differnet tables to create a join.

In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.

For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.

Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.

Thanks in advancePerhaps the SOUNDEX function could help you out: SOUNDEX - Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.