SQL Server 2008 :: Using Expression In GROUP BY Clause

Feb 23, 2015

I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:

SELECT StartTime + Offset, min(Sensor), max(Sensor)
FROM SensorData
WHERE SensorID = @SensorID AND
StartTime + Offset > @BeginTime AND
StartTime + Offset < @EndTime
GROUP BY (StartTime + Offset) / 100
ORDER BY StartTime + Offset

What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.

Under MS SQL Server 2008, SP3, I get the two following error messages:
Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

View 9 Replies


ADVERTISEMENT

SQL Server 2008 :: How To Remove Group By Clause

Mar 5, 2015

SELECT DISTINCT

'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
-- (t.c_items_total) / (t.cover_count) as AvgPer_Cover--

[Code] ....

The output needs to be grouped by the t.c_items_total...I just need the avg per cover (person) / items_total.

View 4 Replies View Related

SQL Server 2008 :: Aggregate Function Or GROUP BY Clause Error

Apr 13, 2015

While running the below query, getting the error: Am I missing any of the columns to include in the SELECT column_list?

Msg 8120, Level 16, State 1, Line 1

Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

<code>
select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
from
(
SELECT database_id,[Database Name]= DB_NAME(database_id),
[Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END ,

[code]...

View 3 Replies View Related

SQL Server Reporting Services 2005 Group Expression

Mar 8, 2007

I have an expression in a group that calculates a percentage of sales:

=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100

If there is a 0 balance for both the book dollar and the net dollar the expression returns #Error.

I have made sure that if the value is null it should return a 0 in the initial query.

,SUM(ISNULL(R.BOOKD,0)) BOOKD
,SUM(ISNULL(R.NET,0)) NET



Any suggestions would be greatly appreciated.

View 1 Replies View Related

SQL Server 2008 :: SSIS Expression On Getting The Specific Files?

Feb 26, 2015

I am developing the SSIS and stuck on copying specific files.

1. We receive CSV file to our drive on a daily basis.

2. The csv file name has the last 8 digits formatted with the yyyymmdd. For example, the file name might be abcdef_20150226.csv This means it will be our CSV file for today, Thursday, February 26, 2015.

3. There are a lot of files in this directory.

[URL]

What we would like to do:

Add the constraints (or expression) that will copy the files from this directory to another directory that have the date equivalent to Monday only. For example, the file abcdef_20150226.csv will not be copied because it is Thursday file. But the abcdec_20150223 will be copied to a new Directory because it is Monday.

View 1 Replies View Related

SQL Server 2008 :: Aggregating Data From Common Table Expression

Apr 2, 2015

I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.

********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';

WITH CTE AS (
SELECT bkg_nbr

[Code] ....

The raw data from the CTE is:

Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1

The result of the query above was:

Category RecCount
Non-Recidivists3
Recidivists 1

The outcome I was looking for was:

Category RecCount
Non-Recidivists2
Recidivists 1

What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.

View 2 Replies View Related

SQL Server 2008 :: Expression Of Non-Boolean Type Specified In Context Where Condition Is Expected

Apr 10, 2015

I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.

SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY

[code]...

what I need to change in the WHERE clause to rectify this error ?

View 3 Replies View Related

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

May 14, 2008

2 examples:

1) Rows ordered using textual id rather than numeric id


Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id






Result set is ordered as: 1, 11, 2
I expect: 1,2,11


if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.




Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id




Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


and


Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View 12 Replies View Related

SQL Server 2008 :: Where Clause - Look For 5 Records Data In A Table

Apr 14, 2015

I have a query needs to look for 5 records data in a table. Basically i need to hardcode. Below is my query which didn't work out.

select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD,BF_BDOB_CD,BF_TM_PERD_CD) in ***** i guess this is the wrong query****
('A1', 'B1', 'C1')
('A2', 'B2', 'C2')
('A3', 'B3', 'C3')
('A4', 'B4', 'C4')
('A5', 'B5', 'C5')

but if i use the query below it will generate more records than these 5 records

select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD) in ('A1', 'A2', 'A3', 'A4', 'A5')
and (BF_BDOB_CD) in ('B1', 'B2', 'B3', 'B4', 'B5')
and (BF_TM_PERD_CD) in ('C1', 'C2', 'C3', 'C4', 'C5')

View 4 Replies View Related

SQL Server 2008 :: Where Clause - Decrypt Column Or Hash?

Jul 16, 2015

We're encrypting a column of Social Security numbers. Developer needs to execute a query -->similar<-- to:

Select <bunch of columns>
From dbo.People p
Where p.SSN = @Input_SSN_Param --I know this won't work.

If we write the where clause like

Where DecryptByKey(p.SSN) = @Input_SSN_Param

Won't every SSN in the table be decrypted? Seems like a bad idea, performance-wise.

Should I/we add a column to hold a hash of the SSN; and use the hash for comparisons?

View 3 Replies View Related

Adding A Group By Clause And Getting A Count Of A Group

Feb 6, 2008

HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate   varchar(12)DECLARE @Region    varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate   = '12/31/2008'SET @Region    = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM  dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate  AND A.createdon <= @EndDate)AND   (B.new_RegionName = @Region)AND   (A.casetypecode = 2) 
 

View 1 Replies View Related

SQL Server 2012 :: Error Message - Aggregate Function And Group By Clause

Feb 19, 2014

I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?

Here is my code so far:

selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,

[Code] ....

View 8 Replies View Related

SQL Server 2008 :: Querying XML Data With Column Value In Same Select Clause

Aug 3, 2015

I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.

Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....

Current Query - HardCode Script

SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s

When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"

Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].

BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.

BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

View 9 Replies View Related

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

SQL Server 2008 :: Group By Total Sum

Oct 31, 2015

Despite the products are different, I want to get the sum of all products according to customer card.(client ref)

select (ORF.PRICE) *( ORF.AMOUNT - ORF.SHIPPEDAMOUNT) from [.dbo.LG_032_01_ORFLINE ORF JOIN [.dbo.LG_32_01_ITEMS ITM ON ORF.STOCKREF = ITM.LOGICALREF where ORF.CLIENTREF = XXX

View 1 Replies View Related

SQL Server 2008 :: Field As Group Header?

Jan 29, 2015

I have a table returning results like that

Row1 ||Row2 ||ERPID||ParentID||LevelID||Category||SubCategory||DDate ||publish
1 ||1 ||10152159||1015 ||2159 ||LOCTITE ||LOCTITE1||29/01/2015 12:10||0
1 ||2 ||10152134||1015 ||2134 ||LOCTITE ||LOCTITE2||29/01/2015 12:10||0
1 ||3 ||10152157||1015 ||2157 ||LOCTITE ||LOCTITE3||29/01/2015 12:10||0
2 ||1 ||10062003||1006 ||2003 ||COMPUTER||COMPUTER1||29/01/2015 12:10||1

[code]....

But I want to look like that

Row1||Row2||ERPID||ParentID||LevelID||Category||SubCategory||DDate||publish
1||1||10151015||1015 ||1015||LOCTITE||||29/01/2015 12:10||0
1||1||10152159||1015 ||2159||LOCTITE||LOCTITE1||29/01/2015 12:10||0
1||2||10152134||1015||2134||LOCTITE||LOCTITE2||29/01/2015 12:10||0
1||3||10152157||1015||2157||LOCTITE||LOCTITE3||29/01/2015 12:10||0
2||2||10061006||1006||1006||COMPUTER||||29/01/2015 12:10||1

[code]....

View 3 Replies View Related

SQL Server 2008 :: Getting Row Numbers For Each People Group?

Feb 18, 2015

I have a set of rows in a table like for example

Client ID Client Name Date Score
1 Smith 12/31/2014 25
1 Smith 10/15/2014 45
2 John 08/11/2014 55
2 John 06/18/2014 15
3 Rose 04/15/2014 12
4 Mike 07/23/2014 28
5 Mary 01/5/2014 56
6 Lisa 08/1/2014 54
6 Lisa 05/10/2014 34

Now I want to use Row Number function or any way where I can get the result as below

Client ID Client Name Date Score RowNo
1 Smith 12/31/2014 25 1
1 Smith 10/15/2014 45 2
2 John 08/11/2014 55 1
2 John 06/18/2014 15 2
3 Rose 04/15/2014 12 1
4 Mike 07/23/2014 28 1
5 Mary 01/5/2014 56 1
6 Lisa 08/1/2014 54 1
6 Lisa 05/10/2014 34 2

View 3 Replies View Related

SQL Server 2008 :: Get Max Of Group No Matter Currency

Aug 5, 2015

I have this data below :

isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)

DEX | XFFA | DEM | 013 | 2009 | 5 | 8
DEX | XFFA | EUR | 013 | 2014 | 11 | 25

get this data by running the following query :

select b.isin, a.market, a.ccy, a.stock_mkt, max(a.year)
,max(a.month), max(a.day) from market_table a inner join
isin_table b on a.id = b.id where a.isin_closing_date=0
and a.market in ('XFFA', 'XFFA') and
b.isin_type= 'I' and b.isin = 'DEX' group by b.isin,
a.market, a.ccy, a.stock_mkt

what's needed is to get the earliest record all times, no mater the currency :

isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | EUR | 013 | 2014 | 11 | 25

View 3 Replies View Related

SQL Server 2008 :: Moving Tables To New File Group

Mar 16, 2015

I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.

So, how do I get the free space out of the data file? - I've decided to:

1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables

There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?

View 8 Replies View Related

SQL Server 2008 :: Check Constraint On Group Of Records?

May 25, 2015

I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition.

Something along the lines of this condition:

(select count(ClovekAutoID)
from TableOfClovekNames tCN
where JeHlavni = 1
group by ClovekAutoID
having COUNT(JeHlavni ) > 1) = 1In fact,

I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.

View 9 Replies View Related

SQL Server 2008 :: Query To Group And Find Latest

Aug 25, 2015

I have a scenario as below for one ID -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+

I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+

But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.

CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)

INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')

View 8 Replies View Related

SQL Server 2008 :: Get Total Number Of Attachments Of Group ID

Sep 10, 2015

Copy and paste the code to review sample:

Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)

[Code] ...

I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.

View 9 Replies View Related

Transact SQL :: CASE Expression In WHERE Clause?

Nov 6, 2015

I have a scenario where the End User is going to select two parameters.  @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.

If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.

@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).

@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:

WHERE
 
 gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate)
 AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END
 AND gl.Location = @Location

View 9 Replies View Related

SQL Server 2008 :: File Group Is Running Out Of Space Frequently

Sep 1, 2015

We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.

Error:

The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.

tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB
templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited

View 9 Replies View Related

SQL Server 2008 :: Rows Holding Group-wise Maximum Of Certain Column?

May 13, 2015

is there any more efficient way for example to implement the next query?

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
WHERE dealer = 'dealer sample'

What indexes I should create for this query?

View 2 Replies View Related

Expression Type Numeric Is Invalid For COLLATE Clause

Oct 16, 2013

I have a query however i am getting the following error message “Msg 447, Level 16, State 0, Line 1 Expression type numeric is invalid for COLLATE clause.“

This is my query

SELECT
sjo.ID,
sjo.MID,
sjo.Trade_Association_Name,
da1.Account_Name As Trade_Association_Name,
substring(do.[MM-CHN-AGENT],2,12) as Mass_Agent_Chain_No,

[Code] ....

And Dan.Stg_Jitter_Opp2 table consists of the following

ColumnNameData Type
Idvarchar(50)
Mid numeric(18, 0)
RecordTypeIDvarchar(50)
Trade_Association_Name varchar(50)

And [FDMS].[SalesForce].[DailyAccounts]table consists of the following

ColumnNameData Type
Idint
Account_Idvarchar(18)
account_Name varchar(150)
mid_externalvarchar(15)
Mid_internalvarchar(15)

View 5 Replies View Related

Group Expression Does Not Work

Dec 10, 2007

HI,

I tried to run the following query.






Code Block
WITH YesterdayCTE AS
(
SELECT type = 'Members Joined Yesterday'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )

),

MonthlyCTE AS

(
SELECT type = 'Members Joined Last Month'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )

, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )

),

YearlyCTE AS

(
SELECT type = 'Members Joined Last year'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )


),

combinedCTE AS

(
SELECT * FROM YesterdayCTE
UNION ALL
SELECT * FROM MonthlyCTE
UNION ALL
SELECT * FROM YearlyCTE


)

SELECT *
, Members = Borrowers + Depositors
FROM combinedCTE






But I get the following error message.


An error occurred while reading data from the query result set.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer)
===================================
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)

Please can anyone help me to make this work?

Thanks

View 7 Replies View Related

GROUP BY Column-name Problem (need Expression)

Aug 11, 2005

Please advise on how to get the GROUP BY coded in an acceptable way:DECLARE @LO INTDECLARE @HI INTDECLARE @StartDate varchar(10)DECLARE @EndDate varchar(10)SELECT @StartDate = '01/01/2005'SELECT @EndDate = '06/30/2005'SELECT @LO= 250SELECT @HI= 333SELECTStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate, JumboAmount = SUM(JumboAmount), JumboMortgages = SUM(JumboMortgages), JumboFIXMortgages= SUM(JumboFIXMortgages), JumboFIXAmount = SUM(JumboFIXAmount), JumboARMMortgages = SUM(JumboARMMortgages), JumboARMAmount= SUM(JumboARMAmount)FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred ZON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =Z.ZipCodeGROUP BYStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate

View 2 Replies View Related

Using An Expression To Group On Multiple Fields

Mar 23, 2008



I have an exisitng report that lists unit name, provider, runday, shift, patient. The report groups by unit name and there is a page break after each unit name. So in the current environment the report prints one page per unit that contains all of the providers, rundays, shifts, and patients for that unit name its grouping on. So the report would like like this when it prints:

Unit A
Provider 1 Runday 1 Shift 1





patient 1
patient 2
Provider 1 Runday 1 Shift 2





patient 1
patient 2
Provider 1 Runday 2 Shift 1





patient 1
patient 2
Provider 1 Runday 2 Shift 2





patient 1
patient 2
Provider 2 Runday 1 Shift 1





patient 1
patient 2
Provider 2 Runday 1 Shift 2





patient 1
patient 2
Provider 2 Runday 2 Shift 1





patient 1
patient 2
Provider 2 Runday 2 Shift 2





patient 1
patient 2
PAGE BREAK
Unit B............(repeat data from page 1)
PAGE BREAK
Unit C............(repeat data from page 1

The end user would like the ability to keep the report as is but would like to also be able to print the report as one page per each unit name, provider, runday and shift. so it would look like this

Unit A
Provider 1 Runday 1 Shift 1





patient 1
patient 2
PAGE BREAK
Unit A
Provider 1 Runday 1 Shift 2





patient 1
patient 2
PAGE BREAK
Unit A
Provider 1 Runday 2 Shift 1





patient 1
patient 2




So I created a boolean parameter with a prompt of Page break by Unit, Provider, Runday & Shift? My thought is if the users sets this to False, the report will group on just Unit Name (the first example). If the user sets this to True, the report will group on Unit Name, Provider, Runday & Shift.

I set the grouping expression for this data table as:
=iif(Parameters!Grouping.Value = "False", Fields!unit_Name.Value,(Fields!unit_Name.Value,Fields!Lname.Value,Fields!Rundays.ValueFields!Shift.Value))


Within the expression editor window it displays a syntax error and the report will not run.

Any help would be greatly appreciated!!!!!!!!!

View 7 Replies View Related

Group Expression That Calculate Percentages.

Mar 8, 2007

I have an expression in a group that calculates percent of sales:

=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100

The problem I'm having is that if both the

Sum(Fields!BOOKD.Value)=0 and

Sum(Fields!NET.Value)=0 ,

the expression returns the message #ERROR instead of a 0.



I have the initial query set to display 0 if the value is null.

,SUM(ISNULL(R.BOOKD,0))BOOKD
,SUM(ISNULL(R.NET,0))NET



I would greatly appreciate any help on how to solve this issue.

Thank Barb

View 4 Replies View Related

Having Clause Without GROUP BY Clause?

Nov 20, 2004

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View 3 Replies View Related

Top Clause With GROUP BY Clause

Apr 3, 2008

How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent


thanks in advance

regards
Waqas

View 10 Replies View Related

SQL 2012 :: Group By Expression - Outer Reference

Sep 2, 2014

When attempting to pull this query I get this error: "Each GROUP BY expression must contain at least one column that is not an outer reference."

View 1 Replies View Related







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