1 Table Subquery With Group
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
ADVERTISEMENT
Dec 27, 2004
Some what of a SQL noobie here, I'm struggling with how to create the statement.
I have two tables,
Event
EventDates
it's a 0ne to many relationship each event can have several dates, problem is I want to get only one instance (distinct) of every event but I have to have it sorted by the date. So I have to inner join the two, I"ve done that with the following statement,
____________
SELECT TOP 100 PERCENT dbo.tblEventsDates.*, dbo.tblEvent.IDEventRef AS idEventRef
FROM dbo.tblEvent INNER JOIN
dbo.tblEventsDates ON dbo.tblEvent.IDEvents = dbo.tblEventsDates.IDEvents
WHERE (dbo.tblEvent.IDEventRef = 0) AND (dbo.tblEvent.fldStatusWorkflow = 3) AND (dbo.tblEvent.fldStatus = 1) AND (dbo.tblEventsDates.fldStatus = 1)
ORDER BY dbo.tblEventsDates.fldDateStart
_____________
But how do I weed out the duplicate events while still maintaining the correct sort order? I wrapped that with a select disctinct, then did a select * from by table events where IDEvents in, but I lose my sort order.
TIA,
John
View 7 Replies
View Related
Nov 20, 2006
On sqlserver 2000 SP 4 or SP3 boxes I have the below query. It will fail with Server: Msg 8624, Level 16, State 3, Line 6. If I comment out the group by in the subquery it works. have researched and there seems to be an issue wih the group by in subquery. ANyone see or resolved this?
declare @SiteID varchar(35),@BeginDate datetime, @EndDate datetime
set @SiteID = N'eastern'
set @BeginDate = N'01/01/2006'
set @EndDate = N'12/31/2006'
select v1.*, v2.*,
dbo.fnGetMaxTranscribe(v1.orderid)
from
mar_vw_rpt_orders_history v1
inner join
(Select orderid,hh,scheduletimesorderby
from mar_vw_history_mo
where mar_vw_history_mo.fullmedadmindate
between convert(datetime,(convert(varchar(10),@begindate,1 01)))
and convert(datetime,(convert(varchar(10),@enddate,101 )))
--Group By orderid,scheduletimesorderby,hh
) v2
on v1.orderid = v2.orderid
andv1.PatientCurrentSiteID = @siteid
order by v1.inmateid,v2.orderid,v2.scheduletimesorderby
View 2 Replies
View Related
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
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
Nov 21, 2007
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 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
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
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
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
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
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
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
Feb 2, 2007
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE (
OuterID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL,
InnerID int NULL
)
INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2
DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)
INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3
INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5
INSERT @Inner VALUES (4, 2, 1) -- InnerID 6
-- UPDATE Outer Table with best match from Inner table
UPDATE @Outer SET
InnerID = IJ.InnerID
FROM @Outer OJ
INNER JOIN
(
SELECT TOP 1 I.*
FROM @Inner I
WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID
AND IsNull(I.CompID, OJ.CompID) = OJ.CompID
AND IsNull(I.FormID, OJ.FormID) = OJ.FormID
ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC
) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID)
AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID)
AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer
The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5.
Can anyone help me? Thanks in advance.
View 6 Replies
View Related
Nov 7, 2007
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
View 14 Replies
View Related
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Jul 20, 2005
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
View 2 Replies
View Related
Jan 21, 2007
Hi guys
I need to aggregate a table to three different levels but I need the results in a single table. Here is the sample data
IndicatorName
DHBName
PHOName
Practice
PracticeName
Numerator
Denominator
ABC
SAM
a
PracticeA
QW
22500
22.5
BNN
SAM
b
PracticeB
SSS
22500
22.5
dddd
JONES
c
PracticeC
FFFF
22500
45
ssss
Alter
d
PracticeZ
QW
22500
22.5
rrrr
Sam
a
PracticeA
FFFF
52500
60
ABC
GINI
b
PracticeA
ASDFF
45000
45
BNN
Hoe
c
PracticeD
Tahunanui Medical Centre
45000
15
Now I need to group this table first on the dhb level:
Query used
SELECT IndicatorName, DHBName,sum( Num),sum( Den)
FROM DHBLevel
GROUP BY IndicatorName, DHBName
Then group on PHO Level
SELECT IndicatorName, DHBName, phoname,SUM(Num) AS Expr1, SUM(Den) AS Expr2
FROM DHBLevel
GROUP BY IndicatorName, DHBName,phoname
Then on Practice Level
SELECT IndicatorName, DHBName, phoname,practicename,SUM(Num) AS Expr1, SUM(Den) AS Expr2
FROM DHBLevel
GROUP BY IndicatorName, DHBName,phoname,practicename.
Now I need to see the aggregates in 1 single table only.
How shall i do this??
Here is the create table script
USE [PhoTest]
CREATE TABLE [dbo].[performanceOctober](
[IndicatorName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[DHBName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PHOName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PracticeName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Numerator] [float] NULL,
[Denominator] [float] NULL
) ON [PRIMARY]
View 4 Replies
View Related
Sep 20, 2007
I have a report that looks like below. It's grouped by Product then by Year. I want to include within the Product grouping and item called "All products". If I swapped order of Grouping to Year then Product, I could simply add a SUM in the group header, but the users don't want it displayed that way round.
Any suggestions for "All products" ?
Thanks
Richard
Revenue
Product 1
2007
2008
2009
Product 2
2007
2008
2009
etc
I want to add
All products
2007
2008
2009
View 2 Replies
View Related
Feb 19, 2008
Hi all,
I am creating a report based on a table in SQL Server 2005 Reporting Sevices. I need to display a subreport within the groupheader of a group I inserted to the table.
When I try to preview the report I'll get the following error: "An error occured during local report processing. An internal error occurred on the report server."
When I place the subreport outside of the table the report runs.
I tested the scenario in an emty table. When I place the report in the details row the subreport is displayed succesfully. If I insert a group to the table and place the subreport in the groupheader the same error occurs.
Is it impossible to use a subreport in a groupheader? If not, does anyone have an idea how to sove this issue?
Thanks!
Dirk
View 3 Replies
View Related
Mar 10, 2008
I'm trying to grab the Total for each "brand" in my table group (this is not in a matrix).
Period 1 Period 2 Period 3
Sensible Choice 2007 3843 4020 4746
2008 1830 3352 1025
Total: 1830
Maxi 2007 124388 132248 162429
2008 127729 194458 13732
Total: 127729
I want to make the total the same amount as the 2008 total per period. (everything in yellow is the group 1 (outer group and the red is the inner group. When i tried to get the value for the year 2008 and put it in the total field, i put Last(Fields!PeriodTotal.value), but it doesnt work, because sometimes it gives me the 2007 total, and sometimes it gives me the 2008 total. I dont know why. Any suggestions on how i can get the 2008 value everytime.
View 1 Replies
View Related
Sep 3, 2015
I have 2 table (Employee T0 & Employee Absense T1), T0 & T1 use Employee Code to connect.
In T0, I have EmployeeCode, Location Code, Shift Code & Hour Rate....,
T1 has EmployeeCode, Date, Quantity ...,
How to write a query to get summary of
T1.[Quantity] * T0.[Hour Rate] as AbsHR group by T0.[Location], T0.[Shift] ?
so the result has [Location], [Shift], [Date], [AbsHR] 4 column.
View 2 Replies
View Related
Jun 1, 2007
Week1
Task1
58.12%
Task2
98.34%
Task3
74.45%
Task4
66.54%
Overall
28.31%
Week2
Task1
74.35%
Task2
56.68%
Task3
66.88%
Overall
28.18%
For the table above, I need to calculate the Overall Percentage
Overall = Task1*Task2*Task3*Task4 (Maximum 4 Tasks in one week)
I Used one Week Group and another Task group
If I place the "overall" in the Week group Footer, the value is coming as Zero.
How do I calculate the Overall value?I wrote custom code but didn't help. Also the overall value has to be one per week.
Thanks for u'r help.
View 2 Replies
View Related
Aug 19, 2006
Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario:
Table A:
_____________
BidID - Int identity
AuctionID - int
BiderName - varchar(50)
bidAmount - money
______________________
Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly.
BidID AuctionID BiderName BidAmount
1 4005 joeblow 100.00
2 4005 janedoe 101.00
3 4005 joeblow 107.00
4 4006 joeblow 100.00
5 4006 janedoe 105.00
6 4006 joeblow 106.00
I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be
3 4005 joeblow 107.00
6 4006 joeblow 106.00
Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right.
Thanks in advance for your help.
View 4 Replies
View Related
Oct 21, 2005
Hi,I guess my brain stopped but I need help with this. create table #RETURN( uid int, cid int, serviceid int )
insert into #RETURN select 44,75,2 insert into #RETURN select 44,76,1 insert into #RETURN select 44,77,3 insert into #RETURN select 45,78,3I need to query this table that will get me a distinct UID with matching CID ordered by serviceid from lowest to highest.The solution should look likeuid cid44 7645 78Thanks
View 2 Replies
View Related
Oct 25, 2005
Hi, I have huge SQL table (5 mil records), currently it is on primary file group, is there any way to move it to another file group?Thanks.
View 2 Replies
View Related
Mar 4, 2006
My Table
ID,Customer,Type,Date
records
1,XXX,AAA,Date
2,ZZZ,BBB,Date
3,QQQ,BBB,Date
I group them with the following query
Select Source,Count(*) from table where date=month(getdate()) group by Type order by 2 desc
the result looks like that
AAA,1
BBB,2
------------
Also there are another table for this results (Totals)
fields
Type,Quantity
--------------
AAA,45
CCC,76
(attention, there are no BBB record currently in this table)
I want that
the results of the first query goes to Totals table.
what I need like this
Type,Quantity
--------------
AAA,45 + AAA,1
CCC,76
BBB,2
I don't know how to do
if there is a LOOP solution in sql server , I would like to know
thanks in advance
View 6 Replies
View Related
Oct 31, 2007
I have a table, Table1 with 3 columns as follows: colItemKey, colGrouping1, colGrouping2.
colItemKey is the primary key. Say colGrouping1 has 4 different types: Grp1A, Grp1B, Grp1C and Grp1D and colGrouping2 has 5 as follows: Grp2A, Grp2B, Grp2C, Grp2D and Grp2E. How do I setup my select so that the result set is as follows:
Grp2A Grp2B Grp2C Grp2D Grp2E
Grp1A nnn nnn nnn nnn nnn
Grp1B nnn nnn nnn nnn nnn
Grp1C nnn nnn nnn nnn nnn
Grp1D nnn nnn nnn nnn nnn
View 2 Replies
View Related
