Transact SQL :: Subtract Two Columns With Windows Function
Dec 5, 2015
How can i subtract two columns:
I have table:
ID COL 1 COL 2
-------------------------
1 200.00 70.00
2 200.00 30.00
3 200.00 90.00
4 200.00 110.00Col1 - COL2
But to continue for each row and value is reduced by the previously value.
My output should be like as:
ID COL 1 COL 2 [COL3 AS RESULT]
-------------------------
1 200.00 70.00 130
2 200.00 30.00 100
3 200.00 90.00 10
4 200.00 110.00 -100
View 3 Replies
ADVERTISEMENT
Jun 16, 2015
SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date, @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:
CREATE TABLE MyTable
(
IDTable INT IDENTITY NOT NULL,
DateLog DATE NOT NULL,
CountLog INT NOT NULL
)
[code]....
In this case I need to apply a filter with the Dates:
@dateFrom ='2014-01-01',
@dateTo ='2014-04-28'
But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:
Id MonthYear Diff
1 Jan2014 5
2 Feb2014 3
3 March2014 0
4 April2014 5
With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way.
[URL]
View 16 Replies
View Related
Sep 8, 2013
select Doctor, count(monthyear)- count(paidinfull) as Patients
from tableA
where paidinfull = 'YES'
group by doctor
So I have a table like
Doctors Patients
------ --------
Burns 12
White 7
where Doctors is a group by from tableA and Patients is a result of the row count of paidinfull = yes minus the rowcount of monthyear.
View 5 Replies
View Related
Sep 15, 1999
Can any one tell me how to subtracts and add hours to the current date and time?
In my case I have to store the Vancouver date and time in Toronto.
Thanks,
T.Lingam
View 1 Replies
View Related
May 12, 2015
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
View 13 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
Jul 9, 2007
hi all.
please help with my dilemma.
this is my task:
i should write a service that will run every 1 hour.
this service should check a folder.
inside this folder there are some xml files, all of them in the same format exactly.
when the service encounters with this xml file, it should open it,pull some data out of it, and update a table in the db.
NOW - I HAVE 2 OPTIONS, AND THIS IS THE DILEMMA:
should i write a clr function to do this task, or should i just write a standard c# windows service, that will open a connection to the db when it should perform update?
what are the advatages?
View 3 Replies
View Related
Aug 31, 2015
I am migrating the BE of an Access app. to SQL server 2012. I need to get the user's login name (Windows Authentication login). This can be done using xp_cmdshell, but, xp_cmdshell is considered dangerous and I wouldn't be able to run it once I deploy the app. to the company servers (currently I have SQL server on my computer and as an admin I can enable xp_cmdshell to run, but IT doesn't allow it in company servers for security reasons).
Another question, is it possible to send data from the logged in user from Access to SQL server? What I need to do is let SQL know the username of the logged in user, then, use it to filter the data on SQL. Idea is that user can only run queries for his data (he can't view other user's data unless he is a manager or an admin (currently the app. in Access logs the user in automatically if his Windows Domain username is found in the user's table, and set's his role found in the Roles table). It is this functionality that is giving me some problems to migrate to SQL.
I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account username.
View 10 Replies
View Related
Sep 24, 2015
I have set up two SQL server (e.g. Server1 and Server2) which are set up to use Windows Authentication with my login (e.g. login1).On Server1 I have created a linked server to Server2, I have then selected the Security tab and added my local login and selected impersonate. I have then selected ‘Be Made using the login’s current security context’.I then receive the error message ‘The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?’
View 9 Replies
View Related
Jul 1, 2015
I have a table with the next structure:
DECLARE @MaxCountHistogram TABLE
(
MaxId INT IDENTITY PRIMARY KEY NOT NULL,
PublicationId INT NOT NULL,
ProviderId INT NOT NULL,
DateLog DATETIME NOT NULL,
Amount FLOAT NOT NULL
)
INSERT INTO @MaxCountHistogram
VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider and getting the diferential between two different months, for example:
Period Delta Amount Provider PublicationId
20150530 10.2564 3 432
20150630 3 Result of (13.2564- 10.2564 ) 3 432
View 4 Replies
View Related
Dec 21, 2004
SQL Server 2000
Windows 2000 Advanced Server
We recently moved our servers from one domain to another. Now, we can't grant Windows users access to any databases. We CAN create the user IDs successfully via Enterprise Manager, and get a corresponding row in MASTER..SYSXLOGINS, but can't click on the Database Access tab in the SQL Server Login Properties dialog and grant accesss to any databases. When we try to do so, we get error 15401, "Windows user or group xxx not found". The underlying call is to MASTER..SP_GRANTDBACCESS, and running that via Query Analyzer returns the same error (naturally)
Looking through the code of SP_GRANTDBACCESS, I've determined that what is failing is a call to the undocumented TSQL function GET_SID. This proc takes two parameters, the first is either G<nt group name> or U<nt user name> and the second is NULL in the call in SP_GRANTDBACCESS. If I execute
SELECT GET_SID('U<valid user>', NULL)
it returns NULL, however, if I run
SELECT SUSER_SID(<valid user>)
then I get the Windows SID of whatever valid user name I supply.
We have 3 servers in question, namely production, development and test. We noticed the problem on production. Curiously enough, development and test worked fine.
NOW THE PLOT THICKENS. If I run this query on the dev box....
SELECT SUSER_SID(<id>), GET_SID('U<id>',NULL)
...the first function returned the SID, BUT THE SECOND FUNCTION DID NOT!!! How could that be? Clearly GET_SID was working inside of SP_GRANTDBACCESS, but not as a discrete call. So I went into the master database and added code to print out the SID returned by GET_SID to the proc. Lo and behold, SP_GRANTDBACCESS promptly failed with a 15401 error. It continues to get 15401s now, even after I returned it to the original code. What gives? Now my dev box has the same error production has, and all I did was recompile SP_GRANTDBACCESS a couple of times. FWIW, I did *not* ever make any changes to SP_GRANTDBACCESS on production.
Why doesn't GET_SID() work outside of SP_GRANTDBACCESS?
Why did recompiling SP_GRANTDBACCESS break it permanently?
It almost seems like the query compiler can't correctly link a call to GET_SID to the correct function in some DLL, except I thought that the compiled code didn't survive a restart, and all TSQL procs were recompiled the first time they were called after a restart. If that's the case, then the compiler is - or at least was - producing a functioning compiled version of SP_GRANTDBACCESS after every restart.
View 3 Replies
View Related
May 10, 2007
I have used the following Windows C codes to retrieve records from the bus_newjob table in SQL server:
==========================================================
// construct command buffer to be sent to the SQL server
dbcmd( dbproc, ( char * )"select job_number, job_type," );
dbcmd( dbproc, ( char * )" disp_type, disp_status," );
dbcmd( dbproc, ( char * )" start_time, end_time," );
dbcmd( dbproc, ( char * )" pickup_point, destination," );
dbcmd( dbproc, ( char * )" veh_plate, remark," );
dbcmd( dbproc, ( char * )" customer, cust_contact_person," );
dbcmd( dbproc, ( char * )" cust_contact_number, cust_details" );
dbcmd( dbproc, ( char * )" from bus_newjob" );
dbcmd( dbproc, ( char * )" where disp_status = 0" );
dbcmd( dbproc, ( char * )" order by job_number asc" );
result_code = dbsqlexec( dbproc ); // send command buffer to SQL server
// now check the results from the SQL server
while( ( result_code = dbresults( dbproc ) ) != NO_MORE_RESULTS )
{
if( result_code == SUCCEED )
{
memset( ( char * )&disp_job, 0, sizeof( DISPATCH_NEWJOB ) );
dbbind( dbproc, 1, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_number );
dbbind( dbproc, 2, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_type );
dbbind( dbproc, 3, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_type );
dbbind( dbproc, 4, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_status );
dbbind( dbproc, 5, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.start_time );
dbbind( dbproc, 6, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.end_time );
dbbind( dbproc, 7, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.pickup_point );
dbbind( dbproc, 8, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.destination );
dbbind( dbproc, 9, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.veh_plate );
dbbind( dbproc, 10, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.remark );
dbbind( dbproc, 11, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.customer );
dbbind( dbproc, 12, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_person );
dbbind( dbproc, 13, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_number );
dbbind( dbproc, 14, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_details );
// now process the rows
while( dbnextrow( dbproc ) != NO_MORE_ROWS )
{
new_job = malloc( sizeof( DISPATCH_NEWJOB ) );
if( !new_job )
return( 0 );
memcpy( ( char * )new_job, ( char * )&disp_job, sizeof( DISPATCH_NEWJOB ) );
append_to_list( &Read_Job_List, new_job );
}
}
else
{
sprintf( str, "Results Failed, result_code = %d", result_code );
log_str( str );
break;
}
==========================================================
where the job_type columIn is of the char(1) type, NTBSTRINGBIND is the vartype argument in the dbbind() function.
However, what I have gotten is nothing more than a null string from the job_type column. I have alternatively changed the vartype argument to STRINGBIND, CHARBIND and even INTBIND, but the results are the same.
Who can tell me the tricks to retrieve a char(1) column from SQL server?
View 1 Replies
View Related
Oct 4, 2007
Hello All,
I need to determine that a piece of XML in a character field or variable is well-formed XML. I need to store this validation result (valid or not valid flag) in the table.
Does SQL provide some function for this? Is there any other way?
Thank you!
David
View 2 Replies
View Related
Mar 22, 2002
HI there,
Is there a function on transact SQL which test if a character belong to expression,I want to select from a table only the address that contain '@'.
Thanks in advance
Jalal
View 1 Replies
View Related
Nov 2, 2015
The input is like this : xxxxxxxx=yyyyy=key_id=12345xyxyx
I would like to use the right function and get 12345xyxyx alone. The function must search for the first '=' symbol from the right of the word and get the characters from the end till the '=' symbol.
View 2 Replies
View Related
Sep 11, 2013
I have the following Table:
ORDER_ID SKUS
12345678 SCINC, SCNRQ, SRPPR
All the values in the 'SKUs' column are combined together, seperated by a comma.
Is there an easy to way "de-pivot" this so that the Order ID gets repeated as follows:
ORDER_ID SKU
12345678 SCINC
12345678 SCNRQ
12345678 SRPPR
Here's some code:
SELECT ORDER_ID, SKU FROM ORDERS
View 4 Replies
View Related
Jul 20, 2005
Is it ill-advised to have columns whose values pull from scalar functionsusing other fields in the record as parameters? For example, if I havecreate table a(iID int primary key)create table b(iID int ,iDetail int,CONSTRAINT PK PRIMARY KEY(iID,iDetail),CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID))Let's say in table b I put price information for each detail and in table aI'd like to put a column that sums these prices for the children of eachrecord. Should I make a computed column that references a function usingiID as a parameter? Or would it be better to create a view for this kind ofpurpose?Regards,Tyler
View 4 Replies
View Related
Jul 31, 2007
Hello,
I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like:
Code Snippet
public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z)
{
//Do Something...
x = ....
y=...
z=...
}
I want to call this code from SQL code:
Code Snippet
create table #Temp (float a,float b)
declare @x float
declare @y float
declare @z float
exec dbo.DoSomething(a,b,@x,@y,@z) ---????
Do someone have an idea?
View 1 Replies
View Related
Jun 9, 2015
I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)
BEGIN TRANSACTION
CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
(
[ID]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID])
DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
COMMIT TRANSACTION
View 2 Replies
View Related
Oct 14, 2005
Hi All,
Once again I find myself smacking my head into a wall (and it will probably be for naught). I'm trying to find a specific character in a string within a specific record. I've tried substring, but it's not quite getting me where I want to be. I even tried "nesting" the substring and got nowhere (but I could have had the syntax wrong when I nested it).
Here is a sample
Select substring(productimagefile, 20, 5)
from my table
What I need is something like this:
Select substring(findoneof(productimagefile, _r, 1), 1, 5)
from my table
Actually, on the start of the substring, I need it to be where it finds the _r, just not sure how to tell it that.
The replace function is the closest I've seen to what I need, but I don't want to replace it with anything, I just need to find it in the string.
So, if anyone has any ideas, I'd be grateful (and my head would stop hurting....)
Schimelcat
View 1 Replies
View Related
Aug 25, 2015
The charindex can run this
LEFT([Description], CHARINDEX('(', [Description]) + 1) as NewDesc,
But I can't run
LEFT([Description], CHARINDEX('(', [Description]) - 1) as NewDesc,
View 5 Replies
View Related
Jun 2, 2006
I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error.
I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too?
Thanks for any help.
ALTER PROCEDURE [dbo].[pTAPServiceWeb]
@sUrl varchar(200),
@response varchar(8000) out
AS
DECLARE @obj int
DECLARE @hr int
DECLARE @status int
DECLARE @msg varchar(255)
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
IF @hr < 0
BEGIN
RAISERROR('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1)
RETURN
END
EXEC @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod Open failed'
GOTO err
END
EXEC @hr = sp_OAMethod @obj, 'send'
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod Send failed'
GOTO err
END
EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod read status failed'
GOTO err
END
-- IF @status <> 200
-- BEGIN
-- SET @msg = 'sp_OAMethod http status ' + str(@status)
-- GOTO err
-- END
EXEC @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
IF @hr < 0
BEGIN
SET @msg = 'sp_OAMethod read response failed'
GOTO err
END
EXEC @hr = sp_OADestroy @obj
RETURN
err:
EXEC @hr = sp_OADestroy @obj
RAISERROR(@msg, 16, 1)
RETURN
GO
View 3 Replies
View Related
May 3, 2006
Dear all,
I want to know difference between Metadata function and Transact-SQL. When to use metadata function and when Transact-SQL like
To know database exists in SQL Server we have two options
through Transact-SQL
if not exists(select * from sysdatabases where name = 'Testing')
through Metadata function
if db_id('Testing') is not null
When should I use Transact-SQL and when metadata function.
Thanks,
Ashok
View 1 Replies
View Related
Jun 23, 2015
I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).
declare
@tblDurations
TABLE
(StateName
nvarchar(30),Duration
nvarchar(30))
[code]...
I want the output to have sum of the duration ( from my function ), grouped by the state.So output should be :
Breaks 00:02:03:40
Meetings 00:00:01:50
Running 15:21:07:16
I think this can be done with windows functions, but how I don't know.
View 10 Replies
View Related
Oct 11, 2015
I want to create a subset of a geography table. My intention is to select only a few cities with a where and after that select a percent of village for each of the cities. My query look like this:
SELECT TOP 160 [CodMunicipio] ,[NombreMunicipio] ,[CodProv],[Provincia] ,[COMUNIDAD AUTÓNOMA]
,[Longitud ETRS89/REGCAN95],[Latitud ETRS89/REGCAN95]
,[XUTM ETRS89/REGCAN95],[YUTM ETRS89/REGCAN95],[Bandera]
--INTO [PracticasSCD].DBO.DIMGEOGRAFIA
FROM [SportFunDW].[dbo].[DimGeografia2] TABLESAMPLE (3 PERCENT)
[Code] ....
I have tried with tablesample but it doesn´t return what I expect because I have many villages from one city (for example 15 % of it) and only 2 % of other. How can I achieve my goal?
View 5 Replies
View Related
Aug 6, 2015
SSMS does not like mine! THis is the error that I receive:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
And this is my syntax:
Select
employeeID
,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) +
COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End)
FROM production
GROUP BY employeeID
ORDER BY employeeID
View 6 Replies
View Related
Jun 9, 2015
When I am doing the divide all values are showing Zero.
DECLARE @test_sample TABLE (ClientID VARCHAR(5), FiscalYear varchar(4), QtrNumerator int, QtrrDenominator int)
INSERT INTO @test_sample VALUES
('ABC','2014',0,100),
('ABC','2015', 10,40),
('CDE','2013',14,0),
('CDE','2012',20,50)
select QtrNumerator/nullIf(QtrrDenominator,0) as QTR from @test_sample
and also I want to show the QTR with %, for example, 66.57%
View 6 Replies
View Related
Sep 23, 2015
I have an SP that is working, it creates a Union between 3 tables then creates a single table - I am using Visual studio Grid Control to view this by a browser, its working. at the bottom of the SP is a line of code that calls a function, the function queries a View. When I run the SP, SQL returns the data from the Union plus the data from the select statement on the Function. This all works but I can't get the data returned by the function query onto a browser.
I am doing it this was as the parameters in my SP are also used when calling the function, works really well. Is GTotal a return value?
Here is my function:
USE [LOGONs]
GO
/****** Object: UserDefinedFunction [dbo].[FX_AnnaulBudget1] Script Date: 23/09/2015 12:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
...and here is my SP:
USE [LOGONs]
GO
/****** Object: StoredProcedure [dbo].[USP_AnnualBudget] Script Date: 23/09/2015 12:57:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_AnnualBudget]
[Code] .....
View 2 Replies
View Related
Jul 1, 2015
Update query to modify a Url in the text column with another url
Ex:
Col1 Col2
1 An unexpected event occurred
https://abc.def.com/default/_workitem/10325
3 This alert occurs when service jobs run on
https://abc.def.com/default/_workitem/10118
10 This alert fired to indicated that error with
https://abc.def.com/default/_workitem/10150
to
Col1 Col2
1 An unexpected event occurred
https://abc.def.com/default/_workitem/11111
3 This alert occurs when service jobs run on
https://abc.def.com/default/_workitem/11111
10 This alert fired to indicated that error with
https://abc.def.com/default/_workitem/11111
View 3 Replies
View Related
Apr 25, 2015
I have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate
from tblCsEventReminders
where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
View 3 Replies
View Related
Feb 5, 2007
Hi guys
I have a column with datas as
"0.0"
"123.0"
"45.0"
i have a query were i have to match the value only. i don't want the decimal and followed by the any thing.
or
how do i extract just the value not the decimal part so that i can compare it with other value.
how do i do that.
thanks
View 10 Replies
View Related
Apr 9, 2006
I have two tables that contain product SKUs (12-character strings):
Table 1Product IdSKU...
Table 2ProductVariantIdSKU...
I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers.
Thanks!
Duncan
View 4 Replies
View Related