Order By Date, So The Oldest Is First..
Feb 3, 2008
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/2008
28/03/2008
20/02/2008
08/03/2008
06/02/2008
How can I do, so it work?
Regards Jeppe Richardt
View 8 Replies
ADVERTISEMENT
Mar 4, 2008
Hi,
I have a base query that will return the ID, StartDate and Code for all IDs. I SELECT only for Codes 5 and 9. For most of the IDs I return a record for both a Code 5 and Code 9. They will have different dates however. How could I select from this base query one record for each ID with the oldest date? The items in yellow are the ones that I would want to return to a report in SSRS. Is there a way to put this data in a temp table and read through it to compare IDs and grab the one with the older date?
ID
StartDate
Code
100
1/2/2000
5
100
4/6/2004
9
205
3/13/2002
5
205
9/10/2002
9
300
10/10/1999
9
407
2/12/2005
5
407
7/17/2007
9
Thanks,
rb
View 10 Replies
View Related
Sep 17, 2014
I have a table that I need to delete the oldest date. I thought I could just do an update statement saying...
Delete T_BreakDown
where Date = MIN(Date)
But that doesn't work.
View 4 Replies
View Related
May 8, 2007
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 4 Replies
View Related
Feb 15, 2000
Is there a quick and dirty way to find the oldest record in a select query? There is a date column of type DateTime.
-Ringo
View 1 Replies
View Related
Jul 4, 2001
Hi,
I have a database that stores monthly nameed tables for historical data, but after a time (could be 1,2,3 months or on request) I need to be able to delete the oldest table. The tbale name format is ?????_mm_yyyy. Is there a MIN statement that could work on the create date?
Any T-SQL statements that would help me on my way would be great
Thanka!
View 4 Replies
View Related
Jan 25, 2001
Is there a stored proc to tell details of the oldest active transaction ?
View 3 Replies
View Related
Apr 9, 2008
I have a database that keeps track of pledges, scheduled reminders, and payments.
A user sets up a pledge and then sets up a schedule for reminders to be sent for each payment of the pledge.
Pledge table - PledgeID,PledgeDate,PledgeAmount,etc...
ScheduledReminders table - ScheduleID,PledgeID,DueDate,ReminderDate,ReminderPrintedDate,ReminderPaidDate
AppliedPayments table - ScheduleID,PaymentID,PaidAmount,PaidDate (Junction table between ScheduledReminders and PledgePayments
PledgePayments table PaymentID,PaymentAmount,PaymentDate
What I need to know is, when a payment is being Inserted I need the payment amount applied to the oldest scheduled reminder record that has not been paid. But if the payment is more than the amount shown in the schedule reminder record, I need the balance to be applied to the next oldest schedule reminder record, etc...
Can anyone offer some good suggestions or examples on how to do this?
View 18 Replies
View Related
Nov 9, 2007
Hello all,
I need assistance in finding the oldest CDR record in Cisco Callmanager DB using sql query. The CCM version is 3.3(5).
Any assistance is needed. Thanks.
View 3 Replies
View Related
Jan 29, 2007
I may have one or several files in a folder that act as source files.
How do I loop through the folder, picking the oldest dated file according to its 'Date Modifield' attribute.
Do I have to use a Script Task or similar as I can't seem to find a way to do it with normal tasks.
If so, does anyone have example code.
Thanks
P R W.
View 1 Replies
View Related
Oct 10, 2007
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 7 Replies
View Related
Jul 23, 2014
I have the following tables in my DB
Employee table - This table has EmployeeID, Name, DOB.
EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.
EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.
I want my query to output me the following fields...
EmployeeID
EmployeeName
EmployeeDOB
EmployeeStartingDesignation
EmployeeCurrentDesignation
EmployeeStartingSalary
EmployeeCurrentSalary
Here is a piece of code to generate sample data sets
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATE)
INSERT @Employee VALUES (101, 'James Bond', '07/07/1945'), (102, 'Tanned Tarzan', '12/13/1955'), (103, 'Dracula Transylvanian', '10/22/1967')
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATE)
INSERT @EmployeeDesignation VALUES (101, 'Bond Intern', '01/01/1970'), (101, 'Bond Trainee', '01/01/1975'), (101, 'Bond...James Bond', '01/01/1985')
[Code] ....
Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.
selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation,
(select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,
[Code] ....
View 5 Replies
View Related
Apr 26, 2007
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 4 Replies
View Related
Feb 1, 2007
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 2 Replies
View Related
Jul 20, 2005
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 5 Replies
View Related
Oct 12, 2006
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 5 Replies
View Related
Sep 21, 2006
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 2 Replies
View Related
Jan 18, 2005
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 3 Replies
View Related
Oct 3, 2006
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 7 Replies
View Related
Jun 2, 2008
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 6 Replies
View Related
Jan 16, 2015
I have below SQL, which should be order by posteddate ASC
SELECT AnnouncementID,[Subject],[Description],
CONVERT(nvarchar(10),PostedDate,101) AS PostedDate,
CONVERT(nvarchar(10),ExpiredDate,101) AS ExpiredDate,
CountryID,CreatedBy, CreatedDate, ModifiedBy, ModifiedDate
FROM Announcements a
WHERE isActive = 1
AND CountryID = 2
AND (GETDATE()>= PostedDate)
AND (GETDATE()<= ExpiredDate)
ORDER BY PostedDate ASC
But result is displaying as below, PostedDate datatype is datetime
01/01/2015
01/02/2015
12/28/2014
12/31/2014
Expected result is
01/02/2015
01/01/2015
12/31/2014
12/28/2014
View 1 Replies
View Related
Jul 20, 2005
How do I order a query by a date field ASC, but have any NULL valuesshow up last? i.e.7/1/20037/5/20037/10/2003<NULL><NULL>Any help will greatly be appreciated
View 3 Replies
View Related
Aug 2, 2007
<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 2 Replies
View Related
Mar 4, 2004
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 3 Replies
View Related
Feb 3, 2006
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 2 Replies
View Related
Jun 18, 2014
Below SQL gives the results of 2 columns and I need the DepartmentandDate column to be ORDER BY on date desc for that CID. Expected result is in the screenshot(attachment)...
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(25))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2014-01-14 00:00:00.000','B is alphabet'),
[Code] ....
View 6 Replies
View Related
May 23, 2008
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 4 Replies
View Related
Sep 29, 2015
I have a report that displays its date dropdown badly: 10AM,10PM, 11AM, 11PM etc when it should be 10AM, 11AM, etc. The used measure is Hour Name which looks like "01AM" "02AM" etc. And here is the current query
WITHÂ
MEMBER [Measures].[ParameterCaption] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.MEMBER_CAPTIONÂ
MEMBER [Measures].[ParameterValue] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.LEVEL.ORDINALÂ
SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue],Â
[Code] ....
View 2 Replies
View Related
Apr 10, 2014
I have files which has date in file name and I want to load all files in sequential order.
Here I am giving you an example of my file name.
"NetworkActivity_869_403722_01-01-2014.log"
"NetworkActivity_869_403722_01-02-2014.log"
"NetworkActivity_869_403722_01-03-2014.log"
"NetworkActivity_869_403722_01-04-2014.log"
"NetworkActivity_869_403722_01-05-2014.log"
These are my files in that I want to load files sequentially means jan 1st, 2nd like this way.
View 5 Replies
View Related
Jun 16, 2014
I am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'
[code]....
WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?
View 9 Replies
View Related
Feb 1, 2007
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 4 Replies
View Related
Nov 18, 2015
I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.
I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.
Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".
View 2 Replies
View Related
Feb 19, 2008
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 4 Replies
View Related
