T-SQL (SS2K8) :: Order By On Date Descending In A Column

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


ADVERTISEMENT

Order By Descending Date

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

Sorting A Column In Descending Order Which Contains Two Years

May 21, 2014

I am trying to sort this simplied table:

ID - Time
1 2000-2001
2 2002-
3 2001-2003
4 1999
5 2005-2006

I want this as a result:

1999
2000-2001
2001-2003
2005-2006
2002-

Because the "-" means "continues", it the thing is still activated, so if it makes it easier, i could put the today's year afterwards during the query, if it ends with a - ...

Now, simply doing a
SELECT * FROM [table] ORDER BY TIME;

Sorts it "perfectly", apart from the "2002-" is just placed before 2005 and after 2001.

So, of course, it fails on all entries with a leading "-" ...

Right when I clicked "submit", of course, I can simply replace all entries with a time ending with "-", with the todays Year, so at least they will get at the end of the query...

Well, have to do a union, first sorting all without the "-", then sorting all with the "-", and that should be it...

View 5 Replies View Related

How To Get Dates In Descending Order.

Sep 21, 2007

Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.

How do I keep it in DESC order for viewing? Here's the statement:

SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC

View 3 Replies View Related

T-SQL (SS2K8) :: Change Column Order In Dynamic Pivot?

Sep 17, 2014

I am creating dynamic pivot and my column order is as below

[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]

but i want to display as below:

[Prior],[2014-08],[2014-11],[2015-02],[2015-04]

View 1 Replies View Related

T-SQL (SS2K8) :: Unpivot Sorting Column In Alphabetical Order Automatically

Jan 27, 2015

I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,

Here is my code

@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

View 1 Replies View Related

Ordering A Column In Descending With Numbers First

Oct 8, 2013

I have a Column which has both numeric values and Alphabets in them.I want to sort them in descending with numbers first and then Alphabets later.

Example:

Source column:
2008
2005
2010
2013
All-Year
Month-year

Required Output:

Source Column:
2013
2010
2008
2005
Month-Year
All-year

View 4 Replies View Related

T-SQL (SS2K8) :: Date Column With 1900-01-01 Value

Dec 23, 2014

I have a date column with 1900-01-01 value, I am trying to get the min(date) without 1900-01-01.My problem here is: there are some records with both 1900-01-01 and real date. How do I get the MIN of the real date without getting 1900-01-01. Below is my query sample.

CASE WHEN MIN(CAST(PROJECTEDSTARTDATE AS DATE)) = '1900-01-01' THEN NULL ELSE MIN(CAST(PROJECTEDSTARTDATE AS DATE)) END as 'ProjectStartDate'

View 3 Replies View Related

T-SQL (SS2K8) :: Dynamically Delete Data Based On Date Column

May 19, 2015

DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)

DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'

Actually the year stating form july.

Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.

So what I need is dynamically I want to delete the data every year prior to current year.

View 4 Replies View Related

Find Order By Date Range Or Order Id

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

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

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 14 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

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 5 Replies View Related

T-SQL (SS2K8) :: Sort Data By Using ORDER BY

Mar 7, 2013

When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer?

1) Bubble Sort
2) Quick Sort
3) Merge Sort
4) Heap Sort
5) Insertion Sort

View 9 Replies View Related

T-SQL (SS2K8) :: Partition And Order Records

Oct 8, 2014

I have the below requirement:

1. Group records according to docno column.
2. Records will sort in desc order. (According to date1 column)
3. In date1 column if more than one date is same than we ll consider the date2 column.
EX: 2008-04-30 00:00:00is same here so sorting will happen based on Date2 column. So internal sorting should happen instead assigning random values.
4. Number column is the expected output column.

docnodate1 date2 Number
d1 2008-08-25 00:00:00 2009-09-08 11:23:41 1
d1 2008-04-30 00:00:00 2008-09-08 14:40:53 2
d1 2008-04-30 00:00:00 2008-09-08 14:29:43 3
d1 2008-04-30 00:00:00 2008-09-08 13:30:04 4

[Code] ....

View 5 Replies View Related

T-SQL (SS2K8) :: Display Result In Required Order

Jun 17, 2014

I have an issue while display the result in the required order. How to get the required output.

Code :

USE tempdb
GO

IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL
DROP TABLE #VersionFormat_tbl
CREATE TABLE #VersionFormat_tbl
(
[FormatID] [smallint] NOT NULL,
[Description] [varchar](50) NULL,

[Code] ....

Present output :
fileExtension FormatID Description fileExtension versionFormatTypeId
txt 1 Text txt 1
html 2 HTML html 1
xml 3 XML xml 1
pdf 4 PDF pdf 1
xls 5 Excel xls 1
doc 6 Word doc 1

Required output:
fileExtensionFormatIDDescriptionfileExtensionversionFormatTypeId
html2HTMLhtml1
xls5Excelxls1
doc6Worddoc1
pdf4PDFpdf1
txt1Texttxt1
xml3XMLxml1

View 5 Replies View Related

T-SQL (SS2K8) :: Group By And Order By - Location Starting With A And B

Jul 7, 2014

I am having below schema.

CREATE TABLE #Turnover (
location varchar(50),
Total int
)

insert into #Turnover (location,Total) values('A', 500)
insert into #Turnover (location,Total) values('AB', 200)
insert into #Turnover (location,Total) values('ABC', 100)
insert into #Turnover (location,Total) values('BA', 100)
insert into #Turnover (location,Total) values('BAC', 500)
insert into #Turnover (location,Total) values('BAM', 100)

Now i want output order by total but same time i want to create two groups. i.e. location starting with A and order by total and after locations starting with B and order by total.

View 5 Replies View Related

T-SQL (SS2K8) :: Order By Numeric Doesn't Work

Sep 26, 2014

the select statement in the join orders the numbers asc and if you run everything below the case statement it works, but as you run the whole thing the order is no longer there.

I need the order to work. What am I missing?

--Assign Intern's students

Update tblObjectType3_14
Set [FldNumeric23764]= case when tID = 1 and Q.n <= 100 then 2
when tID = 2 and Q.n <= 150 then 2
when tID = 3 and Q.n <= 150 then 2
when tID = 4 and Q.n <= 130 then 2

[code]....

View 7 Replies View Related

T-SQL (SS2K8) :: Select Statement With Derived Order Status

Aug 28, 2015

Here is my requirement

Table 1 Order
order ID, Sales order ID order date, order type

Table 2 Order details
Order Details ID, Order ID, Order Stage

Table 3 Related Order details
Order ID(FK to Order ID), Related Order Details ID(FK to Order Details ID), Related Order ID( FK to Order ID)

Here is example

Table 1 Order
1, 1234, 2015-01-01, Refill
2, 1234, 2015-02-02, Extension

Table 2 Order Details
1, 1, Approved
2, 1, Approved
3, 2, Rejected

Table 3 Related Order Details
2, 1, 1
2, 2, 1

I have to Select Order, Order Details and Order Status

Order Status is determined from Order Stage as follows:

If, at least one order detail line(from Order Details and Related Order details table) is approved, that Order status=Approved.

For the example, Order Status of Order ID=2, is Approved based on order status for order details lines 3(from table 2) and order details ID 1 and 2 (from table 3)

How to combined order stage from table 2 and table 3 and then compute order status.

View 3 Replies View Related

T-SQL (SS2K8) :: How To Add Previous Row End Date As Next Row Start Date

Mar 14, 2014

Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.

View 9 Replies View Related

T-SQL (SS2K8) :: Trying To SUM Row With Current Date To Row With Last Month Date

Jul 23, 2014

I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.

=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,

[Code] ....

Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.

SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID

View 9 Replies View Related

T-SQL (SS2K8) :: Joining Two Tables - Output Result In The Order By DivID

Mar 25, 2014

I have two tables to be joined

Doc:
ID, DivID

Division:
ID, Div
CREATE TABLE [dbo].[Doc](
[ID] [int] NULL,
[DivID] [int] NULL

[Code] ...

As you can see, some Divisions have no correspondents in Doc, I want to show the count(1) result as 0 for those Division, and output the result in the order by DivID

View 5 Replies View Related

T-SQL (SS2K8) :: Outer Join Add Non Matching Rows To Each Order Group?

Mar 30, 2015

In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?

if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table

[Code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Order Change In Parent To Its Child Tables Using FK Relations?

Apr 20, 2015

I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.

Link:

[URL]

--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child

--When I am deleting a grand child table, it should be linked to child table first followed by Parent

--- query 1

DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';

--Query 2

DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';

how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2.

View 1 Replies View Related

Descending Sort On Index

Jul 23, 2005

I have a unique index based on the following columns:ProjectID (int)MaterialCatalogID (int)Material catalogues are pretty much static but projects are dynamic andpeople are most likely to be working on the latest project so wouldusing a descending sort on the ProjectID in the index gain anyperformance?

View 2 Replies View Related

T-SQL (SS2K8) :: Finding 5 Most Recent Records For Each Customer With Abnormal Order Amounts

Nov 5, 2014

The database consists of the following tables:

create table dbo.customer (
customer_id int identity primary key clustered,
customer_name nvarchar(256) not null
)
create table dbo.purchase_order (
purchase_order_id int identity primary key clustered
customer_id int not null,
amount money not null,
order_date date not null
)

Implement a query for the report that will provide the following information: for each customer output at most 5 different dates which contain abnormally high or low amounts (bigger or less than 3 times SDTDEV from AVG), for each of these dates output minimum and maximum amounts as well.

Possible result: [URL] ......

View 7 Replies View Related

T-SQL (SS2K8) :: Cannot Define Primary Key Constraint On Nullable Column But Column Not Null

Sep 30, 2014

We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-

/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](

[Code] ....

The script I am using is

DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script

[Code] ....

When I the the script I get the error message Could not create constraint. See previous errors.

Looking at the strings I build

ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

They all seem fine except the last one which returns the error

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

None of the fields I try to create the key on are nullable.

View 2 Replies View Related

Sort Table Descending Without Orderby

Jun 24, 2008

can any one help me to sort a table on the basis of one colomn without using ORDER BY Desc.

Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View 6 Replies View Related

Descending Dates In Sql 2005 Vs. Sql 2000

Jun 18, 2007

I have been using Access 2003 as a front end to a SQL 2k database. Dates are sorted in a descending fashion. I recently upgraded the sql server database to 2005 and dates can no longer be sorted properly. I have set the database compatibility level to sql2000 within the sql2005 database engine but no luck. Any suggetions on how to fix this?



Thanks in advance.

View 1 Replies View Related

T-SQL (SS2K8) :: Adding N Prefix In Update Statement In Order To Store The Text Correctly?

Sep 4, 2014

The FirstName and LastName values being passed in are in cyrillic text.

How do I add the N prefix in the update statement in order to store the text correctly.

I've tried FirstName = N@Firstname

or FirstName = '''N''' + @FirstName + '''

[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),

[code].....

View 8 Replies View Related

T-SQL (SS2K8) :: Replace Column With Another Column When It Does Not Exist

Jun 24, 2014

I have a script that loops through a series of tables to send data to a table from each of the tables. My issue is that not all tables have the columns I need in them. What I would like is to replace the column with another column when it does not exist. Something like below

Select Misisng_Column(A.Name, replace with B.Name) as Name
FROM SomeTable A
Cross Join (Select Name FROM AnotherTable) B

AnotherTable has one record in it. To avoid a Cartesian issue. Like I said just an example

In my real script the table aliased as A is from a list of tables in a sys.tables query that loops through to the end.

View 2 Replies View Related

Order By Date

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

Convert Date And Order By

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

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/200828/03/200820/02/200808/03/200806/02/2008How can I do, so it work?Regards Jeppe Richardt

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved