Integration Services :: Joining Two Select Queries Results In One Row?
Jun 12, 2015I want to get output of below query in single row.
Select 'Name'
Select 'Surname'
Expected output is Name,Surname
ADVERTISEMENT
'joining' Results Of 2 Queries
Jul 20, 2005Does anyone know how I can 'join' the results ofone SQL query to the bottom of another?Eg. I have two queries:1. SELECT Name, Surname FROM People WHERE Surname = SmithNAME SURNAMEAdam SmithJohn SmithMichael SmithSteve Smith2. SELECT Name, Surname FROM People WHERE Surname = JonesNAME SURNAMEBob JonesLarry JonesTom JonesWhat I want to produce is:NAME SURNAMEAdam SmithJohn SmithMichael SmithSteve SmithBob JonesLarry JonesTom JonesHowever, if I use UNION like this:SELECT Name, Surname FROM People WHERE Surname = SmithUNIONSELECT Name, Surname FROM People WHERE Surname = Jonesit mixes up all the results:NAME SURNAMEAdam SmithBob JonesJohn SmithLarry JonesMichael SmithSteve SmithTom Jones(I guess it's sorting by the first field, NAME).Is there a way to stop it sorting the results, so that itjust tacks the second query results to the bottom of thefirst query results?(I realise I could use "ORDER BY Surname" to get the same resultin this simple example, but for the more complicated queriesI want to use it won't work).Thanks for any help,Matt.
View 3 Replies View RelatedT-SQL (SS2K8) :: Joining Results Of Two Queries Without Creating Temporary Tables?
Nov 16, 2014In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
[code]....
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
Integration Services :: Joining Object Variable Values In Script Task
Jul 23, 2015I have a object variable named "UserList" which gets populated through execute sql task - stored procedure.I wish to concatenate all the values in "UserList" using ssis script task - in lang vb.Output should be like (User1,User2,User3,....)
View 8 Replies View RelatedIntegration Services :: Two Results From Single Task
Jul 22, 2015I have a table with email addresses and CC_Flag.
Email | CC_Flag
xxxx 0
yyyy 1
zzzz 1
Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag..Is it possible to fill two variables from a single SQl Task,.
Integration Services :: Get Data From Source By Executing Set Of Queries That Have Temp Tables
Jul 29, 2015I need to grab data from teradata(using odbc connection).. i have no issues if its just bunch of joins and wheres conditions.. but now i have a challenge. simple scenario, i have to create volatile table, dump data into this and then grab data from this volatile table. (Don't want to modify the query in such a way i don't have to use this volatile table.. its a pretty big query and i have no choice but create bunch of volatile tables, above scenarios is just mentioned on simple 1 volatile table ).
So i created a proc and trying to pass this string into teradata, not sure if it works.. what options i have.. (I dont have a leisure to create proc in terdata and get it executed when ever i want and then grab data from the table. )
Integration Services :: Iterating Over Object In SSIS With Fix Number Of Results Each Time
Aug 11, 2015I've got this issue with a query in SSIS. From a table in SQL Server I'm getting over 25000 different identifiers. These identifier are associated to many values in a table in one Oracle Database. This is the schema that I have implemented for doing this.
The problem is that some days the identifiers can be over 45000, and at this point perform a loop for every one is not the best solution (It can take to much time to get the result). Previously I have performed another query where from the SQL statement.
I am creating and sending a unique row with all the values concatenated and then I have recover this unique string from an object and use it to create the query in the ODBC Source that invoke the table in Oracle: something like this:
'Select * from Oracle_table' + @string_values
with @string_values = 'where value in (........)'. It works good because the number of values is small enough to be used, like 250. But in this case I can not use this approach because the number is really big and obviously the DBA of Oracle is going to cancel the query.
So I wonder, how can I iterate over the object getting only a few number of values everytime, something like 300 or maximum 500, to avoid the cancellation of the query but at the same time doing the minimum number of loops.
Integration Services :: Assign Variables To Multiple Table Results From Stored Procedure
Sep 21, 2015If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?
View 6 Replies View RelatedIntegration Services :: Oracle Procedure Is Not Returning Results When Executing From Script Task On SSIS
Jul 13, 2015I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable. i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider. I'm able to execute the procedure successfully, but when i do check (while dr.Read()) its not returning any records. But I know as per stored procedure results, it should return 66 records.
Dim conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader
Dim QSQL As String
[code]...
Joining Two Queries
Jun 2, 2008Hello I have two queries that I would like help joining: Query #1SELECT UserName, (SELECT COUNT(*) AS Expr1 FROM Jokester WHERE (InvitedBy = aspnet_Users.UserName)) AS invsFROM aspnet_Users Query #2 SELECT UserName, SUM(Hits) AS HitsFROM (SELECT UserName, (SELECT COUNT(*) AS Cnt1 FROM (SELECT COUNT(*) AS Cnt2 FROM Hits WHERE (JokeId = j.JokeId) GROUP BY UserId) AS T1) AS Hits, (SELECT AVG(CAST(Rating AS numeric(12, 2))) AS Rating1 FROM Ratings WHERE (JokeId = j.JokeId)) AS Rtng FROM Jokes AS j) AS t2WHERE (Hits >= 1) AND (Rtng >= 3)GROUP BY UserName They both return correct values, I just want to join them on UserName Thank you, Louis
View 8 Replies View Related? Joining Two Queries
Feb 27, 2005Right now I have two Queries that I've created:
What I'm wanting to do is graft everything from Query1 onto Query2 but only where Query1.UserID = Query2.UserID . Any suggestions on how I would go about doing this?
--Query 1
(
SELECT
PC1.ID,
PC1.UserID,
PC1.ModuleID,
PC1.ModifiedDate
FROM
Projex2_0_0_Cores PC1
WHERE
PC1.ModuleID = 369
)
--Query 2
(
SELECT
PC2.UserID,
MAX(PC2.CreatedDate) as CreatedDate
FROM
Projex2_0_0_Cores PC2
WHERE
PC2.ModuleID = 369
GROUP BY
PC2.UserID,
PC2.ModuleID
)
Joining Two Queries
Apr 29, 2008Hi Can anyone help me join these two quries together?
SELECT SUM(SchemeAccount.TotalVar) AS TotalVar, Scheme.Code
FROM SchemeAccount LEFT OUTER JOIN
Scheme ON SchemeAccount.SchemeID = Scheme.SchemeID
GROUP BY Scheme.Code
and
SELECT SUM(Variation.VarAmount) AS VarAmount, Scheme.Code
FROM Variation RIGHT OUTER JOIN
Scheme ON Variation.SchemeID = Scheme.SchemeID
GROUP BY Variation.SchemeID, Scheme.Code
Joining Queries For An Export?
Jan 9, 2004I have three queries - but in the end I want one list to export. Is their anyway I can join the queries by B.ADMINISTRATOR so that I could export one list with the Administrator and the three counts separately. I don't want to go through the process of putting them into tables and then joining them later.
SELECT B.ADMINISTRATOR, COUNT(*) FROM SPONSOR_PRIMARY AS A, SPONSOR_SECONDARY AS B, SPONSOR_TERTIARY AS C, SALES.DBO.COMPANY AS S
WHERE (A.SPONSORID = B.SPONSORID AND A.SPONSORID = C.SPONSORID AND S.XTELELINK = B.ADMINID AND S.PROVIDER_TYPE = 'TPA Only') GROUP BY B.ADMINISTRATOR
SELECT B.ADMINISTRATOR, COUNT(*) FROM SPONSOR_PRIMARY AS A, SPONSOR_SECONDARY AS B, SPONSOR_TERTIARY AS C, SALES.DBO.COMPANY AS S
WHERE (A.SPONSORID = B.SPONSORID AND A.SPONSORID = C.SPONSORID AND S.XTELELINK = B.ADMINID AND S.PROVIDER_TYPE = 'TPA Only' AND C.SPONSOR_SEARCH = 1) GROUP BY B.ADMINISTRATOR
SELECT B.ADMINISTRATOR, COUNT(*) FROM SPONSOR_PRIMARY AS A, SPONSOR_SECONDARY AS B, SPONSOR_TERTIARY AS C, SALES.DBO.COMPANY AS S
WHERE (A.SPONSORID = B.SPONSORID AND A.SPONSORID = C.SPONSORID AND S.XTELELINK = B.ADMINID AND S.PROVIDER_TYPE = 'TPA Only' AND C.SPONSOR_CHANGE = 1) GROUP BY B.ADMINISTRATOR
Transact SQL :: Joining / Combining Two CTE Queries?
Apr 29, 2015I have these two CTE queries, the first one is the existing one which runs fine and returns what we need, the second is a new CTE query which result I need to join in to the existing CTE, now that would be two CTE's can that be done at all?The second query is working on the same table as the first one so there are overlaps in names, and they are the same, only columns I need to "join" is the "seconds" and "AlarmSessions".
;with AlarmTree as (
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
[code]....
Transact SQL :: Joining Two Queries (one Regular And One Grouped)
Apr 29, 2015I have these two queries I would like to join, however the later is a grouped query how can I join it with the first query? Has to be joined on EventId. The second query is a total table scan.
SELECT AH.EventID,
AH.TechnicalAddress, AH.AlarmAlias, AH.AlarmPath as [OrgAlarmPath], AH.AlarmCounter as AlarmCount, AH.EventDateTime as EventTime,
AH.[Priority], AH.AlarmMessage, AH.EventText, AH.CallListName, AH.AlarmReadDate as EndTime,
AH.alh_EventEndedUserRemark as [EndRemark] --, SUM(seconds) here, and AlarmSessions here
FROM AlarmHistory AH
[Code] ...
2)
WHERE ia.EventTypeId = 0
group by ia.EventId
order by EventId desc
Joining Two Results Sets
Apr 8, 2008Dear friend, the following is my query.
1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'
2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'
i need to join this two resultsets. please give me sample query to join this two result sets
Joining Two Results From Same Table
Apr 9, 2008Dear friend, the following is my query.
i am retreiving from the same table with different condition
1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'
2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'
this two query gives resulsets
i need to join this two resultsets. please give me sample query to join this two result sets
SQL 2012 :: Queries Based On ER Diagram And Joining Tables
Feb 22, 2015I need to create a few select queries based on an er diagram.
These are my Create Table statements and import statements:
Create Table Agent (Aid integer primary key, Pid integer, aName text);
Create Table Product (Pid integer primary key, pName text);
Create Table Supplier (Sid integer primary key, sName text);
Create table Supplies (Sid integer, Pid integer, price decimal(8,2));
.import agent.txt Agent
.import product.txt Product
.import supplier.txt Supplier
.import supplies.txt Supplies
I think I got all my create table statements are correct.
I need to Find the number of agents for each supplier that has at least one agent. The result should be tuples of the form (sid, sName, number of agents)
-Select Sid, sName, count(Aid) from Agent A join Supplier S on (S.Sid = A.Sid) group by S.Sid, S.sName, Aid;
But it gives me this error: no such column: A.Sid
Im thinking I might have a problem with my create table statement and/or primary key statements?
Integration Services :: SSIS 2012 - Set Up Parameter In Select Statement
Jun 19, 2015I am using a OLE DB source task and i want to setup a parameter in the select statement. Is this possible and how would this be done. I know you can pass a parameter in the where clause like:
select id, lname, fname, startDate, endDate from Employee where id = ?
How would i set startDate and endDate as parameters as i would like to be able to change these parameters when running the package. I would like to set dates in the select statement as parameters like how you would in the where clause.
Select id, lname, fname, ? startDate, ? endDate, from employee where id = ?
I would like to be able to change these dates without having to go in to the package and hard code it back in there and then deploy.
The package uses a OLE DB source to Flatfile Destination.
Integration Services :: Write A Select CAML Query With Where Clause In SSIS?
Sep 17, 2015I have 3 columns in my SharePoint list. I wasn’t to select the max date for a particular condition. How to write it in CAML?So from the below table I want to get the max date for column 1 when the value is A or B
View 3 Replies View RelatedIs There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?
Apr 1, 2007hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedUsing Results Of First Query In Other Queries
Sep 1, 2004Hi,
I would like to use the result table of the first query in a number of other queries. How do I do this ?
Thanks.
Combining Queries/ Results
May 4, 2005I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))
When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?
Combine Results From 2 Queries
Nov 9, 2005I'm trying to create a list of orders in my db that has been created correctly (some orders are not dealt with correctly...) An order should go from "open -> assigned" to "assigned -> responded" status.
I got the following query:
select org.name, count(order) AS correct, NULL AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
where
o1.status = 'Open -> Assigned'
and o2.status = 'Assigned -> Responded'
and o1.time_stamp < o2.time_stamp
This gives me a list of all organisations with the correct number of orders in the system...
But now I need to add the total number of tickets they got in the system. So I was thinking about a union with a query without the were constraints
UNION 'with the above query
select org.name, NULL AS correct, count(order) AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
..but that gives me a list like this:
name correct total
org1 324 NULL
org1 NULL 423
How can I combine them, or maybe doing it a better way?
Adding The Results Of 2 Queries
Jun 21, 2007Hi,
I have to queries that return tables with the same names. How do i add these 2 so it returns one table?
Thanks for your help.
Mike
Merge The Results Of Two Queries
Jul 20, 2005Hi all,Here is my problem, I have 3 tables :People-------------IDPeopleFirstnameLastnameCars------------IDPeopleCarnameBoats------------IDPeopleBoatname1 person can have 0 or n car/boatI want to a result set displaying : Firstname, Lastname, NumberOfCars,NumberOfBoatsI have two queries, but i want to merge the results in one. how can i dothis ?This one gives me FIRSTNAME, LASTNAME and CARCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Cars.CarName) AS CARCOUNTFROM dbo.People LEFT OUTER JOINdbo.Cars ON dbo.People.IDPeople = dbo.Cars.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThis one gives me FIRSTNAME, LASTNAME and BOATCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Boats.BoatName) AS BOATCOUNTFROM dbo.People LEFT OUTER JOINdbo.Boats ON dbo.People.IDPeople = dbo.Boats.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThanks in advancePhil
View 2 Replies View RelatedMDX Queries Return Different Results
May 29, 2008Hi,
I am new to MDX and I have created a query listed below, this returns the correct information from the cube. However when I split the query into a CREATE SET and Query the data returned is wrong. I need to include the set creation in the cube but this returns the wrong information. I thought that information returned by these two queries would be indentical can anyone explain please.
Thanks David
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0) on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
----------------------------------------------------------------------------------------------------------------------------------------
create SET [Finance].[LFL Stores List] AS
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0)
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
[LFL Stores List] on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
How To Merge Two Queries' Results?
Nov 12, 2007
hi,
my first query is:
"SELECT TBL_STOK.stok_adi, TBL_STOK.fiyat1 FROM TBL_STOK INNER JOIN" _
TBL_BARKOD ON TBL_STOK.stok_id = TBL_BARKOD.stok_id " _
where TBL_BARKOD.barkod=@barkod"
second query :
"SELECT TBL_STOKDEPO.fiyat1 FROM TBL_BARKOD left outer JOIN TBL_STOKDEPO ON TBL_BARKOD.stok_id = TBL_STOKDEPO.stok_id" _
where TBL_BARKOD.barkod=@barkod and TBL_STOKDEPO.depo_kod=@depokod "
i want to merge these queries' results.first query returns 2 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1)
second query returns 1 column (TBL_STOKDEPO.fiyat1) .but i want a query that returns 3 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1,TBL_STOKDEPO.fiyat1)
Join Results Of SQL Queries
Jan 25, 2008Hello all,
I have been using T-SQL for a while now although the majority of my work required relativley simple queries.
I just need to know is there a way to JOIN the results of several SELECT queries, maybe through the use of functions??
A reference to any online article would be most helpful.
Cheers,
Sean
Need One Query To Obtain Results I Can Only Get With Two Queries
Jul 23, 2005I'm trying to devise a query for use on SQL Server 2000 that will dowhat was previously done with one query in MS Access. The MS Accessquery was like this:SELECT Count(*) as [Opened],Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened)Order By Year(DateOpened), Month(DateOpened)Here were I'm at with SQL Server, TSQLSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Opened]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yieldsMonthOpened======================Aug 2004503Sep 2004752Oct 2004828Nov 2004658Dec 2004533Jan 2005736Feb 2005707Mar 2005797Apr 2005412AndSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus like 'Cancelled%'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yields;MonthCancelled=========================Aug 200478Sep 2004105Oct 2004121Nov 2004106Dec 200475Jan 200582Feb 200571Mar 200594Apr 200533What is desired isMonthOpenedCancelled============================Aug 200450378Sep 2004752105Oct 2004828121Nov 2004658106Dec 200453375Jan 200573682Feb 200570771Mar 200579794Apr 200541233Any assistance would be appreciated.Cheers;Bill
View 3 Replies View RelatedJoining Two Select Statements
Feb 4, 2008I 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
Joining Select Statements
Apr 23, 2008Hi ,
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
*************
Combining Results Of Two Similar Queries Into One Result Set?
Mar 5, 2012Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.