Subquery Max Disconnect Date Within Table
Sep 12, 2014
I've written a query calling for the max disconnect date within a table, but I would also like to make sure to only retrieve records with the most recent connect date. I'm unsure how to go about adding the additional max date function to my query. Here's what I have written:
select distinct T.PRIN_MON, T.AGNT_MON, T.SUB_ACCT_NO_MON, T.TRAN_DTE_MON, T.TRAN_CDE_MON,
T.TRAN_AMT_MON, EQ.eqp_stat_eqp,
case when h.acct_stage_ohi = 'v' then 'VOL_DISCO'
when h.ACCT_STAGE_OHI = 'n' then 'NON_PAY_DISCO'
ELSE '' END AS 'CUSTOMER_STATUS',
[Code] ....
View 1 Replies
ADVERTISEMENT
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Jun 30, 2015
I have the following query.
SELECT a.Line_Number as Line_Number,
Cast(a.election_effective_date as DATE) as election_effective_date,
Cast(a.Plan_Year_Effective as DATE) as Plan_year_Effective
FROM (SELECT aa.* FROM TEMP_Validation aa WHERE IsDate(aa.Plan_Year_Effective) = 1 AND IsDate(aa.election_effective_Date) = 1) a
Where Cast(a.election_effective_date as Date) Not Between Cast(a.plan_year_effective as Date)
and DATEADD(DD,-1,DATEADD(YY,1,Cast(a.plan_year_effective as Date)))
There is a malformed date in the Temp data, at line 4932. This is why I created a subquery that checks if the plan year and election effective dates are dates using the "IsDate" function. However, when I execute this I get the error "Conversion failed when converting date and/or time from character string". If I eliminate line 4932 the error no longer occurs. Somehow line 4932 is not being eliminated in the subquery, except if I run JUST the subquery it will not include line 4932. What am I doing wrong on these queries and how can I correctly get the subquery to eliminate the malformed date?
View 5 Replies
View Related
Oct 6, 2006
Note the following sql query. It contains two separate queries, an correlated subquery and outer query to work against the results of the subquery. Its purpose is twofold (1) get the TOP n ranked field entities using a certain value, (2) return all records for those entities.
SELECT MasterLoanID, NoteNumber, LendingOfficer,OriginalAmount, ReviewSampling FROM MasterLoanData WHERE Import_AsOfDate = '2006-05-31' AND BankID = '1' AND clientID = 1 AND LendingOfficer IN(SELECT TOP 3 LendingOfficer FROM MasterLoanData WHERE Import_AsofDate = '2006-05-31' AND ClientID = 1 AND BankID = '1' GROUP BY LendingOfficer ORDER BY SUM(OriginalAmount) DESC) ORDER BY LendingOfficer, Notenumber
Note that both queries need to filter the same fields -- import_AsofDate, BankID, and ClientID -- in order produce accurate results. Separate indexes exist for all three fields. Both queries work against the Sql Express database, however, when I combine them in a sql statement, Sql Express seems totally lost -- the query runs but never finishes -- I have to abort execution!
I've isolated the problem down to referencing of the import_AsofDate field in the outer query WHERE clause. If I remove that field reference from the outer WHERE clause, the query works quickly -- in seconds, however the results aren't accurate because I'm not getting a filter against the correct Import_AsofDate value. Note too that the same Access database executes the same query in seconds! Sql Express just seems totally confused by date reference contained in both WHERE clauses. I'd say that signifies a definite problem in Sql Express.
Has anyone experienced a similar problem with a correlated subquery and the same date field being referenced in both WHERE clauses of each query? I don't currently have a full fledged Sql Server database to test this query against, but seems as though it should work.
Rick
View 7 Replies
View Related
May 25, 2015
Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query.
Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.
how i can achieve this query without using RANK() and other functions.
"SELECT Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem,
PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,
[Code] ....
View 7 Replies
View Related
Aug 25, 2006
Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?
Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)
select
OrderDate,
ShipCountry,
ShipCity,
Freight,
/* how do I get to the outer query's date range ? */
(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
Northwind.dbo.Orders O
where
ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'
Thanks
View 9 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
Jun 9, 2014
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
View 3 Replies
View Related
Mar 21, 2001
Does anybody have a job where it will disconnect certain users from the database
What I would like to do is schedule a job that will run at 11:00pm that will disconnect certain users (not all of them). This program should also kill any jobs that these users may be running.
I running into a problem occasionally, where a certain program hangs up and will not release its DB locks. This then keeps a couple of the nightly batch jobs from running.
(I run on SQL SERVER 7.0 on a NT server)
Matt Atkinson, mratkinson@www.com
View 2 Replies
View Related
Jun 28, 2004
I'm trying to detach my database.But i couldn't do that coz there are some users connected to it. So how can i disconnect them?is there any way to disconnect them from the DB?
Thanks.
View 2 Replies
View Related
Jun 28, 2004
Is there a way to forcibly disconnect users from a db with t-sql? I'm doing a nightly restore of a db and users who shouldnt remain connected after hours, are. So I cannot restore the db unless I disconnect them as the db cannot be obtained for exclusive access.
Any thoughts on this?
Edward Hunter, Data Application Engineer
comScore Networks
View 3 Replies
View Related
Oct 12, 2007
Dear All,
i've to drop one database....i'm sure....
but it is saying presently it is in use....
there no no replication....
some user is using....
that is a new database.....how can i throw all users from the database
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 4 Replies
View Related
Nov 5, 2007
Running server 2003. I am running a program from home and am telneting in. When I leave for over 1/2 hour without inputting anything it disconnects me. How can I reset this setting to several hours befor it will disconnect me?
View 1 Replies
View Related
Mar 9, 2000
Hi All
IF some users are still connected to the database and I want to shutdown the server How can I disconnect them in order to shutdown.
Sincerely.
View 1 Replies
View Related
Jul 21, 2007
The package has 15 DTS 2000 tasks that execute sequentially, depending on 15 successes. The 15th task then invokes 11 more DTS 2000 tasks simultaneously which each invoke a mail task on their success. The package is saved as part of a valid project. The entire package is successful executing within Visual Studio -- 37 green boxes. I move the package as file system .dtsx to my database as a stored package within MSDB and then schedule it as a job via SQL Agent invoking the MSDB stored package.
The package has a log file referenced in the scheduled job for errors and diagnostics while each of the 26 Transforms Data Tasks within the DTS 2000 packages writes to a fully qualified exception file on my server. The log and exception files have valid data when the package runs in Visual Studio but the exception files are untouched when the job fails and the log only references one of the 11 dependent steps with a job failed message. SQL tables are updated and files created from the Visual Studio execution but nothing is created when the job fails.
I am assuming there is a disconnect with the way I am moving the package to the scheduled job but I don't know what the problem is. Can anyone help?
Den
View 2 Replies
View Related
Mar 20, 2007
Can i have a query on table "POC" with a sub query on the same "POC" table? Much like the code i have below:
dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr);
when i go to build/run i get this error: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
I'm trying to select all the columns of a row, where there are unique combos of [CustomerLastName],[CustomerFirstName],[CustomerEmail].
thanks!
View 8 Replies
View Related
Mar 6, 2004
Hello,
I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.
Thanks
Dakki
View 7 Replies
View Related
Jan 16, 2006
I am building a package that will be used to transfer data from Access 2000 to SQL 2005. For development purposes, the first thing I do is search for my SQL database. If it is there, I drop and recreate it. If I run the package once, it runs fine, . After that, I get the message that the database is in use and can not be deleted. I do not have Mgmt Studio open, so the only thing that could have a connection is the BI Studio. The only way I can seem to clear it, is to close and reopen BI Studio and the package. To make matters worse, it does not happen all the time. So sometimes I can run the package as many times as I would like, until it chokes, and then I get to start all over again. Has anyone else seen something like this, or am I missing something in the package and/or task settings? Please let me know.
Thanks,
DO
View 7 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Feb 5, 2015
I have a table that has Finance transactions in it. I want to find the last time a transaction was put in and the balance that was recorded for that last entry for each account.
The Table looks like this
CustomerCode, AccountID,BillDate,Balance
1,1234,2014-11-31,1000.00
1,1234,2014-12-31,900.00
2,1234,2014-10-30,2000.00
2,1234,2014-11-30,1500.00
3,4567,2013-10-30,2200.00
3,4567,2013-11-30,2000.00
I am trying to write a query statement that can show me this.
1,1234,2014-12-31,900.00
2,1234,2014-11-30,1500.00
3,4567,2013-11-30,2000.00
Once I have this part working I actually need to make it a sub-query for more customer information from another table.
View 9 Replies
View Related
Jul 23, 2005
We were trying to remove duplicates and came up with two solutions.One solution is similar to the one found in a book called "AdvancedTransact-SQL for SQL Server 2000" by Ben-Gan & Moreau. This solutionuses temp tables for removing duplicates. A co-worker created adifferent solution that also removes duplicates, but the other solutionuses subqueries instead of temp tables.Theorhetically, which solution would result in faster performance withlarge tables? Would using temp tables peform faster when the sourcetable has 100,000 records, for example, or would the subquery functionmore quickly in that situation?
View 3 Replies
View Related
Oct 13, 2015
i have two tables Student and Daily Attendance with following structure ,as i want to extract students of a class order by roll number which are not in Daily Attendance table , i know it will be solved by subquery but I don't know how to solve it.
Student:-
CREATE TABLE [dbo].[Student](
[Student_Id] [bigint] IDENTITY(1,1) NOT NULL,
[Course_Id] [smallint] NULL, //Foreign key
[Class_Id] [int] NULL, //Foreign key
[Batch_Year] [varchar](20) NULL,
[Student_Initials] [varchar](20) NULL,
[code]....
View 2 Replies
View Related
Apr 27, 2015
I have a table which is called PneumoniaCareBundleDiagnosisCodes with the fields DiagnosisCode, DiagnosisDesc
I have a another table called dbo_OP_APPOINTMENT_PROCEDURE_PIVOT
With fields Procedure01, Procedure02, Procedure03, Procedure04, Procedure05, Procedure06, Procedure07, Procedure08, Procedure09, Procedure10, Procedure11, Procedure12.
(for info purposes the relationship here is DiagnosisCode = Procedure01, 02, 03 etc)
Is it possible to right a query that shows all records in dbo_OP_APPOINTMENT_PROCEDURE_PIVOT where the DiagnosisCode in table PneumoniaCareBundleDiagnosisCodes appear in any of the Procedure01, 02, 03 fields etc.
If I was using an IF statement the logic i would right it as follows:
IF dbo_OP_APPOINTMENT_PROCEDURE_PIVOT.Procedure01
IS IN PneumoniaCareBundleDiagnosisCodes.DiagnosisCode OR
IF dbo_OP_APPOINTMENT_PROCEDURE_PIVOT.Procedure02
[Code] ......
View 5 Replies
View Related
Jun 2, 2007
Hello, I need help because I may have either mental or Jennt
My question is that I have three tables CONTAIN OF
EMPLYEES TABLE:
EMP ID
EMP LAST NAME
ORDERS TABLE:
EMP ID
ORDER ID
ORDERS DITEALS:
ORDER ID
PRODUCT ID
UNIT PRICE
COUNTUTY
WHAT I NEED
The names of all the required staff who supervised every request so that the total quantity * price not greater than 1000
I NEED HELP IN THAT AS FAST AS ANY ONE READ MY Q
GREAT THANKS FOR ANY HELP
OH I FORGOT THIS IS WHAT I DID
elect EmployeeID,LastName
from dbo.
where Employees.EmployeeID IN
(select orderid,employeeid
from dbo.Orders as ord
where Employees.EmployeeID=ord.employeeid and ORD.orderid) in
(select orderid , sum(UnitPrice*Quantity) total
from dbo.[Order Details] as ords
group by orderid
having sum(UnitPrice*Quantity)>1000)
I KNOW THAT THERE IS SOMTHING NOT WRIGHT
View 4 Replies
View Related
Aug 20, 2015
I need a script to kill existing sessions on [db1] and [db2] except sessions from [user1]. I have a script like this now:
USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('db1)
EXEC(@kill);
I just need to extend to run on db2 and don't kill sessions from user1
View 3 Replies
View Related
Sep 29, 2007
Hi,
I have a SSIS Package which I would like to modify using SSIS API. I need to put new component between some two existing data flow's components. During this process I need to disconnect two data flow's components using SSIS API. How can I do that?
Thanks,
Rafal
View 1 Replies
View Related
Apr 11, 2004
Hi there, here is my current query:
sqlTemp = "SELECT id,code,description FROM tbl_content " &_
"WHERE EXISTS " &_
"(SELECT * FROM tbl_published_content " &_
"WHERE tbl_content.id = tbl_published_content.id ORDER BY tbl_published_content.nCorrectOrder)"
I don't get results sorted by tbl_published_content.nCorrectOrder, and have also no iea how to retrieve that values.
Please can any one tell me how can I have access to tbl_published_content.nCorrectOrder and get it sorted by this field?
So I can use objRS.("tbl_published_content.nCorrectOrder") or some alias?
thanks a million!
View 2 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related
Jun 1, 2004
I have issued a simple insert statement on table and the statement could not insert a single row into the table.When i check the locks on the tables i found that almost 5000(five thousand) locks on the table for a single user with different SPID(s).
The user is and sql server user(not a windows user) and used to connect to the application.I wanted to disconnect the user from the DB so that all the locks will be freed.But i dunno how to disconnect a user from the DB. I know that i can issue KILL command to Terminate a user process based on the system process ID,but here the same user has nearly 1000 SPIDs.I thought that it would be very big job to kill each and every process and restared the sql server.but i guess its not the correct process to do.
how can i dosconnect a specific user from the DB.
Thanks.
View 1 Replies
View Related
Nov 11, 2015
Every day between 1AM to 2AM client disconnect happens and it's not too long also...it's about 30secs...nothing is recorded from SQL side and application server side and then we checked the network...there is no disconnect from network side too. How can i find the cause for disconnect.
View 4 Replies
View Related
Jan 31, 2008
Hi all,
I have 2 Tables "dbo.Samples" and "dbo.TestResults" listed below:
dbo.Samples:
SampleID SampleName Matrix SampleType ChemGroup ProjectID
1
Blueriver01
Water
Primary
VOCs
1
2
Blueriver02
Water
Duplicate
VOCs
1
3
Blueriver03
Water
QA
VOCs
2
4
Blueriver11
Soil
Primary
VOCs
1
5
Blueriver12
Soil
Duplicate
VOCs
1
6
Blueriver13
Soil
QA
VOCs
3
where SampleID is Primary key in this table.
dbo.TestResults:
AnalyteID AnalyteName Result Unit SampleID
1
Acetone
120.800
ug/L
1
2
Benzene
25.600
ug/L
1
3
Trichloroethene
13.000
ug/L
1
4
Xylenes
0.000
ug/L
1
5
Acetone
90.700
ug/L
2
6
Benzene
31.400
ug/L
2
7
Trichloroethene
19.200
ug/L
2
8
Xylenes
2.000
ug/L
2
9
Acetone
140.300
ug/L
3
10
Benzene
21.500
ug/L
3
11
Trichloroethene
22.200
ug/L
3
12
Xylenes
0.000
ug/L
3
13
Acetone
222.100
ug/Kg
4
14
Benzene
10.300
ug/Kg
4
15
Trichloroethene
30.200
ug/Kg
4
16
Xylenes
50.700
ug/Kg
4
17
Acetone
211.900
ug/Kg
5
18
Benzene
16.400
ug/Kg
5
19
Trichloroethene
34.700
ug/Kg
5
20
Xylenes
60.000
ug/Kg
5
21
Acetone
220.300
ug/Kg
6
22
Benzene
13.200
ug/Kg
6
23
Trichloroethene
32.000
ug/Kg
6
24
Xylenes
55.500
ug/Kg
6
NULL
NULL
NULL
NULL
NULL
where AnalyteID is Primary key and SampleID is Foreign key in this table.
Note: The relationship between "dbo.Samples" and "dbo.TestResults" has been established.
============================================
I want to get a new T-SQL output "dbo.Report" like:
SampleID SampleName Unit Acetone Benzene Trichloroethene Xylenes
1 Blueriver01 ug/L 120.800 25.600 13.000 0.000
2 Blueriver02 ug/L 90.700 31.400 19.200 2.000
3 Blueriver03 ug/L 140.300 21.500 22.200 0.000
4 Blueriver11 ug/Kg 222.100 10.300 30.200 50.700
5 Blueriver12 ug/Kg 211.900 16.400 32.000 60.000
6 Blueriver13 ug/Kg 220.300 13.200 32.000 55.500
I want to get the first 2 columnns from the "dbo.Samples" table and the 3rd, 4th, 5th, 6th, and 7th columnns from the "dbo.TestResults" table. I think I can create a new "dbo.Report" table with the 7 Columnn Names, and retrieve the data of SampleID and SampleName from the "dbo.Samples" table and put them into the "dbo.Report" table. But I do not know how to retrieve the data of Unit, Acetone, Benzene, Trichloroethene, and Xylenes from the "dbo.TestResults" table and put them into the "dob.Report" table formatted and presented in the above blue-colored table. The following is my T-SQL programming plan:
-- Create a new table called "Report"
CREATE TABLE Report
(
SampleID int,
SampleName nvarchar(25),
Unit nvarchar(25),
Acetone decimal(9,3),
Benzene decimal(9,3),
Trichloroethene(9,3),
Xylenes decimal(9,3),
);
-- Insert the data of SampleID and SampleName from dbo.Samples --
SELECT SampleID, SampleName
FROM dbo.Samples
-- Insert the data of Unit, Acetone, Benzene, Trichloroethene, and Xylenes from dbo.TestResults --
I guess I may be able to use the following code:
INSERT INTO dbo.Reort(Unit, Acetone, Benzene, Trichloroethene, Xylenes)
SELECT x????
FROM y????
WHERE z????
(SELECT xxx??
FROM yyy??
WHILE zzz='##??")
GO
I do not know how I can get and use the above planned "subquery" correctly to get the data I want!!?? Please help and give me a delaied instructions or direction to achieve this task.
Thanks in advance,
Scott Chang
View 16 Replies
View Related
