How To Get The First Row Of A Sorted List

May 14, 2007

Hi,

I am a newbie so this might be a very basic question. I have a table that not sorted. So I write a query to sort the table like this

Select * from custInfo where custID <> '0' order by custID Desc

Now obviously I have a selection of the original table in desc order. Now I want to get the first custId from thid Desc selection. How do I do this.

So if the unsorted table looked like this

001 Martha
005 Steve
002 Mike
003 David

and the sorted selection looks like this

005 Steve
003 David
002 Mike
001 Martha

And then I want to select Steve's custID which is 005. how do i do this.

Thanks in advance.

View 2 Replies


ADVERTISEMENT

List All Tables Sizes, Sorted By Size

Jan 13, 1999

How do I get a list of table names and their sizes, sorted by sizes? The command sp_spaceused only lists one table at a time.
Thanks for your help.

Lan.

View 2 Replies View Related

Cant Get The Result Sorted

Apr 13, 2008

Hi guys,
The result of my query I am not able to get it sorted by months.Here is my query & the output.


declare @tbl as table(date datetime,amt int)
insert into @tbl
select '2-jan-2008',100 union all
select '15-jan -2008',200 union all
select '20-jan -2008',500 union all
select '12-jan-2008',300 union all
select '02-feb-2008',100 union all
select '09-feb-2008',250 union all
select '03-mar-2008',500 union all
select '05-mar-2008',800

select Months,SumAmt,MaxAmt,MinAmt from
(
select datename(mm,date)as Months,sum(amt)as SumAmt,min(amt)as MinAmt,max(amt)as MaxAmt,row_number()
over(partition by datename(mm,date) order by datename(mm,date))as rowid from @tbl
group by datename(mm,date)
)t

The result is

Months SumAmt MaxAmt MinAmt
------------------------------ ----------- ----------- -----------
February 350 250 100
January 1100 500 100
March 1300 800 500


I want to get it sorted month wise i.e Jan,Feb,March.But I cannot find a way

View 2 Replies View Related

Sorted Input

Mar 17, 2006

If a component requires a sorted input it would seem reasonable that you can check the IsSorted property of the attached input, but this will always return false. I have tried this when connecting the output of the Sort transform to my component, and then check the IsSorted property for this input. It is always false. How can this be, and also how can I see if the path is indeed sorted?

If using a virtual input column in my UI, I get a SortKeyPosition on the columns, but when overriding SetUSageType in the component class I always get zero for the key. Why is the sort information not quite there for me?

View 8 Replies View Related

Keeping Tables Sorted

Nov 28, 2003

we have a simple table

Key, Name, Address, City, State, Zip ................ect

I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.

I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.

Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.

Appleby
Jones
Robertson

This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.

thanks in advance -arthur

View 3 Replies View Related

Retrieving Sorted Data

Jul 18, 2005

I have four tables that need to be loaded into an ASP.NET application. They need to be loaded together into one result set and sorted. Is it possible to load four tables together and sort them using an SQL statement?

To clarify, say I have the following data:

Table1: Anglesey, Cardiff, Ceredigion
Table2: London, Dorset, Lancashire
Table3: Antrim, Armagh
Table4: Glasgow, Berwick, Edinburgh

I'd want the data retrieved from all four tables and sorted so that the data retrieved would be:

Anglesey, Antrim, Armagh, Berwick, Cardiff, Ceredigion, Dorset, Edinburgh, Glasgow, Lancashire, London

I am aware of and am using the SELECT ... ORDER BY feature of MSSQL in my present ASP.NET application to retrieve from single database tables. I'm using merged datasets and a sort method to solve the above problem at the moment.

View 9 Replies View Related

Concatentate Sorted Strings

Aug 2, 2006

Hi All,

I'm new to SQL Server.

I have the following table in a database of SQL Server 2005 Express.

ColA ColB
----- -----
A 12
A 10
B 50
B 13

What I want to achieve is the following result :

Col A Aggr
----- ------
A 2,12
B 13, 50

I tried the following query :

SELECT ColA, dbo.Concatenate(ColB) as Aggr
FROM
(SELECT TOP(100) PERCENT ColA, ColB
FROM TABLE_A
ORDER BY ColB) AS Derived_A
GROUP BY ColA

where Concatenate is the CLR User-defined aggreate function written in C#.

What I want to do is to first sort the rows on ColB and then to execute aggregate function over the sorted rows so as to get the above-mentioned results. However, what I got is the following

Col A Aggr

----- ------

A 2,12

B 50,13 -- not sorted

I learnt from the SQL Server documentation that even though the ORDER BY clause is presented in the subquery, the query result is not guaranteed to be sorted. Only ORDER BY clause used in the outer query should work. So, how should this problem be solved? One way of which I'm thinking is to do the sorting in the aggregate function, but I'm worrying if this is harmful to the performance. Could anyone help me to solve the problem?

Thanks!

Regards,
Nathan





View 3 Replies View Related

Find Next Entries Value (as Sorted By Date)

Jul 29, 2015

In the database I am querying, there is a field called "group". I can find out when "group" changes with a time field that was logged whenever group changes. I am trying to find what the value was changed into.

Code:
SELECT
Assignment_Log.DBID,
Assignment_Log.Assigned_Group,
Assignment_Log.Submit_Date
FROM Assignment_Log

This will tell me:

The unique ID of the database
The group it was assigned FROM
The time the assignment took place

I need to find the assignment TO

so what I need to do for the last column is something along the lines of...

Find the next record after the current record by looking at the next Submit_Date and view the Assigned_Group.

If no "next record" can be found, leave the cell empty.

is this possible?

View 2 Replies View Related

Join Tables On Sorted Values

Nov 19, 2013

Is there a way to join tables that have multiple matches to each other (2 records in one table and 2 in another) so that you get 2 records returned instead of 4 with only 1 JOIN ON qualifier?

In our warehouse DB, there is a master location table, an inventory location table, and physical table for counting all product in the warehouse. The master location table has one record per location, but there could be multiple items in that location so my outer join from the master location to the inventory table returns something like:

select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC
order by M.LOC_CODE

LOCATION ITEM LOT QTY
01-01-A 100 abc 25
01-02-A NULL NULL NULL
01-03-A 200 def 50
01-03-A 200 ghi 50

My problem is adding the third counted inventory table because it could look like anything depending on what we find in the racks:

LOCATION ITEM LOT QTY
01-01-A 100 abc 25 (exact match)
01-02-A 150 cba 75 (Item found)
01-03-A 200 ghi 50 (LOT swapped)
01-03-A 300 def 50 (Item Changed)

My join is returning 4 rows for location 01-03-A which I understand, but I'm wondering if I can sort within the join or make some temp tables so that instead of:

select M.MASTER_LOC, C.AS ORIG_ITEM, C.ORIG_LOT, C.ORIG_QTY, E.AS CNTD_ITEM, E.CNTD_LOT, E.CNTD_QTY
from
M_LOC M LEFT OUTER JOIN
C_INVT C ON M.MASTER_LOC = C.INVT_LOC LEFT OUTER JOIN
E_PHYS_INVT E ON M.MASTER_LOC = E.LOC_CODE
order by M.LOC_CODE

LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 50 01-03-A 200 ghi 50
01-03-A 200 def 5001-03-A 300 def 50
01-03-A 200 ghi 5001-03-A 200 ghi 50
01-03-A 200 ghi 5001-03-A 300 def 50

I'd like to just end up with 2 lines sorted by location, item, lot, qty so I can see that there is a problem with that location.

LOC1 ITEM LOT QTY LOC2 ITEM LOT QTY
01-03-A 200 def 5001-03-A 200 ghi 50
01-03-A 200 ghi 5001-03-A 300 def 50

View 2 Replies View Related

Selecting Single Sorted Row From Many Column

Jul 23, 2005

HiBeen at this for 2 days now.Each business has several packages which they can sort usingsort_order.I'm trying to get one package for each business(that I can do), howeverI want it to be the one with the lowest sort_order valueAs you can see below the first record has sort_order=5 when it shouldbe 1.Most of the sort_order columns will be zero by defaultAny help so i can get on with my life!CheersGary------------Current select-------------------SELECT *FROM dbo.testAccommodation_Packages T1WHERE (NOT EXISTS(SELECT *FROM testAccommodation_PackagesWHERE business_id = T1.business_id AND Package_ID < T1.Package_ID))--------------results:-----------------------Package_IDbusiness_iditem_namesort_order123rd Night FREE ...5113Donegal Town ... 0204Executive ...0--------------To recreate----------------------CREATE TABLE [testAccommodation_Packages] ([Package_ID] [int] IDENTITY (1, 1) NOT NULL ,[business_id] [int] NULL ,[Item_Name] [nvarchar] (300) NOT NULL ,[sort_order] [int] NULL CONSTRAINT[DF_Accommodation_Packages_sort_order] DEFAULT (0),)-------------------------------------------------INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('1','2','3rd Night FREE when you stay 2 nights MIDWEEK (129 EuroPPS)','5')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('2','2','Selected Donegal Town Hotel Weekend Sale - 2 B&B and 1Dinner Only € 129 PPS','4')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('3','2','2 Night Specials -Jan, Feb & Mar 2 B&B and 1 Dinner 149Euro PPS','3')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('4','2','Easter Hotel Breaks in Donegal Town - 2 B&B + 1 D€169pps','2')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('5','2','2005 Bluestack Hillwalking, 2 nights B&B, 1 Dinner, 5course Lunch 159 Euros PPS (~109 Stg)','1')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('6','2','April Pamper Package - 2 Night Special ONLY€195pps','10')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('7','2','Discount Hotel Prices for 8th & 9th April Only € 119PPS','7')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('8','2','Golden Year Breaks in Donegal - 4B&B + 2 Dinner€229pps','8')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('9','2','Hotel Summer Breaks Sale in Donegal - 2B&B + 1 Dinner€169pps','9')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('10','2','STAY SUNDAY NIGHTS FOR €25PPS','6')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('11','3','Donegal Town Midweek Special 99 Euro PPS 3 Nights B&B','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('12','3','Bridge Weekend 2 nights B&B 79 Euro PPS (approx 55Stg) Double Room','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('13','3','Donegal Spring Weekend Specials 2 B&B 1 Dinner109.00euros pps','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('14','3','Valentines Weekend 2 nights B&B and 1 four coursegourmet dinner 99Euro PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('19','3','Golden Years Break.40% OFF 4 nights B&B€129.00p.p.s.','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('20','4','Executive Celebration Offer 1 night B&B + Dinner €139 PPS','0')INSERTtestAccommodation_Packages(Package_ID,business_id, Item_Name,sort_order)VALUES('21','4','Watercolour Painting Break 3 B&B Full Board andTuition € 335 PPS','0')

View 1 Replies View Related

Getting Line Number Sorted Out In Matrix

Dec 27, 2007

Hi there,

I have a matrix report that shows a certain columns and rows. I need a row counter to show me the amount of rows in that matrix but it doesn't count properly by using the expression: =Int(RowNumber("matrix1") -1)
I actually don't have any idea on what this expression should be to fix the row count...to illustrate what I get in the report:

______________________________________________________________________________
Chkp | Row | Serial No | Call of Date |Colour| Pillar | AV | Sport | USA | SRA | TAPE |
2019 | 1 | 7006892 | 2003/02/11 | 3644 | 78222 | 3902 | 9182 | | | |
| 3 | 7000123 | 2003/02/11 | 3299 | 17283 | | 9238 | 2793 | | |
| 5 | 7302031 | 2004/01/11 | 3902 | 28932 | | 3233 | 2332 | | |
| 7 | 7273211 | 2004/02/08 | 6727 | 39232 | 8228 | 3293 | | | |
| 8 | 7382728 | 2004/11/11 | 7822 | 32342 | 3902 | | | | |
| 13 | 7342934 | 2004/12/03 | 8323 | 23422 | 2031 | 9212 | 2934 | 2934 | 2982 |
| 18 | 7329491 | 2004/13/02 | 8291 | 92423 | 7922 | 0231 | 2342 | 2355 | 2223 |
| 22 | 7328438 | 2004/12/22 | 8399 | 92311 | | 0289 | 8392 | | 3982 |


As you can see above, the row count numbers are not lining up at all, it starts at 1, then jumps 2 every count then all of a sudden jumps 5 per line...a pattern that is interesting but difficult to understand. The unique value in this matrix is that serial number but even though i group it by the serial number, it still gives me the funny Row count values.

What would be the approach to solving this?

Regards
Mike

View 3 Replies View Related

Trying To Join Within A Table - Get Result Sorted By Primary ID

Aug 27, 2014

So if an item is a bottle, it will have a unique BottleKey and Null for CaseKey and if an item is a Case, it will have a unique CaseKey and Null for BottleKey. However, the PrimaryIDs are the same for both the bottle and case. I get the results to look like this:

Bottlekey CaseKey PrimaryID
4754 NULL ABC-234
NULL 5465 ABC-234
.... .... .......

Is there a way to get the result sorted by Primary ID?

So that the rows are halved from what we have in the table above and the results look like

PrimaryID CaseKey BottleKey
ABC-234 5465 4754

View 1 Replies View Related

MS SQL Server 2000: Rows Get Sorted Automatically

Feb 6, 2008

Hi all,

I import MS Excel 2003 spread sheet in MS SQL Server 2000 through MS SQL Server 2000 Enterprise Manager (rightclick on the table to be filled with dataall taskimport data). My excel file have 2000 rows and 100 columns of data. All the data are imported in relevant attributes cells in good manner. But the rows are sorted automatically. I am trying to say that first row data is match with my excel file. But second row data have gone to 7th row and 7th row have gone to 5th row like that. I need the data sequence what I have in my excel file. What is the problem occurred? How can I solve this?
Can I export my MS Excel 2003 file to MS SQL Server database?
Please help me. I don't have more knowledge in MS SQL Server 2000.
If your answer has any query to run then please mention where should I run that query.
Thanks,

With Regards,
bala.

View 3 Replies View Related

Why Is There No Warning If An Input To An Aggregate Component Is Not Sorted

Nov 16, 2005

All in the subject.

View 11 Replies View Related

MS SQL Query, What's The Default Order The Rows Returned Are Sorted By?

Jan 12, 2005

i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?

this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in

View 6 Replies View Related

Selection From Sorted Subquery Is Unsorted In SQL Server 2005

Jun 14, 2007

I have some relatively simple SQL that acts differently between SQL Server 2000 and 2005. Although it is easy to fix I'd like to know if this difference is expected (documented) or a bug, and if there is perhaps a setting/switch I can use to avoid a code review of hundreds of stored procs to look for similar scenarios. Executed the following script in SQL Server 2005 –CREATE TABLE #Floats(FloatID INT IDENTITY,FloatNumber FLOAT NOT NULL)DECLARE @sngCounter floatSET @sngCounter = 20WHILE @sngCounter >= 0BEGININSERT INTO #Floats ( FloatNumber ) VALUES( @sngCounter )SET @sngCounter = @sngCounter - 1ENDSELECT * FROM (SELECT TOP 100 PERCENT * FROM #Floats ORDER BY FloatNumber) AS FloatNumbersDROP TABLE #FloatsGOProduces the following resultset –FloatID FloatNumber----------- -----------1 202 193 184 175 166 157 148 139 1210 1111 1012 913 814 715 616 517 418 319 220 121 0In SQL Server 2000 resultset is this –FloatID FloatNumber----------- --------21 0.020 1.019 2.018 3.017 4.016 5.015 6.014 7.013 8.012 9.011 10.010 11.09 12.08 13.07 14.06 15.05 16.04 17.03 18.02 19.01 20.0

View 4 Replies View Related

Merge Join - Output Of Lookup As Sorted Field?

Nov 3, 2007



I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join. With the new, converted field, I do a look up. From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK. This is necessary because SUMDWK is one of the sorted fields. In the look up, it is not possible to change the Output Alias. Does anybody know a way around this? Thanks.

View 14 Replies View Related

Ensuring That Matrix Data Region Col Headers Are Sorted

Dec 25, 2007

i ran a preview of a matrix based report whose column headers are dates. The dates seem to be displaying in a somewhat (not completely) random order from left to right. How can I ensure that they display chronologically from left to right?

View 1 Replies View Related

Integration Services :: Why Merge Transformation Need To Sorted Inputs

Jul 16, 2015

Why Merge Transformation Need to Sorted Inputs?

View 4 Replies View Related

Weird Replication Bug. Bulk Data Stream Was Incorrectly Specified As Sorted.

Apr 17, 2006

before anyone even says it, i checked the collation order on everything and it's the same. i get the error when the snapshot is trying to be bulk copied to the subscriber.

i'm on sql2k sp4, server and db collations are SQL_Latin1_General_SP1_CI_AS. here's a repro. 1st, run this in a blank db:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Event_Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Event_Transactions]
GO

CREATE TABLE [dbo].[Event_Transactions] (
[EventTransactionId] [int] IDENTITY (1, 1) NOT NULL ,
[OrphanedFlag] [bit] NOT NULL ,
[ProcessedFlag] [bit] NOT NULL ,
[ProcessedTimeStamp] [datetime] NULL ,
[EventTimeStamp] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [EventTransactions_IDX_ProcessedOrphanedEventTimeSt amp] ON [Event_Transactions] (
[ProcessedFlag],
[OrphanedFlag],
[EventTimeStamp]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Event_Transactions] ADD
CONSTRAINT [PK_Event_Transactions] PRIMARY KEY NONCLUSTERED
(
[EventTransactionId]
) ON [PRIMARY]
GO

insert into Event_Transactions (
OrphanedFlag
,ProcessedFlag
,ProcessedTimeStamp
,EventTimeStamp
)
values (
1
,0
,NULL
,'2004-05-07 15:15:24.000'
)

insert into Event_Transactions (
OrphanedFlag
,ProcessedFlag
,ProcessedTimeStamp
,EventTimeStamp
)
values (
0
,1
,'2004-07-08 13:04:01.513'
,'2004-07-07 16:52:08.000'
)

Now, use transactional replication to replicate it to another db taking all the defaults. when the distribution agent tries to apply the snapshot, it fails with the message mentioned in the title..

Has anyone ever seen this? It's keeping us from considering MS replication for one of our major products. Thanks.

View 1 Replies View Related

SQL 2012 :: Populate Int Column With A Sequence Of Numbers But Sorted By Another Field

Oct 8, 2015

The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).

While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.

What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.

Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;

View 2 Replies View Related

SQL 2012 :: Environment Variables Not Sorted In Dropdown When Configuring Parameters / Datasources

Sep 25, 2014

Any fix for the seemingly random sort order of the variables in the dropdown list when configuring parameters and connection managers in the SSISDB catalog?

I imported all of our connection strings into an environment (about 200 of them). They were inserted in alpha order and the ID values within the internal.environment_variables table shows them in order as well, by ID and by name.
When I run profiler and capture the command that retrieves them and run it in ssms they are in order but in the dropdown they seem random.

There are no values within any of the tables that accounts for the order they are in.

If a package has 5 connections you need to go through the unsorted list 5 times to find them.

Sometimes you get lucky and they are in the first 20 or so.

I know I can write a script, just wondering if there is a fix for the sorting.

View 2 Replies View Related

Transact SQL :: How To Change Auto-sequence Numbers To Match Sorted Data

Jun 15, 2015

I have a database that has entries that I want sorted by date order. Each entry has an auto ID number allocated (primary key auto sequencing), which I want to change to reflect the sorting (so the first date has the first auto ID number and so on).I've gone into the database and sorted the entries as I want them. Then I've gone into Design View to delete and restablish the primary key autosequence. However, it is not keeping the date order in the database (ie entry ID 3140 date is 12/06/2015, but 3141 is 02/02/2012). How do I get it to maintain the order?

View 3 Replies View Related

Reporting Services :: Report Builder 3.0 - Additional Columns Sorted After Values

May 12, 2014

I'm somewhat new to report builder and have been trying to recreate a report previously created in an Excel Pivot Table. I'm encountering an issue arranging the data the way it's arranged in Excel. 

Specifically, I would like the values column to precede an additional column.  

Until I can post pictures I'll have to try and mock it:

COLUMNS
Values
Results (my data I want as a 2nd column)

I can't figure out how to get report builder to do it the same way. Whenever I add the 'Result' data as a column it always appears on top. I'm guessing what I need to do is somehow get result set as a child of the first Static group, but I'm unsure how to do that.

View 4 Replies View Related

SSIS: Merge Problem: The Input Is Not Sorted (for Use In Exporting A Multi-record Format File)

Feb 21, 2008



I am using the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

I have created the 2 datasources, ordering each on a field commmon to both.

I have created the two derived columns headers and am now moving on to the merge.

It is failing with the following error:
"the input is not sorted"

And whilst I definitely have an order by on the query, when I look at the metadata between the datasource and the derived column, the Sort Key Position items displays "0" for all my fields, I was expecting the sort field to have a "1" in this column. What am I missing?

Any help would be most appreciated!

View 7 Replies View Related

SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List

Jun 15, 2015

So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function

So the output would look like :

CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]

SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0

GROUP BY
preop.Case_ID

View 8 Replies View Related

Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)

Apr 9, 2008



I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.

However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).

For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).

Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac


How can I accomplish this?

I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.

Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?

View 21 Replies View Related

Insert Value List Doest Not Match Column List

Apr 18, 2007

HI...



I need to do a simple task but it's difficult to a newbie on ssis..



i have two tables...



first one has an identity column and the second has fk to the first...



to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!



i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"



here is the script



INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)



what's the problem ??

View 7 Replies View Related

Transact SQL :: Returning Non Indexed Records From Sorted Table - Speed Same As Indexed?

Dec 1, 2015

I have created a table from another table where I specified that one of the fields, an number field,  is sorted in ascending order and have NOT specified that it is to be an indexed field and there are 10 million records, from 1 to 10,000,000 exactly.

Now, if I query that table, asking to return records 1-1,000 from that non indexed number field that I sorted in ascending order (where number field <= 1,000) , will it run as fast as if it were indexed?

In other words, does SQL know somehow that these records are sorted in ascending order and so will not do a full table scan, stopping at 1,000 to return my data set?

Or is there no way for SQL to know this and only specifying an indexed field allows SQL to know that its in some order and so it doesn't have to do the full scan?

View 15 Replies View Related

Reporting Services :: SSRS - Get Common Values Between Two Columns Where Values Sorted Comma Separated

May 6, 2015

I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:

ColumnA :  abc,cde,efg    
ColumnB : cde,xyz,abc    

the result in    

ColumnC : cde,abc

similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.

View 5 Replies View Related

Items In List A That Don't Appear In List B (was Simple Query...I Think)

Jan 20, 2005

Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true


In pseudo-SQL, I want to write a clause like this

Code:

IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True


Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

I may have to achieve this with some kind of logical loop but I don't know how to do that.

Can anyone help?

View 3 Replies View Related

Select List Contains More Items Than Insert List

Mar 21, 2008

I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?

Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,

CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,

tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)

View 2 Replies View Related

Insert Only New Items From A List But Get ID's For New And Existing In The List.

Feb 12, 2008

Hi,

I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.

Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'

My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.

performance is a not that great so I'm thinking that my code can be improved.

SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)

TIA





Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO

CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)

--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token

INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0

return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO




View 5 Replies View Related







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