I have a table which contains order information, which I would like to have line number associated with them
what SQL statement do I use in order to add the line numbering for each line, and have it dependent on reseting on the sales order number?
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
Hello,
I am having trouble getting the results I am looking for from this query:
select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo ORDER BY TransTime) as CustCount
, ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions
The results I am trying to get would be like this:
CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042000 04:00:00 NW
1 2 042000 04:00:00 NW
1 1 042000 06:51:00 AA
1 1 042000 08:45:00 NW
1 1 042000 08:52:00 NW
1 1 042000 10:30:00 NW
1 1 042000 10:45:00 NW
2 1 042000 10:45:00 AA
1 1 042000 11:30:00 NW
1 2 042000 11:30:00 NW
But the closest I have gotten is:
CustCount DelCount DriverNo TransTime AirPortCode
-------------------- -------------------- -------- --------- -----------
1 1 042000 04:00:00 NW
2 2 042000 04:00:00 NW
1 1 042000 06:51:00 AA
1 1 042000 08:45:00 NW
1 1 042000 08:52:00 NW
1 1 042000 10:30:00 NW
1 1 042000 10:45:00 NW
2 1 042000 10:45:00 AA
1 1 042000 11:30:00 NW
2 2 042000 11:30:00 NW
The CustCount field should read 1 if there is only one unique AirPortCode during the TransTime with the specific DriverNo, but the query is counting both rows during that transtime. Any ideas?
Thanks.
Hi guys,
I have a table with 3 col like below
col1 col2 col3
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67
none of them are unique, some are null and there are about 800,000 rows
I want to insert a col before 1 or after 3 and have numbering from 1 to the last row. How can I do that?
Thank you
Mina
I have table with 10-20 rows with field P6 which is empty. I want toupdate numbers to P6 starting 1 and increasing by 1. I suppose it isdone by triggers but I don't know how to do that. Help :-)
View 7 Replies View RelatedGood Day everyone...
I created a simple report for getting the top selling items of a store. I am getting the report well but my team mate requested to include a number at the first column that will hold the ranking of the item. Is there a function for including a rowrank or someway that i can add a column to my query that will provide the rowrank. Please show me how... c",)
thanks in advance guys...
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies View RelatedIs It possible that, if there are 5 rows selected out, each row will automatically have the number from 1 to 5?
for example, after select query, results:
---------------------------------------
1Tom100
2May93
3Apple87
4Johnny72
5Noel67
Hi All,
I've upsized my database from access to SQL server, now im very new to SQL etc and i've been reding up on sqential numbering etc and my mind is buzzing with ideas. This is my problem.
I have a table and a colum called PIR ID this colum needs to start the numbering at 372 and increment by 1 each time. Now initially this was o.k by using identify. However it's a problem when 2 users open the form in access they are given the same number.
Now my though on this was to initially have a seperate table called new PIRID and hold the next bumber in there, so that a new number is taken from the list and entered into the record on save ( does that make sense) now the code i've got up to so far is that i've created the table and i get stuck from there.
Can anyone help me with the code of format of code i'm meant to be using, i'm under the understanding that to perform this funtion i may have to use a trigger of some sort.
Thanks In advance
Carol
Hello,For analyses and reporting I would like to add a 'ranking' to atable/view.Example:Using the 'order by desc' clause in query I get a list of Customersordered by Turnover (descending). I would like to add that rankingnumbers (same as recordnumbers) in the query. I would like to have thefollowing result:Cust_nr Cust_Name Turnover_2004 Ranking002234 Bayer 139.000 1003456 Rentokill 123.456 2001231 Air France 105.000 3etc.When the 'ranking' is part of the query/table I can use this ranking inan other query.Important: This questions is not about making an (empty) table structurefor filling in by an application and generating a new unique number eachtime a record is added.I hope you can help me.Thanks,Hans*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedIs there a quick way to number a query?ie I want to number the records returned by a recordset consecutively.This seems like it should be simple but I haven't figured out how to do ityet.Any help is appreciated!TIACarter
View 2 Replies View RelatedI have created an ID column for auto numbering, but in the properties, they didn't have the option of setting it to auto numbering, and how am i suppose to set or code it to autonumbering.
Please and thankyou
I want to add numbering in group in the reports.(number each row in the detail)
when I worked with access it was possible.
can I do it in the report services?
thanks!
We have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
View 1 Replies View RelatedI have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?
kam
Hi,I'm
trying to insert records from one table into another table. The
destination table has a ROWID field which cannot be an identity key,
but needs to 'act like' an identity key and have its value populated
with (Max(ROWID) + 1) for each row added to the table.To my
thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not
work as it will only be evaluated once so if I am adding 1000 records
and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of
1235.Is there a way to accomplish this?Thanks
I know its a simple question. But couldnt find a solution of my own.
My SQL 2005 table has 2 fields, namely - id (primary key), comments(varchar).
I want to automatically increment the value of 'id', starting from 1000.
Is there any DataType which i can specify at the time of table designing, so that the value of the id
field will be automatically incremented, upon inserting each new record.
Im using SQL 2005 Express Edition. Can anyone tell, how to do that ?
Thanks
I'm creating an application to track project issues. I would like to have the issue number start over at 1 for each project. I would like to have the issue numbers assigned to each issue in sequential order for each project. Below is a brief example of what I need it to do. I don't know where to even start to try to figure this out. Thanks
ProjectNumber, IssueNumber
TEST0001, 1TEST0001, 2TEST0002, 1TEST0003, 1TEST0003, 2TEST0001, 3TEST0004, 1TEST0003, 3TEST0002, 2TEST0005, 1
work_id Work_item_code
--------------------------------
3 Sup
14 100001
5 100002
16 100003
8 IWSM
9 ES3000
10 AppDev
11 100004
I want to have a counter on this select like
work_id Work_item_code counter
----------------------------------------
3 Sup 1
14 100001 2
5 100002 3
16 100003 4
8 IWSM 5
9 ES3000 6
10 AppDev 7
11 100004 8
I can use rownum in Oracle, what do I do to get this in SQL server ?
Thanks Sonali
Hello all,
I'm trying to number rows in a resultset by a grouping.
e.g. if my data looks like this:
personname persondata
----------- -----------------
person1 data
person1 more data
person1 other data
person2 stuff
person2 more stuff
person2 even more stuff
person2 lots of stuff
person2 last bit of stuff
person3 info
person3 more info
I want it to return this, with x as a sub-numbered value (like an identity for each grouping):
x personname persondata
-- ----------- -----------------
1 person1 data
2 person1 more data
3 person1 other data
1 person2 stuff
2 person2 more stuff
3 person2 even more stuff
4 person2 lots of stuff
5 person2 last bit of stuff
1 person3 info
2 person3 more info
Any ideas?
Thanks,
Dan
Hi guys,
Is it possible to make a table with 'Auto-numbering'? I mean the same function as in MS Access.
Thanks in advance.
Hi!
I'm trying to get a MSSQL view to return some data marked with a week number based on a date (which is present on every row in the source table). Since I reside in Denmark, I would like the numbering to follow the rule '1. week of a year is the first week with a least 4 days in it'. My instance of MSSQL however is convinced that the rule '1. week starts 1. January'.
Are there any ways to configure my way out of this problem??
Examples:
This year 1. and 2. January is returned as week=1, 3. January is returned as week 2.
I would like 1. and 2. to be week=53 and 3. to be week=1.
Thanx in advance! :)
Ult
I've created a sproc that will provide a recordset for an Access report via a pass-thru query. The report is a production schedule.
Some of the runs on the schedule have a note associated with them. I need
to be able to number these notes, so that they can be displayed in the report
bibliography style. When I populate the data in the sproc, if the "notes"
field in my table contains any data I display a 1, else it's a 0
Like:
SELECT NOTE_FLAG = CASE WHEN ISNULL(SCHED_NOTE,'')<>'',1,0
FROM MASTER_SCHEDULE
The problem is, I really need to display sequential numbers instead of 1's
If I create a base set with this:
CREATE TABLE #TMPRST (
RECID INTEGER,
PRODUCT VARCHAR(10),
QTY FLOAT,
NOTE_FLAG INTEGER)
INSERT INTO #TMPRST
SELECT 1, 'ABC123', 4, 0
UNION ALL
SELECT 4, 'DEF123', 5, 1
UNION ALL
SELECT 5, 'ABC456', 12, 0
UNION ALL
SELECT 13, 'PQR789', 10, 1
How do I go back and convert the note_flag column to read
... 0
... 1
... 0
... 2
We're performing an insert and the column that has identity is numbering the records without a problem, but it's assigning duplicate ID's on the records. Does Autoincrement or Autonumbering need to be turned on somewhere?
spencer
Good afternoon,
I hope the title is self explaining. Here is an example of what i would like to do:
Before...
1
1.1
1.1.1
1.2.1
1.3.1
1.10.1
After...
01
01.01
01.01.01
01.02.01
01.03.01
01.10.01
Thanks,
Phil.
OK,Here is my challenge.If I have a query that produces the followingItemSold_OnA01-10-2004 8:03A01-11-2004 10:05A01-12-20041:37A01-14-20047:16B01-10-20049:37B01-12-2004 11:42B01-13-20049:37But I need it to produce this insteadItemSold_On InstanceA01-10-2004 8:031A01-11-2004 10:052A01-12-20041:373A01-14-20047:164B01-10-20049:371B01-12-2004 11:422B01-13-20049:373So basically I need it to chronologically number the rows, but I needthe count to start over when the item changes.
View 3 Replies View Related
I don't think this is possible, but I promised my users I would bring this question to the forum. The users want to have the page numbering change per each group on the report. The report is large, and there are up to 5 pages in each group. I have a large List Control surrounding the many tables on my report that controls the over all report grouping. Everything looks great and the users are pleased.
But when I try to add the Global!PageNumber and Global!TotalPages to the bottom of the List Control I get the error that the Globals properties can only be used in the Page Header and Page Footer section.
Is there any way around this? I could not think of a way around it, but I am not an expert in Reporting Services by any means.
Thank you,
T.J.
Hi,
I've a column say page#. This has by default 4 pages which I'm numbering as 1,2,3,4. I'll be adding pages to this table and the number needs to start from 5 (since 4 pages are already present)
Page# is not an identity column. I want to know if theres a function which can do the trick.
Thanks for your help!
Subha
HI
Has anyone figured out how to reset the page numbers after a grouping and have it display in the format €˜Page X of Y€™? I can get it to reset the page number for each group and display €˜Page X€™ but it€™s the €˜of Y€™ part that is a problem.
Thanks,
Hi everyone,
The problem is about numbering groups. I'm trying to display data like this-
1. U.S
1.1 San Francisco
1.1.1 employee1
1.1.2 employee2
1.2 Dallas
1.2.1 employee3
2. UK
2.1 London
2.1.1 employee4
I've tried using RunningValue(1, Sum, "group_name") but it gives erroneous results. I'm using 3 lists, one at each group level. The outermost list (1st list) is grouped by "country" (group_1), 2nd one is grouped by city (group_2) and the innermost (3rd one) by employee name (group_3).
I can't figure whats wrong. Can anyone help me?
Thanks in advance.
Using the TOAD application for Oracle, I am able to number the SQL query lines for readability. Can the same be done for SQL Server 2005?
View 4 Replies View Related