Select Query Involving Many JOIN Statements.
Mar 19, 2008
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email
FROM #temp_pregovb, [Survey Return]
INNER JOIN SelectedInvited ON
[Survey Return].SelectedID = SelectedInvited.SelectedID
--*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact]
ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID
WHERE
[panelist contact].email=#temp_pregovb.email
AND SelectedInvited.CellRef IN (
SELECT surveycell
FROm [Survey Cells]
WHERe SurveyRef='5')
View 3 Replies
ADVERTISEMENT
Apr 8, 2008
Hello friends,
I am inserting non-english strings into my database table from my java program.
Code Snippet
sql = "insert into static_string1 values (?)";
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1,statString);
where, statString is a string variable containing Hebrew characters.
Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database.
The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.
Code Snippet
String sql = "select String_Id from Static_String1 where String like ('" + statString +"')";
Statement statement=connection.createStatement();
ResultSet rs=statement.executeQuery(sql);
rs.next();
int stringId=rs.getInt("String_Id");
I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below
Code Snippetselect String_Id from Static_String1 where String like( ' ืืื' );
But even this is returning null rows, even though the entry is present in the table Please help me out asap.
Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.
View 14 Replies
View Related
May 11, 2007
Table 1
ID PID From To Code
1 1 14/02/07 17/02/07 X
2 1 17/02/07 19/02/07 X
3 1. 19/02/07 23/02/07 E
4 1 26/02/07 28/02/07 X
5 1 1/4/07 1/5/07 E
6 2 01/03/07 03/03/07 X
7 2 04/03/07 10/03/07 X
8 2 10/03/07 14/03/07 E
Result
ID PID Date
4 1 26/02/07
7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next from record should be the same date as the to date. If the code is E then the next to date can be anytime after the previous to date.
I want to be able to report on all record where there is a day difference between the previous to date. I.e. ID 4 and 7 the previous records both have an X and there is at least a days difference between the dates.
View 4 Replies
View Related
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
May 15, 2008
I have two seperate tables that I need to get the data into one select statement. Here is the two separate select statements that I have created.
Select AGEGROUP, [YEAR], SUM(CASES) as Cases FROM tbHosp a
LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID
LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID
LEFT JOIN tbSEX g on g.SEXID = a.SEXID
where a.AgeGroupID in (1,2,3)
and a.YEARID in (1,2,3)
Group BY AgeGroup, [YEAR]
ORDER BY AgeGroup, [YEAR]
Select AGEGROUP, [YEAR], SUM(POPULATION) as [Population] FROM tbPopulation a
LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID
LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID
LEFT JOIN tbSEX g on g.SEXID = a.SEXID
where a.AgeGroupID in (1,2,3)
and a.YEARID in (1,2,3)
Group BY AgeGroup, [YEAR]
ORDER BY AgeGroup, [YEAR]
Results - First Select with tbHosp
AgeGroup YEAR CASES
<1 2001 9
<1 2002 32
<1 2003 10
1-4 2001 13
1-4 2002 11
1-4 2003 23
5-9 2001 13
5-9 2002 14
5-9 2003 34
Second Select with tbPopulation
AgeGroup YEAR POPULATION
<1 2001 40686
<1 2002 39768
<1 2003 40438
1-4 2001 174346
1-4 2002 170191
1-4 2003 167223
5-9 2001 247071
5-9 2002 242548
5-9 2003 237816
What I am trying to do is get one result set with Population and Cases together like the following
AgeGroup YEAR CASES POPULATION
<1 2001 9 40686
<1 2002 32 39768
<1 2003 10 40438
1-4 2001 13 174346
1-4 2002 11 170191
1-4 2003 23 167223
5-9 2001 13 247071
5-9 2002 14 242548
5-9 2003 34 237816
Any help would be greatly appreciated.
Thanks!
View 12 Replies
View Related
Jun 5, 2014
In t-sql 2008 r2, I have 2 select statements that I would like to join by calendarID . I would like to obtain the results in the same query but I keep getting syntax errors.
The first select is the following:
SELECT Section.number, Section.homeroomSection,
Course.number, Course.name, Course.homeroom, Calendar.calendarID
FROM Section
INNER JOIN Course
ON Course.number = Section.number
INNER JOIN Calendar
ON Course.calendarID = Calendar.calendarID
The second select is the following:
SELECT Person.studentNumber, Enrollment.grade, Calendar.calendarID, Calendar.name, Calendar.endYear
FROM Enrollment
INNER JOIN Person
ON Enrollment.personID = Person.personID
INNER Calendar
ON Enrollment.calendarID = Calendar.calendarID
I would like the following columns to display:
Section.number, Section.homeroomSection, Course.number, Course.name, Course.homeroom, Calendar.calendarID
Person.studentNumber, Enrollment.grade, Calendar.name, Calendar.endYear
Thus can you show me how to change the sqls listed above to obtain the results I am looking for?
If possible I would like the sql to look something like the following:
select Section.number, Section.homeroomSection, Course.number, Course.name, Course.homeroom, Calendar.calendarID
Person.studentNumber, Enrollment.grade, Calendar.name, Calendar.endYear
from
(SELECT Section.number, Section.homeroomSection,
[Code] ....
View 6 Replies
View Related
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
Nov 8, 2006
Hi, everyone.
I have read a lot of topics about execution plan for query, but I got little.
Please give me some help with examples for comparing different select statements to find the best efficient select statement.
Thank you very much.
View 4 Replies
View Related
Aug 31, 2015
I have setup a Database Audit Specification as follows:
Audit Action Type: SELECT | Object Class: DATABASE | Object Name: SHOPDB | Principal Name: public
Now, when I perform a SELECT query with a bound parameter such as:
SELECT * FROM myTable WHERE name='queryname'
What I see through the Audit Logs is something like:
SELECT * FROM myTable WHERE name='@1'
I understand that it is by design that we cannot see these parameters throught Database Level Auditing. I would like to know whether it is possible to see these parameters by any other means using
(1) SQL Server Enterprise Edition,
(2) SQL Server Standard Edition, or
(3) by an external tool.
View 9 Replies
View Related
Apr 30, 2008
Hi All,
I have a table called [History] with the following fields and types.
Date(datetime), PriceZone(varchar), ProductID(varchar), ProductName(varchar), Category(varchar), SubCategory(varchar), Price(decimal 2 places), Cost(decimal 2 places), Units(Big Int), DollarSales(decimal 2 places), Margin(decimal 2 places), Profit(decimal 2 places)
Each record represent information for each ProductID, per PriceZone, per Date. There's always one record per product; per zone; per Date.
Here is the sample of the records (Just for example I am just using one product in one zone)
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost, Units, DollarSales, Margin, Profit
2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,10,20,25,5
2008-04-29,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,8,16,25,4
2008-04-28,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,15,30,25,7.5
2008-04-25,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,13,26,25,6.5
2008-04-22,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,11,22,25,5.5
2008-04-21,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-20,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-19,Zone1,001,Lays Chips,Snacks,Chips,1.95,1.5,18,35.1,23.08,8.1
2008-04-16,Zone1,001,Lays Chips,Snacks,Chips,1.9,1.5,20,38,21.05,8
2008-04-15,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,40,72,16.67,12
2008-04-10,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,32,57.6,16.67,9.6
2008-04-09,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,27,48.6,16.67,8.1
2008-04-08,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,29,52.2,22.22,11.6
2008-04-01,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,30,54,22.22,12
Now using the table information, I want to run a script to get the following fields;
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates
So output in the above example would be;
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates
2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,46,224,92.00,424.70,25,22.30,5.00,88.70
Here;
Date is the maximum date available in the entire [History] table. Meaning the "Date" value will be same for all the records in the output.
Price is the Price for that product in that zone on Date
Cost is the cost for that product in that zone on Date
About "Last4Dates" & "Last12Dates":
"Last4Dates" is the last 4 dates in the entire table from 'Date" value , including "Date" value and prior 3 dats before the "Date" value. In the above data set example it would be from "2008-04-25" to "2008-04-30"
"Last12Dates" is the last 12 dates in the entire table from 'Date" value, including "Date" value and prior 11 Dates before the "Date" value. In the above data set example it would be from "2008-04-09" to "2008-04-30"
UnitsSum_ForLast4Dates and UnitsSum_ForLast12Dates is the sum of Units for 4 and 12 weeks respectively; per product, per zone
DollarSalesSum_ForLast4Dates and DollarSalesSum_ForLast12Dates is the sum of DollarSales for 4 and 12 weeks respectively; per product, per zone
AvgMargin_ForLast4Dates and AvgMargin_ForLast12Dates is the Average of DollarSales for 4 and 12 weeks respectively; per product, per zone
ProfitSum_ForLast4Dates and ProfitSum_ForLast12Dates is the sum of Profit for 4 and 12 weeks respectively; per product, per zone
How Can I achieve this in SQL?
Can someone please help me as soon as possible.
Thanks,
Zee
View 3 Replies
View Related
Jan 8, 2005
Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:
create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go
create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go
create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go
I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @MinDate - @MaxDate)
Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--------- ---------------- --------------- ---------------- ----
HR 5 7 12 .84
Finance 10 15 25 1.75
So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.
One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!
View 2 Replies
View Related
Sep 28, 2007
Hi all,
I am quite surprise by getting wrong resultset from a simple query like:
select Order_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'
In the database, there are data since the last two years. There is no data beyond today's date, in fact. But when I tried to query for 'Order_No' with specified cust_id within above date range (which data is not in the DB), the result will be the 'Order_No' from '6/15/2007' to '6/31/2007'. Isn't it supposed to return null? simply because there is no such date as 2008 yet in the DB.
Help from anyone is needed. Thanks in advance.
Sha.
View 11 Replies
View Related
Apr 1, 2006
I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
i'm somewhat at a loss of where to begin.
View 6 Replies
View Related
Nov 27, 2001
Here is the working query, shortened for the example:
SELECT a.SalesMan,a.CustomerName,b.Entry_Comments,b.Entry _Date
FROM MyMaster a LEFT OUTER JOIN MyDetail b ON a.id = b.id WHERE blah ORDER
BY blah
This works fine and I get all my detail reocrds for each master. Now I need
to be able to select only a single most recent b.Entry_Date. How can I do this, Ive played with MAX but cannot get the sytax correct?
Thanks,Adrian
View 4 Replies
View Related
Feb 28, 2008
i have select query....
select distinct duo.messageid_ from [detected unique opens] duo
left outer join (select MailingID, count(*) as cnt
from lyrCompletedRecips
where mailingid = duo.messageid_
and FinalAttempt is not null
AND FinalAttempt >= '1945-09-10 00:00:00'
group by MailingID) ad
on ad.mailingid = duo.messageid_
i m getting error like:
The column prefix 'duo' does not match with a table name or alias name used in the query.
can anyone tell me what's the reason?
thanks.
View 3 Replies
View Related
Feb 1, 2008
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
View 2 Replies
View Related
Feb 8, 2008
I have a very confusing/complicated query that I am trying to run and getting not the results that i want.
Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables.
When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.
Select t1.*, t3.field2, t3.field3
FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6
I suspect there is plenty wrong with this query - where should I start correcting it?
View 10 Replies
View Related
Jan 20, 2005
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
Thanks in advance
greg
View 8 Replies
View Related
Aug 12, 2012
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
[code]...
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
View 2 Replies
View Related
Oct 19, 2015
script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies
View Related
Aug 13, 2007
I know that you can use the JOIN's to join together data from different tables within a database.
But my question is, can you join data from different databases?
i.e. get a users name from database A using an id you got from database B
View 9 Replies
View Related
Jul 20, 2005
I have the following situation. We sell books on our website, and someof the books have more than one author. So I needed to create amany-to-many table, which is the intermidiate table between the authortable and the book table.I can't get the right join statement to work. I've used the code below,submitting an isbn (book id number) to identify the book, but the returnfrom the query simply sends me back all of the authors that are in themany_to_many table(called the book_to_author table here). I'd like it to return only theauthors attached to that isbn, instead of all the authors that are inthat table. What's wrong with the code below? Thanks for your help!SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id ASExpr3 FROM author a INNER JOIN book_to_author m ONa.author_id=m.author_id CROSS JOIN book_detail_final bf WHEREbf.isbn='"&isbn&"' order by m.id descBill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Nov 20, 2006
In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:
DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL
SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?
Gerrit
View 3 Replies
View Related
Aug 9, 2013
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
