Creating FUNCTION Append Rows At The End
Feb 1, 2008
SQL Server 2000 Standard SP4 Build 2187.
When we are creating the function below the compilation process append one row at the end of the function text:
Code Snippet
ALTER FUNCTION [dbo].[FX_LOJA_REPOSICAO] (
@sParametros varchar(8000),
@sFiltroFiliais varchar(8000),
@sFiltroProdutos varchar(8000)
)
RETURNS @RESULT TABLE (
FILIAL VARCHAR(25) COLLATE DATABASE_DEFAULT NOT NULL,
PRODUTO CHAR(12) COLLATE DATABASE_DEFAULT NOT NULL,
COR_PRODUTO CHAR(10) COLLATE DATABASE_DEFAULT NOT NULL,
COD_TIPO SMALLINT NOT NULL,
DESC_TIPO VARCHAR(40) COLLATE DATABASE_DEFAULT,
QTDE AS (T1+T2+T3+T4+T5+T6+T7+T8+T9+T10+T11+T12+T13+T14+T15+T16+T17+T18+T19+T20+T21+T22+T23+T24+
T25+T26+T27+T28+T29+T30+T31+T32+T33+T34+T35+T36+T37+T38+T39+T40+T41+T42+T43+T44+T45+T46+T47+T48),
T1 SMALLINT, T2 SMALLINT, T3 SMALLINT, T4 SMALLINT, T5 SMALLINT, T6 SMALLINT,
T7 SMALLINT, T8 SMALLINT, T9 SMALLINT, T10 SMALLINT,T11 SMALLINT,T12 SMALLINT,
T13 SMALLINT,T14 SMALLINT,T15 SMALLINT,T16 SMALLINT,T17 SMALLINT,T18 SMALLINT,
T19 SMALLINT,T20 SMALLINT,T21 SMALLINT,T22 SMALLINT,T23 SMALLINT,T24 SMALLINT,
T25 SMALLINT,T26 SMALLINT,T27 SMALLINT,T28 SMALLINT,T29 SMALLINT,T30 SMALLINT,
T31 SMALLINT,T32 SMALLINT,T33 SMALLINT,T34 SMALLINT,T35 SMALLINT,T36 SMALLINT,
T37 SMALLINT,T38 SMALLINT,T39 SMALLINT,T40 SMALLINT,T41 SMALLINT,T42 SMALLINT,
T43 SMALLINT,T44 SMALLINT,T45 SMALLINT,T46 SMALLINT,T47 SMALLINT,T48 SMALLINT,
FILIAL_ORIGEM VARCHAR(25) COLLATE DATABASE_DEFAULT,
FILIAL_DESTINO VARCHAR(25) COLLATE DATABASE_DEFAULT,
ORIGEM_DESTINO VARCHAR(25) COLLATE DATABASE_DEFAULT NOT NULL,
PACK VARCHAR(20) COLLATE DATABASE_DEFAULT,
PRIMARY KEY NONCLUSTERED (FILIAL, PRODUTO, COR_PRODUTO, COD_TIPO, ORIGEM_DESTINO) )
AS
...FUNCTION BODY...
RETURN
END
SP_HELPTEXT returns the function text with the row bellow appended:
Code Snippet
...FUNCTION BODY...
RETURN
END
(T1+T2+T3+T4+T5+T6+T7+T8+T9+T10+T11+T12+T13+T14+T15+T16+T17+T18+T19+T20+T21+T22+T23+T24+
T25+T26+T27+T28+T29+T30+T31+T32+T33+T34+T35+T36+T37+T38+T39+T40+T41+T42+T43+T44+T45+T46+T47+T48)
Why this happening....
Any suggestions?
View 1 Replies
ADVERTISEMENT
May 21, 2004
i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks.
Thank you,
Thomas
insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms)
SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#,
dbo.tblCustomersIOA.Active,
iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) ,
dbo.tblCustomersIOA.CreditLimit,
FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN
dbo.tblCustomersIOA LEFT OUTER JOIN
WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);
View 1 Replies
View Related
Oct 8, 2007
how to append rows of 2 tables. Each table consist only 1 row.
sample:
table1
f1 f2 f3
table2
g1 g2 g3
my expected result is:
f1 f2 f3 g1 g2 g3
Note: tables have no relation to each other...
thanks
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
View 2 Replies
View Related
Mar 23, 2007
hello sir,
as u explained very well transfered data through Import& Export Wizard for creating destination table but sir i want append rows in the
destination table example. how should i append data in the destination table while copying or transfering data in the exsisting destination table
is there any specfic type of table structure or any setting is require for that if sir yes pls. let me know by my above email add.
as delete rows in the destination table option working properly in IMPORT & EXPORT Wizard only problem in append rows in the destination
table every time i am transfereing data or copying its showing double of exsisting rows its not appending exsising rows & as i want each time
the already exsist rows data should append & insert only new row below that exsisting one .
i hope sir u got my query if u have answer, sir pls revert back with soluntion on my above email add.
thanks & regards
Tamanna Shaikh
View 1 Replies
View Related
Aug 15, 2007
Hi,
Im creatting an SSIS project that uses an Data Flow OLE DB Source to read data from an SQL Table and import it into a Destination table using Data Flow OLE DB Destination. but now everytime I run the project it appends all the rows not the new data rows only. How can I make the application so that it appends only the new data from a source table to a destination table. Is there maybe another Data Flow Control that can copy source table to destination and the next time it runs it only copy new rows. or any other way to do this using SSIS.
Your assistance will be highly appreciated.
View 4 Replies
View Related
Apr 26, 2007
Hi guys,
I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.
I hope you guys can help me with this.
Hoping to hear from you soon.
Thank you so much.
Kind regards,
Neil
View 7 Replies
View Related
Nov 4, 2015
I'm trying to append rows horizontally - I'm using the "xml path" approach
SELECT
E.[USER_NAME] As 'User Name',
(
SELECT ',' + C.[PERMISSION_NAME]
FOR XML PATH('')
) As [Associated Groups]
FROM TABLEA As A
JOIN TABLEB AS B ON A.PK_OBJ_ID = B.FK_APP_OBJECT_REF
JOIN TABLEC AS C ON C.PK_PERMISSION_ID = B.FK_PERMISSION_REF
JOIN TABLED AS D ON D.FK_PERMISSION_REF = C.PK_PERMISSION_ID
JOIN TABLEE AS E ON E.PK_PERSONNEL_ID = D.FK_PERSONNEL_REF
WHERE A.[OBJECT_NAME] = 'MyObjectName'
It's not working. I'm getting:
User nameAssociated Groups
A. SmithG1
A. SmithG2
A. SmithG3
etc...
What I'm looking for is:
User NameAssociated Groups
A. SmithG1, G2, G3
etc...
View 3 Replies
View Related
Jan 9, 2005
I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.
I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.
If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.
Any thoughts or suggestions are greatly appriciated in advance
Thanks again in advance
RB
View 1 Replies
View Related
Nov 21, 2006
Help,
I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
View 1 Replies
View Related
Apr 3, 2014
I want to loop through rows and append values to a declared variable. The example below returns nothing from Print @output, it's as if my @output variable is being reset on every iteration.
declare @i int,@output varchar(max)
set @i = 1
while @i < 10
begin
set @output = @output + convert(varchar(max),@i) + ','
print @output
set @i = @i +1
end
View 6 Replies
View Related
Apr 3, 2007
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
View 3 Replies
View Related
Jan 17, 2007
Am abit new to SQL Server Programming and I have an issue which I need Assistance.
I have a table with the following Colunms
Inv_Number
ItemCode1
ItemCode2
ItemCode3
ItemCode4
ItemCode5
this table as data which i want to put to another table with the following Columns
Invoice_Number
ItemCode
Amount
What i mean here is that Invoice Items were static and were about five and the new programs am designing will have more item codes.
View 4 Replies
View Related
Mar 19, 2007
Create a Function call InitCap
accepts 1 parameter of type varchar
returns a varchar
It should only capitalize the first letter of the string passed to it.
Items to watch out for.
a-z not starting the string
The 1st letter is already capitalized
This problems deals with ASCII characters, just wanted to get some insight here.
View 3 Replies
View Related
Nov 28, 2007
Hi im trying to create a function called SQLExecute that takes an SQL query, executes it and returns the resultant dataset in dsetResponse and if an error in strError, however i am unsure if whether im on the right track or not, where would i put the sql query and what else needs to be done, my code is as follows;public static DataSet SQLExecute(string strSQL, string strError)
{
DataSet dsetResponse = new DataSet();
try
{using (SqlConnection conn = new SqlConnection(DHOC.clsDHOC.GetConnString()))
{SqlCommand cmd = new SqlCommand();cmd.CommandType = CommandType.Text;
}
}catch (ThreadAbortException thEx)
{throw;
}catch (Exception ex)
{
string strError
}return dsetResponse;
}
View 3 Replies
View Related
Sep 6, 2005
Hi,
When I try to create a CLR function in SQL 2005 (June CTP) I get the following error:
Msg 6505, Level 16, State 1, Procedure Extenso, Line 1Could not find Type 'Extenso' in assembly 'ExtensoNET'.
The assembly registers successfully, with no errors.
I use the following command to create the function:CREATE FUNCTION Extenso (@Valor float)RETURNS VARCHAR(255)AS EXTERNAL NAME ExtensoNET.Extenso.EscreveExtensoGO
The function's code is the following:
using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlClient;
namespace ExtensoNET{ public class Extenso { [SqlFunction(DataAccess = DataAccessKind.Read)] public static string EscreveExtenso(double? nValor) {
//Valida Argumento if (nValor==null || nValor <= 0 || nValor > 999999999.99) return "";
//Variáveis int nTamanho; string cValor, cParte, cFinal; string[] aGrupo = { "", "", "", "", "" }; string[] aTexto = { "", "", "", "", "" }; . . . }
return cFinal;
} }}
Thanks in advance,
Anderson
View 1 Replies
View Related
Feb 17, 2008
Alright, I'll write this again. The first try didn't go so well. I am learning SQL Server from an online tutorial. Maybe there are a lot who do that, maybe not, but it's a good tutorial. There are projects to work on at the end of each section, and they involve more than just regurgitating everything I just read. They even make me figure out stuff that wasn't covered in the section (which is probably a mistake, but it makes the learning more interesting and I like it). The problem is that I've hit one of those places and I can't figure it out. The last project for this section is to write a function that calculates and displays the diameter of the base, the circumference of the base, the base area, the side area, the total area, and the volume, given the radius and the height of a cylinder. Now I had to do the same thing as a query for the previous section, and hunting down all the formulae involved was more time-consuming than writing the thing, but I'm stumped here. Every example and work-along project was, for lack of a better description, "one function, one result". So how do I get a function to return multiple results? If I've posted this in the wrong place, someone please let me know where I might find the information I need. For those of you who may see this and be stunned that I haven't figured out the obvious, remember you were new once too. Thanks in advance.
Now that I've seen some of the other posts, maybe I should clarify. Here's what I had from the last project.
declare @Radius float(10),
@BaseDiameter float(10),
@BaseCircumference float(10),
@BaseArea float(10),
@Height float(10),
@SurfaceArea float(10),
@TotalArea float(10),
@Volume float(10)
set @Radius = 19.1
set @BaseDiameter = @Radius * 2
set @BaseCircumference = @BaseDiameter * PI()
set @BaseArea = power(@Radius,2) * PI()
set @Height = 16.27
set @SurfaceArea = 2 * (pi() * @Radius * @Height)
set @TotalArea = 2 * (pi() * @Radius * @Height) + 2 * (power(@Radius,2) * PI())
set @Volume = (power(@Radius,2) * pi()) * @Height
select @Radius as [Base Radius],
@BaseDiameter as [Base Diameter],
@BaseArea as [Base Area],
@Height as Height,
@SurfaceArea as [Side Area],
@TotalArea as [Total Area],
@Volume as Volume
go
So how do I make a function that does the same thing?
Schiz
View 3 Replies
View Related
Jul 25, 2005
When I declare a cursor,I use a variable to replace the sql statement:DECLARE rs CURSOR LOCAL FAST_FORWARD FOR@sqlPlanBut it is not true.Who can correct for me.Another question is :How to execute a sql statement state by a variable "@sqlPlan" andinsert the result to a table "@FeatRequestStatus"?I am a new hand of sql programming.Thank you very much for your help
View 2 Replies
View Related
Aug 6, 2007
I had succesfully created a CLR function which was fully operational on the development server. Today the powers that be decided to do a full restore from the Production server because the wanted the most recent data on the server. Since our code has not been deployed to production I went through the exercise of redeploying everything to the server and all goes well until I try to create the function:
Code Snippet
CREATE FUNCTION f_GetGroupMembership(@providerKey nvarchar(200), @connection nvarchar(1000))
RETURNS TABLE (groupID uniqueidentifier, groupName nvarchar(100), groupType nvarchar(25))
AS
EXTERNAL NAME NavigatorSecurity.groups.GetGroupsForUser
This returns an error:
Msg 10305, Level 16, State 1, Procedure f_GetGroupMembership, Line 1
The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.
Now remember this CLR was working correctly previous to the restore from backup. But just in case I got a case of dumb*** I checked my clr code anyhow here it is:
Code Snippet
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.DirectoryServices;
using System.Collections;
public class GroupMembership
{
[SqlFunctionAttribute(FillRowMethodName = "FillGroupRow")]
public static IEnumerable GetGroupsForUser(String ProviderKey, String Connection)
{
return groups.GetGroupsForUser(ProviderKey, Connection);
}
public static void FillGroupRow(Object obj, out SqlGuid guid, out SqlString name, out SqlString type)
{
//cast the enumerator passed in
group grp = (group)obj;
//create output parameters
guid = new SqlGuid(grp.guid);
name = new SqlString(grp.Name);
type = new SqlString(grp.applicationData);
}
}
Can anyone tell me why this is happening? How can I overcome this problem?
Help will be highly appreciated.
View 5 Replies
View Related
Jan 5, 2007
I have compiled a dll with the following code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Public Class SICTrans
Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String
If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)
If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))
tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)
If Val(Right(TempSIC, 1)) > 0 Then
tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If
NewSIC = tempFSIC
TransSIC = NewSIC
End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty
End Function
End Class
Which compiles fine...
i then use the following code to create the assembly in SQL which is fine:
USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO
but when i goto create the function with the following code it wont have it!
CREATE FUNCTION TransSICCode(@inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO
It gives me the following error message
Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.
Any ideas??????????
Thanks
Marek Kluczynski
View 6 Replies
View Related
Apr 1, 2008
I am writing a SQLServer script that I want to be schema name independent. I mean I know that all users of the script will have the same tables, but not necessarily the same schema name.
When I hard code the script to use the name of my schema, wcadmin, it works OK.
CREATE FUNCTION wcadmin.dectohex(@a numeric)
RETURNS varchar(8)
BEGIN
DECLARE @x varchar(8)
DECLARE @y varchar(1)
DECLARE @z numeric
DECLARE @w numeric
SET @w=@a
SET @x=''
WHILE @w > 0
BEGIN
SET @z = @w % 16;
SET @y= CASE @z
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE CAST(@z AS varchar)
END
SET @w = ROUND(@w/16,0,1)
SET @x = @y + @x
END
-- Pads the number with 0s on the left
SET @x = RIGHT(REPLICATE('0',8) + @x ,8)
RETURN @x
END;
GO
select 'WTDOCUMENT' HOLDER,
dm.WTDocumentNumber ITEMNUMBER,
dm.name ITEMNAME,
ad.fileName ContentFilename,
fh.hostName VaultHost,
wcadmin.dectohex(fi.uniqueSequenceNumber) VaultFile,
fm.path VaultPath
from
WTDocument di,
WTDocumentMaster dm,
HolderToContent hc,
ApplicationData ad,
FvItem fi,
FvFolder ff,
FvMount fm,
FvHost fh
where di.idA3masterReference = dm.idA2A2
and fm.idA3A5 = ff.idA2A2
and fm.idA3B5 = fh.idA2A2
and fi.idA3A4 = ff.idA2A2
and ad.idA3A5 = fi.idA2A2
and hc.idA3B5 = ad.idA2A2
and hc.idA3A5 = di.idA2A2
DROP FUNCTION wcadmin.dectohex;
GO
But when I remove my schema name I get the error
'dectohex' is not a recognized built-in function name.
In this case I'm just using :-
CREATE FUNCTION dectohex(@a numeric)
.
.
.
.
select 'WTDOCUMENT' HOLDER,
dm.WTDocumentNumber ITEMNUMBER,
dm.name ITEMNAME,
ad.fileName ContentFilename,
fh.hostName VaultHost,
dectohex(fi.uniqueSequenceNumber) VaultFile,
.
.
.
.
DROP FUNCTION dectohex;
Creating and dropping the function seems to work OK when I drop the schema name, I just can't call it.
I've been trying various permutations of dbo and [dbo] prefixes unsuccessfully.
Any suggestions?
Thanks
David
View 5 Replies
View Related
Jun 7, 2008
i have a function
Create Function ReturnAmountB(@CMID int) Returns Decimal
as
BEGIN
declare @Return decimal
select @Return =sum(PD_PaymentAmount) from Payment_Details where
PD_IsRefund=1 and PD_PaymentType=0 and PD_CMID=@CMID
return @Return
END
when i run this on server(remote) this error shows...
Incorrect syntex near 'Function'
Must declare the variable '@CMID'
A RETURN statement with a return value cannot be used in this context
i am not able to understand why this error shows...
Any solution!!!
spandey
View 6 Replies
View Related
Jul 17, 2006
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/
View 1 Replies
View Related
Mar 18, 2008
hi to all,
i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void)
i am creating sql function using SQL script
CREATE FUNCTION dbo.Amex ()
RETURNS NULL
AS CALLER
WITH EXECUTE
AS
EXTERNAL
NAME [AMEX].[UserDefinedFunctions].[Function1];
GO
but i am getting error.
there is something wrong in my sql syntax..can anybody help me?
when creating funtion having signature
public static void Function1()
i am getting error.......................
Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
can anybody help me?
thanks in advance
Chetan S. Raut
View 4 Replies
View Related
Aug 6, 2004
Hi everyone, I'm migrating some information for a client at the moment. They had everything in Excel files and I'm getting them into SQL Server. There are some differences in the way I am storing data and the way they were storing data.
For each client they stored, they had something like
Rel1 Rel2 Rel3
100 101 102
Now, what I have is a seperate row for each of Rel1, Rel2 and Rel3 so I would have 3 seperate rows with identical information except for Rel1. So I would have:
Rel1
100
101
102
So one way I thought of doing it was inserting a new row specifying that the value for Rel2 should be stored in Rel1 and for the next row that the value for Rel3 should be stored in Rel1.
Now, I am able to do this but SQL Server inserts an extra row will the NULL value in Rel1. Does anyone know why this would be happening? I think what it is doing is finding a NULL value in Rel3 after creating the two extra rows and is inserting that NULL. So I think I need to check for NULLs and not allow it to create a new row if, say, Rel3 is NULL.
Any pointers are gladly welcome. (I know it's complicated )
View 1 Replies
View Related
Feb 9, 2015
I have following 4 rows in a table with field name as ID
ID
--
AA
BB
CC
DD
I require output as
ID NewColumn
-- ----------
AA AA
BB AA~BB
CC AA~BB~CC
DD AA~BB~CC~DD
View 3 Replies
View Related
Jun 15, 2006
I am ultra new to this so thanks in advance for any help.
I was trying to create a connection to a database that I created in SQL Express. I am essentially trying to submit three attributes to the existing database from a table that consists of three textboxes and a submit button. I would like all of the code to be in the head of the page (because that is the standard here) so I wanted to know what the connection string should be in Visual Basic 2005 Express to establish a connection on the same machine. I'm not sure about the connection string, but I am also not sure about a lot of the code. Also, the Using clauses seem to give me an error (where should it go?). This is what I have in the head of the page (visual C# by the way). Also, I got this from http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx :
Using System.Data;Using System.Data.SqlClient;protected void Button1_Click(object sender, EventArgs e){SqlConnection conn = new SqlConnection("Data Source=(local);Database=CustomersDB;Trusted Connection=true");conn.Open();string insertstring = @" insert into Catagories (CustomerID, CustomerName, CustomerEmail) values (" + textbox1.value + ", " + textbox2.value + " + ", textbox3.value");SqlCommand cmd = new SqlCommand(insertstring, conn);Sql.ExecuteNonQuery();conn.close();}
If there is absolutely any insite into the problems with my code, I really appreciate it.
View 12 Replies
View Related
Sep 8, 2004
I maintain a simple employment (job) tracking web application.
Jobs can be set to 5 different statuses: Open, Closed, Filled, Pending, or Cancelled.
There is a table in the database called statusLog, which records everytime a job is set to opened, or set to closed, etc. It records the job number, the date it was changed, and what the job was changed to.
Here is a short example of what a few entries might look like:
Status Date Job number
Open10/6/2002 2:34:56 PMTEST2845
Hold10/11/2002 12:19:29 PMTEST2845
Closed10/29/2002 2:00:54 PMTEST2845
Pending10/25/2002 3:37:06 PMTEST2877
What I need to do is write an SQL query that will return to me all entries in this table, between two certain dates, that ONLY have entries during those dates.
Basically I need to know how many "new" jobs were set to open during a month. I can easily just do a count of how many jobs were set to open, but this will not give a count of "new" jobs.
Example: during june a job could be set to open. Then in july it could be set to pending. Then in august, it could be re-opened, and set to open.
If I ran this query for the month of August, it would return that job as being opened in august. But it wasn't a new job, meaning it had already been in the system in previous months.
Is there some way I can select all "Open" jobs, between a certain date, that do not exist anywhere in the table previous to the date it was entered? This would give me a result set containing only new jobs.
The only way I've thought of yet is to get a result set of all jobs simply set to open during a month, then one by one for each record, go back and run another SQL query to see if it exists in the table anywhere other than in that month.
This seems horribly inefficient to me however, as I do not want to be doing 34,000 independent SQL calls for every single "open" job it finds during a certain month.
thanks
View 2 Replies
View Related
Mar 20, 2015
We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)
Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1
1 0 0 2
0 0 0 2
0 0 1 2
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
Can I do this in SQL ?...some kind of grouping ?
View 2 Replies
View Related
Nov 16, 2006
Hi,
I have a table where I want to use the sum function, but only on the rows with a positive value.
Is there any way to do this?
View 5 Replies
View Related
Jul 16, 2015
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue
AS
(
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
return @ConcatenatedString
)
View 3 Replies
View Related
Aug 20, 2006
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
View 7 Replies
View Related
Jan 24, 2008
I have to write a couple scripts that will update a couple columns in two separate tables and also insert a new row with the same data except for a few calculated or provided values ...... see specs below ...
1. tGradeHist Table Script One (Needs to be run first)
a. Read tGradeHist Table and Select rows with GradeEndDate = NULL and GradeStartDate = '1/1/2007 12:00:00 A.M.'
b. Calculate New Step Amount = StepAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
GradeCode - Same
GradeLocationCode - Same
Step - Same
GradeStartDate - '7/1/2007 12:00:00 A.M.'
GradeEndDate - NULL
GradeCurrencyCode - Same
StepAmount - Result of b (above)
GradeFrequencyCode - Same
RangeMaximumAmount - Same
RangeMidAmount - Same
RangeMinimumAmount - Same
GradeCurrentFlag - 'True'
MarketMaximumAmount - Same
MarketMidAmount - Same
MarketMinimumAmount - Same
GradeGUID - Same
TSCOL - Same
d. Update Row read in a (above) with GradeEndDate = '6/30/2007 12:00:00 A.M.' and GradeCurrentFlag = 'False'
2. tPersonBasePayHist Table Script Two (Needs to be run second)
a. Read tPersonBasePayHist Table and Select rows with PersonBasePayEndDate = NULL
b. Calculate New PersonBasePayAmount = PersonBasePayAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
PersonGUID - Same
PersonBasePayStartDate - '7/1/2007 12:00:00 A.M.'
PersonBasePayEndDate - NULL
PersonBasePayCurrencyCode - Same
PersonBasePayAmount - Result of b (above)
PersonBasePayFrequency - Same
PersonBasePayPayrollFrequencyCode - Same
BasePayReasonCode - 'SA'
ConductedBasePayReviewDate - Same
ScheduledBasePayReviewDate - Same
PayrollCode - Same
PersonBasePayCurrentFlag - 'True'
ApprovedByPersonGUID - Same
PersonBasePayGUID - Same
TSCol - Same
d. Update Row read in a (above) with PersonBasePayEndDate = '6/30/2007 12:00:00 A.M.' and PersonBasePayCurrentFlag = 'False'
View 7 Replies
View Related
Nov 14, 2007
hi can anyone please help me
i have a stored procedure ,can i put the value of this into a variable......????
I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ...
example if my stored procedure is
[dbo].[GetPortfolioName](@NT_Account varchar(100) )
and ans of this are
NL
UK
IN
GN
JK
then how can i put this into a variable like
Declare @PortfolioName as varchar(250)
set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) )
.......
and my ans is
@PortfolioName = 'NL','UK','IN','GN','JK'
now can i make a function which returns 'NL','UK','IN','GN','JK'
View 1 Replies
View Related
