SQL Server 2008 :: Splitting Param Array Into Groups Of Four With Offset
Feb 25, 2015
I'm getting a file that essentially has a parameter array: a specific like would have three known values, and then x number of groups of four parts;
The question is how to handle logical groups of parameters; i though maybe by using a modulous on their itemnumber ...
So I can easily use DelimitedSplit8K to definitively find the first thee values, but how do I dynamically get X number of groups of four; eventually i need the first three fields combined with each quadgroup.
So a rough example of the desired output is this:
this:
'03,0000001,USD,010,81257946,,,015,121809761,,'is split into rows with the first three columns included in each row:
030000001USD01081257946NULLNULL
030000001USD015121809761NULLNULL
Here's a setup i've put together:
IF OBJECT_ID('tempdb.[dbo].[#AccountIdentifier]') IS NOT NULL
DROP TABLE [dbo].[#AccountIdentifier]
GO
CREATE TABLE [dbo].[#AccountIdentifier] (
[AccountIdentifierID] INT IDENTITY(1,1) NOT NULL,
[DateReceived] DATETIME NULL DEFAULT getdate(),
[RecordCode] VARCHAR(3) NULL,
[Code] ....
View 2 Replies
ADVERTISEMENT
Dec 27, 2007
I'm working with a dataset something like this:
TypeID Sales($)
------ -------
1 123.45
1 47.98
2 9.21
3 87.23
3 99.88
4 123.43
And a multivalued parameter that lets the user select which TypeIDs specifically he wants to see:
ParamID ParamValue
1 Q1
2 Q2
3 Q3
4 Q4
And in my Report, I have data showing up something like this:
CountofAllSales: 6
SumOfAllSales: 491.18
CountofCustomSales: (count of sales with type specified in parameter)
SumOfCustomSales: (sum of sales with type specified in parameter)
The count and sum of custom sales should show -ONLY- the numbers from the TypeIDs selected in the multi-value parameter. But the CountAll and SumAll show everything, regardless. This is where I run into problems. I can't seem to find an "in" clause in the SSRS expressions. If the TypeID parameter was single value, I could write something like this
Expression for CountOfCustomSales:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value, 1, 0))
However, since its multi-valued, that won't work. You'd have to write something like:
=SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(0), 1, 0)) +
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(1), 1, 0)) +
....
SUM(iif(Fields!TypeID.Value = Parameters!TypeID.Value(length), 1, 0))
And obviously this doesn't work when you don't know exactly how many elements are going to be selected.
What would be ideal would be something like an "in" clause, but I can't find any such functionality or think how to write my own function:
=SUM(iif(Fields!TypeID.Value in
Parameters!TypeID.Values, 1, 0))
Short of modifying the StoredProc itself (and for me, that means red tape. :( :( ) can anyone think of a way to count/sum only the values specified in an MVP??
View 1 Replies
View Related
Mar 19, 2015
I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"
How do I get the "01/01/1900" between ":" and ")"
View 6 Replies
View Related
Mar 7, 2015
I am still fairly new to Sql Server 2008 R2 (express) , but am enjoying exploring it.
I have several databases up and running and now a large number of users in each.
But I notice whenever I write a stored procedure I have to go in and add each user to give them permission to exec it. When the user numbers are low its not a problem but I now have over 20 users on 1 database and its becoming tiresome.
I have heard of Active Directory on Windows Server but my database is installed on windows xp professional PC. Its running well and we don't have a budget to change it.
I've been searching the internet to see if there is some way I can create a group within sql server give the group all the necessary permissions/ privileges and then add the users to that group thereafter...
View 6 Replies
View Related
May 16, 2008
Hi,
We have just created a new Windows 2008 server running SQL 2005 64 bit. I am in the process of migrating old SQL 2000 databases onto this new server. One of the databases required a SQL login for some Windows groups that get created from the SMS software. I'm having a problem when I go into create a new login, select object type Groups, from this location , I enter the object name and click Check Names and it finds it. I select okay and everything looks good. But when I click ok on the New Login screen, I get the 15401 error saying that Windows NT user or group not found. I have gone into the Server Manager - Groups and verified that the group exists, and it does. The only thing I see when I click properties is on the bottom right had side of the screen it says "Changes to a user's group membership are not effective until the next time the user logs on." It says this for all the groups on this server and I get the same error message when I try to add any group. I have had the user that is a memeber of this group log off and back on a few times, but it still says the same thing and I still get the same error.
Anyone have any ideas on what the problem might be? Is there something on the Windows 2008 server setup that we missed?
Thanks,
Isabelle
View 1 Replies
View Related
Jul 20, 2005
We recently translated the backend db from Access(97) to SQL Server.We are still using Access frontends. I have an update query in theAccess front end that uses a lookup table to populate fields. Thecommon fields between the table and the lookup table are the primarykey (LocID) and date & time fields. The query is:UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],tblPT.Salinity = [tblPT_Offsets].[Salinity]WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And[EndDate]));This worked fine in Access and seemed to work fine after switching toAccess, but on closer look, there is exactly a 2 day error beingintroduced. A quick search of the newsgroups brings up lots of Accessto SQL date problems, but a 2 day offset seems rather strange? Anyideas??I know the field names Date and Time are inappropriate, but legacyissues are a pain in the butt to resolve!! Could this be a problem?David
View 3 Replies
View Related
Aug 7, 2015
I have following simple code in my stored proc. even I have hard coded OFFSET to non zero, but it always return result from starting point 0. End limit "Fetch Next" is working perfect.Only problem is with start.
SELECT
*
FROM #invoices
ORDER BY #invoices.InvoiceDateTime ASC
OFFSET @StartRow ROWS Fetch NEXT @EndRow ROWS ONLY;
View 9 Replies
View Related
Jan 25, 2007
All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
View 10 Replies
View Related
Jun 25, 2007
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200)
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
Thanks
View 5 Replies
View Related
Jan 25, 2008
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.
I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:
ID1
ID2
DisplayData
1
1
A
1
2
B
1
3
C
2
1
A
2
2
B
2
3
C
Parent group is on ID1, child group is on ID2, report would show:
1
1
A
2
A
3
A
2
1
A
2
A
3
A
Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
View 2 Replies
View Related
Jul 29, 2014
I have a query which can return multiple rows. I want to select only the nth row. In other db languages, this is achieved using the OFFSET command, but in SQL Server this command does not exist. I saw that you can hack it using ROWCOUNT and an ORDER BY clause, but I don't have anything to order by. Is there some other way to do this? My query is
Code:
SELECT S.SystemName FROM Projects AS P INNER JOIN ProjectSystems AS PS ON PS.ProjectId = P.ProjectId LEFT JOIN Systems AS S ON S.SystemId = PS.SystemID WHERE P.ProjectId = @ProjectID
This will select an example result of:
Code:
System Name
System 1
Test System 2
Test 123
How can I do something like (psuedo code)
Select Row @row From query
View 4 Replies
View Related
Jan 12, 2004
What is the fastest way to select a value offset by n rows from the start row? I used to use a cursor with FETCH ABSOLUTE in Sybase SQLAnywhere, but this is incredibly slow in SQL Server. Here's the current function I'm using:
FUNCTION dbo.TradingDaysBack ( @ItemID int, @FromDate smalldatetime, @DaysBack int )
RETURNS smalldatetime
AS
BEGIN
declare @BackDay int
declare @OADay int
set @OADay = dbo.GetOADate(@FromDate)
declare curDaysBack cursor scroll for
select OADate
fromData_Daily
whereItemID = @ItemID and OADate <= @OADay
order byOADate desc
open curDaysBack
fetch absolute @DaysBack
from curDaysBack
into@BackDay
close curDaysBack
deallocate curDaysBack
if @BackDay is null
begin
set @BackDay= ( select Min(OADate) from Data_Daily where ItemID = @ItemID and OADate <= @OADay )
end
RETURN convert(smalldatetime, @BackDay)
END
The idea is to get the date n rows of data back from the starting date (i.e. 30 trading days back from 12/1/2003). Any ideas?
View 2 Replies
View Related
Aug 27, 2007
Please, please, please, please just add limit/offset to the language.
7 years of working around this unnecessary limitation are enough. And that's just me personally, i'm sure there are those out there with a longer history and are more irritated. I know this isn't the first post on this here, nor will it be the last. I think that should be taken as a hint, and something should be done about this. Something like this:
SELECT column FROM table LIMIT 10 OFFSET 10
See, it wasn't that hard =) Now there are no ugly contortions to go through just to paginate the data. And don't recommend I return all 20k rows and do it on the client side, m'kay, or write ridiculous sql. It seems that for all the hard work that went into making those other features work that this could have been done. Don't lie, you know it could have! If there was a voting feature for suggested improvments, or a bug tracker + watch list that functioned the same, it would help. Others do that, as well.
//Another Annoyed DeveloperAnd seeing as there aren't any feature/suggestion channels that work (see:connect), I guess this forum is the best we can do. Sorry.
View 3 Replies
View Related
Mar 3, 2005
I have a query in MySQL which needs to be translated to an equivalent in MSSQL Server. The query uses a LIMIT clause with an offset.
select * from test LIMIT 10,5
(meaning from the 10th row containing 5 rows in the result set)
How can we port the same logic to SQL Server ? I know TOP can retrieve top n rows but how can we specify the offset ?
Any help is appreciated !
View 4 Replies
View Related
Jul 23, 2005
In MS SQL 2000, I would like to obtain the timezone offset from UTC fora given date. For today's date, I can doDATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a futuredate may not be the same as today because some countries go in DaylightSaving mode. Can you suggest a way to obtain the timezone offset forany given date?ThanksYash
View 3 Replies
View Related
Aug 27, 2014
I'm quite new to SQL. I'm able to extract the info that I need, but only into a result of one row, like:
Order header | Order details
ID | Customer name | Customer address | Product number | Product name | Quantity | Price | Product number | Product name | Quantity | Price
2 Andy Andy's way 2 24 Glue 3 35 39 Oyster 2 9
I would like the query to return it in this way:
2AndyAndy's way 2
24Glue335
39Oyster290
Is it even possible?
View 7 Replies
View Related
Oct 25, 2005
Hi,
I need the equivalent for below mysql query :
select *from test limit 5, 20
I know that the below is possible in SQL Server :
select top 20 * from test where column1 not in (select top 5 column1 from test).
But my problem is I want a generic solution wherein I will not be aware of the columns available within the table.
Please advice,
- Mira
View 1 Replies
View Related
Dec 15, 2014
Here's the resultset:
Main
CC-09-00032-D
CC-09-00113-A
PR-10-01004-2
Expected result:
P C
PR-10-01004-2 CC-09-00032-D
CC-09-00113-A
What I need is split the data into two columns if data in column Main starts with 'PR-' then output result to column P and if it starts with 'CC-' then to column C (the output needs to be in one table).
View 3 Replies
View Related
Feb 18, 2005
Hi,
Sorry I have to post this here, but its sort of related to MS SQL anyway
I'm running a PHP system with MS Access. I need to order results in pages. (For those who are familiar with MySqL and Postgresql - I need the equivalent to LIMIT/OFFSET in MS Access).
I know I can "SELECT TOP 50" in my sql - so that solves the number of results per page.
But what about page 2,3,4..etc.... how do I select results from an offset?
There doesnt seem to be a solution for it
Thanks!
View 2 Replies
View Related
Oct 13, 2015
I have a store procedure who receive an xml file.
In this store procedure I try to put all record in temporary table for some treatement ..
But I have a problem with one field ...
When I execute the code below I receive this error message :
The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.
The problem comes from the field DeleteDate from the second row of the xml file
how I can get the correct conversion for this field ?
declare @tbl table(actorPersonId int, personType int, actorType int, person int
, firstName varchar(100), surname varchar(100), phone varchar(100), email varchar(100)
, publication int, logUsr varchar(20), active bit, delDate datetime )
[Code] .....
View 5 Replies
View Related
Oct 15, 2015
I have the follwing function in my SQL data base which is hosted in Azure.
All date and time field in my db are store as UTC and I have a function define as below :
ALTER FUNCTION [dbo].[func_GetCurrentLocalTimeFromUTC]
(
)
RETURNS datetime
AS
BEGIN
DECLARE @OffSet as int = 1
DECLARE @CurrentDate as datetime = getUTCdate()
[Code] ...
Now what I have trying to do is to perform a select statement on a table where I have a dateTime value field and add the proper offset value to the field in order it is display based on where user will run the query. For example if I run this simple query :
SELECT INVENTORYDATE from Inventory
Will return the UTC dateTime value.
How can I use the select statement in order to get the field format with proper offset based on user pc timezone/. Please note that the query will be called from a rrs.
View 5 Replies
View Related
Jan 27, 2004
How would one implement an array in SQL Server ?
Specically, i'm parsing a field into the array, and doing operations on these elements.
Thanks.
View 4 Replies
View Related
Jul 10, 2014
Looking for returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.
Entry might look like:
(datetime) date: 7/10/2014
(int) start-time 820
(int) end-time 1000
(int) duration 20
The result would needed:
(datetime) 7/10/2014 08:20:00
(datetime) 7/10/2014 08:40:00
(datetime) 7/10/2014 09:00:00
(datetime) 7/10/2014 09:20:00
(datetime) 7/10/2014 09:40:00
View 8 Replies
View Related
Mar 3, 2015
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
For Eg:
Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L
View 9 Replies
View Related
Jun 22, 2015
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example:
PostCodeLatitudeLongitude
66000/6610042.6965952.899370
20251/2027042.1964719.404951
Would become
PostCodeLatitudeLongitude
6600042.6965952.899370
6610042.6965952.899370
2025142.1964719.404951
2027042.1964719.404951
how this can be done?
View 2 Replies
View Related
Aug 18, 2015
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018
The query I am using
select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
from Loan AS L
LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'
View 3 Replies
View Related
Jun 30, 2000
I heard from my friend that, in "Oracle 8i", they have Array concepts.
Is there any "ARRAY CONCEPTS" available in SQL Server 7.0?. I have seen in BOL, there they have given command like,
"SetToArray(Set[, Set]...[, Numeric Expression])"
Like 'C'-Language, can we able to store datas in that. If that so, can anyone give explanations on that.
thanks in advance.
Srinivasan
View 1 Replies
View Related
Jul 20, 2005
Hi All,I want declare an array of int in SQL Serverplease help me to convert the following code from VB to TSQL------------Dim md_mon(12) As Integermd_mon(1) = 31-------------Thanks
View 4 Replies
View Related
May 11, 2006
hello
I have created one store procedures that return a table variable
'CREATE PROCEDURE sptcondconsiglieri @immobile_id varchar(6)
as
DECLARE @tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @colA nvarchar(50)
DECLARE @MyCursor CURSOR
/*declare @mycursor1 cursor*/
SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@immobile_id
order by posizione
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColA
WHILE @@FETCH_STATUS = 0
BEGIN
Insert @tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @MyCursor
INTO @ColA
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
select * from @tbl
/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
Dim rs as new adodb.recordset
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you
View 5 Replies
View Related
Dec 11, 2013
How do I write a query using the split function for the following requirement.I have a table in the following way
Identity Name Col1 Col2 Col3
1 Test1 1,2,3 200,300,400 3,4,6
2 Test2 3,4,5 300,455,600 2,3,8
I want an output in the following format
Identity Name Col1 Col2 Col3
1 Test1 1 200 3
1 Test1 2 300 4
1 Test1 3 400 6
2 Test2 3 300 2
2 Test2 4 455 3
2 Test2 5 600 8
If you see the data, first element in col1 is matched to first element in col2 and 3 after splitting the string.
View 2 Replies
View Related
Jul 4, 2005
I would like to write a fun or stored procedure to do some operation. It require me to know that what category is currently belong to certain people(people_table: category_table1 to Many)However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.Question:1. Is there any way to hold the collection of result(like array)?2. Also, how to determine to use fun or stored procedure?(Since a integer is need to return by them)Thx
View 4 Replies
View Related
Jan 8, 2006
Hello, I am using .net 1.1 and sql server 2000.
I want to store a byte array, but it seems that only a string is stored there.
Code:
SHA512 sha = new SHA512Managed();
byte[] ReturnedPasswordByte = sha.ComputeHash(ToCryptByte);
MyGenericCommand.Parameters.Add("@Password", ReturnedPasswordByte);
MyConnection.Open();
MyGenericCommand.ExecuteNonQuery();
MyConnection.Close();
If I select the record later, all I get is "System.Byte[]" (as string)
View 1 Replies
View Related
Jun 11, 2008
My main sql server lost the system board due to overheating. I need to get the (external enclosure scsi attached)array up and running as it has production data as well as SQL backups stored there. My most recent/valid backups are located with the production data. It is possible to connect this array to different server? This is a HP DL380 and all of my other servers are Dell 2900's.
any help is appreciated!
View 1 Replies
View Related