DBA HELP: Performane Tune SELECT, SUM, && CASE

Jul 23, 2005

HELP!!!

I am trying to fine tune or rewrite my SELECT statement which has a
combination of SUM and CASE statements. The values are accurate, but
the query is slow.
BUSINESS RULE
=============
1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or both
have a value.
2. Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.
4. TotalCount = Count1 + Count2 -- (Below, basically had to reuse the
SQL from both Count1 and Count2)
3. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.


SQL Code
========
SELECT
SUM(CASE
WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR (FIELD_1 IS NOT
NULL AND FIELD_2 IS NOT NULL))
THEN 1
ELSE 0
END) AS Count1 ,
SUM(CASE
WHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)
THEN 1
ELSE 0
END) AS Count2,
SUM(CASE
WHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)
THEN 1
ELSE (CASE WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR FIELD_1
IS NOT NULL AND FIELD_2 IS NOT NULL) THEN 1 ELSE 0 END)
END) AS Total_Count,
SUM(CASE
WHEN ( FIELD_1 IS NULL AND FIELD_2 IS NULL)
THEN 1
ELSE 0
END) AS None_Count,
FROM
TABLE_1

View 1 Replies


ADVERTISEMENT

Performane Problem

Jan 15, 2007

Hello.
Your help is very much appreciated.

I have SQL SERVER 7.0 running about 10 databases.
Only 1 database is problematic.
When running quries on the two largest tables on this database(one has 2.4 million records, the other one has 3 million records), it stops responding/times out, even if the query is run on the server itself.
We do have another table(in other database, on the same server) with 15 million records, which runs slow but keeps on working.

(in the past,rebooting every few days solved the probelm)
It might be indexing or maybe table/record locks?
What is your recommendation regarding how to find where (and later, what) the problem is?

Thank you
Cheers
Roy

View 3 Replies View Related

Performane Tuning And Deadlock Trapping In This Scenario.

Apr 11, 2007

SQL2K
SP4


Howdy all. I have done a fair amount of performance tuning and deadlock resolving in my day, but never under these circumstances. Here is the scenario:

A sproc written in Dynamic SQL (with a dynamic WHERE clause I should add) queries a View. This View is joined to several more Views, that are joined to several more Views, that are joined to several more Views, that are.......

So, what I've been having to do to find out if my column is indexed or not is read View1, then View2, and so on and so forth until I figure out what the table really is.

This is extremely time consuming. I can actually have 10 - 20 joins by the time all is said and done.

Now, I am aware that Dynamic SQL is bad, and I'm also aware that 10 - 20 joins is bad. But that doesn't solve anything for me. Can anyone provide any ideas on how to troublshoot perfomance issues and deadlocks in this scenario?

TIA, ChrisR

View 9 Replies View Related

How To Tune The Sql Server?

Mar 22, 2004

hi here has a question for the mssql,

a problem occurs when i run a large database, the running speed is very slow.

for example, if i want to seek the record of 1500 employees with 15 per person(average) within 1 year(12 months), that mean i have to find record for 1500 * 15 * 12 time.

so, could i find a way to solve this problem? is this call tune the sql server/index/view?

what different of tune the sql server with index and view?

thanks for giving me advice.

:)

View 4 Replies View Related

How To Tune My Query

Sep 21, 2005

I have written a query which fetches data from a table with huge amount of data. This query is actually being used in a stored-procedure and it takes a lot of time, hence slows down my SP.

Here is the query that I'm builiding in the stored-procedure:
-------------------------------------------------------------
SET @selectLeadsInPeriodString = ' SELECT LM_Dealer, LM_Brand, SUM(LM_ImpressionCount)
FROM LM_ImpressionCount_Dealer'
IF(@timeCriterion IS NULL OR LTRIM(RTRIM(@timeCriterion)) = '' OR LTRIM(RTRIM(@timeCriterion)) = 'NULL')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE (CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) >= ''' +
CONVERT(varchar,@startDateTime,102) +
''' AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= ''' +
CONVERT(varchar,@endDateTime,102) + ''')'
ELSE IF(@timeCriterion = 'CurrentMonth')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE MONTH([LM_ImpressionCount_Dealer].[LM_ImpressionDate]) = MONTH(GETDATE())'
ELSE IF(@timeCriterion = 'PreviousMonth')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE MONTH([LM_ImpressionCount_Dealer].[LM_ImpressionDate]) = MONTH(GETDATE()) - 1'
ELSE IF(@timeCriterion = 'YearToDate')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' WHERE ([LM_ImpressionCount_Dealer].[LM_ImpressionDate] >= cast((''1/1/''+cast(year(getdate()) AS varchar(4))) AS datetime)
AND CONVERT(varchar,[LM_ImpressionCount_Dealer].[LM_ImpressionDate],102) <= CONVERT(varchar,GETDATE(),102))'

IF(@brand IS NOT NULL AND LTRIM(RTRIM(@brand)) <> '')
SET @selectLeadsInPeriodString = @selectLeadsInPeriodString +
' AND [LM_ImpressionCount_Dealer].[LM_Brand] = ''' + @brand + ''''

SET @selectLeadsInPeriodString = @selectLeadsInPeriodString + ' GROUP BY LM_Dealer, LM_Brand'

The variables used in the query formation above are passed as input parameters to the SP.
The table being queried has columns 'LM_Dealer', 'LM_Brand', 'LM_ImpressionCount' and 'LM_ImpressionDate'.
Also, the table has a non-clustered index on the column LM_ImpressionDate.

With all this information, can anyone suggest as to how I can optimize the query above.

Thanks in advance.

-Dex

View 10 Replies View Related

How To Tune Query??

Sep 30, 2005

I think this is very silly question
but It is hard for me :eek:

SELECT email, host FROM WAITING_AUTH WHERE email NOT IN
(SELECT email FROM MEMBER)
AND host NOT IN (SELECT host FROM MEMBER)

thanks~ Have a nice weekend

View 3 Replies View Related

How Can I 'tune-up' My SQL Installation

May 11, 2004

Hi,

I have SQL desktop version installed and for the last few days it has really slowed down. I have ran many anti-virus etc. and all is okay on that front.

Any tips regarding how I can tune things up? What should I look for and how do I go about it. Deleting LOG etc. etc???

Please guide.

Thanks.

View 3 Replies View Related

Looking To Tune Query...

Sep 15, 2006

If anyone is able to provide advice for tuning the below query in sqlserver, it is much appreciated. In addition any index suggestions arealso appreciated as I have access to the tables. Thank you.select a.id, isnull(b.advisement_satisfaction_yes, 0) asadvisement_satisfaction_yes,isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,casewhen isnull(b.advisement_satisfaction_yes, 0) >isnull(c.advisement_satisfaction_no, 0) then 'YES'when isnull(b.advisement_satisfaction_yes, 0) <isnull(c.advisement_satisfaction_no, 0) then 'NO'when isnull(b.advisement_satisfaction_yes, 0) =isnull(c.advisement_satisfaction_no, 0) then 'TIE'end as Satisfied_With_Advisementfrom aleft Join(select id, count(answer_text) as Advisement_Satisfaction_yes from awhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'and answer_text = 'yes'GROUP BY id) bon a.id = b.idleft join(select id, count(answer_text) as Advisement_Satisfaction_NO from awhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'and answer_text = 'NO'GROUP BY id) con a.id = b.idwhere question = 'The level of Academic Advisement I received from theUniversity staff during this course was appropriate.'

View 2 Replies View Related

Select Case - A Better Way?

Jul 7, 2005

Hi, i have just written this:
select distinct department = case department
when 'ict' then 'Partnership Office'
when 'finance' then 'Partnership Office'
when 'Employee Development' then 'Partnership Office'
when 'Contract Compliance & MIS' then 'Partnership Office'
when 'Quality, Standards & Effective' then 'Partnership Office'
when 'Partnership administration Exe' then 'Partnership Office'
when 'Partnership Executive' then 'Partnership Office'
when 'Personnel' then 'Partnership Office'
when 'Operations' then 'Partnership Office'
when 'Quality Standards and Effectiv' then 'Partnership Office'
when 'Marketing and Communications' then 'Partnership Office'
when 'Marketing and Information' then 'Partnership Office'
else department end from personnel_records

is there some way to do this on one line, like
...
when in ('ict','finance','personnel', etc etc) then 'Partnership Office'
else
... ?

TIA

View 2 Replies View Related

Is This Possible To Use Select Case

Feb 8, 2007

hi, this is my procedure.

CREATE procedure yard_retrieve
(
@yard varchar(50),@vessel varchar(50),@customer varchar(50)
)
AS
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@yard and category_code=(select category_code from categoryMaster where category_description='Yard') )
select 'found' as result
else
select 'not found' as reault
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@vessel and category_code=(select category_code from categoryMaster where category_description='vessel'))
select 'found' as reault1
else
select 'not found' as reault1
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@customer and category_code=(select category_code from categoryMaster where category_description='customer'))
select 'found' as reault2
else
select 'not found' as reault2
end
GO

if i run this procedure i need the result as following

result result1 result2
found notfound found

is this posible to do using select case ? please help me to get my need please

View 4 Replies View Related

How To Tune Server For Better Performance

Mar 23, 1999

ENVIRONMENT:
I have SQL Server6.5 running under a dedicated NT Server. NT configuration includes dual pentium 200Mhz processors, 256MB RAM and RAID system.
The Database size is 1GB with actual data size about 500MB.

PROBLEM:
I have an application which uses lots of joins to get the results. My select query is running too slow even when I run it on the server.

I updated the statistics and rebuild all the indexes on the tables used by the query.

Any suggestions on using SQL Trace and tuning the server/database are welcome.

Srini

View 4 Replies View Related

Tune Procedure !!!!!!!!!!!!!!!!! Urgent

Apr 18, 2002

How can l trim the code and make the procedure run faster ???????????????

CREATE Procedure Disbursements_Cats
(@startdate datetime,@enddate datetime)
As
Begin
SELECT Transaction_Record.loan_No AS loan_no,
Transaction_Record.transaction_Date AS Transaction_Date,
Transaction_Record.transaction_type AS Transaction_type,
Transaction_Record.transaction_Amount AS Transaction_Amount,

Product.product AS Product,
Product_Type.product_Type AS product_type,
Product_Type.loan_Type AS Loan_type,

Customer.first_name AS first_name,
Customer.initials AS initials,
Customer.second_name AS Second_Name,
Customer.surname AS surname,
Customer_identification.idno AS ID_No,

Bank.Bank_name AS Bank_Name,
Bank_detail.Account_no AS Account,
Bank_detail.Branch AS Branch

FROM Transaction_Record CROSS JOIN
Bank_detail CROSS JOIN Bank CROSS JOIN
Customer CROSS JOIN Product CROSS JOIN
Loan_Type CROSS JOIN Product_Type CROSS JOIN Customer_identification
End;
GO

View 1 Replies View Related

Fine-tune Query

Apr 24, 2008

Hi,
I have a query as mentioned below:

SELECT BillCurrencyID,DistD,Amount,PartnerFlag1
FROM Factsales_tab_Dtls (NOLOCK)
WHERE (sales_year =2007 OR sales_year=2008)
AND Country ='US'

1) Table Factsales_tab_Dtls is having more than 5.5 million of records.

2) It is having 32 columns.

3)There's nonclusetered index on columns sales_year & country
4)The query takes longer time for execution

Please help me fine-tune the query

View 1 Replies View Related

How To Tune Particular Store Procedure

May 12, 2008

Hello,

i need to tune some of my store procedures.

can any one help me how it's possible to tune particular store procedures?

Thanks
Prashant Hirani

View 3 Replies View Related

How Do We Tune A Large Sql Query

Feb 6, 2006

hi,I have a problem asked by one of my senior person and finding theanswer .What is the step by step procedure for tune a large sql query.OR how do we tune a large SQL query with somany joins

View 6 Replies View Related

Select Case Issue

Sep 19, 2007

I need to do the following so that I can convert a Null value to 0 in my db... however I am getting errors "Incorrect Syntax near '='.
Facilities.PortfolioID AS PortfolioID =
CaseWhen Facilities.PortfolioID = Null then Facilities.PortfolioID = 0
End,

View 5 Replies View Related

CASE In Select Statement

Jan 17, 2008

Hi, i've got this stored procedure:
 @LidFederatieNummer varchar(20),
@ClubID int,
@LidClubNummer varchar(50),
@PersoonNaam varchar(100),
@ClubStamnummer varchar(10),
@ClubNaam varchar(50),
@SeizoenID int,
@ShowAllJN bit,
@LidFederatieJN int,
@CategorieID int
AS
BEGIN

IF @CategorieID IS NOT NULL BEGIN
DECLARE @LicentieVerplicht int
SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
END

IF (@ShowAllJN = 0)
BEGIN
SET RowCount 100
END

CASE @LicentieVerplicht
WHEN '1' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 1
Order By Persoon
END

WHEN '2' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN)
ORDER BY Persoon
END

WHEN '3' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 0
Order By Persoon
END
 According the value of my @LicentieVerplicht variable:
DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
My where clausule should change... how can  i achieve this cause my case is not working because it is not inside the select statement

View 4 Replies View Related

Case When In A Select Statement

Apr 13, 2006

Hi everyone,
I have the following problem withj this query:

SELECT DocNumber,
       Title,FirstName,LastName,Paper,
       ANNO,Location,Keywords,
       URL= case WHEN URL IS NULL THEN
'|<a
href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+
CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+
'&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' +
CONVERT(varchar,Firstname)+
'&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')>
Order through ILLiad</a>' ELSE URL END
FROM DocTable1 WHERE FREETEXT....

When URL is null, I don't get the string showing. It works properly only in the query analyzer.

Any ideas?

Christian

View 3 Replies View Related

Search Or Select CASE????

Nov 21, 2001

I'm using a 'searched case' however I don't care which method I use.

I'm open to using anything....

I just want to correct a problem in my pivot table where I need to create a ficticous eventdate for 'S' records, those that do not have an event date in the past 7 days.

-- eventdate is a datetime column
select User
,res =
case res --event result
when 'S' then '0' --S generic non-event
when 'SUC' then '1' --successfull event
end
,eventdate =
case eventdate
when enventdate < getdate()-8 then getdate()-8
end
from eventlog_1 --my view of events

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '<'.

TIA

JeffP...

View 2 Replies View Related

Select Case Question

Jul 7, 2005

I am doing a mail merge from data returned from a query. My problem is one of my fields is to long for the document. I want to be able to break up the line into 2 sections. I'm assuming I can do this within the case statement but am unsure how.
This is the code I have. I want the break to come before the "at".


case
when rb.building in (9420,420) then 'DEERFIELD SUMMER SCHOOL at BAKER WOODS #99'
when rb.building=9722 then 'JAMES SUMMER SCHOOL' 'at JOHN MARSHALL MS'
when rb.building=9723 then 'NORTHWEST SUMMER SCHOOL at CRISPUS ATTUCS MS'
when rb.building=9717 then 'BROAD SUMMER SCHOOL at SIDENER MS'
when rb.building=9715 then 'MANUAL SUMMER SCHOOL at MCFARLAND MS'
else upper(trim(rb.building_name)) end,

View 2 Replies View Related

Case Statement Within A Select

Sep 7, 2006

Hey guys, i'm trying to use a CASE within a select but i'm not sure how to get started.

Here is my select so far, i'm inserting into a temp table that is used within a loop to gather data from my trigger:


Code:


insert into @temp_par
select CONVERT(varchar(20),d.PAT_ID),
ep.EXT_PAT_ID,
d.DRUG_DESC_ID,
d.ALLERGY_CD,
CONVERT(varchar(20),d.PAR_DT,120),
ISNULL(d.PAR_TXT, 'NULL'),
ISNULL(REPLACE(m.DRUG_NM,'"','^"'),'NULL'),
ISNULL(m.DOSAGE_FORM_CD,'NULL'),
ISNULL(m.STRGTH,'NULL'),
ISNULL(m.STRGTH_UM,'NULL')
from deleted d --PATIENT_PRIOR_ADVERSE_REACTIONS
join EXTERNAL_PATIENT ep on ep.PAT_ID = d.PAT_ID
join MEDNAME m on m.DRUG_DESC_ID = d.DRUG_DESC_ID



I would like to use a CASE for the d.ALLERGY_CD column. The data in this column is usually a 1,2,3,4,5 but i'd like to convert those to their real data, for example:


Code:

1 = A
2 = B
3 = C
4 = D
5 = E



Any help?

View 3 Replies View Related

Select Within Case Statement?

Jan 20, 2005

Limitations: Needs to be single sql statement for exec, no udf/stored proc
Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals
if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty
if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg

What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice!

tblPerson: person_pk, affiliation_fk; home_city_fk, mail_goes_to
tblAffiliation: affiliation_pk, organization_fk
tblOrg: organization_pk, city_fk
tblCity: city_pk, county_fk
tblCounty: county_pk, s_fk

For resulting display only:
tblPersonArea: personArea_pk, area_fk, person_fk
tblArea: area_pk, areaDesc, division_fk
tblDivision: division_pk, divisionDesc

View 1 Replies View Related

SELECT CASE WHEN Problem

Dec 1, 2006

Hello !
For MS SQL 2000/2500

I am having a table ::

User
Ident int NOT NULL,
Numb int NOT NULL,
Info nvarchar(100) NOT NULL,

with this query ::

SELECT Numb, Info, Ident
FROM Users
WHERE (Numb IN (100,200, 300))
ORDER BY Ident

I get ::

Numb | Info | Ident

100 | dgwrg | 1
200 | dgeth yiuyi | 1
300 | Agt hgn ngtn | 1

100 | jttytujky | 2
200 | yjtut iuiu | 2
300 | dgd 2gggwekew | 2


what I need is something like::

SELECT
(CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA,
(CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB,
(CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC,
Ident
FROM Users
WHERE Ident = Ident (for the same Ident)

to get ::

InfoA | InfoB | InfoC | Ident

dgwrg | dgeth yiuyi | Agt hgn ngtn | 1

jttytujky | yjtut iuiu | dgd 2gggwekew | 2


Actually I am getting a NULL value everywhere
of course I cannot use MAX(CASE WHEN ... , or I am getting only a few rows and not all

thanks a ton for helping

View 6 Replies View Related

Select Case With Date

Aug 2, 2013

OK so I want to use datepart(month,getdate() with a Case statement with this logic:

SELECT CASE
datepart(month,getdate()='8' then
SELECT *
FROM [Master]
WHERE [Month]='Jul'
END

OK what's wrong with the code?

View 6 Replies View Related

Case Not Making The Select?

Aug 2, 2013

The following code is a front end parameter - Quantity but when the user enters '0' it is not returning any rows (there are). Is there something wrong here or to add something?

(Select {?Quantity}
CASE "ALL":
1=1
CASE "Negative":
({ACBALMPK.IRQOH#}<0) OR ({ACBALMPK.IEQOH#}<0)
CASE "Zero":
({ACBALMPK.IRQOH#}=0) OR ({ACBALMPK.IEQOH#}=0)
CASE "Greater than Zero":
({ACBALMPK.IRQOH#}>0;) OR ({ACBALMPK.IEQOH#}>0;))

View 4 Replies View Related

Select Case Statement

Aug 12, 2005

Hi,

I'm wondering what syntax to use for a select case statement.

I have a list of questions. If a particular question is on the list, then it shouldn't show some other related questions.

For example, "have you been in the military?". If you haven't , then you wouldn't be asked all the military related questions.

So- I'm saying:
select case when question_id = 488 then don't return question_id 220, 962, 963

I'm trying something like this:

select q.client_id,
case when q.question_id = 488 then end q.question_id = 220 else q.question_id = 220 end q.question_id
from questions_yes_no q

How would I say 'dont show' question 220? End is not the way to go, obviously.

If you can see what I'm trying to do, can you point me to an article or bol search?

Thank you for any help.

View 11 Replies View Related

How To Set The Value Of Variable In Select Case

Oct 10, 2007

Select Case l.GLTransactionTypeID
When 'Asset' --AND l.GLTransactionDate BETWEEN convert(datetime,'1/1/2007')AND convert(datetime,'12/31/2007') and l.GLTransactionSource=@assetID
Then Begin Set @Cost=l.GLtransactionAmount end
end from LedgerTransactions l,FixedAssets f where l.GLtransactionsource=f.assetID and l.GLtransactionsource= @assetID

View 1 Replies View Related

Help With Syntax (select Case)

Mar 4, 2008

Hi,

I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..

I have my SPROC configured as below, can anyone help me out as to why its not working?

btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)


IF @genderID > 2
..
ELSE
........WHERE genderID = @genderID


thanks very much once again!
mike123



CREATE PROCEDURE [dbo].[select_123]
(
@genderID tinyint
)

AS SET NOCOUNT ON


SELECT

CASE @genderID
WHEN 1 THEN

SELECT TOP 40 *

FROM tbl
WHERE .......


WHEN 2 THEN

SELECT TOP 40 *

FROM tbl
WHERE .......
ELSE
SELECT TOP 40 *

FROM tbl
WHERE .......



END

View 3 Replies View Related

VB 'Select Case' Equivalent In T-SQL?

Jan 24, 2008

I'm still new to T-SQL and have been trynig to find an equivalent to VB's select case. Is the IF statement all that's available?

View 6 Replies View Related

How To Select A Case Sensitive Value In SQL With C#

Feb 11, 2006

Hello,

I have an application that at the begining a user will login with a user name and password which is stored in the database. The SQL statement is as follows:

"SELECT id_employee FROM employee WHERE employee_number='" + txtUserName.Text + "' AND passWord='" + txtPassword.Text + "'";

For testing purposes I have set the password to the word test. The problem is, if the user enters in TEST or TeSt or TESt it will grant them access. How do I set it to force the correct case?

I am using SQL 2005 for the database.

Thanks!

~zero

View 11 Replies View Related

Select Case Statement

Apr 30, 2008

INSERT INTO #rpt(Hic, DtImported, Source, PlanID, LastName, FirstName, MemID, CaseStatus)
SELECT
s.Hic,
s.DtImported,
s.Source,
dbo.LastErolledPlan_C_D(s.Hic,s.IsPart_C) as PlanID,
m.LastName,
m.FirstName,
dbo.GetMemID_PartC_D(s.Hic,s.IsPart_C) as MemID,
s.CaseStatus = CASE WHEN CaseStatus ='1' or CaseStatus='0' THEN 'New'
CASE WHEN CaseStatus <>'1' or <> CaseStatus <>'0' Old

ElSE 'No Valid'
End


from tbEsccSuspects s inner join tbPDMMembers m
on s.Hic = m.HicNumber
Where s.EUFStatus = 1 -- AND DtEUFSent is NULL


what I am doing wrong in the case statment that I am getting an error. ?????

View 8 Replies View Related

SELECT QUERY Without USING CASE

May 29, 2006

I have a select query

select col1-tbl2,col2-tbl3,col3-tbl4 ,col4-tbl1,col5-tbl1 from tbl1where condition,

The above quey col1-tbl2,col2-tbl3,col3-tbl4 are from table1 only, but it have only id(ie) the foreign key references of tables 2,3,4

I want the column with their values to join with the tables 2,3,4.

Except USING CASE
Except USING CASE

Please Guide me

View 1 Replies View Related

Need To Tune A Table For Performance Gains

May 2, 2007

Hi :I have a TableA with around 10 columns with varchar and numericdatatypesIt has 500 million records and its size is 999999999 KB. i believe itis kbi got this data after running sp_spaceused on it. The index_size wasalso pretty big in 6 digits.On looking at the tableAit didnot have any pks and hence no clustered index.It had other indicesIX_1 on ColAIX_2 on ColBIX_3 on ColCIX_4 on ColA, ColB and ColC put together.Queries performed in this table are very slow. I have been asked totune up this table.I know as much info as you.Data prior to 2004 can be archived into another table. I need to run aquery to find out how many records that is.I am thinking the following, but dont know if i am correct ?I need to add a new PK column (which will increase the size of thetableA) which will add a clustered index.Right now there are no clustered indices2. I would like help in understanding should i remove IX_1, IX_2, IX_3as they are all used in IX_4 anyway .3. I forget what the textbox is called on the index page. it is set to0 and can be set from 0 to 100. what would be a good value for it ?thank you.RS

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved