Multiple Order By Statements In Union Select
Jan 20, 2005
I'm new to SQL stored procedures, but I'm looking to be able to select order by statement.
declare @OrderBy
@OrderBy = 'first_name'
Select first_name,last_name from table1
Union
Select first_name,last_name from table2
if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end
You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help
View 6 Replies
ADVERTISEMENT
Aug 31, 2007
i have 2 selects:select * from view_veiculos where nome_marc like '%fiat%' and ano='2001'union select * from view_veiculos where nome_marc like '%fiat%'when i execute it on sql server, i get the following results:id 1 _______ ano 2004id 2 _______ ano 2001the row with ano 2004 is before the row with ano 2001the problem is that id like it to be ordered following the select order, which means that 2001 should be displayed before 2004,like that:id 1 _______ ano 2001id 2 _______ ano 2004all the results from the first select from the query need to be placed before the results from the second query.how can i make it ?thanks for all
View 23 Replies
View Related
Oct 25, 2007
Hi!
I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?
Thanks so much
select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what from shows
union
select date3, date2, date1, who, what from shares
union
select date3, date2, date1, who, what from soiree
)
a order by date3, date2, date1
View 4 Replies
View Related
Apr 30, 2008
Good day all.
Let's see if I can stump you with this one.....
Code Snippet
SELECT Account.New_DistributorAccountNumber, Account.New_NameonBill, ActivityPointer.Subject, ActivityPointer.CreatedOn AS ExprX
FROM Account INNER JOIN
ActivityPointer ON Account.AccountId = ActivityPointer.RegardingObjectId
UNION
SELECT Account_1.New_DistributorAccountNumber AS Expr1, Account_1.New_NameonBill AS Expr2, ActivityPointer_1.CreatedOn AS Expr19,
ActivityPointer_1.Subject AS Expr20
FROM Account AS Account_1 INNER JOIN
Contact AS Contact_1 ON Account_1.AccountId = Contact_1.AccountId INNER JOIN
ActivityPointer AS ActivityPointer_1 ON Contact_1.ContactId = ActivityPointer_1.RegardingObjectId
gives the error mentioned above. Each select statement, when run separately, works fine. Any clues?
Thank you in advance
View 4 Replies
View Related
Feb 19, 2014
How do I properly write a CASE statement for the following business logic:
I want three of the following fields to be true in order to return 'Y'.
For example:
If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'
Otherwise, I want it to return 'N'.
I have tried the following code below and it is not working.
, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'
View 2 Replies
View Related
May 14, 2007
I'm trying to get a UNION of UserIDs from multiple subselects. Theoriginal query--which puts all the found UserIDs into separatecolumns, looks like this:--------------------------------------------------------------------------------SELECTtmp_MY_TABLE_2_3_4.LAST_UPDATED_USER_ID AS UserID_1MY_DB_1.MY_TABLE_5.LAST_UPDATED_USER_ID AS UserID_2MY_DB_1.MY_TABLE_6.LAST_UPDATED_USER_ID AS UserID_3FROM(SELECT MY_DB_1.MY_TABLE_1.PRIMARY_KEY_IDFROM MY_DB_1.MY_TABLE_1WHERE MY_DB_1.MY_TABLE_1.PRIMARY_KEY_ID = 12345) AS tmpMY_TABLE_1LEFT JOIN(SELECT MY_DB_2.MY_TABLE_2.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_2.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_2UNIONSELECT MY_DB_2.MY_TABLE_3.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_3.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_3UNIONSELECT MY_DB_2.MY_TABLE_4.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_4.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_4) AS tmp_MY_TABLE_2_3_4ON tmpMY_TABLE_1.PRIMARY_KEY_ID = tmp_MY_TABLE_2_3_4.PRIMARY_KEY_IDLEFT JOINMY_DB_1.MY_TABLE_5 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =MY_TABLE_5.PRIMARY_KEY_IDLEFT JOINMY_DB_1.MY_TABLE_6 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =MY_TABLE_6.PRIMARY_KEY_ID--------------------------------------------------------------------------------As you can see, I'm getting "LAST_UPDATED_USER_ID" from all tables/aliases--but those tables/aliases need to LEFT JOIN to"tmpMY_TABLE_1.PRIMARY_KEY_ID", because I only care about"LAST_UPDATED_USER_ID"'s that are related to my "tmpMY_TABLE_1"records.I tried putting, parenthesis around the whole SQL (aliasing as"tmpAllTables") and putting in the front:--------------------------------------------------------------------------------SELECT tmpAllTables.UserID_1 AS UserID FROM tmpAllTablesUNIONSELECT tmpAllTables.UserID_2 AS UserID FROM tmpAllTablesUNIONSELECT tmpAllTables.UserID_3 AS UserID FROM tmpAllTables--------------------------------------------------------------------------------But that didn't work. Any thoughts?
View 1 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
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
Jul 27, 2007
I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?
View 2 Replies
View Related
Dec 16, 2014
I know I should know the answer to this, but I just can't quite get the syntax down
Code:
Select case when zipCode = '10185' Then 'Deliver'
Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '88221' And paid = 'No' Then 'Hold'
Else when zipCode = '34123' Then 'Deliver'
End
From postalDeliveryDatabase
View 7 Replies
View Related
Sep 11, 2007
Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?
View 2 Replies
View Related
Apr 29, 2008
Hi,
OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
View 5 Replies
View Related
Aug 29, 2006
hi
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode,
EngagementCode,
EngagementDescription
)
SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
tibor
View 5 Replies
View Related
Aug 9, 2007
Hi all,
I have 2 select statements in my Stored Proc.
I want to display the results of each query in my DataGridView.
However, only the data of the last select query is returned.
Why is this?
Thanks.
View 7 Replies
View Related
May 24, 2007
I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz
View 3 Replies
View Related
May 19, 2008
Hi,
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too.
Basically those are SELECT statements.
I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report.
If possible, can anyone show me the format?
Thanks
View 4 Replies
View Related
Dec 3, 2006
Hey Guys. I’m having a little trouble and was wondering if you could help me out. I’m trying to create a custom paging control, so I create a stored procedure that returns the appropriate records as well as the total amount of records. And that works fine. What I’m having problems with is reading the data from the second select statement within the code. Anyone have any idea on how to do this? Also.. how can I check how many tables were returned?
Here's my code. I'm trying to keep it very generic so I can send it any sql statement:public DataTable connect(string sql)
{
DataTable dt = new DataTable();
SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ToString());
SqlDataAdapter SqlCmd = new SqlDataAdapter(sql, SqlCon);
System.Data.DataSet ds = new System.Data.DataSet();
SqlCmd.Fill(ds);
dt = ds.Tables[0];
//Here's where I don't know how to access the second select statement
return dt;
} Here's my stored procedure:
ALTER PROCEDURE dbo.MyStoredProcedure
(
@Page int,
@AmountPerPage int,
@TotalRecords int output
)
AS
WITH MyTable AS
(
Select *, ROW_NUMBER() OVER(ORDER BY ID Desc) as RowNum
From Table
where Deleted <> 1
)
select * from MyTable
WHERE RowNum > (((@Page-1)*@AmountPerPage)) and RowNum < ((@Page*@AmountPerPage)+1);
Select @TotalRecords = COUNT(*)
from Table
where Deleted <> 1
RETURN
Thanks
View 3 Replies
View Related
Nov 5, 2007
My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
View 5 Replies
View Related
Nov 24, 2014
I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?
SELECT o.name, o.object_id,
ps.last_execution_time ,
ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds,
ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds,
ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds
[code]...
View 4 Replies
View Related
Jan 18, 2008
Hello,
I have two union statements that I need to join to each other. Then I need to join that result to another table. So-
[(TBL-A UNION TBL-B)
JOINED TO
(TBL-C UNION TBL-D)]
JOINED TO
TBL-E
Is this a subquery thing, or something else? I have not been able to figure out how to make it work! The unions I have made work fine though, on their own.
View 3 Replies
View Related
Dec 17, 2005
Wondering if there is a physical or realistic limitation to the numberof UNION statements I can create in a query? I have a client withapprox 250 tables - the data needs to be kept in seperate tables, but Ineed to be filtering them to create single results sets. Each tableholds between 35,000 - 150,000 rows. Should I shoot myself now?lq
View 15 Replies
View Related
Aug 20, 2015
I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code. Each block is the same except for the date. How could I rewrite these 30 union statements into a more concise query?
Each client (people_id) has a distinct agency_id_number. So the code below totals the number of distinct clients/day and groups them by programs and facilities.
Sample data that you can use to test this report:
IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census
create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)
[Code] ....
View 3 Replies
View Related
Sep 28, 2007
I wanted to use UNION and 'order by' in the same script but it keeps giving me error
eg;
select name,date
where name = 'sel'
union
select name,date
where name = 'ali'
order by name
I don't know where to put the 'order by'
Can anybody help me?
View 10 Replies
View Related
Feb 27, 2008
Hi
I have two tables which I'm combining.
Select employeeID as id, startDt as startDate from Table1
union all
Select CustomerID as id, startDt as startDate from Table2
order by startDt
This works fine, unfortunately, my problem is I only want to display the first latest customer. In a Union All, how do i distinguish whether I'm ordering the table it is trying to union or the actual unioned table tiself?
For example, this does not work.
Select employeeID as id, startDt as startDate from Table1
union all
Select top 1 CustomerID as id, startDt as startDate from Table2
order by startDt desc
order by startDt
View 3 Replies
View Related
Mar 12, 2008
hello,
I have 2 tables which contain a lot of articles, and they have same structure.
what I want to do is that, select top 10 articles from each table and then union those records to another table, also I need to order these articles in desc order by their added date before i union them.
what I know is that for union sql phrase , there is only one order by is allowed, so how could I order those 2 tables before i union them.
thanks
View 2 Replies
View Related
Jul 23, 2005
I am having problems retrieving some data from a table,Tablename = "Magazine"ColumnsMagazineID MagazineName12 Times3 People4 National Geographic5 US6 Sports IllustratedI am trying to bind a dropdownlist in .net with the 4 newest magazinesand the empty magazine.SELECT TOP 1 MagazineID, MagazineName from MagazineUNIONSELECT TOP 4 MagazineID, MagazineName from MagazineOrder by MagazineName;The first part returns1,''The second part returns1,''2,'Times'3,'People'4,'National Geographic'Given the above data, how can I get the sql results to display5 'US'4 'National Geographic'3 'People'2 'Times'1 ''Essentially i need to do the followingSelect Top 1 MagazineID, MagazineName from Magazine Order by MagazineIDand add to itSelect Top 4 MagazineID, MagazineName from Magazine Order by MagazineIDdesc
View 3 Replies
View Related
May 13, 2008
Hello,
I am trying to use dynamic ORDER BY with UNION in a stored procedure but I keep getting this error message:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
The ORDER BY works fine without the UNION, and the UNION works fine without the dynamic ORDER BY (just putting an ORDER BY works fine)
Here is the whole query in case this helps, thank you
SELECT
leave_id, leave_type, annualLeave.from_date,
convert(nvarchar(10), annualLeave.request_date, 103) as dated,
convert(nvarchar(10), annualLeave.from_date, 103) as from_date,
convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
annualLeave.canceled,
annualLeave.working_days,
staff.staff_name,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem'
ELSE ''
END AS pendingStyle,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
ELSE ''
END AS historyStyle
FROM annualLeave
INNER JOIN staff ON annualLeave.staff = staff.staff_id
WHERE staff.department like @Department
and (@staffID is Null or annualLeave.staff = @StaffID)
and (@Canceled is Null or annualLeave.canceled = @Canceled)
and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
and (@StartingDate is Null or annualLeave.from_date > @Startingdate)
and (@IsPastLeave is Null or annualLeave.from_date < @IsPastLeave)
and (@EndingDate is Null or annualLeave.to_date <= @EndingDate)
UNION
SELECT
leave_id, leave_type, annualLeave.from_date,
convert(nvarchar(10), annualLeave.request_date, 103) as dated,
convert(nvarchar(10), annualLeave.from_date, 103) as from_date,
convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
annualLeave.canceled,
annualLeave.working_days,
staff.staff_name,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem'
ELSE ''
END AS pendingStyle,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
ELSE ''
END AS historyStyle
FROM annualLeave
INNER JOIN staff ON annualLeave.staff = staff.staff_id
WHERE staff.department like @Department
and (@staffID is Null or annualLeave.staff = @StaffID)
and (@Canceled is Null or annualLeave.canceled = @Canceled)
and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
and annualLeave.leave_type like 'Leave in lieu'
ORDER BY
CASE @OrderDir
WHEN 'ASC' THENannualLeave.from_date
END
ASC,
CASE @OrderDir
WHEN 'DESC' THEN annualLeave.from_date
END
DESC
View 3 Replies
View Related
Jun 8, 2007
I have the following query and for some reason when I try to run it, it tells me there is an incorrect syntax near the 'union' statement. Can you tell me what is wrong with this?
SELECT MDN,
DATEPART(d,CallDate) as CallDate,
DATEPART(hh,CallDate) as Hour,
sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3
AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00'))
OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN
UNION
SELECT MDN,
DATEPART(d,CallDate) as CallDate,
DATEPART(hh,CallDate) as Hour,
sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3
AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00'))
OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN
View 5 Replies
View Related
Dec 7, 2007
Hi,
I'm currently have a problem with a query using a top clause. When I run it by itself as a single query, I have no problems and the results are valid. However, if I try duplicate the query after a union clause, the order by ... desc doesn't order properly.
The following is the query I'm using along with the results. Then I'll have the query I was trying to unite and the results (date ranges selected were the same in both):
QUERY 1
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=20411
order by s.ldate desc
RESULTS 1
DATE MDT IDPU Odometer DO Odometer Total Miles
12/6/2007 2041112810.6 12874.5 63.9
QUERY 2 (with Union)
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=[From Date,Date]
and s.ldate<=[To Date,Date]
and v.mdtid=20411
Union
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=2642
order by s.ldate desc
RESULTS 2
DATE MDT ID PU OdometerDO Odometer Total Miles
4/10/2007 20411 1207.21252.5 45.3
1/2/2007 2642 193652.6193817 164.4
As you can see, the results are sorted very differently. Is there any way to have the order by apply to both queries?
Thanks!
Craig
View 5 Replies
View Related
Aug 16, 2006
SELECT *FROM tblCountryWHERE Country_Id = 26UNION ALLSELECT *FROM tblCountryWHERE Country_Id <26--order by country_Namei need to select country_id =26 and then the rest i want them ordered byname.if i put order by country_name, the country_id 26 isn't displayed as thefirst one.is there anyway to apply the order by only to the second select not thewhole?*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Jul 20, 2005
All,I've seen several posts regarding using UNION or UNION ALL to mashtogether two or more resultsets into a single result set, but can'tseem to find enough info here to help me answer my particularquestion.I have a stored procedure that gets the column names in a particularformat (i.e. "chassis_id"|"chassis_description"|"modify_date") as wellas actual data for a given table (in a quote-separated, pipe-delimitedmanner i.e. "1"|"description for the chassis"|"2004-09-08").I'd like to get both of these resultsets and mash them together. Thisworks, but when I need to order the second resultset (i.e. select *from chassis order by chassis_id), SQL Server returns an errorcomplaining about the chassis_id column name (invalid column name'chassis_id') in the Order By clause.From what I can tell, I'm using the UNION and Order By in correctly,but I'm not sure exactly what's wrong with it. If I take out the OrderBy, everything works great. Although I would like to be able to ordermy second resultset (in the same sproc) if possible.The actual queries I'm running are actually quite long, but here's onethat's a bit shorter to help illustrate:SELECT '"app_group_id"|"app_group_name"|"create_date"|"create_by"|"modify_date"|"modify_by"'UNION ALLSELECT + ISNULL(CONVERT(varchar,app_group_id), '') + '|'+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 1))), 1,0)+ '"' + ISNULL(CONVERT(varchar(1000), +REPLACE(CONVERT(nvarchar(4000),app_group_name), '"', '""')), '') +'"|'+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 2))), 1,0)FROM app_grouporder by app_group_idThank for any help on this./bc
View 3 Replies
View Related
Oct 18, 2007
Hi All,
I have this table in my DB.
ProductID | ProductSerial | Product Date
1 |FRT123 | 3/3/07
2 |EDR432 |5/5/07
I need to run two select statment on the same tabel and i Use Union to join the result comes from the first table with the one comes from the second one. but one i use order by union dose not work.
my question is : How Can i run the two select statment on the same table and join the results and order each select?
my queries:
Select ProductID,ProductSerial,ProductDate
Where productID = @ProductID
Order by ProductID
Union
Select ProductID,ProductSerial,ProductDate
Where productID = @ProductID
Order by ProductID
Thank you
Sms
View 14 Replies
View Related
Jun 1, 2006
Hello,
I want to order by a field that is a code, like 1,2,3,11,12,13, and i get the following error
"Only text pointers are allowed in work tables, never text, ntext, or image columns sql server"
Why do i get this, what can i do to overcome this?
Thank you
View 7 Replies
View Related