Restore Database: case

Showing posts with label case. Show all posts

Tuesday, March 27, 2012

Help with case when

I need help.
This is the code I currently have and need to change:
p.code as "Code",
p.detail_type as "Detail Type",

p.code shows diagnostic code numbers and billing procedure numbers
while p.detail_type indicate which is which. Diagnostic code numbers
are designated as '-2' (minus 2) in the 'detail_type' and procedure
codes are designated as '-4' (minus 4) in the 'detail_type.' The query
I am using now (see below) gives me duplicate appt dates to show both
the diagnostic code and procedure code. I need to clean this up a bit.

What I want this to do is find a statement that will separate the codes
into diagnosis numbers and procedure numbers and place these numbers in
different columns in the ad hoc report that is generated.

I would like something to the effect of:
If p.detail_type = -2 then place the code number in a column known as
"Code"
If p.detail_type - -4 then place the code number in a column known as
"Procedure"

Any ideas on how to write this?

Select
/* Individual Client Task List */

a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
p.code as "Code",
p.detail_type as "Detail Type",
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>From Appointments a, Patientmedicalrecords p

Thanks for your help. dwerden.dwerden (dwerden@.purdue.edu) writes:

Quote:

Originally Posted by

I need help.
This is the code I currently have and need to change:
p.code as "Code",
p.detail_type as "Detail Type",
>
p.code shows diagnostic code numbers and billing procedure numbers
while p.detail_type indicate which is which. Diagnostic code numbers
are designated as '-2' (minus 2) in the 'detail_type' and procedure
codes are designated as '-4' (minus 4) in the 'detail_type.' The query
I am using now (see below) gives me duplicate appt dates to show both
the diagnostic code and procedure code. I need to clean this up a bit.
>
>
What I want this to do is find a statement that will separate the codes
into diagnosis numbers and procedure numbers and place these numbers in
different columns in the ad hoc report that is generated.
>
I would like something to the effect of:
If p.detail_type = -2 then place the code number in a column known as
"Code"
If p.detail_type - -4 then place the code number in a column known as
"Procedure"
>
Any ideas on how to write this?

CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, Erland. Your code separated them like I wanted but I continue
to get 2 entries for each actual appointment (as shown below).

Provider--Session Date--Session Type--Code--Proceure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--(empty)--Done--bb
Bossick--7/6/2006--1 hr Session--(empty)--99212--Done--bb

Is there a way to force these to combine into only one entry like this?

Provider--Session Date--Session Type--Code--Procedure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--99212--Done--bb

Erland Sommarskog wrote:

Quote:

Originally Posted by

CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Select
/* Individual Client Task List */

a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>From Appointments a, Patientmedicalrecords p

Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
and a.Personal = 0
and a.Ingroup = 0
and a.Appttype not like 'TELE'
and a.Apptdate between '1-Jul-2006' and sysdate - 1
and a.Appttype not in ('AEP2','AEP4','AOD','TOCE2','TOCE4','ETOH2
Class','AEP4 Class','AOD1 Class')
and (substr(a.Complaint1,1,2) = 'TS'
or a.Complaint1 like 'Secretary Signed'
or a.Complaint1 is null
or a.Signoffinits is null)
/* Next line excludes Pyle, Kyler, Aunt Bee, Rowdy */
and a.Patientnumber not in ('57629','82362','125163','139842')
Order by
a.Provider,
a.Apptdate,
a.Lastname|||Assuming there is at most one row in Patientmedicalrecords with detail_type
= -2 and at most one with detail_type = -4 for each matching row in
Appointments, then
Change:
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,

To:
Coalesce(p1.Code,'') As Code,
Coalesce(p2.Code,'') As Procedure,

And change:
From Appointments a, Patientmedicalrecords p
Where a.uniquenumber = p.appt_uniquenumber
and a.Division = 3
and a.Inactive = 0
<rest of where conditions>

to:

From Appointments a
Left Outer Join Patientmedicalrecords p1 On a.uniquenumber =
p1.appt_uniquenumber
And p1.detail_type = -2
Left Outer Join Patientmedicalrecords p1 On a.uniquenumber =
p12.appt_uniquenumber
And p1.detail_type = -4
Where a.Division = 3
and a.Inactive = 0
<rest of where conditions>

Tom

"dwerden" <dwerden@.purdue.eduwrote in message
news:1154457844.644843.182100@.m73g2000cwd.googlegr oups.com...

Quote:

Originally Posted by

Thanks, Erland. Your code separated them like I wanted but I continue
to get 2 entries for each actual appointment (as shown below).
>
Provider--Session Date--Session Type--Code--Proceure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--(empty)--Done--bb
Bossick--7/6/2006--1 hr Session--(empty)--99212--Done--bb
>
Is there a way to force these to combine into only one entry like this?
>
Provider--Session Date--Session Type--Code--Procedure--Note
Written--Co-Signed
Bossick--7/6/2006--1 hr Session--309.28--99212--Done--bb
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

> CASE p.detail_type WHEN -2 THEN p.code END AS Code,
> CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
Select
/* Individual Client Task List */
>
a.Provider as "Provider",
a.Apptdate as "Session Date",
a.Appttype as "Session Type",
CASE p.detail_type WHEN -2 THEN p.code END AS Code,
CASE p.detail_type WHEN -4 THEN p.code END AS Procedure,
a.Complaint1 as "Note Written",
a.Signoffinits as "Co-Signed",
a.Lastname as "Lastname",
a.Firstname as "Firstname"

Quote:

Originally Posted by

>>From Appointments a, Patientmedicalrecords p

|||>p.code shows diagnostic code numbers and billing procedure numbers while p.detail_type indicate which is which. <<

Stop writing code like this. You never cram two or more attributes
into one column. Hey, why not have a column for "squids and
automobiles", too?

What you have done is re-invent the variant record from COBOL, FORTRAN,
Pascal, etc. and other procedural languages.|||That's a great idea!

I think I remember seeing a squid looking automobile thingy in some animated
film recently.

Joe, Did you create the database used by the film company?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1154462822.641976.57700@.m73g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>p.code shows diagnostic code numbers and billing procedure numbers while
>>p.detail_type indicate which is which. <<

>
Stop writing code like this. You never cram two or more attributes
into one column. Hey, why not have a column for "squids and
automobiles", too?
>
What you have done is re-invent the variant record from COBOL, FORTRAN,
Pascal, etc. and other procedural languages.
>

Help with CASE T-SQL

Anyone have much experience with SQL Server T-SQL CASE statement?
I have some code that someone else wrote that looks like this:
SELECT m.messageID, m.isTop,
CASE @.Mode
WHEN 10 THEN m.subject
WHEN 12 THEN p.userID
END
FROM Messages m
INNER JOIN People p ON m.userID=p.userID
WHERE [blah][blah][blah].
What I need to do is return something that is not the userID, but still have
that name. This did not work:
SELECT m.messageID, m.isTop,
CASE @.Mode
WHEN 10 THEN m.subject
WHEN 12 THEN pr.userName As userID
END
FROM Messages m
INNER JOIN People p ON m.userID=p.userID
INNER JOIN Personalize pr ON m.userID=pr.userID
WHERE [blah][blah][blah].
SQL dies on the "As"... (or, if I leave out the "As", it dies on "userID")
Any idea how I can do this?
Thanks,
OwenCASE @.Mode
WHEN 10 THEN m.subject
WHEN 12 THEN pr.userName
END As UserID
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:OWsmKlxPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Anyone have much experience with SQL Server T-SQL CASE statement?
> I have some code that someone else wrote that looks like this:
> SELECT m.messageID, m.isTop,
> CASE @.Mode
> WHEN 10 THEN m.subject
> WHEN 12 THEN p.userID
> END
> FROM Messages m
> INNER JOIN People p ON m.userID=p.userID
> WHERE [blah][blah][blah].
> What I need to do is return something that is not the userID, but still
> have that name. This did not work:
> SELECT m.messageID, m.isTop,
> CASE @.Mode
> WHEN 10 THEN m.subject
> WHEN 12 THEN pr.userName As userID
> END
> FROM Messages m
> INNER JOIN People p ON m.userID=p.userID
> INNER JOIN Personalize pr ON m.userID=pr.userID
> WHERE [blah][blah][blah].
> SQL dies on the "As"... (or, if I leave out the "As", it dies on "userID")
> Any idea how I can do this?
> Thanks,
> Owen
>|||Thanks. This variation on that theme actually worked:
CASE @.Mode WHEN 10 THEN m.subject END As subject,
CASE @.Mode WHEN 12 THEN pr.userName END As UserID
"Norman Yuan" <NotReal@.NotReal.not> wrote in message
news:utqlB9xPGHA.1216@.TK2MSFTNGP14.phx.gbl...
> CASE @.Mode
> WHEN 10 THEN m.subject
> WHEN 12 THEN pr.userName
> END As UserID
>
> "Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
> news:OWsmKlxPGHA.3460@.TK2MSFTNGP15.phx.gbl...
>

HELP with case statement [Divide by zero error encountered.] !

Hi,
I have a case statement that has been giving me hell for the past day, can
anyone suggest another way of validating is those field have zeros or not.
The field types are numberic, looking at the table their is no Null values i
n
there a few hundred zeros (0, .000). I have tried this statement in many wa
y
still the same result.
Divide by zero error encountered.
CASE when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end as [dollar_turns],in your case statement you r not checking for dids.supplier_cost = 0
and in your else clause you are divinding by
( (dihs.qty_total) * dids.supplier_cost )
hope thishelps
rgds
abhishek
"ITDUDE27" wrote:

> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],
>|||By any chance are you rinning SQL 2005? I seem to recall someone else
having a similar issue where the optimizer evaluated the whole
statement rather than one test at a time.
Stu
ITDUDE27 wrote:
> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],|||Hi
Your assumption is wrong :)
Let me explain it.
For simplicity I am showing the columns as a(supplier_cost)
,b(qty_total),c(revenue)
lets say the table is like this...
a b c
1 1 1
0 0 0
For all the cases the sum is going to be 1 (for all three columns)
But analyze this..
sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
its actually 1/(1*1) + 0/(0*0)
I guess maybe you are trying for something like this in your else clause..
sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
If not change your logic accordingly.
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This thread illustrates exactly why one of the best ways to get help is to
include the *ACTUAL* table DDL code *AND* some sample data INSERT
statements.
No so many back-and-forths just trying to understand the issue.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6DEF9528-1748-4905-ACEE-0567A384126B@.microsoft.com...
> Hi
> Your assumption is wrong :)
> Let me explain it.
> For simplicity I am showing the columns as a(supplier_cost)
> ,b(qty_total),c(revenue)
> lets say the table is like this...
> a b c
> 1 1 1
> 0 0 0
> For all the cases the sum is going to be 1 (for all three columns)
> But analyze this..
> sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> its actually 1/(1*1) + 0/(0*0)
> I guess maybe you are trying for something like this in your else clause..
> sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
> If not change your logic accordingly.
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>sql

HELP with case statement [Divide by zero error encountered.] !

I am getting the same result pandey, I might have left that of by accident.
I
have tried it many ways.
case when sum (dids.supplier_cost) = '0'
then '0'
when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
THEN '0'
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end [dollar_turns],
"Abhishek Pandey" wrote:
> in your case statement you r not checking for dids.supplier_cost = 0
> and in your else clause you are divinding by
> ( (dihs.qty_total) * dids.supplier_cost )
> hope thishelps
> rgds
> abhishek
> "ITDUDE27" wrote:
>i am not sure what exactly are you trying to do here..
in your case statement you are checking "sum(dids.supplier_cost)" and other
things for 0 and handling it accordingly but in else clause you are divindin
g
by dids.supplier_cost.
say for example
dids.supplier_cost has three values
1
0
3
while sum(dids.supplier_cost) <> 0 but this doest garuntee that individual
values are also not zero.
so in this case when it goes to else clause sql server experience a divide
by zero error coz what it will try to do is this
sum( somethingvalue / somenonzerovalue + somevalue/ 0 + somevalue/somevalue)
i hope this is clear enough.
You gotta design your query in a better way. if you are having a hard time
try to post your FULL query and some1 would be able to help you out.
regards
Abhishek
"ITDUDE27" wrote:
> I am getting the same result pandey, I might have left that of by accident
. I
> have tried it many ways.
> case when sum (dids.supplier_cost) = '0'
> then '0'
> when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> THEN '0'
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end [dollar_turns],
> "Abhishek Pandey" wrote:
>

Help with Case Statement

Since I'm fairly new to SQL - I need help making the following into a case
statement:
-- Set Reporting date if the policy is a changed policy
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = CHG_EFF_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE CHG_EFF_DATE IS NOT NULL
-- Set Reporting date if the policy is a termination
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = POL_TERM_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE POL_TERM_DATE IS NOT NULL
-- Set Reporting date if the policy is not changed and not terminated
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = POL_EFF_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE CHG_EFF_DATE IS NULL
AND POL_TERM_DATE IS NULL
-- Set Reporting date if Policy date and time is greater than all of the
above
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = convert(varchar(50),policy_date_time,101
)
FROM STAGE_PHX_FACT_POLICY
WHERE
EXISTS (SELECT NULL FROM STAGE_PHX_FACT_POLICY b
WHERE b.POLICY_NUMBER = STAGE_PHX_FACT_POLICY.POLICY_NUMBER
GROUP BY b.POLICY_NUMBER
HAVING STAGE_PHX_FACT_POLICY.POLICY_DATE_TIME >
STAGE_PHX_FACT_POLICY.REPORTING_DATE)
Thanks in advance for any help!Without table structures & sample data, the following expression is
untested:
CASE WHEN chg_eff_date IS NOT NULL
THEN chg_eff_date
WHEN pol_term_date IS NOT NULL
THEN pol_term_date
WHEN chg_eff_date IS NULL
AND pol_term_date IS NULL
THEN pol_eff_date
WHEN policy_date_time > reporting_date
THEN CONVERT( VARCHAR(50), policy_date_time, 101 )
END
I would also suggest you spend some time on the topic CASE in SQL Server
Books Online and go through the examples.
Anith|||On Fri, 28 Oct 2005 08:35:03 -0700, Patrice wrote:

>Since I'm fairly new to SQL - I need help making the following into a case
>statement:
(snip)
Hi Patrice,
Most of it can be caught in a COALESCE. Only the last requirement calls
for a CASE.
UPDATE Stage_PHX_Fact_Policy
SET Reporting_Date =
CASE
WHEN COALESCE (Chg_Eff_Date, Pol_Term_Date, Pol_Eff_Date)
< Policy_Date_Time
THEN CONVERT(char(8), Policiy_Date_Time, 112)
ELSE COALESCE (Chg_Eff_Date, Pol_Term_Date, Pol_Eff_Date)
The above is untested, and assumes that Policy_Number is the primary key
of your table. If it isn't, or if the query doesn't do what you want,
then see www.aspfaq.com/5006 for the recommended way to explain your
problem here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with Case Statement

I have the following code in which I need to check something in the ELSE. The problem is how to form it correctly. I could use a cursor I guess to transverse through the records.

In the Else, I want to:

1) Check the count of the results of my statement. IF > 1 then check to see if m.original is between lowlimit and highlimit for any of those records found
2) If m.original is between the lowlimit and highlimit, then select Fee1 from FeeScheduleDetails

m.original and m.FeeSchedule is out here somewhere, just know this...it's part of the query that you don't see

CASE WHEN Len(c.FeeSchedule) < 3 then
CONVERT(int, c.feeSchedule)
ELSE
Select Count(*) FROM FeeScheduleDetails fd
INNER JOIN Master m ON m.FeeSchedule = fd.code

IF Count(*) > 3
Check to see if m.original is BETWEEN fd.lowlimit AND fd.highlimit
If yes, then bring me back fee1, if no then just bring me back m.FeeSchedule
END AS FeeSchedule

Master

FeeSchedule

FeeScheduleDetails
-
Code
LowLimit
HighLimit
Fee1

? Hi dba123, In your qeury pseudo-code, where is the alias c (used in c.FeeSchedule) defined? It's easier to help you if you post CREATE TABLE statements for your table structures, INSERT statements with some rows of sample data and the expected results of the query. Not only will that help others understand what you ask, it also enables them to easily test what they post. -- Hugo Kornelis, SQL Server MVP <dba123@.discussions.microsoft.com> schreef in bericht news:7fb6e0f7-ae20-451c-abee-677d4d3ee6f8@.discussions.microsoft.com... I have the following code in which I need to check something in the ELSE. The problem is how to form it correctly. I could use a cursor I guess to transverse through the records.In the Else, I want to:1) Check the count of the results of my statement. IF > 1 then check to see if m.original is between lowlimit and highlimit for any of those records found2) If m.original is between the lowlimit and highlimit, then select Fee1 from FeeScheduleDetailsm.original and m.FeeSchedule is out here somewhere, just know this...it's part of the query that you don't seeCASE WHEN Len(c.FeeSchedule) < 3 thenCONVERT(int, c.feeSchedule)ELSESelect Count(*) FROM FeeScheduleDetails fdINNER JOIN Master m ON m.FeeSchedule = fd.codeIF Count(*) > 3Check to see if m.original is BETWEEN fd.lowlimit AND fd.highlimitIf yes, then bring me back fee1, if no then just bring me back m.FeeScheduleEND AS FeeScheduleMasterFeeScheduleFeeScheduleDetails-CodeLowLimitHighLimitFee1|||

thanks for the heads up. Here's the entire query...and a new attept at fee1

INSERT INTO ReportingServer.dbo.DCR

SELECT

m.customer,

c.name,

c.customer,

c.state,

CASE WHEN Len(c.FeeSchedule) < 3 THEN

CONVERT(int, c.feeSchedule)

WHEN Len(c.FeeSchedule) > 3 THEN

SELECT fd.Fee1 FROM FeeScheduleDetails fd

where c.feeSchedule = fd.code AND m.original BETWEEN fd.LowLimit AND fd.HighLimit

ELSE

CONVERT(int, c.feeSchedule)

END AS FeeSchedule,

m.Branch,

CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' Then

(ph.totalpaid - ph.ForwardeeFee)

ELSE

0.00

END AS [Posted Amount],

ph.systemmonth,

ph.datepaid,

ph.totalpaid,

ph.batchtype,

m.desk,

0 AS [New Old CC],

0 AS [New Old PDC],

'In-House' AS Type,

1 AS Active,

ph.UID,

m.number,

dc.amount CC,

p.amount AS PDC,

m.original,

CONVERT(money, ph.OverPaidAmt),

FROM dbo.Master m (NOLOCK) LEFT JOIN dbo.payhistory ph ON m.number = ph.number

LEFTJOIN dbo.DebtorCreditCards dc ON dc.number = m.number

LEFTJOIN dbo.pdc p ON p.number = m.number

LEFTJOIN dbo.Customer c ON c.Customer = m.Customer

LEFTJOIN Apex_ReportingServer.dbo.FeeGoal fg ON fg.CustomerID = c.Customer

GROUP BYm.customer,

c.name,

c.customer,

c.state,

c.FeeSchedule,

m.Branch,

ph.OverPaidAmt,

ph.systemmonth,

ph.datepaid,

ph.totalpaid,

ph.batchtype,

m.desk,

ph.UID,

m.number,

dc.amount,

p.amount,

m.original ,

ph.systemmonth,

ph.systemyear,

ph.ForwardeeFee

HAVINGph.systemmonth = datepart(mm, getdate()) AND ph.batchtype <> 'DA' AND

ph.batchtype <> 'DAR' AND ph.systemyear = datepart(yy, getdate())

AND (c.Name is not null AND c.Name <> '')

ORDER BY m.customer

|||? Hi dba123, Thanks - but you didn't post CREATE TABLE and INSERT statements, so I still can't test any code, nor see what exactly you try to do. Anyway - I'll respond to your other post; it looks to be a simplified version of this problem. If you do need more help in this thread, then add some CREATE TABLE and INSERT statements and expected results, and I'll have a look at it. -- Hugo Kornelis, SQL Server MVP <dba123@.discussions.microsoft.com> schreef in bericht news:662f6401-ea73-4c37-a98e-dd9267c0ed2c@.discussions.microsoft.com... thanks for the heads up. Here's the entire query...and a new attept at fee1 INSERT INTO ReportingServer.dbo.DCR SELECT m.customer, c.name, c.customer, c.state, CASE WHEN Len(c.FeeSchedule) < 3 THEN CONVERT(int, c.feeSchedule) WHEN Len(c.FeeSchedule) > 3 THEN SELECT fd.Fee1 FROM FeeScheduleDetails fd where c.feeSchedule = fd.code AND m.original BETWEEN fd.LowLimit AND fd.HighLimit ELSE CONVERT(int, c.feeSchedule) END AS FeeSchedule, m.Branch, CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' Then (ph.totalpaid - ph.ForwardeeFee) ELSE 0.00 END AS [Posted Amount], ph.systemmonth, ph.datepaid, ph.totalpaid, ph.batchtype, m.desk, 0 AS [New Old CC], 0 AS [New Old PDC], 'In-House' AS Type, 1 AS Active, ph.UID, m.number, dc.amount CC, p.amount AS PDC, m.original, CONVERT(money, ph.OverPaidAmt), 0, 0, '' FROM dbo.Master m (NOLOCK) LEFT JOIN dbo.payhistory ph ON m.number = ph.number LEFT JOIN dbo.DebtorCreditCards dc ON dc.number = m.number LEFT JOIN dbo.pdc p ON p.number = m.number LEFT JOIN dbo.Customer c ON c.Customer = m.Customer LEFT JOIN Apex_ReportingServer.dbo.FeeGoal fg ON fg.CustomerID = c.Customer GROUP BY m.customer, c.name, c.customer, c.state, c.FeeSchedule, m.Branch, ph.OverPaidAmt, ph.systemmonth, ph.datepaid, ph.totalpaid, ph.batchtype, m.desk, ph.UID, m.number, dc.amount, p.amount, m.original , ph.systemmonth, ph.systemyear, ph.ForwardeeFee HAVING ph.systemmonth = datepart(mm, getdate()) AND ph.batchtype <> 'DA' AND ph.batchtype <> 'DAR' AND ph.systemyear = datepart(yy, getdate()) AND (c.Name is not null AND c.Name <> '') ORDER BY m.customer|||So are you asking for my table structure then? The table has already been created...so are you asking for the erd ?|||

I am not sure what you are asking. I have also never seen a CASE statement used in this way. CASE statements are always used within a SELECT clause to select a result value to display in the column based on the value in a particular row. SQL has an IF condition of form:

IF.....

BEGIN

END

ELSE

BEGIN

END

Firstly I suggest you use IF that instead. Is your IF statement returning multiple values, or just one value? If it is just returning one value (and with a count(*) you normally only return one valuer, unless using a GROUP BY) then use and IF, otherwise use a case statement. IF example below)

declare @.result int

declare @.lowlimit int

declare @.highlimit int

IF Len(c.FeeSchedule) < 3

BEGIN

set @.result = CONVERT(int, c.feeSchedule)

END

ELSE
work out @.lowlimit and @.highlimit here.....

delcare @.cnt int

Select @.cnt = Count(*) FROM FeeScheduleDetails fd
INNER JOIN Master m ON m.FeeSchedule = fd.code

IF @.cntBETWEEN @.lowlimit AND @.highlimit

BEGIN

@.result = (get Fee1......)

END

ELSE

BEGIN

@.result = (get m.FeeSchedule.....)

END

Otherwise, if you want the second conditional to be a CASE statement, you will need to rewrite it accordingly

Clarity Consulting

|||I don't think you can do a select statment inside of a CASE statement. Store the result in a variable, and then select that in the CASE statement instead if possible. Otherwise, get the value from a JOIN instead, then you just need to select the column in the CASE...WHEN .. THEN statement.|||? Hi dba123, Sorry for the delayed reply... >>So are you asking for my table structure then? The table has already been created...so are you asking for the erd ? What I'm askking you for is a bunch of statements that I can copy and paste into Query Analyzer and run to recreate the problem. To give you a very simplified example: instead of asking "how to get the lowest wage for each department", you'd have to post some SQL: CREATE TABLE Personnel (EmpID int NOT NULL PRIMARY KEY, DeptID int NOT NULL, Wage decimal(7,2) NOT NULL) go INSERT INTO Personnel (EmpID, DeptID, Wage) VALUES (1, 1, 20000) INSERT INTO Personnel (EmpID, DeptID, Wage) VALUES (2, 1, 30000) INSERT INTO Personnel (EmpID, DeptID, Wage) VALUES (3, 2, 15000) INSERT INTO Personnel (EmpID, DeptID, Wage) VALUES (4, 3, 20000) go And then, you'd add the required results: DeptID LowestWage 1 20000 2 15000 3 20000 Anyone can now copy the SQL statements, execute them in a test database, try some queries and finally come up with this reply: SELECT DeptID, MIN(Wage) AS LowestWage FROM Personnel GROUP BY DeptID Of course, the example above is pretty basic. Your questions (at least the ones I've seen here) are a lot more complex. Trying to answer them without knowing exactly how the tables look (i.e. columns, datatypes, constraints, indexes, defaults, ... everything you'd see in a CREATE TABLE statement), what kind of data is in them (i.e. the INSERT statements) and what results you expect to get makes it more an exercise at guessing, or even mind reading, than an exercise in writing SQL. I'm pretty good at writing SQL. I think I help a lot of people with my skills in this and other forums and groups. But my clairvoyance skills are lousy. In other words: if you don't explain the problem clear enough, you're likely to get no answer or a wrong answer from me - and probably from others as well. From experience, I know that most probles are best explained by posting CREATE TABLE statements, INSERT statements, expected results and a short explanation. Check out www.aspfaq.com/5006 for more information about this and for some techniques that can help you assemble the information for your posts. I hope this helps. -- Hugo Kornelis, SQL Server MVP|||thanks, I was using the case statement to determine which to return back as FeeSchedule. It's dependent on the lenght of a certain field. I the field was>3 I needed to do a lookup else, juse use that field.|||thanks

NNTP User
. Sometimes I don't have all those statements created because the statement I'm working on is it and is my only attempt/approach at the time! Yes, I could have posted some data examples though. Thanks for your explanation.

Help with CASE statement

I need some help with a case statement that I'm trying to write. I wrote thi
s
query a couple of w

s ago, but then my sql server had a hardware failure
and I lost all of my stored procs and now I can't remember how I implemented
it. I'm trying to write a dynamic "IN" statement using a case statement, but
I can't get the syntax quite right. Here's an example of what I'm trying to
do (using different data). I realize this could be simplified using a decode
table, but that isn't an option in this case. My problem is how I'm handling
the part of the statement after each "THEN" statement (I might have used a
Convert statement, but I don't remember). I think it's reading my values as
one long string (i.e.-'Apples, Oranges, Bananas) instead of separate values
(i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
SELECT column1
FROM tblMyTable
WHERE column2 IN(
CASE @.inputVariable
WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
END
)You cannot use in like that..
try this
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'fruit' and
column2 IN( 'Apples' ,'Oranges' ,'Bananas'))
or
(@.inputVariable = 'Vegetable' and column2 IN('Corn' ,'Green Beans'))
Hope this helps.
"Dustin" wrote:

> I need some help with a case statement that I'm trying to write. I wrote t
his
> query a couple of w

s ago, but then my sql server had a hardware failure
> and I lost all of my stored procs and now I can't remember how I implement
ed
> it. I'm trying to write a dynamic "IN" statement using a case statement, b
ut
> I can't get the syntax quite right. Here's an example of what I'm trying t
o
> do (using different data). I realize this could be simplified using a deco
de
> table, but that isn't an option in this case. My problem is how I'm handli
ng
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values a
s
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate value
s
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||Hi, Justin
The best way would be to use a table for this, but since you say that
it's not an option, try something like this:
SELECT column1
FROM tblMyTable
WHERE @.inputVariable='Fruit' AND column2 IN
('Apples','Oranges','Bananas')
OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
Razvan|||Dustin,
CASE is an expression (not a statement) that returns a scalar value. It
cannot return a set of values. In your code the CASE expression generates a
single character string value made of the concatenated elements. e.g., when
@.inputVariable = 'Fruit', you logically get:
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples, Oranges, Bananas');
Which is logically equivalent to:
SELECT column1
FROM tblMyTable
WHERE column2 = 'Apples, Oranges, Bananas';
Which probably isn't what you're after.
Here are a couple of options (not tested); I suspect the former will perform
better:
IF @.inputVariable = 'Fruit'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Apples', 'Oranges', 'Bananas');
ELSE IF @.inputVariable = 'Vegetable'
SELECT column1
FROM tblMyTable
WHERE column2 IN('Corn', 'Green Beans');
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Dustin" <Dustin@.discussions.microsoft.com> wrote in message
news:475EA188-4D13-4613-8478-E9C268572184@.microsoft.com...
>I need some help with a case statement that I'm trying to write. I wrote
>this
> query a couple of w

s ago, but then my sql server had a hardware failure
> and I lost all of my stored procs and now I can't remember how I
> implemented
> it. I'm trying to write a dynamic "IN" statement using a case statement,
> but
> I can't get the syntax quite right. Here's an example of what I'm trying
> to
> do (using different data). I realize this could be simplified using a
> decode
> table, but that isn't an option in this case. My problem is how I'm
> handling
> the part of the statement after each "THEN" statement (I might have used a
> Convert statement, but I don't remember). I think it's reading my values
> as
> one long string (i.e.-'Apples, Oranges, Bananas) instead of separate
> values
> (i.e.-'Apples', 'Oranges', 'Bananas'). Thanks in advance for your help.
> SELECT column1
> FROM tblMyTable
> WHERE column2 IN(
> CASE @.inputVariable
> WHEN 'Fruit' THEN 'Apples' + ',' + 'Oranges' + ', ' + 'Bananas'
> WHEN 'Vegetable' THEN 'Corn' + ', ' + 'Green Beans'
> END
> )|||of course with the braces in place ;)
--
"Razvan Socol" wrote:

> Hi, Justin
> The best way would be to use a table for this, but since you say that
> it's not an option, try something like this:
> SELECT column1
> FROM tblMyTable
> WHERE @.inputVariable='Fruit' AND column2 IN
> ('Apples','Oranges','Bananas')
> OR @.inputVariable='Vegetable' AND column2 IN ('Corn','Green Beans')
> Razvan
>|||I believe it will work fine without the extra parenthesis, but the
parenthesis will make it easier to read and leave no question as to what is
intended.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
> of course with the braces in place ;)
> --
>
>
> "Razvan Socol" wrote:
>|||boy.. thats a news to me.. Always thought AND and OR had the same precedence
.
thanks for pointing that out. Though I will never use it without braces :)
--
"Jim Underwood" wrote:

> I believe it will work fine without the extra parenthesis, but the
> parenthesis will make it easier to read and leave no question as to what i
s
> intended.
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:57F875E5-4A0C-4DBA-956D-7EE9640E5D1A@.microsoft.com...
>
>|||I never use it without parenthesis either. It just gets too confusing, and
too easy to get the wrong results. That and I still forget sometimes that
AND has a higher precedence than OR, so the parens protect me from myself.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:7EAE1F57-00B1-4002-8B32-E6BCC8DDCB84@.microsoft.com...
> boy.. thats a news to me.. Always thought AND and OR had the same
precedence..
> thanks for pointing that out. Though I will never use it without braces :)
> --
>
>
> "Jim Underwood" wrote:
>
is
that|||Thank you all for your help. I was able to solve the problem using the
following suggested syntax:
SELECT column1
FROM tblMyTable
WHERE (@.inputVariable = 'Fruit'
AND column2 IN('Apples', 'Oranges', 'Bananas'))
OR (@.inputVariable = 'Vegetable'
AND column2 IN('Corn', 'Green Beans'));

Help with CASE in Stored Procedure

hi guys! Help please..I'm new to sotred procedure. Currently, i have a stored procedure (see Below) but i can't figure out on how to solve the errors(see below). What im trying to do with my stored procedure is to take two(2) parameters, the string to be search(@.Search_Arg) and the search by(@.Search_by) Either by Login name or by firstname. Under my CASE statement, When the @.Search_by is equal to Username i want to select the user with User_login equals to @.Search_Arg and when the @.Search_by is equal to Firstname it will select all user with User_FirstName equals to @.Search_Arg...Any help please..Thanks in advance!

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN

CASE @.Search_By
WHEN 'Username' THEN SELECT * FROM RCPS_UserAccount WHERE User_login = @.Search_Arg
WHEN 'Firstname' THEN SELECT * FROM RCPS_UserAccount WHERE User_FirstName = @.Search_Arg
END
END

Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 7
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 10
Incorrect syntax near the keyword 'END'.what you have there is a dynamic search condition. read this for various techniques:

http://www.sommarskog.se/dyn-search.html

in particular, this might suit you:

select col1, col2, col3 from RCPS_UserAccount
where
(User_login = @.user_login or @.user_login is null) and
(User_FirstName = @.user_firstname or @.user_firstname is null)|||Case can return only one value at a time
use of 'Select *' is not allowed in case statement. What you can do is :

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN
SELECT * FROM RCPS_UserAccount
WHERE
CASE
When @.Search_By = 'Username' then
User_login
When @.Search_By = 'Firstname' then
User_FirstName
End
= @.Search_Arg
END
END

...but I think way suggested by jezemine is better than this one.sql

Help with CASE in Stored Procedure

Hi there,
I have a table with the following columns:
wgt_id
wgt_lower_weight
wgt_higher_weight
wgt_country
wgt_Parcel_Price
wgt_RMSD_Price
wgt_RMSD_Pre_9_Price
wgt_RMSD_Pre_1_Price
wgt_RMSD_Sat_Price
wgt_Citylink_Price
wgt_Citylink_Sat_Price
I want to create a stored procedure that is passed:
weight
country
shippingtype
This is what I have so far:
SELECT *
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower < @.Weight)
AND (wgt_weight_higher > @.Weight)
I need to add the parameter shippingtype. This will select the
matching column and output the price. I think it needs the use of CASE
but I can't figure it out. Can anyone help me create my store
procedure?This is what I have tried but its not returning the Price, its
returning all the rows but blank.
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(10))
AS
SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
Price
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO
Any ideas where I am going wrong?|||(a) never use sp_ prefix on stored procedures.
(b) provide DDL for the tblShippingRates table (another questionable prefix,
btw), some sample data, and desired results. I have no idea what data is in
the table, what parameter values you are passing in, what should be returned
by the query, and what "all the rows but blank" means...
Aaron Bertrand
SQL Server MVP
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186057919.366437.11420@.d55g2000hsg.googlegroups.com...
> This is what I have tried but its not returning the Price, its
> returning all the rows but blank.
> CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
> @.Weight decimal(12,2), @.Shipping varchar(10))
> AS
> SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
> WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
> WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
> WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
> WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
> WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
> Price
> FROM dbo.tblShippingRates
> WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
> AND (wgt_weight_higher >= @.Weight)
> GO
> Any ideas where I am going wrong?
>|||Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Price wgt_Citylink_Sat_Pric
e
0 0.5 uk =A34.90 =A313.00 =A35.60 =A38.10 =A310.40 =A325.40
0=2E51 1 uk =A36.20 =A315.00 =A37.00 =A39.50 =A310.40 =A325.40
1=2E01 2 uk =A36.70 =A318.70 =A39.20 =A311.70 =A310.40 =A325.40
2=2E01 4 uk =A39.70 =A323.00 =A310.40 =A325.40
4=2E01 6 uk =A323.00 =A310.40 =A325.40
6=2E01 8 uk =A323.00 =A310.40 =A325.40
8=2E01 10 uk =A323.00 =A310.40 =A325.40
10.01 15 uk =A312.90 =A327.90
15.01 20 uk =A315.40 =A330.40
I am passing the stored procedure country =3D uk weight =3D 2 and shipping
=3D RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve|||I worked it out!
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(12))
AS
SELECT ShippingPrice = CASE @.Shipping WHEN 'Parcel' THEN
dbo.tblShippingRates.wgt_Parcel_Price
WHEN 'RMSD9' THEN dbo.tblShippingRates.wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN dbo.tblShippingRates.wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN dbo.tblShippingRates.wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN dbo.tblShippingRates.wgt_Citylink_Price
WHEN 'CitylinkSat' THEN dbo.tblShippingRates.wgt_Citylink_Sat_Price
END
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO|||It seems that the selection criteria is based on weight and country. I
don't understand the RMSD9 bit. In your case, the query would be something
like:
select
wgt_Parcel_Price
from
MyTable
where
wgt_country = @.country
and
@.weight between wgt_weight_lower and wgt_weight_higher
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186060353.152602.170690@.q75g2000hsh.googlegroups.com...
Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Price wgt_Citylink_Sat_Price
0 0.5 uk 4.90 13.00 5.60 8.10 10.40 25.40
0.51 1 uk 6.20 15.00 7.00 9.50 10.40 25.40
1.01 2 uk 6.70 18.70 9.20 11.70 10.40 25.40
2.01 4 uk 9.70 23.00 10.40 25.40
4.01 6 uk 23.00 10.40 25.40
6.01 8 uk 23.00 10.40 25.40
8.01 10 uk 23.00 10.40 25.40
10.01 15 uk 12.90 27.90
15.01 20 uk 15.40 30.40
I am passing the stored procedure country = uk weight = 2 and shipping
= RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve|||I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
etc.). So the values in the different columns in the actual table are
actually relevant... the shipping price is not always wgt_Parcel_Price.
Aaron Bertrand
SQL Server MVP
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eH8U5iQ1HHA.464@.TK2MSFTNGP02.phx.gbl...
> It seems that the selection criteria is based on weight and country. I
> don't understand the RMSD9 bit. In your case, the query would be
> something
> like:
> select
> wgt_Parcel_Price
> from
> MyTable
> where
> wgt_country = @.country
> and
> @.weight between wgt_weight_lower and wgt_weight_higher
> --
> Tom|||On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.|||Oh, OK. So you'd have a CASE:
CASE @.shipping
WHEN 'RMSD9' then wgt_RMSD_Pre9_Price
WHEN 'RMSD1' then wgt_RMSD_Pre1_Price
..
END
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186062055.667781.242240@.r34g2000hsd.googlegroups.com...
On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.|||>I worked it out!
Your procedure suddenly started returning data because you changed AS
ShippingPrice to ShippingPrice = and CASE WHEN @.Shipping = to CASE @.Shipping
WHEN ? That doesn't seem right. Anyway, how about readability? Do you
need to repeat dbo.tblShippingRates 18 times?
CREATE PROCEDURE dbo.usp_GetShippingCharge
@.Country VARCHAR(2),
@.Weight DECIMAL(12,2),
@.Shipping VARCHAR(12)
AS
BEGIN
SET NOCOUNT ON;
SELECT
ShippingPrice = CASE @.Shipping
WHEN 'Parcel' THEN wgt_Parcel_Price
WHEN 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN wgt_Citylink_Price
WHEN 'CitylinkSat' THEN wgt_Citylink_Sat_Price
END
FROM
dbo.tblShippingRates
WHERE
wgt_country = @.Country
AND (@.Weight BETWEEN wgt_weight_lower AND wgt_weight_higher);
END
GO
Finally, since this will only ever return one column and *should* only be
returning one row, why not make it a scalar function, or at least capture
the data via an output parameter?
Aaron Bertrand
SQL Server MVP

Help with CASE in Stored Procedure

Hi there,
I have a table with the following columns:
wgt_id
wgt_lower_weight
wgt_higher_weight
wgt_country
wgt_Parcel_Price
wgt_RMSD_Price
wgt_RMSD_Pre_9_Price
wgt_RMSD_Pre_1_Price
wgt_RMSD_Sat_Price
wgt_Citylink_Price
wgt_Citylink_Sat_Price
I want to create a stored procedure that is passed:
weight
country
shippingtype
This is what I have so far:
SELECT *
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower < @.Weight)
AND (wgt_weight_higher > @.Weight)
I need to add the parameter shippingtype. This will select the
matching column and output the price. I think it needs the use of CASE
but I can't figure it out. Can anyone help me create my store
procedure?This is what I have tried but its not returning the Price, its
returning all the rows but blank.
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(10))
AS
SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
Price
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO
Any ideas where I am going wrong?|||(a) never use sp_ prefix on stored procedures.
(b) provide DDL for the tblShippingRates table (another questionable prefix,
btw), some sample data, and desired results. I have no idea what data is in
the table, what parameter values you are passing in, what should be returned
by the query, and what "all the rows but blank" means...
--
Aaron Bertrand
SQL Server MVP
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186057919.366437.11420@.d55g2000hsg.googlegroups.com...
> This is what I have tried but its not returning the Price, its
> returning all the rows but blank.
> CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
> @.Weight decimal(12,2), @.Shipping varchar(10))
> AS
> SELECT CASE WHEN @.Shipping = 'Parcel' THEN wgt_Parcel_Price
> WHEN @.Shipping = 'RMSD9' THEN wgt_RMSD_Pre9_Price
> WHEN @.Shipping = 'RMSD1' THEN wgt_RMSD_Pre1_Price
> WHEN @.Shipping = 'RMSDSat' THEN wgt_RMSD_Sat_Price
> WHEN @.Shipping = 'Citylink' THEN wgt_Citylink_Price
> WHEN @.Shipping = 'CitylinkSat' THEN wgt_Citylink_Sat_Price END AS
> Price
> FROM dbo.tblShippingRates
> WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
> AND (wgt_weight_higher >= @.Weight)
> GO
> Any ideas where I am going wrong?
>|||Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Price wgt_Citylink_Sat_Price
0 0.5 uk =A34.90 =A313.00 =A35.60 =A38.10 =A310.40 =A325.40
0=2E51 1 uk =A36.20 =A315.00 =A37.00 =A39.50 =A310.40 =A325.40
1=2E01 2 uk =A36.70 =A318.70 =A39.20 =A311.70 =A310.40 =A325.40
2=2E01 4 uk =A39.70 =A323.00 =A310.40 =A325.40
4=2E01 6 uk =A323.00 =A310.40 =A325.40
6=2E01 8 uk =A323.00 =A310.40 =A325.40
8=2E01 10 uk =A323.00 =A310.40 =A325.40
10.01 15 uk =A312.90 =A327.90
15.01 20 uk =A315.40 =A330.40
I am passing the stored procedure country =3D uk weight =3D 2 and shipping
=3D RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve|||I worked it out!
CREATE PROCEDURE dbo.sp_GetShippingCharge(@.Country varchar(2),
@.Weight decimal(12,2), @.Shipping varchar(12))
AS
SELECT ShippingPrice = CASE @.Shipping WHEN 'Parcel' THEN
dbo.tblShippingRates.wgt_Parcel_Price
WHEN 'RMSD9' THEN dbo.tblShippingRates.wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN dbo.tblShippingRates.wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN dbo.tblShippingRates.wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN dbo.tblShippingRates.wgt_Citylink_Price
WHEN 'CitylinkSat' THEN dbo.tblShippingRates.wgt_Citylink_Sat_Price
END
FROM dbo.tblShippingRates
WHERE (wgt_country = @.Country) AND (wgt_weight_lower <= @.Weight)
AND (wgt_weight_higher >= @.Weight)
GO|||It seems that the selection criteria is based on weight and country. I
don't understand the RMSD9 bit. In your case, the query would be something
like:
select
wgt_Parcel_Price
from
MyTable
where
wgt_country = @.country
and
@.weight between wgt_weight_lower and wgt_weight_higher
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186060353.152602.170690@.q75g2000hsh.googlegroups.com...
Hi Aaron,
Firstly thank you for helping! I am a self taught asp developer, so
wasn't aware of the naming conventions, I will change them straight
away.
wgt_weight_lower/wgt_weight_higher/wgt_country/wgt_Parcel_Price/
wgt_RMSD_Pre9_Price/wgt_RMSD_Pre1_Price/wgt_RMSD_Sat_Price/
wgt_Citylink_Price wgt_Citylink_Sat_Price
0 0.5 uk £4.90 £13.00 £5.60 £8.10 £10.40 £25.40
0.51 1 uk £6.20 £15.00 £7.00 £9.50 £10.40 £25.40
1.01 2 uk £6.70 £18.70 £9.20 £11.70 £10.40 £25.40
2.01 4 uk £9.70 £23.00 £10.40 £25.40
4.01 6 uk £23.00 £10.40 £25.40
6.01 8 uk £23.00 £10.40 £25.40
8.01 10 uk £23.00 £10.40 £25.40
10.01 15 uk £12.90 £27.90
15.01 20 uk £15.40 £30.40
I am passing the stored procedure country = uk weight = 2 and shipping
= RMSD9
I am expecting Price to be returned as 6.70
The results that I am getting back at the moment are all the column
names from the table with no data in them at all. It's not like an
empty recordset, this is a row with nothing in it. I am expecting just
the one column, well, the alias called Price.
Cheers,
Steve|||I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
etc.). So the values in the different columns in the actual table are
actually relevant... the shipping price is not always wgt_Parcel_Price.
--
Aaron Bertrand
SQL Server MVP
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eH8U5iQ1HHA.464@.TK2MSFTNGP02.phx.gbl...
> It seems that the selection criteria is based on weight and country. I
> don't understand the RMSD9 bit. In your case, the query would be
> something
> like:
> select
> wgt_Parcel_Price
> from
> MyTable
> where
> wgt_country = @.country
> and
> @.weight between wgt_weight_lower and wgt_weight_higher
> --
> Tom|||On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.|||Oh, OK. So you'd have a CASE:
CASE @.shipping
WHEN 'RMSD9' then wgt_RMSD_Pre9_Price
WHEN 'RMSD1' then wgt_RMSD_Pre1_Price
...
END
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dooza" <doozadooza@.gmail.com> wrote in message
news:1186062055.667781.242240@.r34g2000hsd.googlegroups.com...
On Aug 2, 2:37 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> I think they are zones and/or delivery speeds (e.g. 1 day, 2 day, 9 day,
> etc.). So the values in the different columns in the actual table are
> actually relevant... the shipping price is not always wgt_Parcel_Price.
Each column that ends in Price is a different courier option.|||>I worked it out!
Your procedure suddenly started returning data because you changed AS
ShippingPrice to ShippingPrice = and CASE WHEN @.Shipping = to CASE @.Shipping
WHEN ? That doesn't seem right. Anyway, how about readability? Do you
need to repeat dbo.tblShippingRates 18 times?
CREATE PROCEDURE dbo.usp_GetShippingCharge
@.Country VARCHAR(2),
@.Weight DECIMAL(12,2),
@.Shipping VARCHAR(12)
AS
BEGIN
SET NOCOUNT ON;
SELECT
ShippingPrice = CASE @.Shipping
WHEN 'Parcel' THEN wgt_Parcel_Price
WHEN 'RMSD9' THEN wgt_RMSD_Pre9_Price
WHEN 'RMSD1' THEN wgt_RMSD_Pre1_Price
WHEN 'RMSDSat' THEN wgt_RMSD_Sat_Price
WHEN 'Citylink' THEN wgt_Citylink_Price
WHEN 'CitylinkSat' THEN wgt_Citylink_Sat_Price
END
FROM
dbo.tblShippingRates
WHERE
wgt_country = @.Country
AND (@.Weight BETWEEN wgt_weight_lower AND wgt_weight_higher);
END
GO
Finally, since this will only ever return one column and *should* only be
returning one row, why not make it a scalar function, or at least capture
the data via an output parameter?
--
Aaron Bertrand
SQL Server MVP|||On Thu, 02 Aug 2007 06:22:29 -0700, Dooza wrote:
>I worked it out!
Hi Dooza,
Good for you.
However, I think you'd be better off with a different design of your
table. As it is, you'll have to keep adding and removing columns and
changing your code every time a new courier option comes around, when an
option is removed, or even when an option is renamed.
Instead of different price columns for each courier option, you should
have one column Price and one column CourierOption. The latter should of
course be included in the table's key. That would make this query lots
easier!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Aug 2, 9:06 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Thu, 02 Aug 2007 06:22:29 -0700, Dooza wrote:
> >I worked it out!
> Hi Dooza,
> Good for you.
> However, I think you'd be better off with a different design of your
> table. As it is, you'll have to keep adding and removing columns and
> changing your code every time a new courier option comes around, when an
> option is removed, or even when an option is renamed.
> Instead of different price columns for each courier option, you should
> have one column Price and one column CourierOption. The latter should of
> course be included in the table's key. That would make this query lots
> easier!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Hi Hugo,
You are correct, and this is what I have ended up doing, as my
previous attempt didn't allow me to also save the type of shipping in
the database. The way I am doing it now is much better. I can now
create a drop down list with the available options for that particular
weight, before I couldn't do that, and I now have an ID for the
shipping type that I can store in the database with the order.
The user is now presented with the drop down list to select the type
of shipping, once selected the id of the shipping type is inserted
into the database, I will then lookup the ID and pass the price to the
cart to be included with the total. A much neater solution.
Thank you all for steering me in the right direction!
Dooza

Help with CASE and LIKE

Hello guys! i'm having problem with my stored procedure..can anybody please help me.
I have a stored procedure below that is successfully executed/saved/"Compiled"(whatever you called it) but when I try to use it by supplying value to its paramaters it throws an error (Please see the error message below). I suspected that the error occurs from line with the Bold Letters becuase "@.SeacrhArg" variable is of type varchar while columns "Transac.Item_ID" and "Transac.Item_TransTicketNo" is of type Int. What you think guys?

ERROR:

Msg 245, Level 16, State 1, Procedure sp_Transaction_Search, Line 9
Syntax error converting the varchar value 'Manlagnit' to a column of data type int.

STORED PROCEDURE:

USE [RuslinCellPawnShoppeDB]
GO
/****** Object: StoredProcedure [dbo].[sp_Transaction_Search] Script Date: 09/04/2007 08:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Transaction_Search]
@.SeacrhArg varchar(20),
@.SearchBy varchar(20),
@.TransType varchar(20),
@.FromDate datetime,
@.Todate datetime
AS
BEGIN
SELECT Customer.Customer_LastName,Customer.Customer_Middl eInitial, Customer.Customer_FirstName, Customer.Customer_Address,
Items.Item_Description,Items.Item_Principal, Transac.ItemTrans_Date_Granted, Transac.ItemTrans_DateCreated,
Transac.ItemTrans_Status, Transac.Item_ID,Transac.Item_TransID,Transac.Item_ TransTicketNo

FROM RCPS_TF_ItemTransaction Transac
INNER JOIN RCPS_Customer Customer
ON Transac.CustomerID = Customer.CustomerID
INNER JOIN RCPS_Items Items
ON Items.ItemID = Transac.Item_ID

WHERE
CASE
WHEN @.SearchBy = 'FirstName' THEN Customer.Customer_FirstName
WHEN @.SearchBy = 'LastName' THEN Customer.Customer_LastName
WHEN @.SearchBy = 'Item ID' THEN Transac.Item_ID
WHEN @.SearchBy = 'Ticket No' THEN Transac.Item_TransTicketNo
END
LIKE @.SeacrhArg AND

Transac.ItemTrans_DateCreated BETWEEN @.FromDate AND dateadd(day,1,@.Todate) AND
(
(@.TransType = 'Pawned' AND Transac.ItemTrans_Status = 1) OR
(@.TransType = 'Renewed' AND Transac.ItemTrans_Status = 2) OR
(@.TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3) OR
(@.TransType = 'Sold' AND Transac.ItemTrans_Status = 5)
)
END

CALL STORED PROCEDURE

USE [RuslinCellPawnShoppeDB]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[sp_Transaction_Search]
@.SeacrhArg = '%man%',
@.SearchBy = 'LastName',
@.TransType = 'Pawned',
@.FromDate = N'9/01/2007 12:00:00 AM',
@.Todate = N'9/6/2007 12:00:00 AM'

SELECT 'Return Value' = @.return_value

GOI suspect Transac.ItemTrans_Status is a varchar and that is where the problem is|||WHERE CASE
WHEN @.SearchBy = 'FirstName' AND Customer.Customer_FirstName LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'LastName' THEN Customer.Customer_LastName LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'Item ID' THEN CONVERT(VARCHAR, Transac.Item_ID) LIKE @.SeacrhArg THEN 1
WHEN @.SearchBy = 'Ticket No' THEN CONVERT(VARCHAR, Transac.Item_TransTicketNo) LIKE @.SeacrhArg THEN 1
ELSE 0
END = 1
AND Transac.ItemTrans_DateCreated >= @.FromDate
AND Transac.ItemTrans_DateCreated < DATEADD(DAY, 1, @.Todate)
AND 1 = CASE
WHEN @.TransType = 'Pawned' AND Transac.ItemTrans_Status = 1 THEN 1
WHEN @.TransType = 'Renewed' AND Transac.ItemTrans_Status = 2 THEN 1
WHEN @.TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3 THEN 1
WHEN @.TransType = 'Sold' AND Transac.ItemTrans_Status = 5 THEN 1
ELSE 0
END|||Thanks a loT Peso..But I preferred to use BETWEEN..AND.. for date unless you have there agood reason to use the <>= sign..But anyway thanks alot!|||if you use BETWEEN, you inadvertently get too may records.

BETWEEN '20070101' AND '20071231' will NOT get the records dated "20071231 10:14:23"

BETWEEN '20070101' AND '20080101' will get the records dated "20071231 10:14:23" but ALSO the records dated "20080101 00:00:00".

>= '20070101' <'20080101' will get the records dated "20071231 10:14:23" but NOT the records dated "20080101 00:00:00".

help with case

Good afternoon...
I look for a forum which one I can as for queries.
If this isn't the one, sorry. Please say me where I can find it.
My problem:
I have a table where ParentID is foreign key to ID in the same table.
the others columns are type, value and author.
Type can get 3 values: FAMILY, GENUS and SPECIES.
To take all the FAMILY-GENUS-SPECIES from the table I use:
select distinct
case 'FAMILY'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as FAMILY,
case 'GENRE'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as GENRE,
case 'SPECIES'
when upper(tn.Type) then tn.TaxonName
when upper(tn1.Type) then tn1.TaxonName
when upper(tn2.Type) then tn2.TaxonName
else NULL end as SPECIES
from TaxonName TN
left outer join TaxonName TN1 on TN1.TaxonNameID = TN.ParentTaxonNameID
left outer join TaxonName TN2 on TN2.TaxonNameID = TN.ParentTaxonNameID
and it worked fine. But I want to take the major level author not NULL
too. For example:
if SPECIES is NOT NULL return author from register where type is SPECIES;
else if GENRE is NOT NULL return author from register where type is GENRE;
else if FAMILY is NOT NULL return author from register where type is FAMILY;
Anyone can help me?
thanks for the help
Giscar Paiva
www.cria.org.brit's not entirely clear what you want, but perhaps this will help:
SELECT isnull(tn.Author,isnull(tn1.Author, tn2.Author)) as MajorAuthor,
CASE etc...
Cheers
Will|||Take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Or wait for Joe Celko to guide you to one of his books: "Trees and
Hierarchies".
ML
http://milambda.blogspot.com/|||Will wrote:
> it's not entirely clear what you want, but perhaps this will help:
I'll try to iluminate you more...
in the table, I can have the author in all records. For Example...
if my table is:
ID ParentID Type Value Author
---
1 <null> FAMILY Leguminosae Britton
2 1 GENUS Leucaena Rose
3 2 SPECIES diversifolia Zarate
---
I want the query returns:
FAMILY GENUS SPECIES AUTHOR
----
Leguminosae <null> <null> Britton
Leguminosae Leucaena <null> Rose
Leguminosae Leucaena diversifolia Zarate

> SELECT isnull(tn.Author,isnull(tn1.Author, tn2.Author)) as MajorAuthor,
> CASE etc...
The problem is that I don't know if the tn is Family, Genus OR Species,
'cause my CASE.
But thanks anyway...
Giscar Paiva
www.cria.org.br|||add this as another column
coalesce(tn.author, tn1.author,tn2.author)
Btw, just observed that your join condition is wrong. TN1 and TN2 are joined
the same way to TN. I am surprised you are saying its working fine.|||Omnibuzz wrote:
> Btw, just observed that your join condition is wrong. TN1 and TN2 are join
ed
> the same way to TN. I am surprised you are saying its working fine.
U're right... I just saw that after I post...
Thank u...sql

Help with Case

I have the following data with following conditions:

Old Type New Type

Number: 01 in table1 with Connection : 'J' in table2 'GE'

Number: 01 in table1 with Connection : 'K' in table2 'GPE'

Number: 01 in table1 with Connection: 'L' In table2 'GPA'

Number: 02 in table1 with Connection: 'I' in table2 'I02'

I used queries like:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

ELSE CASE WHEN t2.Connection = 'K' THEN 'GPE'

ELSE CASE WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END END END END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID).

I'll take an example:

An ID can have multiple connections attached to it. Let Say:

Table1 ID : 0001

Number: 01

Table2 ID: 0001

Connection: J, K, S, O, P.

Now, when i do the above query, i would get a result of 3 rows new case types for ID 0001.

I would like to just get one, and the first priority will be put on the condition of (GE).

everytime I see a Number '01' and Connection 'J', I would ignore the other connection. If i do not see Number = '01' and Connection: 'J'. i would go for the rest of the conditions.

Hope you guys see what problem i am having.

Thanks,

Jul.

Try this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

WHEN t2.Connection = 'K' THEN 'GPE'

WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

Since I do not like repeating the condition, I would do this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

|||Still, i get multiple results.....Thanks though.|||So you want only one row returned?

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN (

select id, min(Connection) [Connection]

from Table2

group by id) AS t2

ON (t1.ID = t2.ID)

|||It works....Thanks sooo much.....

Friday, March 23, 2012

Help with accessing data from a webform please...

Ok,
I have a web app that calls several very complex (at least to me) sql
queries like this for example:
SELECT t1.MemberId, t1.PeriodID,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount WHEN
t2.amountTypeId = 23 THEN -t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount WHEN
t2.amountTypeId = 24 THEN -t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount WHEN
t2.amountTypeId = 21 THEN -t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN -t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount WHEN
t2.amountTypeId = 26 THEN -t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN -t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount WHEN
t2.amountTypeId = 14 THEN -t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount WHEN
t2.amountTypeId = 15 THEN -t2.amount END) AS Spent
FROM tblTravelDetail t1 INNER JOIN
tblTravelDetailAmount t2 ON t1.TravelDetailId =
t2.TravelDetailId INNER JOIN
tblTravelDetail t3 ON t2.TravelDetailId =
t3.TravelDetailId INNER JOIN
tblTravelDetailMember t4 ON t3.TravelDetailId =
t4.TravelDetailId INNER JOIN
tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
amount_type t6 ON t2.amountTypeId =
t6.amount_type_id
WHERE (t1.MemberId = 12391) AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID...
It is so slow on our very fast servers. Would an XML connection with
the "SQL XML Support in IIS" be an option? How can I get this view to
be faster for users to get over the net?
Thanks for any help,
Trinttrint
Have you defined any indexes on the table?
Have you seen an execution plan of the query?
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123496340.977863.196110@.g47g2000cwa.googlegroups.com...
> Ok,
> I have a web app that calls several very complex (at least to me) sql
> queries like this for example:
> SELECT t1.MemberId, t1.PeriodID,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount WHEN
> t2.amountTypeId = 23 THEN -t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount WHEN
> t2.amountTypeId = 24 THEN -t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount WHEN
> t2.amountTypeId = 21 THEN -t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN -t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount WHEN
> t2.amountTypeId = 26 THEN -t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN -t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount WHEN
> t2.amountTypeId = 14 THEN -t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount WHEN
> t2.amountTypeId = 15 THEN -t2.amount END) AS Spent
> FROM tblTravelDetail t1 INNER JOIN
> tblTravelDetailAmount t2 ON t1.TravelDetailId =
> t2.TravelDetailId INNER JOIN
> tblTravelDetail t3 ON t2.TravelDetailId =
> t3.TravelDetailId INNER JOIN
> tblTravelDetailMember t4 ON t3.TravelDetailId =
> t4.TravelDetailId INNER JOIN
> tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id
> WHERE (t1.MemberId = 12391) AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID...
> It is so slow on our very fast servers. Would an XML connection with
> the "SQL XML Support in IIS" be an option? How can I get this view to
> be faster for users to get over the net?
> Thanks for any help,
> Trint
>|||I have not defined any indexes on the table ( none are defined ). The
tables are created by another department, but I have exported all of
them as 'copies' to my local machine for my c# app development. I am
willing to go for it though and then present this, if faster, to my
boss to get the changes in the real instances of SQL Server. I will
research on how to do this now.
Any help on making this faster (or easier) is appreciated.
Thanks,
Trintsql

Monday, March 19, 2012

Help with a CASE statement.

I am trying to get this case statement to work where it will
concatenate the values into a single string.
If I place anything with an equals sign such as (SELECT @.status_message
= @.status_message + 'variable') it give me an error. Any ideas. It
probably obvious but I am failing to see it.
DECLARE @.status_message varchar(100)
Set @.status_message = ''
SELECT * from master..sysdatabases
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
WHEN (status & 4194304) <> 0 THEN 'autoshrink'
WHEN (status & 32768) <> 0 THEN 'emergency mode'
WHEN (status & 4096) <> 0 THEN 'single user'
WHEN (status & 2048) <> 0 THEN 'dbo use only'
WHEN (status & 1024) <> 0 THEN 'read only'
WHEN (status & 512) <> 0 THEN 'offline'
WHEN (status & 256) <> 0 THEN 'not recovered'
WHEN (status & 128) <> 0 THEN 'recovering'
WHEN (status & 64) <> 0 THEN 'pre recovery'
WHEN (status & 32) <> 0 THEN 'loading'
WHEN (status & 16) <> 0 THEN 'torn page detection'
WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE 'Unknown'
end
from master..sysdatabasesYou cannot assign values to variables in the same select statement in which
you also return a result-set to the client.
E.g. this is not allowed:
select <column list>
,@.<variable> = <some column>
from <table>
Put the variable assignment ina separate query, then include it in the one
that returns the result to the client.
select @.<variable> = <some column>
from <table>
select <column list>
,@.<variable> as <variable name>
from <table>
Does that answer your question?
ML
http://milambda.blogspot.com/|||Sort of, I know what the problems is, I am looking for a work around,
or a different solution that might achieve the same results. someone
said that i sould thorugh it into a loop, so i might try that.|||Anyone. I must be totaly brainfried.|||>> I am trying to get this case statement to work where it will concatenate
Which values are you talking about? The string values in the THEN clause of
the CASE? Please elaborate on what you are trying to do here.
What is the error message? Which piece of code are you trying to run to
generate the error?
Anith|||I figured it out
/ ****************************************
*******************
Returns a the STATUS of all databases on a server in English
****************************************
*******************/
SELECT @.@.SERVERNAME AS SERVER, VERSION, LEFT(name,30) AS [Databases],
DBID,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',Aautoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',Select Into / Bulk Copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',Truncate Log on Checkpoint' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',Torn Page Detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',Loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',Pre-Recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',Recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',Not Recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',Offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',Read Only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE Only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',Single User' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',Emergency Mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',Cleanly Shutdown' END,
2,8000) AS OPTIONS_1,
SUBSTRING(CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default'
END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END
+
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor'
END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit'
END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS OPTIONS_2, CMPTLEVEL, FILENAME
FROM master..sysdatabases|||On 27 Feb 2006 14:48:32 -0800, Matthew wrote:

>I am trying to get this case statement to work where it will
>concatenate the values into a single string.
>If I place anything with an equals sign such as (SELECT @.status_message
>= @.status_message + 'variable') it give me an error. Any ideas. It
>probably obvious but I am failing to see it.
>DECLARE @.status_message varchar(100)
>Set @.status_message = ''
>SELECT * from master..sysdatabases
>SELECT name, dbid, status, cmptlevel, filename,
>CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
> WHEN (status & 4194304) <> 0 THEN 'autoshrink'
> WHEN (status & 32768) <> 0 THEN 'emergency mode'
> WHEN (status & 4096) <> 0 THEN 'single user'
> WHEN (status & 2048) <> 0 THEN 'dbo use only'
> WHEN (status & 1024) <> 0 THEN 'read only'
> WHEN (status & 512) <> 0 THEN 'offline'
> WHEN (status & 256) <> 0 THEN 'not recovered'
> WHEN (status & 128) <> 0 THEN 'recovering'
> WHEN (status & 64) <> 0 THEN 'pre recovery'
> WHEN (status & 32) <> 0 THEN 'loading'
> WHEN (status & 16) <> 0 THEN 'torn page detection'
> WHEN (status & 8) <> 0 THEN 'trunc. log on chkpt'
> WHEN (status & 4) <> 0 THEN 'select into/bulkcopy'
> WHEN (status & 1) <> 0 THEN 'autoclose'
> ELSE 'Unknown'
>end
>from master..sysdatabases
Hi Matthew,
Though concatenating these in a single string is presentation and
shouyld therefor be handled in the presentation layer, I'll give you a
working SQL solution:
SELECT name, dbid, status, cmptlevel, filename,
CASE WHEN (status & 1073741824) <> 0 THEN 'cleanly shutdown'
ELSE '' END
+ CASE WHEN (status & 4194304) <> 0 THEN 'autoshrink'
ELSE '' END
+ CASE WHEN (status & 32768) <> 0 THEN 'emergency mode'
ELSE '' END
(...)
+ CASE WHEN (status & 1) <> 0 THEN 'autoclose'
ELSE '' END
from master..sysdatabases
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:81e902hircfvvlacle0bg16ois08j7kmoq@.
4ax.com...
>.
> Though concatenating these in a single string is presentation and
> shouyld therefor be handled in the presentation layer,
>.
Hmm where have I heard this same idea before?
Once upon a time many were saying the same thing about
row numbering or worse line numbering.Surely this was
something to be done on the client whereas it had no
place being done on the server.(Not to mention the situation
was further muddled when the same voices used the very same
constructs as intermediate results in a query).Now along comes
Sql 2005 with some Sql-99 analytic functions like row_number()
and viola row numbering is no longer *presentation* and it's
perfectly fine to do on the server.To keep the analytics
company, 2005 introduces some more xml.Now we learn that
concatenating over rows can be done explicitly with it.
Presentation now has now become transformed into a serve-ice.
Since the underlying nature of 2005 hasn't changed
save for the mechanisms to do these things, it is now
*expedient* to do them on the server.Of course the shift
over even includes the dreaded crosstab with the ingenius
implementation of PIVOT.One must marvel at the ease of how
MS can change presentation to serve-ice:) But this shift may
come with some head scratching.Least some users wonder why
they can't simulate the serve-ice of 2005 to overcome the
presentation inherent in 2000.But one of the great things
about expediency is that it takes so shallow an explanation.
Flip flopping is alive and well in the world of sql.And for those
who actually study the subject academically I would say
't'where ignorance is bliss,t'is folly to be wise':)
$.03 from
www.rac4sql.net|||Well I guess as "proof" in SQL 2005 running the Execution Plan, the
cost associated for running query is exactly the same for both. So I
really boils down, which way is the "more correct" way to code.
e.g. which is better in practice.|||'which is better in practice' is just another way of saying what
is the most expedient way.Whatever works best for you:)
"Matthew" <MKruer@.gmail.com> wrote in message
news:1141230238.399541.6200@.z34g2000cwc.googlegroups.com...
> Well I guess as "proof" in SQL 2005 running the Execution Plan, the
> cost associated for running query is exactly the same for both. So I
> really boils down, which way is the "more correct" way to code.
> e.g. which is better in practice.
>

Tuesday, March 27, 2012

Help with case when

Help with CASE T-SQL

HELP with case statement [Divide by zero error encountered.] !

HELP with case statement [Divide by zero error encountered.] !

Help with Case Statement

Help with Case Statement

Help with CASE statement

Help with CASE in Stored Procedure

Help with CASE in Stored Procedure

Help with CASE in Stored Procedure

Help with CASE in Stored Procedure

Help with CASE and LIKE

help with case

Help with Case

Friday, March 23, 2012

Help with accessing data from a webform please...

Monday, March 19, 2012

Help with a CASE statement.

Restore Database

Blog Archive

About Me