Find Order By Date Range Or Order Id
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez
View Complete Forum Thread with Replies
Related Forum Messages:
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following... SQL Server Express edition Visual Studios 2005 Jet 4.0 newest upgrade .Net Framework 2.0 (or should I use 3.0) VS2005 Security upgrade Anything else I need for just creating a database for my VS2005 Visual Basic project? I was trying to use MS Access as my backend db but would like to try SQL Express Thank you, Mark
View Replies !
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?
Hey guys, i need to find out how can i add order items under a Purchase Order number. My table relationship is PurchaseOrder ->PurchaseOrderItem. below is a Stored Procedure that i have wrote in creating a PO: CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT) AS BEGIN INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID) END SET @POno = @@IDENTITY RETURN However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem? CREATE TRIGGER trgInsertPOItem ON PurchaseOrderItem FOR INSERT AS BEGIN 'What do i entered???' END RETURN help is needed asap! thanks!
View Replies !
Trying To Find Best Way To Filter On Date Range
I have a database were a client can have many addresses. Clients may be at one address or another at different time of the year. The table allows for entering a startdate and an enddate for each address. I'm trying to figure out the best way to filter on this to return only the current address. I have tried the Where clause below but I'm not sure this is what i should use. The year is not needed but the datatype is datetime. I think i need to use the startdate also, but I cannot seam to get how to filter this. If anyone has ideas I would like to hear them. Thank you, A.Selected=1 AND A.EndDate Is Null OR DatePart(m,A.Enddate) >= DatePart(m,GETDATE()) AND DatePart(d,A.Enddate) >= DatePart(d,GETDATE())
View Replies !
Recordset's Order And Database's Physical Order?
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
View Replies !
Default Sort Order When Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek: Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Default Sort Order When The Order By Column Value Are All The Same
Hi, We got a problem. supposing we have a table like this: CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times) select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View Replies !
Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name. Product: Microsoft SQL Server Express Edition Version: 9.00.1399.06 Server Collation: SQL_Latin1_General_CP1_CI_AS for example, create table test_sort ( description varchar(75) ); insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons'); then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75)); select * from test_sort order by cast( description as nvarchar(75)); Resultset1 ---------- Non-A Non-O Noni Nons Resultset2 ---------- Non-A Noni Non-O Nons Any ideas?
View Replies !
Order By Clause In View Doesn't Order.
I have created view by jaoining two table and have order by clause. The sql generated is as follows SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth If I run sql the results are displayed in proper order but the view only order by first item in order by clause. Has somebody experience same thing? How to fix this issue? Thanks,
View Replies !
Find Logic Flaw, Order Number Generator
This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.) How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency? CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS DECLARE @NewOrderId int, @nSQLError int, @nRowCount int BEGIN TRAN UPDATE CUSTOMERS SET ORDER_NUMBER=ORDER_NUMBER + 1 WHERE COMPANY_ID=@customer SELECT@nSQLError = @@error, @nRowCount = @@rowcount If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */ Begin Rollback Tran Return -999 End SELECTORDER_NUMBER FROMCUSTOMERS WHERECOMPANY_ID=@customer SELECT@nSQLError = @@error, @nRowCount = @@rowcount If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */ Begin Rollback Tran Return -998 End COMMIT TRAN
View Replies !
Order By Date
Hi, I have this statement: SELECT DATENAME(month, date) AS Month, COUNT(*) AS Total FROM Table GROUP BY DATENAME(month, date) I want to order it in month order... If i order it by DATENAME(month, date) then it is just in alphabetical. Also is there anyway that all months are added to the result even if there are no records for that month (So it will just show 0 in the count) Thanks for anyhelp
View Replies !
Order By Date, So The Oldest Is First..
Hello there,I have a problem when I'm trying to order by the date..I have tried this string:SelectCommand="SELECT TOP 5 [Date], [Id], [Navn], [ShortInfo] FROM [fest] ORDER BY [Date] DESC"> And I have these dates: 04/02/2008 06/02/2008 20/02/2008 29/02/2008 08/03/2008 28/03/2008They should be shown like this: 04/02/2008 06/02/2008 20/02/2008 29/02/2008 08/03/2008 But they dosn't, insted is they shown like this: 29/02/200828/03/200820/02/200808/03/200806/02/2008How can I do, so it work?Regards Jeppe Richardt
View Replies !
Convert Date And Order By
Hi!I have a little problem. I’m trying to sort a date I have converted like thisConvert(datetime,LH.LoginDateTime,103) as RegistrationDateBut when I use Order by on RegistrationDate it only sort on days:01/11/200601/12/200602/11/200602/12/200603/11/200603/12/2006I’ll guess it’s because of the “varchar� convert, but I need the date to bee inn this format, since I only shall check the date and not the time. Is there a way around this, so I can order it like this? (Se under)01/11/200602/11/200603/11/200601/12/200602/12/200603/12/2006Sample SQL;select Convert(varchar,LH.LoginDateTime,103) as RegistrationDate,select count(*) from LoginHistory AS LH2 where datepart(hh,LH2.LoginDateTime)<7 ANDConvert(varchar,LH2.LoginDateTime,103)>=Convert(varchar,LH.LoginDateTime,103) AND Convert(varchar,LH2.LoginDateTime,103)<=Convert(varchar,LH.LoginDateTime,103)) As beforehour07from LoginHistory AS LHwhere LH.LoginDateTime >='''+ Convert(varchar,@FromDate,113) + ''' ' + 'and LH.LoginDateTime <='''+ Convert(varchar,@ToDate,113) + ''' ' + 'group by Convert(varchar,LH.LoginDateTime,103)'Order by RegistrationDate
View Replies !
ORDER BY Earliest Date In A Row
Hi,I have a table (SQL Server 2000) with several date columns in it, all ofwhich are individually NULLable, but in any one row, not all the dates canbe NULL.I want a query which ORDERs BY the earliest date it finds in each row. I'mguessing I have to do this in two steps:STEP 1Using a UDF, find the earliest date and stick it in a new calculatedcolumn "earliest date"STEP 2ORDER BY this UDF-created columnIf this is the right way to go about this, is there a simple SQL way ofdetermining which is the lowest of several dates? (ie of doing STEP 1).Or am I looking at this the wrong way, and missing an easy *one-step* way ofgetting what I want?TIA,JON
View Replies !
Ordering Date In Asc Order ?
SELECT LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date, SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming, SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design, SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload, SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing, SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting, SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as Others From task_table a,act_table b where a.status_id=b.act_id and a.user_id=(select user_id from user_table where user_name='Raghu') and a.task_date like '%/%/2006' GROUP BY LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) Output : Aug 2006 294 0 0 80 0 0 Jan 2006 14 0 0 0 0 0 Oct 2006 336 0 0 0 0 0 Sep 2006 3262 20 24 8 16 0 How to sort the date in ascending Order ? Jan 2006 Aug 2006 Sep 2006 Oct 2006
View Replies !
Order By Descending Date
Here is my sp. I want it to order by descending date, but now it is 1st Feb it is putting this at the bottom, though January is still sorted fine. I need the dates to display in UK format dd/mm/yy CREATE Procedure [dbo].[spRMU_CountNoDailyUsers] AS SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins FROM tblUserLog Where Log_Printed =0 GROUP BY CONVERT(varchar, Log_DateTime, 103) , Log_Username ORDER BY CONVERT(varchar, Log_DateTime, 103) desc, No_Logins desc GO
View Replies !
Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table. My query would look something like this: Select leadershipName From Leadership Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows: Executive Board Members, Delegates, Grievance Chairs, and Negotiators My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC? Thanks, Zoop
View Replies !
Order ID For Latest Order For Every Customer
Hi! For the Orders table (let's assume for the Northwind database), I'm trying to get the order id of the latest order for every customer. That means that the result should be one record per customer and that would display CustomerID and OrderID. Any ideas? Thanks, Assaf
View Replies !
In-Order/Level Order Etc. Traversal Using CTE
Hi, I have some hierarchical data in a table. Say for example: Parent Child ------------------------ NULL 1 1 2 1 3 2 4 2 5 3 6 3 7 5 8 5 9 7 10 7 11 11 12 11 13 Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13... What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well. with Tree (id) as ( select id from WithTest where parent is null union all select a.id from Tree b join WithTest a on b.id = a.parent ) select * from Tree Any ideas? Thanks.
View Replies !
How To Make The Order By Date Fine Enough In SQL
hi,I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.Thanks very much.
View Replies !
Sorting Date/timestamp Is Out Of Order
I have a problem with an Order By sort on a SubmissionDate column in my SQLSERVER DB. I am inputing a timestamp in this format into the column above: 1/18/2005 11:03:19 AM Problem is, once I sort this column in DESC order to return the results to a datalist dates with a time like this: 1/18/2005 1:03:19 AM get placed out of place (lower on the return in DESC/higher on the return in ASC). I am assuming this is happening because it reads 1 as coming before 11 instead of after like it is with time. If this was in 24 hour format this wouldn't be a problem I guess because 1PM would be 13, so that is after 11. Anyone know what I can do to get this sorted correctly?
View Replies !
Order Results By Date Not Working
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this: oct 2 oct 3 sep 13 sep 21 sep 22 sep 30 aug 3 aug 5 aug 16 the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help. also, can you not search here any more? i keep getting timeout errors.
View Replies !
How To Sort Date Column In ASC Order?
hi all. i am getting screwy results when i try to order the "the_date" column of my RecentLogin table. The below select statement should order the "the_date" column in cronological ascending order (defaults to ASC when not specified) but it goes August, October, September. And, not August, September, October. Any ideas why this is happening? Thanks. PS - for what it is worth, this column is stored as a VARCHAR and not a DateTime Code: select * from RecentLogin order by the_date Quote: Aug 23 2006 3:00PM Oct 3 2006 9:45PM Oct 6 2006 2:24PM Oct 8 2006 6:57PM Oct 8 2006 7:27PM Oct 8 2006 9:42PM Oct 8 2006 11:15AM Oct 8 2006 11:35AM Oct 8 2006 11:45PM Oct 9 2006 1:13PM Oct 9 2006 10:47AM Oct 12 2006 7:30PM Oct 12 2006 10:01AM Oct 12 2006 12:28AM Oct 13 2006 4:34PM Oct 13 2006 9:32AM Oct 16 2006 8:01PM Oct 17 2006 9:22AM Sep 3 2006 11:10PM Sep 3 2006 11:12PM Sep 3 2006 11:12PM Sep 3 2006 11:15PM Sep 3 2006 11:15PM Sep 3 2006 11:16PM Sep 3 2006 11:16PM Sep 3 2006 11:17PM Sep 3 2006 11:17PM Sep 5 2006 1:29PM Sep 5 2006 1:30PM Sep 5 2006 2:32PM Sep 7 2006 8:49PM Sep 9 2006 10:48PM Sep 1 2006 3:00PM Sep 13 2006 3:14PM Sep 14 2006 5:07PM Sep 15 2006 8:46AM Sep 17 2006 9:40PM Sep 19 2006 2:39PM Sep 19 2006 2:44PM Sep 21 2006 9:31AM
View Replies !
Ouput Based On Order Date
Hi all, I have 2 tables, Order and Payment Order Order_NumberCust_NumberOrder_Date 10001 C1 23-May 10002 C2 24-May 10003 C1 25-May 10004 C3 28-May Payment Order_Number|Card_Type|Card_#|Merchant 10001 Gift Card1234null 10001 Gift Card1235null 10001 Gift Card 1236null 10001 Credit Cardxxxxprd 10002 Credit Cardxxxxprd 10003 Credit Card xxxxprd 10004 Credit Card xxxx prod I have to populate th below table to track last gift card used for each cust_number. 1. last gift card used for each customer, each order 2. In a single order , if card used is gift card, last gift card used is gift card itself. if card used is a credit card, then the max gift card number from with in the order. 3. First time if a customer uses a credit card, then last gift card used is defaulted to 99 for merchant = prd and 88 for merchant = prod 4. In a new order, a past customer only uses a gift card, then last gift card used is gift card from his previous order. [b]Last_Gift_Card Cust_No|Order_number|card_number|last_gift_card C11000112431234 C11000112351235 C11000112361236 C110001xxxx1236 C210002xxxx99 C110003xxxx1236 C310004xxxx88 Please help me with the sql. I tried this using subqueries to find the max gift card for a customer for an order and could get the last gift card used correctly for credit cards for an order, but not able to insert the gift card from a previous order if the new order has only credit card as in for customer C1. Thanks.
View Replies !
How Do Order The List By Date To Show For New Dates.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>" SelectCommand="SELECT [Title], [URL], [Date] FROM [Article] ORDER BY [Date] DESC"></asp:SqlDataSource> <asp:Repeater id="myRepeaterUL" runat="server" DataSourceID="SqlDataSource1"> <HeaderTemplate> <ul> </HeaderTemplate> <ItemTemplate> <li><a href="<%# DataBinder.Eval(Container.DataItem, "URL") %>"><%#DataBinder.Eval(Container.DataItem, "Title")%></a><br /><%# Eval("Date") %></li> </ItemTemplate> <FooterTemplate> </ul> </FooterTemplate> </asp:Repeater> This is my code above, I am trying to order them to show the four new list of news. Here is a picture, yeah its old and everybody loves pictures. see the box on the right, i want to show only four, not all of it.
View Replies !
ORDER BY [Date] Isn't Working Since It's Not A DateTime (it's A Varchar!)
Hi everyone. I know, I know, it should have been a datetime from the start...but here's the problem. I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using: ORDER BY [Date] Are there any work arounds for this? Is there some way of doing: ORDER BY covert(datetime, [Date], 103) or something? Cheers Andrew
View Replies !
Wrong Date Sort Order In Enterprise Mgr
When clicking on the header for the Date Created column in EM (SQL Server 2000) , in the tables list for a database, I noticed that the tables don't get sorted by created date at all - looks like random order, without looking too close. Is this is a bug (probably not??) or some kind of collation-related problem?
View Replies !
Correctly ORDER BY Date That Has Been Cast As A Varchar
how do you get it to sort correctly for the date? at the minute its sorting on the characters rather than actual date value Code Snippet INSERT INTO @TempItems (OrderID) SELECT OrderID FROM Orders o INNER JOIN Customers c ON c.CustomerID = o.CustomerID INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID ORDER BY CASE @SortOrder --Order ASC WHEN 0 THEN cast(OrderID as varchar(100)) WHEN 1 THEN cast(c.CompanyName as varchar(100)) WHEN 2 THEN cast(e.FirstName as varchar(100)) WHEN 3 THEN cast(o.OrderDate as varchar(100)) WHEN 4 THEN cast(o.RequiredDate as varchar(100)) WHEN 5 THEN cast(o.ShippedDate as varchar(100)) ELSE '1' END,
View Replies !
SQL Server 2005 Order By Date Does Not Sort Properly
I am using Access 2003 as a front-end to a SQL Server 2005 database.I make design changes using SQL Server Management Studio. I have atable that includes a datetime column. I create a view and sort bythe datetime field. When I initially look at the result it is sortedcorrectly. Then I save the view and re-open it and it is not sorted.I've simplified the view so it only contains the date field and itstill does not sort. Here is the view:SELECT TOP (100) PERCENT Period_DateFROM dbo.Period_SummaryORDER BY Period_Date DESCThe date seems to be a random order.I don't have this problem in the SQL Server 2000 version of thedatabase.Help please!Thanks,Jerry
View Replies !
Reporting Services DATE NOT SHOWING IN DESCENDIGN ORDER IN PREVIEW???
Hi everyone, its been long time, i came back here to discuss problems i am facing..in SQL SERVER 2000 Reporting services HAPPY NEW YEAR >> __________________________ OK guys here is a problem i am facing i have Date issue in Preview my Date is appearing in Ascending order in Preivew, but if u look at the Query .. SELECT CONVERT(datetime, CAST(CreatedTime AS varchar(11)), 112) AS Time1, SourceSystem, DestinationSystem, Type, SubType, COUNT(1) AS CountValue FROM dbo.tb_Message WHERE (CreatedTime > CONVERT(DATETIME, '2003-12-01 00:00:00', 102)) AND (CreatedTime < CONVERT(DATETIME, '2004-12-01 00:00:00', 102)) GROUP BY CONVERT(datetime, CAST(CreatedTime AS varchar(11)), 112), SourceSystem, DestinationSystem, Type, SubType ORDER BY CONVERT(datetime, CAST(CreatedTime AS varchar(11)), 112) DESC, CountValue DESC ------------------------ I set date in Descending order, But in Preview it showed me in Ascending order, Making any change in SQL Data is not effecting. IS there any way i can go in Layout and make it Descending. I want Januaray 2005 Reports to come first rather then having my Januaray of 2004 Reports showing up as first page in my reports... its a issue holding me back from all of my other work.... which sucks.. if someone can let me know what to do here , i would really appreciate it.
View Replies !
Interesting Behavior, Sql 2005 Std, Order By Date Convert To String
Note the code below, running on the version noted. I just found this today, figured I'd share. Not sure if it's a known bug or a "special" feature. The only difference between the two queries is the 3rd line, everything else is the same. Notice that the sort order changes, yet no errors or warnings are given. I assume that the table aliases are ignored for the order by, unless there are duplicate column names in the results. I abstracted this from a bug I discovered in one of my apps today, where I have sales reps assigned to their clients with start and end dates. A while back, a developer asked me to format the dates without the time portion, and when I did so, I introduced the problem. I resolved it temporarily by ordering by convert(datetime, startdate) but I found it strange that the column alias match overrides the table alias attempted match in the order by. Another way to get around this would be to change the column aliases, then the sort order would be as desired, but I didn't want to have to change the app code for something so trivial. My apologies if this is a duplicate. @@version = Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) select convert(varchar(10), table_alias.startdate, 101) as startdate, convert(varchar(10), table_alias.enddate, 101) as enddate from ( select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate union select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate union select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate union select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate union select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate ) as table_alias order by table_alias.startdate select convert(varchar(10), table_alias.startdate, 101) as startdate, convert(varchar(10), table_alias.enddate, 101) as enddate, table_alias.startdate, table_alias.enddate from ( select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate union select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate union select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate union select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate union select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate ) as table_alias order by table_alias.startdate
View Replies !
How To Load A Unicode File Into The Database In The Same Order As The File Order
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View Replies !
Force The &"ORDER BY&" To Be In Ascending Order??
I noticed the StockDate is not sorted in proper order, like ascending order... Code: select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd... The sample data is MM/DD/YYYY... So, how do I get it ordered propery by Year, Month then Day??
View Replies !
Query Info Between Time Range & Date Range
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
View Replies !
Find IP In Range
Hi there: This one's a bit tricky. I want to be able to take an IP address of a request on my website and find it in a table. Specifically, I need to be able to record IP address RANGES for search engines so that when they attempt to find a page that isn't there, I can programmatically send a 404 header instead of give them the human-friendly page (please don't make suggestions on this - I'm using PHP, etc., and can't send both a 404 header and a human-friendly page). Anyway. Let's say a search engine has the IP range 33.33.0.0 through 33.33.255.255 (I made that up). if I receive a request from IP 33.33.101.221, how can I store ONE record (as either a regular expression or maybe multiple fields or somehow else) so that I can match that IP? I'm normally pretty good at figuring stuff like this out, but this one has me stumped for the moment. All help is greatly appreciated. I'll be posting a similar request on the PHP forum - I hope that's not considered cross-posting. Jay
View Replies !
Query Help - Giving A Date Range Given The Start Date, Thanks!
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
View Replies !
{RESOLVED} Date Logic - Calculating A Date Range
I have a report that I need to run on 2 different date ranges. Both report's data is 2 days behind today's date. so... WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2) OR SOMETHING LIKE THAT, NO BIGGIE HERE The 2nd report is a month to date report. This is the 1 I can't figure out. WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2) So that would look like WHERE reportdate between 1/1/2007 and 1/21/2007 My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this WHERE reportdaye between 2/1/2007 and 1/30/2007 Any help would be greatly appriciated!
View Replies !
How Can I Find Values Outside Of The Smalldatetime Range?
I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort. I have tried to find the values which are out of the smalldatetime range, with the following query SELECT * FROM midmar WHERE bday BETWEEN '01/01/1900' AND '06/05/2079' Which doesn't quite work, and gives me values that are actually between those two values listed. I have also tried having the WHERE clause read: WHERE bday <'06/06/2079' AND bday>'01/01/1900' and that doesn't really work either. What's going on? Is there likely another problem besides the structuring of my queries?
View Replies !
Date Format In A Date Range Parameter
I´ve made a report with a date range parameter as described at http://msdn2.microsoft.com/en-us/library/aa337401.aspx The language setting is Dutch. The date in de parameter is dd-M-yyyy. Is it posible to change this to dd-MM-yyyy.
View Replies !
Finding Where My Date Falls In Date Range
Hi; We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received. I have a CommisionAudit table that tracks changes in commission rate with the following values. ID | Commission Change | UpdatedOn ---------------------------------------------- 1 | Change from 20->25 | 03/07/2007 09:00:00 ---------------------------------------------- 2 | Change from 25->35 | 10/09/2007 17:00:00 ---------------------------------------------- 3 | Change from 35->20 | 01/10/2007 16:00:00 ---------------------------------------------- 4 | Change from 20->26 | 11/10/2007 10:00:00 ---------------------------------------------- with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.
View Replies !
Sql Order By
Hi all, I had one question on sql statement. I had a table with a field named severity. The field severity will either consist of Minor, Moderate or Severe. How can I construct an sql statement whereby the severity will be order as Severe follow by Moderate and Minor. Thanks
View Replies !
WHERE + ORDER BY ?
Hello I am not sure of the correct syntax. I know that the first part works: ****************************** SELECT Extn, Domain_Name, Price FROM Domains_DB **************************** I am trying to add a WHERE clause is equal to com and an ORDER BY assending order. I have tried all sort of combinations, where am I going wrong with the following: SELECT Extn, Domain_Name, Price FROM Domains_DB [WHERE Extn = com [ORDER BY Domain_Name ASC ]] Thanks. Lynn
View Replies !
ORDER BY
hi, i' ve Drop Down List with sorted catagory and Data Grid that cange according to selected item in drop down list ... i need to send the selected item as value to SELECT statment, so i 've send (option) as a value "SELECT [userstory].* FROM [userstory] WHERE ([userstory].[rel_id] = @rel_id) ORDER BY @options " but there is an error: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name
View Replies !
Order By
Hi everyone,I have a select statement of the form SELECT * FROM temp ORDER BY timeI have a scalar function ConvertToMinutes that takes in varchar and returns int, is there any way to do something like this SELECT * FROM temp ORDER BY ConvertToMinutes(time). I tried doing this and it doesn't work (it tells me ConvertToMinutes is not a built-in function). Please guide me as to how I would accomplish this. Thanks in advance.P.S. Clarification: I am trying to order the table temp by the value returned by the function ConvertToMinutes on the coloumn time.
View Replies !
|
TRACKER
