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

Transact SQL :: Advanced Query With Windows Function?

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

Subtract To Count Columns?

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

How To Subtract 1 Hour From GetDate() Function

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

Transact SQL :: Function To Return Comma Delimited Values From Table Columns

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

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

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

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

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

Should I Use A Clr Function Or Write A Windows Service

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

Transact SQL :: Get Windows Environment Variables

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

Transact SQL :: Using Linked Server And Windows Authentication

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

Transact SQL :: Getting Delta Value With Lead Windows Functions?

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

Strange Situation With Windows Authentication And Undocumented Function GET_SID

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

How To Retrieve The Char(1) Column From SQL Server With Dbbind() Function In Windows C Programming?

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

Transact-SQL IsXML Function (wish)

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

A Transact SQL Function -urgent! -

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

Transact SQL :: Right Function With Delimiter

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

Text To Columns Function?

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

Scalar Function Columns

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

Passing Columns To CLR Function

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

Transact SQL :: Apply Partition Scheme To Existing Table In Query Windows

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

Transact SQL - Does It Have The Equivalent Of FINDONEOF Function?

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

Transact SQL :: Can Add 1 To Charindex Function But Can't Minus 1

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

Trying To Call The Function A Web Service From Transact-SQL

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

Diffrence Between Metadata Function And Transact-SQL

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

Transact SQL :: Summing With A Function In Grouping

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

Transact SQL :: Top Percent In A Window Function OVER

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

Transact SQL :: Using Aggregate Function With Subquery?

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

Transact SQL :: How To Use Divide Function In Query

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

Transact SQL :: Passing Parameters Through SP To A Function

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

Transact SQL :: Modify URL With Replace Function

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

Transact SQL :: Simple Count Function

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

Subtract

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

Need Help With MAX Function Using Two Tables With Common Columns

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







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