MS SQL Server :: Need Help To Group By Top 2 Records

First And Last Records In A Group

Feb 11, 2012

I have a group of records like this;

NAME| RUN START| RUN END| LK1| LK2| DESC
Graeme Brown|2012-02-23 07:20:00|2012-02-23 07:50:00|2213|2244|AK1/4/PI2
Graeme Brown|2012-02-23 08:00:00|2012-02-23 09:25:00|2244|2052|AK1/4/PI2
Graeme Brown|2012-02-23 09:30:00|2012-02-23 11:05:00|2052|917|AK1/4/PI2
Graeme Brown|2012-02-23 12:15:00|2012-02-23 13:55:00|917|2052|AK1/4/PI2
Graeme Brown|2012-02-23 14:05:00|2012-02-23 15:40:00|2052|1111|AK1/4/PI2

They are grouped on the last column [DESC].

I want to get;

NAME| RUN START| RUN END| LK1| LK2| DESC
Graeme Brown| 2012-02-23 07:20:00| 2012-02-23 15:40:00| 2213| 1111| AK1/4/PI2

So what it needs to do is combine the ;

earliest RUN START and corresponding LK1 with
latest RUN END and corresponding LK2.

I've tried creating temp tables with the mins and maxs - but then I can't combine them with the LK1 and Lk2 fields...

View 3 Replies View Related

Group Records

Jun 21, 2007

sample query results:
cid cno cvalue
--- --- -------
835201add edit
835201add edit
836202with VAT
836202with VAT

how can i filter this more into this:
835201add edit
836202with VAT

View 3 Replies View Related

Get Group And Multiply Records

Dec 20, 2013

1.Create the tables with insert queries
2. provide the result as required in an temp table
3. Display the expected result

======================================================================
CREATE TABLE and Insert Data
======================================================================
use master
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]

[Code] .....

View 14 Replies View Related

Select Records If All In Group Have Same Value

Feb 11, 2015

Here is my table data:

CREATE TABLE
#TestTable (
Pk INT,
GroupID INT,
Enabled BIT

[code]..

I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.What would be the most efficient way to write such a query?

View 2 Replies View Related

Top 1 Record Of A Group Of Records

Aug 14, 2007

Hi all, I am relatively new to sql and have an issue I am trying to solve. I have a table with several records with the same id:

id amount date
-- ------ ------
1 100 01/01/2006
1 2000 06/01/2005
2 200 01/01/2005
2 500 06/01/2007

how would I get the data for the record with the newest date?
So I would return:

id amount date
-- ------ ----------
1 100 01/01/2006
2 500 06/01/2007

Thanks in advance for the help.

View 8 Replies View Related

Sorting Records In A Group By

Apr 21, 2006

Hi,

I've a problem with the following stored procedure Select . It does compile and run, but doesn't return the sorted result I wanted, which was to have the records from tblPieces (alias Pcs) sorted by (in order) Pcs.fkBatchId, Pcs.fkProfileCode, Pcs.Colour. What happens instead, I think, is that the correct records are selected, but in the record creation order. CREATE PROCEDURE dbo.LoadOneBatch
(
@BatchId int, -- the pkBatchId of the batch required
@OnlyNew bit -- If true, only consider batches that haven't ever been loaded.
)
AS
SET NOCOUNT OFF
SELECT Bat.pkBatchId,
Pcs.*
FROM tblBatches AS Bat
JOIN (
SELECT Bat1.pkBatchId,
Pcs1.fkProfileCode,
Pcs1.Colour
FROM tblBatches AS Bat1
JOIN tblPieces AS Pcs1 ON Pcs1.fkBatchId = Bat1.pkBatchId
WHERE Bat1.pkBatchId = @BatchId
GROUP BY Bat1.pkBatchId, Pcs1.fkProfileCode, Pcs1.Colour
) SubQ ON SubQ.pkBatchId = Bat.pkBatchId
JOIN tblPieces AS Pcs ON Pcs.fkBatchId = Bat.pkBatchId
WHERE (@OnlyNew = 1 And Bat.IsLoaded = 0 And
Bat.IsCompleted = 0 And Bat.pkBatchId = @BatchId) Or
(@OnlyNew = 0 And Bat.pkBatchId = @BatchId)
GO
EXEC LoadOneBatch @BatchId = 1, @OnlyNew = 0

The DDL for the two tables is:CREATE TABLE [tblBatches](
[Stamp] timestamp NOT NULL,
[pkBatchId] int IDENTITY(1,1) NOT NULL,
[BatchNo] varchar(50) NULL,
[SubmitTime] [datetime] NULL,
[FinishTime] [datetime] NULL,
[IsLoaded] bit NULL,
[IsCompleted] bit NULL,
PRIMARY KEY ( [pkBatchId] ASC )
)
CREATE TABLE [tblPieces](
[Stamp] timestamp NOT NULL,
[pkPieceId] int IDENTITY(1,1) NOT NULL,
[fkBarId] int NULL,
[fkBatchId] int NULL,
[PieceNo] varchar(12) NOT NULL,
[Descrip] varchar(48) NULL,
[Position] real NULL,
[LeadPrep] char(1) NOT NULL,
[TailPrep] char(1) NOT NULL,
[Length] real NOT NULL,
[fkProfileCode] varchar(10) NOT NULL,
[Colour] varchar(5) NOT NULL,
PRIMARY KEY ( [pkPieceId] ASC )
)

The data records output are roughly:pkBatchId Stamp pkPieceIdfkBarIdfkBatchIdPieceNo Descrip PositionLeadPrepTailPrepLengthfkProfileCodeColour
1 0x00000000000036B21 NULL1 00000000000118960 /003/F>1 N2NULL / 913 6000 wht
1 0x00000000000036B32 NULL1 00000000000218960 /003/F<1 N2NULL / 913 6000 wht

View 5 Replies View Related

Group By And Adding Records To Another Table

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

SQL 2012 :: Group By On Consecutive Records?

Apr 4, 2015

Problem 1: I have the following table which shows the location of a person at 1 hour intervals

IdEntityIDEntityNameLocationIDTimexdelta
11MickeyClub house03001
21MickeyClub house04001
31MickeyPark05002
41MickeyMinnies Boutique06003
51MickeyMinnies Boutique07003
61MickeyClub house08004
71MickeyClub house09004
81MickeyPark10005
91MickeyClub house11006

The delta increments by +1 every time the location changes.

I would like to return an aggregate grouped by delta as per example below.

EntityNameLocationIDStartTimeEndTime
MickeyClub house03000500
MickeyPark05000600
MickeyMinnies Boutique06000800
MickeyClub house08001000
MickeyPark10001100
MickeyClub house11001200

I am using the following query (which works fine):

select
min(timex) as start_date
,end_date
,entityid
,entityname
,locationid

[code]....

However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.

Problem 2:I have the following table which shows the location of different people at 1 hour intervals

IdEntityIDEntityNameLocationIDTimexDelta
11MickeyClub house09001
21MickeyClub house10001
31MickeyPark11002
42DonaldClub house09001
52DonaldPark10002
62DonaldPark11002
73GoofyPark09001
83GoofyClub house10002
93GoofyPark11003

I would like to return an aggregate grouped by person and location.

For example

EntityIDEntityNameLocationIDStartTimeEndTime
1MickeyClub house09001100
1MickeyPark11001200
2DonaldClub house09001000
2DonaldPark10001200
3GoofyPark09001000
3GoofyClub house10001100
3GoofyPark11001200

What modifications do I need to the above query (Problem 1)?

View 0 Replies View Related

Query To Get Latest 2 Records For Each Group

Aug 21, 2014

select
DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC),
a.order,a.line,a.datedel,a.recpt,b.status,
b.item,b.t_sup
from historytbl a
inner join order b
on a.order = b.order
and a.line = b.line
and a.status =4
group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup

The query is returned the results below.

Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122
3ccc302014-04-08rc3zzz1231122
4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333
5fff90 2014-02-11rc8yyy123333
6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444
8iii102014-04-10rc5rrr123555

However, I want to have the query only show most recent two records for each group of item and sup, please see the results I want below.

Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122

4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333

6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444

View 4 Replies View Related

Update Group By Changed Records?

Nov 5, 2014

I'm bulk loading employees into an etl table, each employee has a unique ID number, but they have multiple records in the data. Sometimes their name or birthdate will change and I want to identify those records and only insert the newest version into production. I can do this with a series of temp tables, but I'm sure there's a better way. The SQL below updates the etl table with the flag I want to mark the inserts, but it seems convoluted. (Jon's birthday changes, Jane's birthday changes, Bill's gender changes, Amy nothing changes(I handle those inserts later))

DECLARE @Records TABLE(
firstname varchar(50), lastname varchar(50), birthdate date, sex char(1), IDNum varchar(15), moddate date, opflag char(1))
INSERT INTO @Records
VALUES
('JON','SMITH','20000101','M','12345','20140101','I'),
('JON','SMITH','20000101','M','12345','20140201','I'),

[code]....

View 1 Replies View Related

Maximum Date In A Group Of Records

Jul 20, 2005

hi all,i got is table:Id StartDate EndDatea 19/03/2001 18/03/2002a 19/03/2002 18/04/2002*b 13/08/2000 12/08/2001b 13/08/2001 12/08/2002b 13/08/2002 10/07/2002*Sort command and groupins i am ok but i need to select only the records thathas the latest enddate. (See *)any ideas? thanks in advancerashid

View 1 Replies View Related

Transact SQL :: Show Particular Records Within Same Group?

Aug 13, 2015

I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty and source data looks like this

ID   PersonID    Date            Type       Qty

1      1        01/01/2011       Accept      5
2      1        01/01/2011       Accept      5
3      2        02/01/2010       Accept      10
4      2        02/01/2010       Deny        20
5      3        02/01/2012       Accept      15

[Code] .....

Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.

ID PersonID    Date            Type         Qty
1    1        01/01/2011       Accept        5      (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis
same)
3    2        02/01/2010       Accept        10     (show Accept row=3,ignore deny row)

5    3        02/01/2012       Accept        15     (show Accept row=5)

6    4        05/05/2012       Accept        25     (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)

7    4        07/08/2012       Accept        20

11   6        01/01/2011       Accept        5      (show Accept rows=11,12 because Qty is different)

12   6        01/01/2011       Accept        15

Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)

Insert into sample values (1 ,1,'01/01/2011','Accept',5),

(2,1,'01/01/2011','Accept',5),
(3,2,'02/01/2010','Accept',10),
(4,2,'02/01/2010','Deny',20),
(5,3,'02/01/2012','Accept',15),
(6,4,'05/05/2012','Accept',25),
(7,4,'07/08/2012','Accept',20),
(8,4,'07/08/2012','Deny',5),
(9,4,'09/23/2012','Accept',23),
(10,5,'09/08/2012','Deny',12),
(11,6,'01/01/2011','Accept',5),
(12,6,'01/01/2011','Accept',15)

View 4 Replies View Related

How To Retrieve Top 3 Records In The Group Level

Dec 18, 2006

Hi all,

I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.

Please let me know if some body has any idea.

Thx.

View 1 Replies View Related

Can I Group Records In An Ador.recordset?

Feb 8, 2007

Hi,

I have the need to group records in an ador.recordset.

Is this possible and in that case how?

View 1 Replies View Related

Using Min Or Group By To Return Specific Records

Mar 12, 2008

I am fairly new to transact SQL and I am having difficulty retrieving the set of records I require given the data shown below. I want to be able to filter the records just to return the records that have the minimum securityorder for each unique secsyscode. I suspect I need to use min or group by to achieve the desired affect but cannot seem to get it right

any help would be appreciated

eg in the following
secsyscode, securitytypecode and securityorder are integers and securityCode is a char(16).

secsyscode
securityCode
securitytypecode
securityorder

1
Special
1
2

2
Total Fund
999
17

3
PerfInd
995
14

3
PerformanceIndex
999
17

4556
93152
1
2

4556
10815-0
4
1

4557
558372
1
2

4557
12137-0
4
1

4558
656113
1
2

4558
13154-0
4
1

4559
53673
1
2

4559
13672-0
4
1

I only want the following records to be returned.

secsyscode
securityCode
securitytypecode
securityorder

1
Special
1
2

2
Total Fund
999
17

3
PerfInd
995
14

4556
10815-0
4
1

4557
12137-0
4
1

4558
13154-0
4
1

4559
13672-0
4
1

View 5 Replies View Related

T-SQL (SS2K8) :: Update Records Separated By Group

Jun 25, 2014

I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:

Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
UNION ALL
Select 11, 2
UNION ALL
Select 14, 3;

I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.

InternalAuditTeamEmployeeIDInternalAuditTeamIDEmployeeIDFlag
1 1 619 1
218581
316041
425181
517161
639661
711910
819400
92391
1012340
1129541
1228910
1329500
143321
1539450

I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".

CREATE TABLE STATEMENT:

CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,

[Code] ......

View 3 Replies View Related

Select Latest Records From GROUP BY Query

Feb 26, 2014

I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.

View 3 Replies View Related

Find The Last Record By Date In A Sub Group Of Records.

Oct 1, 2007

Looking to see if thier is a better way to find the last record entered in a group of records.

What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.

Table example using simplied data.

PolId

CoveragId

EffDate

Status

Limit1

2

1

9/7/2007

a

10000

2

2

9/7/2007

a

150000

2

2

10/1/2007

a

200000

3

1

9/7/2007

a

10000

The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.

The current query returns a single value. from a single table.

Current code used in the select protion on a larger query. bpi = basicpolicyInformation.

( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)

Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.

View 16 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

How To Update Group Of Records In SQL Statement Or Stored Procedure

Dec 5, 2007

I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those ��groups�? and enter a Budget value = BudgetTotal/Count.

How could I write this in a stored procedure or a SQL statement if possible?

Thanks.

Kevin

SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count,
dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1
FROM dbo.ReportTable INNER JOIN
dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND
dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type
GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget
HAVING (dbo.ReportTable.Budget < 1)
ORDER BY dbo.ReportTable.ProjectNo

ProjectNo

Category

Type

Count

Budget

BudgetTotal

100143

Travel

Travel, Meals, No Report IRS

2

0

300.27

100146

Travel

Travel Costs, Training (all)

1

0

300.27

100164

Supplies & Materials

Supplies, Educational

1

0

300.27

100167

Equipment

Eq NonCapital Desktop Comp

1

0

300.27

100170

Faculty Salaries

FB, Faculty

11

0

300.27

100170

Faculty Salaries

Salary, Faculty, T&R FT

11

0

300.27

100170

Wages

Wages, Student

2

0

300.27

100171

Faculty Salaries

FB, Faculty

19

0

300.27

100171

Faculty Salaries

Salary, Faculty, T&R FT

19

0

300.27

100176

Scholarships & Fellowships

Fell, Assist, Out, Grad

1

0

300.27

100177

Scholarships & Fellowships

Fell, Assist, In, Grad

1

0

300.27

View 5 Replies View Related

Write A Query To Group Records Based On Speed (specific Value Of Zero)?

Jun 28, 2012

I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:

Table - Vehicle_Event

Vehicle_Id____Date_Time______________Speed
C1____________2012-06-28_10:10:00____5
C1____________2012-06-28_10:11:00____0
C1____________2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00____4
C1____________2012-06-28_10:14:00____3

[code].....

OUTPUT_Required:

Vehicle_Id____Date_Time___________________________ __________Speed
C1____________2012-06-28_10:10:00___________________________5
C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00___________________________4
C1____________2012-06-28_10:14:00___________________________3
C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0

[Code] .....

I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.

View 6 Replies View Related

SQL Server 2014 :: Group Up Records Randomly Into N Number Of Batches

Jul 6, 2015

I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.

Say for example, following is the list of records I have in my table which I want to group into 5 batches

A123
A124
A124
A123
A127

After Ntile I will get the below,

Desired output is, Need output like Ntile but all same id should reside in single batch

Even if I n=5, maximum possibility of batches are 3 only.

View 2 Replies View Related

SQL Server 2012 :: How To Group Near Duplicate Records Under A New Common Field

Aug 26, 2015

I've inherited a table of members that has the following structure:

CREATE TABLE [dbo].[dimMember](
[dimMemberId] [int] IDENTITY(1,1) NOT NULL,
[dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)),
[MemberCode] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](250) NOT NULL,
[LastName] [nvarchar](250) NOT NULL,

[Code] ....

Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.

What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:

First Name (1st 3 characters)
Last Name
DOB
CurrentAddress1
MemberCode
SSN
SubscriberCode

I'm at a loss of how to structure the SQL to update the new column in the table.

View 9 Replies View Related

GROUP BY: Need The Selection To Allso Return Count = 0 When No Records Found

Mar 25, 2008

Hi!
I'am new to this forum and would apreciate any feedback on my problem.
I have a quarry that returns the count of former customers with average cell-phone usage between 200 and 299.
The ressult is grouped in year and week with group by. The dates are represented by the closingdate of the customers subscription.

The ressult is used for reporting purposses, but I need my selection to return '0' on weeks where there are "no reccords found".

CODE:

SELECT '200-299' AS ARPU, year AS YEAR, week AS WEEK, COUNT(nummer) AS Antall
FROM

(SELECT SERGEL_PREPAID.SP_Mobilenumber AS nummer, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP) AS year, DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) AS week,
ROUND(AVG(SERGEL_PREPAID.SP_Sum), 0) AS average
FROM SERGEL_PREPAID INNER JOIN
TRANSLOG ON SERGEL_PREPAID.SP_Mobilenumber = TRANSLOG.TRL_MOBILE
WHERE (TRANSLOG.TRL_STATUS = 'NP_FERD')
GROUP BY SERGEL_PREPAID.SP_Mobilenumber, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP), DATEPART(ww, TRANSLOG.TRL_TIMESTAMP)
HAVING (AVG(SERGEL_PREPAID.SP_Sum) BETWEEN 200 AND 299)) AS derivedtbl_1
GROUP BY uke, all aar

NB: Using SQL Server 2005. Any tip or solution will be a big help
Best regards Gard S

View 2 Replies View Related

T-SQL (SS2K8) :: Sequential Data Selection - Identify Different Fields Within A Group Of Records?

Jun 18, 2014

How to identify different fields with in a group of records?

Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'

[Code] ...

I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.

View 1 Replies View Related

Group / Union Statement - Pull Unique Records From A Large Table

Sep 22, 2014

I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.

What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).

I have tried 2 statements:

#1:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
GROUP BY tblTable1.Name, tblTable1.INDate
ORDER BY tblTable1.Name;

#2:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));

Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.

Example:

NameINOUT
John Smith1/1/20141/2/2014
John Smith1/1/2014(blank)

I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?

View 1 Replies View Related

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

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

Selecting ONLY Records From One Table Having ALL Data Contained In Other Table (GROUP BY?)

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

Group Records By Year &"YYYY&"

Sep 24, 2007

I have 3 Field on my table.
ID (int PK),Name(nvarchar(50),PubDate(DateTime).
"PubDate" may have values between "1/1/1996" "1/1/2007"
NOw How could I group Table Records base on Year of the Field "PubDate"-1996,1997,...,2007-
As I think, First I must Retrieve each Record Year
suppose Record1 (ID=1 Name "Something" PubDate=2007) and then List and Group them.
But I don't whether this function Is available in MSSQL 2005.

View 1 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

What's Microsoft Doing About Providing Page Numbering Per Group And Total Pages Per Group?

Nov 27, 2007

Hi!

I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679

Provide Individual Page Numbering per Group and Total Pages per Group

Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.

I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.

Thanks!
Karen

View 1 Replies View Related