Three Different Select Statements In SP - Cant Get All Columns

Sep 3, 2007



Hi,

I have a SP having three differnet select statements like,

IF @reporttype ='A'

SELECT Column1, column2
FROM table1

IF @reporttype = 'B'

SELECT Column3, column4
FROM table2

IF @reporttype = 'C'

SELECT column5, Column6
FROM table3


Now I need three different reports for all three reporttypes.
If i creating first report for @reporttype A, it is executing fine and giving me the Columns which i need.
But while creating reprots for reporttype B and C, I m getting columns column1 and column2 . Its not displaying Column3, Column4, Column5, Column6.

Can anybody help me with this?

View 1 Replies


ADVERTISEMENT

Combine Columns From Two SELECT Statements

Sep 17, 2007

I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).

I have two statements that look like this:

SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber

SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber

Each statement correctly aggregates the sums, but I need a record that shows me:

CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid

I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).

Any help is appreciated.

View 13 Replies View Related

Using SQL Query Columns In Select Case Statements

Jun 5, 2006

I am using Visual Web Developer Express 2005 as a test environment.  I have it connected to a SQL 2000 server.  I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger.  Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP:
Select Case tCOTSSoftware("Type")
      Case 1
         execute an SQL Update Command
     Case 2
         execute a different SQL Update Command
End Select
What would a comparable ASP.Net (Visual Basic) statement look like?  How would I access the column name used in the SQLDataSource?

View 6 Replies View Related

Update Multiple Columns In One Sql Statements

Nov 2, 2004

I am almost sure I can update variables columns in one select/case type
statement, but having problems working out the syntax.

I have a table with transactions - with tran types as the key.

in this example, types = A,B,C ,D.

in this first example I am updating the sum of QTY to value t_A based on
tran types =A.

can I perform sub query/case to update with the same where clause
but for types B,C and D?? I also have to insert for specific lot numbers each sum values.



Create table #t_reconcile(
t_lot_number int not null,
t_A float,
t_B float,
t_C float,
t_D float)


insert #t_reconcile

select t.lot_number, sum(t.qty)
from i , t
where i._id = t.event_id
i.transaction_type = 'A'
group by t.lot_number
order by t.lot_number

View 3 Replies View Related

How To Get ID Columns Of Inserted Statements And Use Across Different Queries

Nov 5, 2015

Currently I have this query which I am inserting into budterminals table

--Insert
INSERT INTO budterminals([TERMINALNUMBER], [ACCOUNTINGUNIT], [TERMINALNAME], [MBFTERMINALNAME], [SOURCESYSTEM], [COMPANYCODE], [STATUSID], [CREATEDUSERID], [CREATEDDATETIME], [LASTMODIFIEDUSERID] , [LASTMODIFIEDDATETIME])
SELECT CAST(left(TerminalName, patindex('%[^0-9]%', TerminalName+'.') - 1) as int)
,''
,SUBSTRING(TerminalName , CHARINDEX('-' , TerminalName) + 1, LEN(TerminalName))

[Code].....

Next I am inserting into budcustomers table 
   
--Customer
INSERT INTO [dbo].[BudCustomers]
([LegalName]
,[EffectiveDate]
,[LawsonCustomerNumber]
,[ChangeReason]
,[ImportedRecord]

[Code] .....

Next I am inserting into budcontracts table but here in this table to insert i want the inserted terminalid and customerid column which are unqiue identity based.

So how to do that here in this query

--Contracts
INSERT INTO [dbo].[BUDCONTRACTS]
([CONTRACTNAME]
,[CONTRACTNUMBER]
,[VERSIONSTARTDATE]
,[VERSIONTERMINATIONDATE]

[Code] .....

View 3 Replies View Related

Nested REPLACE Statements Using Multiple Columns

Jun 20, 2007

I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.

Nesting them in the usual way doesn't seem to work as that only allows for one column.

My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)

My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.

Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.

FUNCTION GetFormula
(@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200)
AS
BEGIN
DECLARE @strExpression nvarchar(200)

SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber',@intTotalNumber)
SELECT @strExpression=REPLACE(@strExpression, 'PF',@numPF )
SELECT @strExpression=REPLACE(@strExpression, 'Hours',@numHours )
RETURN @strExpression
END


Many Thanks

View 3 Replies View Related

Several Select Statements?

Jan 16, 2007

Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data

View 5 Replies View Related

How To Use Two Select Statements

May 12, 2008

Both of these work fine separately; How do I join these two Select Statements?

SELECT MPI.CREATE_DT,MPI.MPI_NBR, MPI.LAST_NM,MPI.FIRST_NM,
MPI_CURRENT_ADDR.ADDR_NBR, MPI_CURRENT_ADDR.ADDRESS_1, MPI_CURRENT_ADDR.ADDRESS_2,
MPI_CURRENT_ADDR.CITY,MPI_CURRENT_ADDR.STATE_CD,MPI_CURRENT_ADDR.ZIP_CD,MPI_CURRENT_ADDR.PHN_NBR,
MPI_PERSON.BIRTHDAY,MPI_PERSON.SEX

FROM MPI,MPI_CURRENT_ADDR,MPI_PERSON

WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR)
AND
MPI.CREATE_DT>=20070101
ORDER BY MPI.CREATE_DT

SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD
FROM PATIENT, PHYSICIAN
WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR


Thanks!
Lisa

View 1 Replies View Related

Two Select Statements

Sep 13, 2007

I have a table that list Canadian provinces and American States it looks something like this:

ID | ProvState

Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order.

I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement.

SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvState
UNION
SELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvState

Anyone have any suggestions to this problem?

View 4 Replies View Related

Select Statements

May 13, 2008

Arnie and All others. Thanks for your help.
The previous case became lenghty and then just mixed up a lot.

To make it easy I have created two temp tables and wrote to test select statement .

You will notice that I tired two select statement but they are giving different set of result however the 2nd Select statement not giving the result as should be looking at the following requirement.


--Selected record must RaType='b'
-- PlanID='H321'
-- Gender='0'
--
--And not to include in select if any one of these meets:
-- Hosp='1' in other words it has to be 0
-- ESRD='1' or Rafctor Type in ('g','f') in other words ESRD should be 0 and rafctorType in ('h','i')
-- Dod is not null in other words Dod has to be null
--


--copy from here

GO
Create table #MyTable

( RowID int IDENTITY,
RD varchar(10),
RAType varchar(5),
History varchar(15)
)

INSERT INTO #MyTable VALUES ( '1', 'A', '1111' )
INSERT INTO #MyTable VALUES ( '2', 'S','2222' )
INSERT INTO #MyTable VALUES ( '3', 'D', '2345')
INSERT INTO #MyTable VALUES ( '4', 'I2','1234' )
INSERT INTO #MyTable VALUES ( '5', 'C','3333' )
INSERT INTO #MyTable VALUES ( '1', 'B','4444' )
INSERT INTO #MyTable VALUES ( '2', 'X','5555' )
INSERT INTO #MyTable VALUES ( '1', 'D' ,'66666')
GO

Go
Create Table #MYTable2

(
RowID int IDentity,
RD varchar(10),
RaType varchar(5),
History varchar(15),
PlanID varchar(6),
Hosp varchar(2),
ESRD varchar(2),
RafctorType varchar(3),
gender varchar(5),
dod varchar (5) NULL

)

INSERT INTO #MyTable2 VALUES ( '1', 'A', '1111', 'H321', '0','0', 'g', '0' ,NULL)
INSERT INTO #MyTable2 VALUES ( '2', 'b', '2222', 'H321', '0','0', 'e', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '3333', 'H321', '0','0', 'f', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '4444', 'H321', '0', '0','d', '0',NULL )
Go


Select #MYtable2.History from #MYTable2
INNER JOIN #mytable on #myTable2.History=#mytable.history
Where #MyTable2.RaType='b' And PlanID='H321' And Gender='0' And Hosp<>'1' And ESRD<>'1' AND RafctorType Not in ('g','f') AND Dod is NULL

Select #Mytable2.History from #MyTable2
INNER JOIN #mytable on #mytable2.History=#mytable.history
where #mytable2.Ratype='b' AND PlanID='H321' AND Gender='0' AND(Hosp<>'1') or ((ESRD<>'1') or (RafctorType Not in ('g','f')) OR DOD is NULL)


Go

drop table #Mytable2
Drop table #MYtable

View 5 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

Aug 27, 2014

I'd like to first figure out the count of how many rows are not the Current Edition have the following:

Second I'd like to be able to select the primary key of all the rows involved

Third I'd like to select all the primary keys of just the rows not in the current edition

Not really sure how to describe this without making a dataset

CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,

[Code] .....

Group by fails me because I only want the groups where the Edition_fk don't match...

View 4 Replies View Related

Two Select Statements In Asp.net 2.0 Problem

Jul 26, 2006

 
    Hi Everyone,
    Can you please let me know what is wrong with the following code? I run the following code using path:    http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin
    public void searchResultsWithClinic()    {              SqlConnection myConnection;           string conString;        conString = ConfigurationManager.AppSettings["calendarString"];        myConnection = new SqlConnection(conString);
        String cmdStr1, cmdStr2, cmdStr3;          cmdStr1 = "SELECT sb_clinic_name FROM sb_client_and_clinic WHERE sb_client_id = '" + Request.Params.Get("LoginID") + "'";
        cmdStr2 = "SELECT sb_client_id FROM sb_client_and_clinic WHERE sb_clinic_name = '" + cmdStr1 + "'";                                  SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection);        DataSet DS = new DataSet();        myCommand.Fill(DS, "SearchPaymentResults");
        repeaterSearchPaymentResults.DataSource = DS;        repeaterSearchPaymentResults.DataBind();              myConnection.Close();
               }
 
Incorrect syntax near 'admin'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'admin'.Source Error:



Line 90: SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection);
Line 91: DataSet DS = new DataSet();
Line 92: myCommand.Fill(DS, "SearchPaymentResults");
Line 93:
Line 94: repeaterSearchPaymentResults.DataSource = DS;Source File: d:Inetpubwwwrootjimmymay_30th_2006vcalendar_cssb_SearchPaymentResults.ascx.cs    Line: 92 ---------------------------------------------------------------------------------
Please note that the 'admin' in the error message comes from http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin
thanks,May

View 7 Replies View Related

Combining Two Select Statements

Apr 2, 2007

I have a SP returning the following result The select statement for this is



Code:

          SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount
FROM dbo.Test2 INNER JOIN
dbo.test3 INNER JOIN
dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN
dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN
dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId)
GROUP BY dbo.TEST1.[OFFICE NAME]
ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC  name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is



Code:

Select  test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks

View 2 Replies View Related

Joining Two Select Statements

Feb 4, 2008

I only want to count the titleids that are on loan can I join these two statements or write the stored procedure a different way?  I hope this makes sense.
select count(libraryrequest.titleid) as [Presently on Loan], libraryrequest.titleid, media.[description]as Media
from libraryrequestjoin titles on titles.titleid = libraryrequest.titleidjoin resources on resources.titleid = titles.titleidjoin media on media.mediaid = resources.mediaidgroup by libraryrequest.titleid , media.[description]
select distinct requestors.Requestorid, titles.title, resources.quantityowned,requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip, libraryrequest.shipdate,libraryrequest.duedate, libraryrequest.returndate
from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.requestorid = Requestors.requestoridjoin resources on resources.titleid = titles.titleid

View 5 Replies View Related

USING MULTIPLE SELECT STATEMENTS

Apr 23, 2008

how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS'
 Code1....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE1')
 
Code2.....
 
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE2')
 
 thanks in advance

View 10 Replies View Related

SELECT EXEC Statements In SQL

Jun 6, 2008

I  am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
 
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints'
 FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID
WHERE  M.EMAIL = @UserEmail
 

View 5 Replies View Related

Two Select Statements On One Table

Apr 1, 2004

Hi,

newbie here.

Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this.

SELECT column1
FROM table1
WHERE column2 = (select column2 from table1 where column3 = 14)

Any ideas greatly appreciated.

View 1 Replies View Related

Multiple Select Statements

Dec 22, 2005

Hi guys and gals,
I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:
'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
My Code is below:
//Start of sql
CREATE PROCEDURE ADMIN_GetSingleUsers( @userID  int) AS
DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,    Cast(Users.Active as  varchar(50)) as Active,   Cast(Users.Approved as  varchar(50)) as Approved,   Users.Unit_ID As usersUnitID,   *    From TITLE, Users   WHERE    User_ID = @userID AND   TITLE.TITLE_ID = Users.Title_ID )
Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL
//End of sql
Can you point to what I am doing wrong? Thanks in advance!

View 4 Replies View Related

Using IF In SELECT Statements? Beginner.

Mar 31, 2003

I am having problems using IF statements. In fact, I don't even know if it is possible to use "IF" in a SELECT statement. Here's my dilemma:

For each employee in my database, I have a numeric rating. I want to assign the rating to a category (green, yellow, red), based on the range the rating falls into.

My data currently looks like this:
EmpName &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Rating
employeeX &nbsp; 100
EmployeeY &nbsp; 85
EmployeeZ &nbsp; 25

After the magic of SQL, I want the data to end up looking like this (actually, my boss wants it like this):

EmpName&nbsp;&nbsp;&nbsp; Green Yellow Red
employeeX &nbsp; &nbsp;100
EmployeeY &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 85
EmployeeZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25

The numeric rating would be inserted into the appropriate column, based on these ranges:
95 to 100 = green
80 to 94 = yellow
< 80 = red

I thought I could use a CASE statement, but I just learned that I can't do comparisons (I'm obviously a beginner). And I can't figure out how to use an If statement (or IIF).

Can anyone help?

Thanks!
Colleen

View 2 Replies View Related

Combining Select-Statements

Sep 5, 2006

Hi !

I have to divide and analyse a bigger table to get a smaller one. My target is to make this division in only one SQL-Statement.

My table looks like this:


Code:


ArtNo Code
16637 C
38827 A
16637 D
44883 C
44883 C
63853 H
24564 D
24564 A


(ArtNo is not the Primary Key)

My SQL-Statement should now find out how often every "code" belongs to a "ArtNo".

The result should be:

Code:



ArtNr A C D H
16637 1 1
38827 1
34343 1
44883 2
63853 1
24564 1 1


Does anyone have ANY Idea how I could realize this as easy as possible (without View etc.) ??

Regards
Gawan

View 1 Replies View Related

Is It Possible To Combine 2 SELECT Statements??

Jan 28, 2005

I am not sure if this is possible, but I was wondering if I can combine 2 SELECT statements so as to aquire a percentage..

I could be overthinking this....I am fairly new to SQL writing.

Here is an example of the 2 SELECT statements that I am using:


Code:

SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS' AND [Overall Rating] = 'Good'



Code:

SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS'

Within my output I am than taking the the data from the 1st query and dividing it by the 2nd query to get a percentage.

I was hoping that I could accomplish the same action within one SQL statement.

Thank you for your consideration!

View 4 Replies View Related

Nested Select Statements

Jan 26, 2005

I need help nesting select statements. Here's what I'm trying to do:

select (select e.emp_name_lf as employee, e.emp_id
from employee e, install_payroll_detail ipd
where e.emp_id = ipd.emplno)
e.emp_name_lf as username
from employee e, install_payroll_master ipm
where e.emp_id = ipm.entered_by

I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated.

Thanks!

View 3 Replies View Related

Difference Of Two Select Statements

Aug 19, 2004

Hi All,

I want to find difference of two Select statement in SQL Server 2000. As in Oracle we have "MINUS". Do we have the same thing in SQL Server also.

Thanks with Regards.

-Mohit.

View 2 Replies View Related

Joining Select Statements

Apr 23, 2008

Hi ,

If any one could help me in Joining these two select statements:

How Can I have Order by and Having Clauses in my final select list ?

***********
SELECTMIN(DISTINCT x.ProductionDate) as ProductionDay,COUNT(DISTINCT v.Number) as Timelost
FROMmaster..spt_values AS v
INNER JOIN
(
SELECTDATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute,
DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute,
Convert(varchar(10),StartDate,102) as ProductionDate
--,COUNT(AlarmMessage) As Occurances
FROM RPMS.dbo.List_PaintShopAlarms
WHERE --Convert(varchar(10),StartDate,102) = @vcProductionDay and
Convert(varchar(10),StartDate,102) = Convert(varchar(10),EndDate,102)
AND (AlarmMessage LIKE '7FM%' OR AlarmMessage LIKE 'CONV%' OR AlarmMessage LIKE 'EMG%' )
)
AS x ON x.StartMinute <= v.Number
AND x.EndMinute > v.Number
WHEREv.Type = 'p' AND Number < 1440
and (x.ProductionDate BETWEEN Convert(varchar(10),@dtProductionDayStart,102) AND Convert(varchar(10),@dtProductionDayEnd,102))
group by x.ProductionDate
order by x.ProductionDate asc
***********
and
***********
SELECT Convert(varchar(25),[StartDate],102)as ProductionDay
,COUNT(AlarmMessage) As Occurances
FROM [RPMS].[dbo].[List_PaintShopAlarms]
WHERE
([AlarmMessage] LIKE '7fm%' or [AlarmMessage] LIKE 'Conve%') AND
Convert(varchar(25),[StartDate],102) BETWEEN Convert(varchar(25),@dtStartDate,102) AND Convert(varchar(25),@dtEndDate,102)
and DATEPART(dw, [ProductionDay]) <> 1

GROUP BY Convert(varchar(25),[StartDate],102)
HAVING SUM([Duration]) < 86400
ORDER BY Convert(varchar(25),[StartDate],102) asc
*************

View 2 Replies View Related

Join 2 Select Statements

Dec 22, 2014

how can i join these 2 queries to produce 1 result

Query 1:
select R.Name, T.Branchid, t.TradingDate,t. TransactionDate,
convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber,
t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt
from TransactionHeader T
join SalesRep R on
R.SalesRepid = T.SalesRepid
where T.SalesRepid is not null

Query 2 :
select C.TradingName,C.AccountNo
From Sale S
Join ClMast c on
C.Clientid = s.CustomerAccountID

The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno..Field Saleid is present in Transactionheader Table and Sale table

View 5 Replies View Related

Track Select Statements.

Aug 29, 2007

different Users are running select statements under their own login. they have readonly permission to the database. My goal is to track all those queries and send notification which tables were opened and which columns were displayed...Looks like I need trigger on select statement for the table...( Unfortunetely it doesn't exists)
I collect all those statements in trace file and in the night time I stop trace, move file data to table and then step by step try to massage sql queries using VB.net... Most of those queries are so complicated, have derived tables inside and it is really hard to parse these sql statements...
1.If anybody has an idea how to say: User B opened table tbl_A col_B twice per today...using different methodology rather then go through SQL.(Other words how to create an event on a table which will be fired in a case of request select any column of that table?)
2.If somebody know where we can take good parser which will help with this job?
Thanks

View 14 Replies View Related

Using Two Select Statements In IF Exists

Jul 20, 2005

I tried this:IF Exists (Select 1 from TESTA..POP10100 where PONumber=@PONumber) and(Select 1 from TESTA..POP30300 where POPType<>2 andPOPRCTNM=@ReceiptNo)BEGINENDIt says Incorrect Syntax error? I tried to cover the whole thing withan extra parantheses. It wont work eitherWhat am i doing wrongThanks, Girish

View 2 Replies View Related

Storedprocedure Which Contains Two Select Statements

Mar 5, 2008

Hi

I have a question.

I have to write a stored procedure.I have a search page having four fields.Giving any of the field should fetch the whole record and display in the gridview. My trouble starts here I have a button field in gridview1 . when i click on the button there should be another gridview which displays refunds of particular customer i.e from another table.There is only one common colum in the two tables. based on that colum value we have to fetch from second table.
now my question is :
how to capture the colum value of first select statement and give it as input to second select staement.

my code is here :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[search]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN
Select*From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))
Select * From refunds where CNo = @val1
END

here if i fill CNo text box in my search page its giving the value
but all the time user may not give CNo.He may search customers based firstname ,lastname etc.
so what should i do to capture CNo from first select statement and give it as input to second select statements

anyone help is appreciated.

ramya.


View 10 Replies View Related

Combining Two SELECT Statements (How To?)

Mar 26, 2008

I have two select statements on a single table as follows:


SELECT * FROM DOCUMENTS

WHERE FILEDATE LIKE '%1987'


SELECT DOCNUM, COUNT(*) AS TOTALS FROM DOCUMENTS

GROUP BY DOCNUM

HAVING (COUNT(*)>1)


I want to combine the them to provide a list of rows in the table that have duplicate "DocNum" but only within the subset of rows LIKE %1987. When I first looked into how this is done I was sure that you would use a subquery. I tried to combine them using the first one as the outer query and the COUNT as the subquery. However, this gave an error that the subquery was returning a value that did not match what the outer query was expecting. So my questions are: Do I really need a subquery in the first place since this is all within one table? And, if not, how can this be done?

Thanks!

DeBug

View 12 Replies View Related

Transactions With Select Statements

Sep 11, 2006

Hello everyone

I need to have a transaction with a select statement like so

trans = cn.beginTransaction

Insert into order table an order record

select unique order Id from order table

insert record 1 into orderdetails using the orderid

insert record 2 into orderdetails using the orderid

commit trans



I am of course experiencing record locking after my first insert. I cannot select records from the order table even though I need the inserted OrderId in order to insert into the orderitemst table.

I have tried switching the isolation level to readuncomitted on my transaction but sp_who2 still shows a block even with the readUncomitted Isolation level.



A foreign key on the orderitems table prevents me from inserting orderitems without a parent order id to prevent orphans. Is this a common transactional issue?

Any help is greatly appreciated.

View 1 Replies View Related

How Do You Use Declarations In Select Statements?

Feb 22, 2008

Hello all.

I have a fairly simple question (I think) about using the @Declare in a select statement.

Essentially what I am trying to do is this


DECLARE @SELECT varchar(max)
SET @SELECT = '*'

SELECT * FROM test

I know this doesn't actually work but it conveys what I am trying to do.

Is there a way to do this?

View 7 Replies View Related

SELECT Query - Different Columns/Number Of Columns In Condition

Sep 10, 2007

I am working on a Statistical Reporting system where:


Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by


different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1


Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2


Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:


Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:


How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View 1 Replies View Related







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