Help with Cross-tab queries

How would you write the SQL statement to do a cross-tab queries.

My Table
DCNID-unique ID
AuditID-FK to the main table
DCN-int
Error Type (combo box with values tooth, date, provider, etc)
Line ID (combo box with values 1-40)
Adjustment Code-text

I would like my report to look like this.
DCN
LineID Tooth Date Provider
1 1 1
2 1
3
4
5
..
40

Thank you so much!

CristyYou could have done some research, this question has been asked and aswered thousands of times, here are some solutions from asktom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:7086279412131,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:419593546543,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:766825833740,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:925229353765,
:rolleyes:|||How would you write the SQL statement to do a cross-tab queries.that depends -- what database system are you using?

Help with creating SQL Statement to get data from single table...

Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table:

I have the following table of data

__User___Votes___Month

__A_______14______2
__A_______12______3
__A_______17______4
__A_______11______5

__B_______19______2
__B_______12______3
__B_______15______4

I want to beable to pull out the total number of votes a user has had over a period of months.

eg Total up each users users votes for months 4 and 5

that would give:

__User____TotalVotes

___A________28
___B________15

An added complecation is that user B does not have any data for month 5

Any help or pointers would be fanstatic

Many thanks

select sum(Votes)
from table
where month in (4, 5)
group by User|||Of cource! thank you.|||rather,
select
[user], sum(Votes) as TotalVotes
from
yourTable
where
month in (4, 5)
group
by [User]

Help with COUNT in SELECT Statement

Could someone assist with getting the count function working correctly
in this example please. I know the count function will return all rows
that do not have null values, but in this case I want to count all the
rows except those with a zero sale price, (which are unsold).

The table shows works offered for sale by an artist, with a positive
figure under SalePrice indicating a sale, and I want to count the
number sold by each auction house, and sum the sale price by auction
house. The table is as follows:

NameSalePriceAuction
Dowling12000Christies
Dowling 0Christies
Dowling10000Christies
Dowling 0Christies
Dowling 0Christies
Dowling 6000Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
Dowling 8000Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
Dowling 0Sothebys

When I run this query:

SELECT MyTable.Name, Count(MyTable.Name) AS [Number],
Sum(MyTable.SalePrice) AS TotalSales, MyTable.Auction
FROM MyTable
GROUP BY MyTable.Name, MyTable.Auction
HAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));

The results are:

NameNumberTotalSalesAuction
Dowling 5 22000 Christies
Dowling 7 14000 Sothebys

The TotalSales is correct, but the Number (Count) is incorrect, as the
rows with zero were also included. The results should be:

NameNumberTotalSalesAuction
Dowling 2 22000 Christies
Dowling 2 14000 Sothebys

How do I prevent the unsolds (zeros) being counted?

Thanks in advance,

John FurphyAssuming you also want to exclude NULL saleprices (if any):

SELECT name, COUNT(NULLIF(saleprice,0)) AS number,
SUM(saleprice) AS totalsales, auction
FROM MyTable
GROUP BY name, auction
HAVING name='Dowling' AND SUM(saleprice)>0;

--
David Portas
----
Please reply only to the newsgroup
--|||"John Furphy" <johnfurphy@.a1.com.au> wrote in message
news:4ce579e8.0312010326.115691db@.posting.google.c om...
> Could someone assist with getting the count function working correctly
> in this example please. I know the count function will return all rows
> that do not have null values, but in this case I want to count all the
> rows except those with a zero sale price, (which are unsold).
> The table shows works offered for sale by an artist, with a positive
> figure under SalePrice indicating a sale, and I want to count the
> number sold by each auction house, and sum the sale price by auction
> house. The table is as follows:
> Name SalePrice Auction
> Dowling 12000 Christies
> Dowling 0 Christies
> Dowling 10000 Christies
> Dowling 0 Christies
> Dowling 0 Christies
> Dowling 6000 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> Dowling 8000 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> Dowling 0 Sothebys
> When I run this query:
> SELECT MyTable.Name, Count(MyTable.Name) AS [Number],
> Sum(MyTable.SalePrice) AS TotalSales, MyTable.Auction
> FROM MyTable
> GROUP BY MyTable.Name, MyTable.Auction
> HAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));
> The results are:
> Name Number TotalSales Auction
> Dowling 5 22000 Christies
> Dowling 7 14000 Sothebys
> The TotalSales is correct, but the Number (Count) is incorrect, as the
> rows with zero were also included. The results should be:
> Name Number TotalSales Auction
> Dowling 2 22000 Christies
> Dowling 2 14000 Sothebys
> How do I prevent the unsolds (zeros) being counted?
>
> Thanks in advance,
> John Furphy

SELECT "Name",
COUNT(*) AS Number,
SUM(SalesPrice) AS TotalSales,
Auction
FROM MyTable
WHERE SalesPrice > 0
GROUP BY "Name", Auction

Regards,
jag

Help with combining Sql Statements

I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).

insertString ="Insert comments (uID) Select uID FROM users WHERE uName = @.uName"

insertString2 ="INSERT comments (eventID, text) VALUES ( @.eventID, @.comment)"

I have tried:

Insert comments (uID, eventID, text)SELECT uID FROM users WHERE uName = @.uNameVALUES(uID, @.eventID, @.comment)

Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!

Thanks in advance.

Try it like this:

INSERT INTO comments (uID, eventID, [text])SELECT uID,@.eventID, @.comment FROM users WHERE uName = @.uName

|||

Spot on, thank you very much!

help with clr trigger

hi all.

i'm writing a simple clr trigger.

the trigger reads values from the INSERTED table.

and i use try - catch statement.

in the catch () i want to inesrt a new row to my Logtbl Table.

how can i insert row to a table in another tables trigger?

can you please give me a simple example?

thanks.

How about: "insert into Logtbl values(your values)" as a SqlCommand.

Niels
|||

should i use the same connection object for this sqlCommand?

|||Sure.

Niels

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),

0,

0,

''

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

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 ws 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 ws 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 ws 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 building query

Hello,

This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have:

Two tables:

Table 1:
Orders
Some of the fields:

ID
PropID
WorkOrderNum
OrderDesc
DateCompleted

Table 2:
OrderDetail

ID
OrderID
TenantName

As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record.

Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc):

SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%'

Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out.

thanks.Acciording to your explanation,
1. PropertyLocations has 1:many relation to ORDERS,
2. Orders has 1:many relation to OrderDetail.

My first try would be like below for TenantName:
SELECT Distinct
PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID, Orders.PropID, Orders.WorkOrderNum,
Orders.OrderDesc, Orders.DateCompleted
FROM PropertyLocations INNER JOIN Orders
ON PropertyLocations.ID = Orders.PropID
INNER JOIN OrderDetail
ON Orders.ID = OrderDetail.OrderID
WHERE Orders.OrderDesc LIKE '%lds%'
AND OrderDetail.TenantName LIKE 'Steve%'|||That's almost it. It's pulling up all my orders that has the specified 'tenantname' (that is great!)...but it's showing duplicate orders.

For instance:
I have a record in my 'Orders' table of ID=2886. And in my 'OrderDetail' table, there are 3 records that have the characters 'dr.' in it that all have the value of 2886 in the 'OrderID' field which means that they all belong to the record in the 'Orders' table with ID of 2886 that I mentioned at the beginning of this paragraph. So it is showing me my data from the 'Orders' table 3 times (duplicates).

I also modified your statement slightly:

SELECT Distinct PropertyLocations.PropertyLocation, OrderDetail.TenantName, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderID WHERE OrderDetail.TenantName LIKE '%Dr.%' ORDER BY WorkOrderNum DESC

I'm only doing 1 'LIKE' at a time...but that's probably my fault...I wasn't real clear about it.

So how do I make it show me just a single instance of a record from the 'Orders' table even though that there may be several records in the 'OrderDetail' table that matches the search criteria?

P.S. Thank you for reminding me about trying to be SQL99 compliant. ;)

thanks.|||The reason that you do have duplicates is because of Orders.DateCompleted even if you have DISTINCT in the SELECT statement.
Therefore my next question would be
1. Do you need Orders.DateCompleted to display?
2. If YES, then duplicates are unavoidable.
3. If NO, then remove Orders.DateCompleted from the SELECT statement. - Problem solved.

In case of YES,
1. Do the dates of Orders.DateCompleted fall into the same date?
If YES, then use CAST(Orders.DateCompleted as varchar(11)) to pick up only the date portion that ignores time portion of the Orders.DateCompleted. - Problem solved.
If NO, then still duplicates are unavoidable.|||Well, I tried removing the Orders.DateCompleted field even though I really wanted it, but it still gave me the same results.

I included 2 screenshots now, one w/DateCompleted so everyone can see exactly what I'm getting, and my SQL statement, and then 1 after I removed the Orders.DateCompleted field.

Just to be sure that everyone knows what I mean when I say duplicates, I mean that the same exact Work Order (which is a row...or an order...or record) is showing up multiple times even though only 1 actually exists in the database. You can see this in the screenshots - there are 3 rows of the WO#6997 which is far from correct - there is only 1 record in the database with that Work Order Number. But there are 3 unique records in the OrderDetail table that contain the characters 'dr.' in it that belong to that WO#6997 work order. (And just to clarify, the WorkOrderNum is not my 'unique id' field in the 'Orders' table, nor is it the field that is 'linked' with the OrderDetail table)

If the above is what everyone already realized/knew, then cool, but if it wasn't, then maybe it will help clear things up for someone to help a little easier.

thanks.|||Let me clarify
1. You have one record in the Order table with WO#6997.
2. You have three records in the OrderDetail table with WO#6997 and TenantName like '%dr%'.
3. WO# in the Order table is not unique.
4. WO# in the OrderDetail is not unique.

OK.
First, my previous email regarding the Orders.DateCompleted was incorrect. Because it belongs to Orders table, it wouldn't generate duplicates. So please ignore my suggestion to remove the column Orders.DateCompleted.

Then please show me the TenantNames. Are they the same?
If they are different, obviously you will have 3 records.
Inside the SELECT statement, you have the following columns to display:

PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompleted

Among them, only TenantName comes from OrderDetail table.
Therefore if TenantName is the same it shouldn't display three records.
I am puzzled and very curious on the result for the TenantName.|||1. Yes
2. Kind of...The OrderDetail table doesn't actually have a WO# field in it. It has an OrderID field that corresponds to the ID field of the Orders table (which contains a WO# field)...but yes, there are 3 records that show up from the OrderDetail field with a TenantName like '%dr.%' that belong to the record that contains WO#6997 in the Orders table
3. Actually, WO# is unique (although there might be some records in the table ('Orders' table as I mentioned), that don't have a value for this field...but the records would be so old, it will never come up...so is it still considered 'unique'?), but isn't my identity field. I have an ID field for that.
4. As I mentioned, the OrderDetail table does not have a WO# field.

Ok, no problem.

Below are the 3 records that came up that matched the criteria:
The following data is from the TenantName field, and yes, as you can see, are unique. They all share the same OrderID of 2886...which is the ID of the Orders table. So I guess this is where the problem is? See, I don't really care that 3 records from the OrderDetail table matches my search criteria...all I need to know is that at least one record matches, and then I want to show the Order/Work Order from the Orders table that it corresponds to...1 time.

At Napa 9, 870 Napa Valley Corp. Dr.:
Work to be done at: [nl]870 Napa Valley Corp.Dr./Napa 9[nl]2511-2515 Napa Valley Corp. Dr./The Vines[nl]~+
At The Vines, 2511-2515 Napa Valley Corp. Dr.:

Yes, those are the columns I would like to display, and yes, TenantName is the only one that comes from the OrderDetail table.
As you see, the 3 records aren't the same that match the search criteria.|||Now I can see whole picture.

You have one record in the Order table with WO#6997.
You have three records in the OrderDetail table with WO#6997 and TenantName like '%dr%'.
ID is primary key in Order table.
OrderID in the OrderDetail is foreign key to Order table.

Inside the SELECT statement, you have the following columns to display:

PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompleted

But OrderDetail.TenantName are not the same. Therefore you have to force it to be the same by using the criteria.

So
Select Distinct
PropertyLocations.PropertyLocation,
'Like ''%Dr.%''' as [TenantName Criteria],
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompletedFROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderID WHERE OrderDetail.TenantName LIKE '%Dr.%' ORDER BY WorkOrderNum DESC

I hope this should satisfy your requirements.|||Cool, I'm sorry it took so long to get there.

1. Yes
2. Yes, in a way...see #2 from my previous post
3. Yes
4. Yes

Ok, this is where you're going to want to kill me. I just realized after re-reading your post...I don't actually need to display OrderDetail.TenantName. You can even see that I'm not showing that field/column in my screenshots...silly me.

So I tried one of your previous SELECT statements, and removed it (the OrderDetail.TenantName part) from the beginning so as to not display it, and it worked! I'm so sorry for all of the confusion and the extra steps.

But out of not wanting all your hard work to not be in vain, I tried your last statement, and it worked too! So now I know what to do if I have a similar situation. Although, I do have a question...I've never seen part of your statement before:

'Like ''%Dr.%''' as [TenantName Criteria],

What is that all about? Obviously it works, but it's new to me. I also don't really understand why it would've worked without showing the OrderDetail.TenantName column.

Thanks for sticking with me and all of your help, TerryP.|||I am glad that I could help you.

Your question:

'Like ''%Dr.%''' as [TenantName Criteria],
This is a constant column. Because the TenantName is not actually the name of the tenant but a memo or description, I tried to make a common string for TenantName which comes from the criteria in the WHERE clause. When it displayed, all three records would have 'Like ''%Dr.%'''. So I could eliminate multiple records of the same constant string to a single record by using DISTINCT keyword in SELECT clause.
I also don't really understand why it would've worked without showing the OrderDetail.TenantName column
You do not have to display all or any of the columns of tables used in FROM clause. But it would have returned three rows if DISTINCT had not been used. So the trick was in DISTINCT keyword.

help with an sql statement please

Hi All

I need to get the maximum Decision number from my decision table where Enquiry Number = 1 but im not having much luck, can anyone help please (Niether are auto number field or primary key)

thanks

Gibbo

e.g. Select Maximum Decision_No from Decisions Where Enquiry_No = 1

Code Snippet

SELECT max( Decision_No )
FROM Decisions
WHERE Enquiry_No = 1

Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.I should give some more info to make the analysis easier...

My raw data looks like this

Date Type
20040101 Meal
20040101 Meal
20040101 Meal

I want the SQL to add a Count column and the Fact table data should look like this (one count per day).

Date Type Count
20040101 Meal 1
20040101 Meal 0
20040101 Meal 0

The only way I have been able to come close is to have a count of '1' for each line item, or to have to sum all the meals and have only one line per day. Is this possible to create without having to sum?

Thanks. Sorry I wasn't more clear.|||I still don't understand what you are trying to do. To get a count of meals, why don't you just
SELECT COUNT(*) From Table WHERE Type = 'Meal'

Do you want to count the number of days that have one meal so that if a single day has multiple meals, it just counts as one:
SELECT COUNT(*) From Table WHERE Type = 'Meal' GROUP BY datepart(year, Date), datepart(dayofyear, Date)|||Yes, I want to count one meal per day, however I still want the other line items to show as zero, because they have costs.

I'll try your SQL, but won't it drop the other 2 line items?

Thanks.|||select date expression
, count(*) as numberofmeals
, sum(costs)/count(*) as averagecostofmeal
from yourtable
group by date expression|||another solution :
select Date, Type into ##temp from yourtable group by Date, Type;
select Type,count(type) from ##temp group by type;

Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.This is tough to help you do. The problem is that we (or I at least) don't understand your schema, so I'm not sure what you've got stored how.

One thing that I'd strongly suggest is to avoid using reserved words like "Count". You can use them, but it makes everything more work. In this case, I'd suggest using meal_count because it avoids the collision with a reserved word and it is more meaningful to some poor bozo like me that might try to help you!

-PatP|||This does not help. I was only giving you an example, I am using the 'Count' for the column title only, and am returning the same rows if I change the column title. I want to edit the SQL so I only count the number of days they had a meal charged.

Thanks.|||I'm sorry, I was trying to explain that I didn't know enough about your problem to help. If you can post the CREATE TABLE statements for your tables, and the SELECT statement (all of it), then I could get a lot closer. As it is, I don't know enough to give you anything more than guesses.

-PatP

Help with an SQL SELECT statement in ASP page

I have built this SQL statement which should
create the RS I need.

strSQL1 = "SELECT [INVENTRY MASTER].BOX_NO FROM [INVENTRY MASTER] WHERE
Left([INVENTRY MASTER].BOX_NO, PatIndex('%821%', [INVENTRY MASTER].BOX_NO) -
1) NOT LIKE '%[1-9]%' AND [INVENTRY MASTER].BOX_NO LIKE '%821%';"

This Line:

objRS1.Open strSQL1, objConn

Causes this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
passed to the substring function.

I have searched google, and found reference to the error meaning it found a
space in the first position. I tried adding LTRIM into my statement to cure
it but it made no difference, I may be barking up the wrong tree so to speak
;) but I cant find any other information on it.

If anyone has any ideas why this statement does not work I'd be very
grateful, the project has to be completed today, and this is the last thing
to get working now!

Ta

Paul McGuireThe length parameter in your LEFT(String, Length) function is

PatIndex('%821%', [INVENTRY MASTER].BOX_NO) - 1

PatIndex can return 0, if your string '821' isn't part of your BOX_NO, which makes the length = -1, which isn't allowed as a valid length. Your additional condition does not help, since the expression as a whole will be evaluated.

Consider to use a view to put your conditon
[INVENTRY MASTER].BOX_NO LIKE '%821%'|||Let me expand on what I am trying to achieve to see if you can help further.

imagine a table collumn

BOX_NO
--------
JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821
JHIS 00821a
JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

A user on the internet page I am writing will enter 821 because he wants a list of the boxes:

JHIS 0000821
JHIS 0000821a
JHIS 0000821b
JHIS 00821

The JHIS is the users account number the leading 0's can vary which is what has caused the need to use PatIndex in the way which I have? Is there another way?

All the user knows is box number JHIS 0000821 is box number 821 if he wanted JHIS 0001821 he would enter 1821 and expect to get these boxes returned:

JHIS 0001821
JHIS 0001821a
JHIS 0001821b
JHIS 01821
JHIS 01821a

Any ideas on the best way to do this? Am i on the right lines?|||Why not just

WHERE BOX_NO LIKE '%0821%'

?|||erm I am not sure :) I will put this in now... it seems like this may work if I just add a proceeding '0' to what has been inputted and do a LIKE

If this works I'm going to be very embarrised but pleased to because its been bugging me for hours!

I will let you know if this solves the issue|||Thank You very much! I obviously could not see the wood for the trees!

I changed it slightly because as it stood if 821 was entered 0008210 returned aswell which was not wanted.

by changing the last % to a [a-z] and adding an OR and just looking for %0821 I get back exactly what I need. And its a nice simple select statement!

So I offer you a big thanks and a :) this should keep the managment happy!|||You are welcome.sql

Help with an error I don''t understand

Hey everyone,

I'm trying to make an image appear differently depending on the values of certain data. The statement that I am using is:

= IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)>15,"redball",IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)<8,"greenball","yellowball"))

And the error that I get is:

[rsRuntimeErrorInExpression] The Value expression for the image ‘image4’ contains an error: Argument 'Interval' is not a valid value.

Preview complete -- 0 errors, 1 warnings

When I preview the report I just get the typical white box with a red x where the image should be. I don't understand.

Thanks in advance,

Keith

Nevermind, argument needs to be "d". Thanks anyway!

-Keith

Help with an error I don''t understand

Hey everyone,

I'm trying to make an image appear differently depending on the values of certain data. The statement that I am using is:

= IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)>15,"redball",IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)<8,"greenball","yellowball"))

And the error that I get is:

[rsRuntimeErrorInExpression] The Value expression for the image ‘image4’ contains an error: Argument 'Interval' is not a valid value.

Preview complete -- 0 errors, 1 warnings

When I preview the report I just get the typical white box with a red x where the image should be. I don't understand.

Thanks in advance,

Keith

Nevermind, argument needs to be "d". Thanks anyway!

-Keith

sql

HELP WITH A VIEW - calculated column

I need a view that contains a select statement that reads through all rows in a table, and based on the value in one of the columns, returns an additional column containing either "Manager" or "employee" depending on the values of that column. I'm not sure whenter to use a loop statement , a local variable, etc - -- but the end result must be a datagrid holding all all rows in the table plus the additional "Manager column" Can someone help me?

SELECT CASE col1 WHEN 'M' THEN 'Manager' ELSE 'Employee' END as EmpType
FROM tablename

You can use a CASE statement. Books online has a very good reference for using CASE.|||Thanks - works great!

Help with a switch statement.

Hey again,

So here's what I'm trying to do: I have three columns of data. Sometimes only one of the columns will contain a value while others may contain a null. If two or three contain a value it will be the same. So if I'm building a table in the layout designed and I want the value of the table to be the value stored in these columns. In pseudocode it looks like this:

Switch(column1 and column2 are null, value = column3, if column1 and column3 are null, value = column2, otherwise, value = column1)

Something like that where column1 is the default so if column 1 has a value then set the textbox value to it otherwise find a column that has it. I know that at least one column will definitely have a value. Anyone that can provide guidance on how to execute these I would appreciate it greatly.

Thank you!

Hello Keith,

Try this:

=Switch(

Fields!Column1.Value is nothing and Fields!Column2.Value is nothing, Fields!Column3.Value,

Fields!Column1.Value is nothing and Fields!Column3.Value is nothing, Fields!Column2.Value,

1 = 1, Fields!Column1.Value

)

Hope this helps.

Jarret

|||Is 'nothing' the keyword for null? I kept typing in null and it gave me invalid identifier or something, I couldn't figure out how to check it in the switch.|||

Yes sir.

You could also use Len(Fields!Column1.Value) > 0 for that check.

Jarret

|||I'll try it out, thanks alot!|||Do you, or anyone else, know how I would do this with the actual query, so they they are all consolidated into one field? I know I need to use the case statement but I can't get the syntax right. Basically it's the same situation, I'm taking from three different places, one or more may have a value but I just want to end up with one column populated. Thank you!|||Looks like you could use a COALESCE() to do this in a query. COALESCE will return the first non null value in the list of values. Ex. COALESCE(Value1, Value2, Value3) will return the first non null value checking them in order of value1, value2 and value3.|||That's great, thank you!sql

Help with a SQL statement

My problem can best be described using the Northwind db as an example.
I would like to select all Customers that have never ordered a
particular Product. Thank you.There are several way to do this
(Don′t have the Northwind here but is think I get the column names right :-
) )
Select * from Customers C
Where not Exists
(
Select * from orders O where O.CustomerId = C.CustomerId
)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"rstewart27104@.gmail.com" wrote:

> My problem can best be described using the Northwind db as an example.
> I would like to select all Customers that have never ordered a
> particular Product. Thank you.
>|||Well, wouldn't you need to tie in orderdetails and productid, since the OP
wants customers that haven't ordered a specific product, not customers who
haven't ordered at all?
(BTW, how many customers that have never ordered, would you expect to find
in the database? :-) )
A
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com...
> There are several way to do this
> (Don′t have the Northwind here but is think I get the column names right
> :-) )
> Select * from Customers C
> Where not Exists
> (
> Select * from orders O where O.CustomerId = C.CustomerId
> )
> --
> HTH, Jens Suessmeyer.|||Well If you want to make them an offer you sure have the customer in the
database with a customerid...
Didn′t saw the word "particular", then you should go by this:
Select * from Customers C
Where not Exists
(
Select * from orders O
inner join [Order Details] od on od.orderid = o.orderid
inner join [Products] P on o.productid = od.productid
where p.ProductName = 'SomeProduct'
and O.CustomerId = C.CustomerId
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, wouldn't you need to tie in orderdetails and productid, since the OP
> wants customers that haven't ordered a specific product, not customers who
> haven't ordered at all?
> (BTW, how many customers that have never ordered, would you expect to find
> in the database? :-) )
> A
>
>
> "Jens Sü?meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com..
.
>
>|||> Well If you want to make them an offer you sure have the customer in the
> database with a customerid...
Right, but my point was, most online stores won't have data about a customer
unless they've purchased from them once. :-)|||OK, but what would you expect from a query displaying all customers which
didn′t by anything if you refer to the online store example. If you have on
e
customer that′ll be
mankind - 1 = SelectList
;-D
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Aaron Bertrand [SQL Server MVP]" wrote:

> Right, but my point was, most online stores won't have data about a custom
er
> unless they've purchased from them once. :-)
>
>|||> OK, but what would you expect from a query displaying all customers which
> didn′t by anything if you refer to the online store example.
Typically, I wouldn't expect such a query to have any practical meaning. I
suppose there is probably a sliding scale, the less credible the company,
the lower their percentage of actual customers in their "customer" list.
:-)|||or
SELECT c.[CustomerID]
FROM [Customers] c
LEFT OUTER JOIN [Orders] o
ON c.[CustomerID]=o.[CustomerID]
WHERE o.[CustomerID] is null
"Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com...
> There are several way to do this
> (Dont have the Northwind here but is think I get the column names right
:-) )
> Select * from Customers C
> Where not Exists
> (
> Select * from orders O where O.CustomerId = C.CustomerId
> )
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "rstewart27104@.gmail.com" wrote:
>