I have a table report that lists the prior 12 months of sales data. I'd like the report to display in reverse order from current month back. When I sort decedning by month it puts the months in reverse alphabetic order. How can I sort by numeric order?
Thanks.
Hi,
I've a field with month & year. I'm trying to sort this column. Something like this is my column values.
April 2001
August 2001
December 2001
February 2001
January 2001
July 2001
June 2001
March 2001
May 2001
November 2001
October 2001
September 2001
April 2002
August 2002
December 2002
February 2002
January 2002
But I want to sort this like below.
January 2001
February 2001
March 2001
April 2001
May 2001
June 2001
July 2001
August 2001
September 2001
October 2001
November 2001
December 2001
January 2002
February 2002
March 2002
April 2002
May 2002
I'm not finding any way to do this. If I sort this list by month, it'll be sorted alphabetically. Any help on this would be much appreciated!
Thanks
SELECT TOP (100) PERCENT Calendar_Key, Calendar_Date, Calendar_Year, Calendar_Month, Calendar_Quarter, Calendar_week, Calendar_DayOfMonth,
Calendar_DayOfYear, Calendar_Datename, Calendar_EndOfMonth, Calendar_EndOfQuarter, Calendar_EndOfYear,
CAST(CASE WHEN Calendar_Month = 1 THEN 'January' WHEN Calendar_Month = 2 THEN 'February' WHEN Calendar_Month = 3 THEN 'March' WHEN Calendar_Month
= 4 THEN 'April' WHEN Calendar_Month = 5 THEN 'May' WHEN Calendar_Month = 6 THEN 'June' WHEN Calendar_Month = 7 THEN 'July' WHEN Calendar_Month
= 8 THEN 'August' WHEN Calendar_Month = 9 THEN 'September' WHEN Calendar_Month = 10 THEN 'October' WHEN Calendar_Month = 11 THEN 'November'
WHEN Calendar_Month = 12 THEN 'December' END AS char(9)) AS Month
FROM EDW.Calendar_Dim
WHERE (Calendar_Year IS NOT NULL)
ORDER BY Calendar_Year, Calendar_Month
I have two vlaue prompts.. which show year & month, when we select year, it shows in order. when we select month, since its character, it showing from 'April, August....
but i want month prompt to start from Jan...
Any suggestion on how to modify..
thanks
Phanicrn
When I sort the effective date, it sorts it in month order with all of the years together under that month.
Is it possible to sort the effective date in year order? The way it if formated right now is 01-01-2007. Is there any type of formula that I can use?
Below is the field that I use.
CONVERT(CHAR(10),pcsp.pcsp_eff , 110)
I have a filed named 'date'. I convert this into 101 format. Now I want to sort that date by year not by month.
View 12 Replies View RelatedI have a report that is grouped by Month and Year.
So I need it to display:
Jan, 2007
Feb, 2007
Mar, 2007
Apr, 2007
Etc…
AND it needs to be sorted with Jan as the first month and Dec as the last (as if it was 1-12 for months).
Now I can get it to display by taking the month name and concatenating it to the year, but then of course it won’t sort the way I want it to.
Any suggestions on this?
Perhaps I could/should return two values, the first being what I display on the report, and the second being what I sort on as a hidden field? So I could return 200701, 200702, 200703, and so on, to sort on that?
I have below report with following data
Date Count
April-2015 100
January-2002 30
November-2010 55
July-2000 10
What is the best approach to sort this data based on date in a tablix and also sort the date in Column bar chart?
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
Hi all,We recently upsized two Microsoft Access Databases to SQL. We'reusing an ADP (2002) as the front end.All the conversion issues have been resolved, except for one:Whenever we insert a record into a table, the table isn't sorted byprimary key like I would expect. Instead, the record can be found atthe end of the table. This makes finding a particular record(especially as time goes on) very difficult.I've tried eliminating all indexes except for the primary key, andalso writing AFTER INSERT triggers, but the table still does not sortcorrectly.Any suggestions would be greatly appreciated!Matt
View 4 Replies View RelatedHello,
Is there a way to re sort data in a table? based on ID.
The
SORT TABLE tlb_Name ON ID Asc
is not working?
Thank you very much.
I have quite a long SP which is made up of various tables, temp tables, etc.
I am creating 11 physical tables which i am using to create a DTS package to auto output to an excel spreadsheet (template for a report).
The problem is although i have used ORDER BY statements to sort the data in ascending order the data isnt sorted.
Heres an example of one of the table outputs
--Create Table of Managers
DELETE FROM Weekly_Term_Code_Output7
INSERT INTOWeekly_Term_Code_Output7
SELECT #Temp_Agent.Manager
FROM #temp5 INNER JOIN
#Temp_Agent ON #temp5.agent_login = #Temp_Agent.agent_login
WHERE (NOT (#Temp_Agent.Manager IS NULL))
GROUP BY #Temp_Agent.Manager
ORDER BY #Temp_Agent.Manager
ive also tried placing an order by statement in the output to excel in the DTS package but this also doesnt work?
Any ideas anyone??
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
I am adding this to my c#.Net app...How can I sort all rows in a table?After it is sorted, will it remain so after i do a cnSQL.Close();so that when I then do a:string strSQL = "SELECT InvoiceNumber " +"FROM tblPrintInvoice " ;Is it still sorted for me to do a:drSQL.Read?Thanks,Trint
View 2 Replies View RelatedHi,
We have developped an ETL. For development we used small test files (10 000 rows) to test if it works correctly.
This runs in less then a minute
In Test we are using a file which contains all rows (7 million). We did twice a test and we first stopped the process after a week and the 2nd time we stopped the process after a weekend.
We are able to trace the problem to the point where it has to sort the tables.
The proces is pretty simple. We use two connectors to directly connect to the tables.
Then we have two blocks to sort the data. And then we have one block to merge the data.
Should we which to let SQL do the sorting ? Since it is in staging is has no index on that column. A select on the tables with an order by takes 3 minutes to return all those rows.
Any idea's ?
Also is there a page with the best practices for ETL ?
Constantijn
I have a "Sales shipments" report with several colums. I want a parameter for the user so he choose how to sort. I have two colums in my report I want to sort on: ItemNumber and DeliveryDate.
I added a Report Parameter UserSort with two labels (and values): Item (ItemNumer) and Date (DeliveryDate).
Then I tried to set the sorting
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
hello
i am currently trying to port a "Business Objects" report to SQL Server Reporting Services.
in B.O., there is a possibility to define the order of the table values by previewing the table values and moving them up or down.
Example : the dataset contains data for 3 items, so in the Custom Sort window, I will see these 3 items and I can order them by moving them up or down in a listbox.
is this possible to do in Reporting Services ?
I'm trying out the interactive sort feature (and have searched high and low for this answer), but can not make the sorting work if I have the data in the table grouped. If I remove the group, the sorting works fine but the output is not acceptable due to the duplicated records in the list.
Can someone steer me in the right direction?
Hi all,
Is there a way i can write a script that will sort the column names of my table?
Example:
Table 1 columns:
First_Name
Last_Name
Address
City
Country
I want to write a script that will sort them like this:
Address
City
Country
First_Name
Last_Name
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
View 5 Replies View Relatedi have table in sql and every month i am appending new data in this table, but i want to sort data Culumn Name "account_no' from smallest to largest, and whenever i append new data to this table it auto sort every time,
View 1 Replies View RelatedHello
I have a table with a RowNumber Group which shows 10 rows in each page.
it works fine.
in the next step i put an Interactive sorting on one column.
i expect that the sorting is on the table no regarding the presentation which shows only 10 rows.
in reality what happens is that the table was sorted seperated for each part of the 10 rows table,
and no for the whole table.
does any one have a suggestion how to force the Interactive Sort , sorting he Whole table first
and only later to present 10 rows each page (use the group expression)
Thanks,
Hi,
Anyone can help me for the Sql query?
I want to sort a table with a priority column, e.g. in the following...
Table A
======
value
======
9
3
1
7
4
======
After sorting:
Table A
========
no value
========
1 1
2 3
3 4
4 7
5 9
========
Anyone can help me?
Thanks.
Daniel.
I have a data load process that reads data from flat file into a Stage table in sql server. The order of the records in the stage table is exactly same as the order in the flat file. The identity column on the Stage table (which is also the clustered index) represents the exact line/row number of the data in the filat file. I perform some transformations on the data in the stage table and then insert it into a cumulative table which has a clustered index on an identity column again. When I do this, does the order of the data in the cumulative table be in the same order as the data in the stage table? Anyone, please let me know if I can rely on SQL server to maintain the same order or I will be forcing a sort order on the Identity column (clustered index) of the stage table when I insert the data into a cumulative table.
Thanks in advance!!
Hello,
I've seem some good posts similair to this, but I haven't been able to find my exact issue.
I have the following table:
ID
Name
Location
Start
End
1
Joe
NY
2000
2001
1
Joe
CA
2002
2004
1
Joe
MA
2005
2008
2
Sue
NJ
2003
2004
2
Sue
FL
2004
2008
3
Bob
CA
1999
2000
3
Bob
WA
2001
2004
3
Bob
OR
2005
2006
3
Bob
MI
2007
2008
As you can see, the Location, Start and End dates can vary for each person and I don't know how many rows a single person might have.
The result I want, is a "pivot like" table.
ID
Name
Location1
Start1
End1
Location2
Start2
End2
Location3
Start3
End3
Location4
Start4
End4
1
Joe
NY
2000
2001
CA
2002
2004
MA
2005
2008
2
Sue
NJ
2003
2004
FL
2004
2008
3
Bob
CA
1999
2000
WA
2001
2004
OR
2005
2006
MI
2007
2008
I assume I can first do a count of the maximum rows for an individual and that is greatest number of columns I would need. But doing that and trying to figure out the rest has really stumped me.
Any thoughts, ideas and suggestions would be greatly appreciated.
Thank you.
-Gumbatman
Hi,We are using Month-year tables to keep the history of long transactionof our application. For example:We capture the details of a certain action in table"TransDtls<CurrMonth><CurrYear>" (this month: TransDtls072005).This way tables keep growing. every month a new table gets created. Wehave done it because we estimated that every month year table willcarry around 2 - 3 Lac records and most of the time the operations willwork on current month year table.Avoiding this way and carrying on with single table instead of "Monthyear" table might lead us system performance issues.But now we are a bit confused on the way we are heading and also facingthe implementation issues like manipulating data from different"month-year" tables. Could anyone please help us to make our visionclear on this?Looking for your valuable comments.Thanks.
View 8 Replies View Relatedhi need help
how can i put all the monday'S day next month in one table like this
so i can use it all years
date index number
---------------------------------------------------------
04/02/2008 first_monday 1
11/02/2008 second_monday 2
18/02/2007 third_monday 3
25/02/2008 fourth_monday 4
TNX
following table global_usage
ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
how to order by month name query returns Dec13,Mar14,Jan14,Nov13 .. etc but i want to Nov13,Dec13,Jan14,Mar14 ... etc
declare @cols as varchar(max),
@query as varchar(max)
set @cols =STUFF((select ','+QUOTENAME(tb3.month) FROM
( select distinct (DATENAME(MONTH,dtDate)+''+CONVERT(varchar(5),YEAR(dtDate)) ) as month
from tableA) tb3
FOR XML PATH(''), TYPE ).value('.','NVARCHAR(MAX)'),1,1,'')
set @query ='SELECT '+@cols +' from (SELECT DATENAME(MONTH,dtDate) + CONVERT(varchar(5),YEAR(dtDate)) as month,Price FROM tableA ) tb
pivot ( sum(Price) for monthin('+@Cols+')) p
'exec(@query)
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
Most of the data is in one table.
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order
without using stored procedure
Ex:
Table: netComputers(3 rows)
Column Name: ipAddress (string data type)
ipAddress
0.0.18.1
0.1.1.2
0.0.0.1
Sql query : if I use the query
Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc
Gives result as :
ipAddress
0.0.0.1
0.1.1.2
0.0.18.1
Where as expected result should be:
ipAddress
0.0.0.1
0.0.18.1
0.1.1.2