MS SQL Server :: Select Statement Problem - Group By Maybe Nested Select?

Nested Select Statement
EMPLOYEE TABLE

EmployeeNo = Int
DATEHIRED = Smalltimedate (I NEED TO GET ALL THE EMPOYEES DATEHIRED
WITHIN THE DATE RANGE (FROMDATE >= DATEHIRED and TODATE <=DATEHIRED)

V_GETOLDNEWBASIC Table
EmployeeNo = Int
Startdate = smalldatetime
Oldbasic = Int
NewBasic =Int
OLDBASICPAY and NEWBASICPAY is in one row (I NEED TO GET ALL THE
EMPOYEES STARDATE WITHIN THE DATE RANGE (FROMDATE >= STARDATE and
TODATE <=STARDATE) LINK to EMPLOYEE TABLE BY EMPLOYEENO

V_GETOLDNEWPOSITION
EmployeeNo = Int
Startdate = smalltimedate
OLDPosition = Varchar
NewPosition =Varchar (I NEED TO GET ALL THE EMPOYEES STARDATE WITHIN
THE DATE RANGE (FROMDATE >= STARDATE and TODATE <=STARDATE) LINK to
EMPLOYEE TABLE BY EMPLOYEENO

V_HONBENEFITS TABLE
EmployeeNo = Int
Startdate = smalldatetime
OLDAmount = Int
(I NEED TO GET ALL THE EMPOYEES STARDATE WITHIN THE DATE RANGE
(FROMDATE >= STARDATE and TODATE <=STARDATE) LINK to EMPLOYEE TABLE BY
EMPLOYEENO

All Tables should be link to EMPLOYEE Table (EmployeeNo) and should
get the value depending on the date specified DATE>=Stardate and
DATE<=Startdate. Please take note that there are no link or
relationship between
(V_GETOLDNEWPOSITION,V_HONBENEFITS TABLE,V_GETOLDNEWBASIC Table).

1. I should get the all the records from EMPLOYEE TABLE where
DATEHIRED>=Stardate and DATEHIRED<=Stardate
2. I should link EMployee table to V_GETOLDNEWPOSITION by EMployeeNo
and should get all the data from V_GETOLDNEWPOSITION where
FROMDATE>=Stardate and TODATE<=Stardate THEN GET THE MAX RECORD
3. I should link EMployee table to V_HONBENEFITS TABLE by EMployeeNo
and should get all the data from V_HONBENEFITS TABLE where
FROMDATE>=Stardate and TODATE<=Stardate THEN GET THE MAX RECORD
4. I should link EMployee table to V_GETOLDNEWBASIC Table by
EMployeeNo and should get all the data from V_GETOLDNEWBASIC where
FROMDATE>=Stardate and TODATE<=Stardate THEN GET THE MAX RECORD

All output should be in One row for each employee, how can I combine all 4 tables

OUTPUT

Employee OLD BASIC NEW BASIC OLDPOS NEWPOS OLDBENEFITS NEWBENEFITS

1 1500 1000 Tech Support Programmer
2 4000 7000 500 600
3 3000 8000
4 Call Agent TeamLDR 1000 2000

I really, really need your help on this .... Again my most
gratitude to your effort. , Thanks a lot.

View Replies !

Group SQL Select Statement
Hi,I am bloody amateure and I was wondering if someone could help me editthe statement below so it groups the field SCDMASTER.SCTY_CLASS_CODEand creates one field called "total balance" equallingCUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNTSELECTCUSTODY_BALANCE.APPLICATIONCYCLEDATE,CUSTODY_BALANCE.ASSET_ID,SCDMASTER.SCTY_CLASS_CODE,CUSTODY_BALANCE.OPENING_BALANCE,CUSTODY_BALANCE.DEPOSIT_AMOUNT,CUSTODY_BALANCE.WITHDRAWAL_AMOUNTFROMFPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,CAPSREPORT.SCDMASTER SCDMASTERWHERECUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND(SCDMASTER.SCTY_CLASS_CODE Not In('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))Thanks,Andreas

View Replies !

Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization....

I have a query that looks like this:

SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)

it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View Replies !

Top 2 Entries For Each Group FLD1,FLD2,FLD3 (was &"SQL SELECT Statement&")
Dear All,
I have the following set of data, where FLD1, FLD2...FLD4 are the fields.
I want to be able to select for each group FLD1,FLD2,FLD3 ORDER BY FLD4 ASC the top 2 entries

Example :
FLD1 FLD2 FLD3 FLD4
ABC XXX 1 98
ABC XXX 1 81
ABC XXX 1 5
ABC XXX 2 1
ABC XXX 2 9
ABC XXX 2 2
BBC XXX 2 2

The result I want is :
ABC XXX 1 5
ABC XXX 1 81
ABC XXX 2 1
ABC XXX 2 2
BBC XXX 2 2

Is there a way to do it using SQL?
Thanks in advance

View Replies !

SQL Select Statement To Select The Last Ten Records Posted
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.

View Replies !

Nested Select
What's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error that inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office

View Replies !

Nested SELECT HELP....
Hi all,I have the following databasedesign :www.marinescu.dk/databasedesign.pdfwhich i have a nested SELECT on but i need some more information which idon't know how to retrieve. I have the following SELECT :SELECT DISTINCT Resource.ResourceID, Localized.ResourceValue,Localized.Font, Resource.ResourceName, Resource.Comment, Type.TypeName FROMLocalized, Resource,Type WHERE Localized.ResourceID = Resource.ResourceIDAND Resource.TypeID = Type.TypeID ORDER BY Resource.ResourceIDFor some Resources there are Rules. I will like to have a new column namedRulesText in my query where there will be shown the RuleText if there is anyfor that particular Resource.Could anybody help me here ????Best RegardsMihai Marinescu

View Replies !

Can SELECT Be Nested As
I know you can do something like:

SELECT
ColumnA,

(SELECT Columnb FROM Table Where...),

ColumnC
...

Can you select multiple columns, and how if possible, such as:

SELECT
ColumnA,

(SELECT ColumnB, ColumnC, ColumnD FROM Table Where...),

ColumnE
...

If this is possible, how would the columns be aliased?

Thanks in advance.

View Replies !

Nested Select - Help
I dont have a clue what i'm doing wrong.

SELECT Tbl_Region.REGION, [NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], [NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], [TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], [TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM (((Tbl_Region LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg, Count(qry_New_Members_HMO_All_Regions_1.CONTRACT_N UM) AS [# of New Members]
FROM (SELECT tbl_hmo.Reg, tbl_hmo.CONTRACT_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo.reg, tbl_hmo.CONTRACT_NUM

) AS qry_New_Members_HMO_All_Regions_1

GROUP BY qry_New_Members_HMO_All_Regions_1.reg
) AS NEW_HMO_CONTRACTS ON Tbl_Region.REGION = [NEW_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_New_Members_HMO_All_Regions_1.reg, Count(qry_New_Members_HMO_All_Regions_1.MEMBER_NUM ) AS [# of New Members]
FROM (SELECT tbl_hmo.reg, tbl_hmo.MEMBER_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo_History.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo.Aff_Area, tbl_hmo.MEMBER_NUM

) AS qry_New_Members_HMO_All_Regions_1
GROUP BY qry_New_Members_HMO_All_Regions_1.reg) AS 4_NEW_HMO_MEMBERS ON Tbl_Region.REGION = [4_NEW_HMO_MEMBERS].reg) LEFT JOIN (SELECT qry_Termed_Contracts_HMO_All_Regions_1.reg, Count(qry_Termed_Contracts_HMO_All_Regions_1.CONTR ACT_NUM) AS [# of Termed Contracts]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
) AS qry_Termed_Contracts_HMO_All_Regions_1
GROUP BY qry_Termed_Contracts_HMO_All_Regions_1.reg) AS TERMED_HMO_CONTRACTS ON Tbl_Region.REGION = [TERMED_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_Termed_Members_HMO_All_Regions_1.reg, Count(qry_Termed_Members_HMO_All_Regions_1.MEMBER_ NUM) AS [# of Termed Members]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
) AS qry_Termed_Members_HMO_All_Regions_1
GROUP BY qry_Termed_Members_HMO_All_Regions_1.reg)
AS TERMED_HMO_MEMBERS ON Tbl_Region.REGION = [TERMED_HMO_MEMBERS].reg;

error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'AS'.

View Replies !

Nested IF's In SQL SELECT
I am trying to get some functionality from nested IF's witin a SQL Select Statement. I do not want to create a stored procedure as I have another program that must use select statements that I will be using once I have this query written. Below is my code.

SELECT
Sales.OrderID,
Buyers.Userid,
Buyers.Email,
Sales.ShippingCo,
IF (Addresses.Company IS NULL OR Addresses.Company = '')
BEGIN
IF ( RTRIM(LTRIM(Addresses.FirstName)) IS NULL
RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName,
ELSE
RTRIM(LTRIM(Addresses.FirstName)) & ' ' & RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName,
END
ELSE
Addresses.Company AS CompanyOrName,
END

All I am trying to do is join the first and last names as the company in my table if the company doesn't exist, and then only display the last name if the first name is null.

I keep getting the error "Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'IF'."

I know I am close to geting this to work, but for some reason googling for tutorials on writing IF statements isn't helping out.

Thanks ahead of time for any help. It will be greatly appreciated.

View Replies !

Can I Use SELECT Statement To Select First 100 Record????
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?

Thanks,

Sam

View Replies !

COUNT ( FROM Nested SELECT)
HI All, I have what is most likely a simple MS SQL query problem (2005).I need to add two computed columns to a result set, both of those columns are required to provide the count of a query that contains a parmamter. (The id of a row in the result set) 3 Tables (Showing only the keys)t_Sessions-> SessionID (Unique) t_SessionActivity-> SessionID (*)-> ErrorID (Unique) t_SessionErrors-> ErrorID (1) I need to return something like (Be warned the following is garbage, hopefully you can decifer my ramblings and suggest how this can actualy be done) SELECT SessionID, (SELECT COUNT(1) AS "Activities" FROM t_SessionActivity WHERE t_SessionActivity.SessionID = t_Sessions.SessionID), (SELECT COUNT(1) AS "Errors" FROM dbo.t_Sessions INNER JOIN dbo.t_SessionActivity ON dbo.t_Sessions.SessionID = dbo.t_SessionActivity.SessionID INNER JOIN dbo.t_SessionErrors ON dbo.t_SessionActivity.ErrorID = dbo.t_SessionErrors.ErrorID WHERE t_SessionActivity.SessionID = t_Sessions.SessionID)FROM t_Sessions Any help greatfully received. Thanks

View Replies !

Multiple Nested (TOP 1 SELECT) S
Hi guys,

I have been struggling over the following problem for a few days... i was wondering if anyone could shed some light...!

I have the following query:

SELECT Field1, Field2
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.Field1 = ( SELECT TOP 1 Field1 FROM Table2 WHERE Field3='X' ORDER BY Date1)
AND Table2.Field2 = ( SELECT TOP 1 Field2 FROM Table2 WHERE Field3='X' ORDER BY Date1)

Is there a better way to do this. I was thinking of something very similar to the below query (Which doesnt work):

SELECT *
FROM Table1 A
INNER JOIN Table2 B ON (A.ID=B.ID)
INNER JOIN ( SELECT TOP 1 * FROM Table2 WHERE Field3='X' ORDER BY Date1 ) C ON (A.ID=C.ID)
WHERE
B.Field1 = C.Field1
B.Field2 = C.Field1

Any ideas?

Many thanks in advance,

TNT

View Replies !

Nested Select, Urgent
I have two tables with the following data:

TableA: Serial_No
A
B
C

TableB: Serial_No
A
B

I need to retrieve the Serial_No in TableA but does not exist in TableB, in this case, data is "C".

I have tried the following Select statements:

1.
Select TableA.SerialNo
From TableA
Where TableA.SerialNo IN
(SELECT TableA.SerialNo
From TableB
Where TableA.SerialNo <> TableB.SerialNo )

2.
Select Distinct TableA.SerialNo
From TableA
Join TableB on
TableA.SerialNo <> TableB.SerialNo

However, the two statement gives me all data, i.e., A,B,C.

How should I discard the unwanted row?

Your help is greatly appreciated.

Thanks & Regards
MMC

View Replies !

Nested Select Statements
I need help nesting select statements. Here's what I'm trying to do:

select (select e.emp_name_lf as employee, e.emp_id
from employee e, install_payroll_detail ipd
where e.emp_id = ipd.emplno)
e.emp_name_lf as username
from employee e, install_payroll_master ipm
where e.emp_id = ipm.entered_by

I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated.

Thanks!

View Replies !

Using A Current Row Value In A Nested Select
I have two tables:

1)table of customers: CustomerName, CustomerId, CustomerAddress
2)table of orders: OrderId, CustomerId, OrderAmount

I would like to have a query that returns everything from the customer table and add one column that has the amount of orders the customer has made, this is what I have so far:

CREATE PROCEDURE dbo.GetAllCutomerInfo
AS

DECLARE @OrderCount int

SELECT CustomerName, CustomerId, CustomerAddress, (SELECT COUNT(OrderId) FROM Cust_Orders WHERE CustomerId= CustomerId)
FROM Customers
ORDER BY CustomerName

Can you add a variable:

CREATE PROCEDURE dbo.GetAllCutomerInfo
AS

DECLARE @CustID int

DECLARE @OrderCount int

SELECT CustomerName, @CustID=CustomerId, CustomerAddress, (SELECT COUNT(OrderId) FROM Cust_Orders WHERE CustomerId= @CustID)
FROM Customers
ORDER BY CustomerName

Thanks for any help.

View Replies !

Temp Table Or Nested Select
hi all,
i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.

SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
INTO #ByItemID
FROM (
SELECT * FROM tblItemdetail
)AS L1
GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate

SELECT *
FROM #ByItemID
ORDER BY CustomLotNo

DROP TABLE #ByItemID

----------------------------
or maybe just use something like nested SELECT like this, but cannot work:-

select customlotno, itemid, locid(

select * from tblitemdetail
where customlotno='IN28606000'

) AS T
GROUP BY customlotno, itemid, locid

~~~Focus on problem, not solution~~~

View Replies !

Nested Select, And Table Joint
Hi Guys

Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:

OPBal| Receipt| IssTrns| Transfer| ClBal

SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )

WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid

OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc

OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost

View Replies !

Insert Query With Nested Select And Parameter
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN

View Replies !

Nested Select Statements Not Working The Way I Was Hoping...
So I have another query I can't seem to function the way I was hoping. I've learned a lot in this past month or so but I've hit another challenge. Anyways what I'm trying to do is when a user/student wants to add a new major I want to show a list of majors that are NOT already in his/her profile. So basically if I had a full list of majors:
Accounting
Computer Science
Mathematics

and the user already had Mathematics in his/her profile I'd like it to display only:

Accounting
Mathematics

Below is the layout of the tables, my attempt at the query, and then below that some example data.

Code Snippet

USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Majors]    Script Date: 04/17/2008 22:38:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Majors](
    [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Majors] PRIMARY KEY CLUSTERED
(
    [MajorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorDisciplines]    Script Date: 04/17/2008 22:38:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorDisciplines](
    [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL,
    [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED
(
    [MajorDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID]
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID])
REFERENCES [dbo].[Majors] ([MajorID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]

USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorDisciplines]    Script Date: 04/17/2008 22:38:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorDisciplines](
    [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL,
    [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED
(
    [MajorDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID]
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID])
REFERENCES [dbo].[Majors] ([MajorID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]

SELECT MajorID
FROM Majors majs
WHERE majs.MajorID NOT IN (SELECT majDis.MajorID FROM MajorDisciplines majDis WHERE majDis.MajorDisciplineID NOT IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = 0))
dbo.Majors
MajorID
Accounting
Computer Science
Mathematics

dbo.MajorDisciplines
MajorDisciplinesID   ...     MajorID                      ...
1                                    Computer Science
2                                    Accounting
3                                    Accounting
4                                    Mathematics

dbo.Student_Majors
MajorDisciplineID          StudentID
1                                  0

Oh also for the MajorIDs I don't want it to return duplicates such as the Accounting in MajorDisciplines...which I was hoping my query wouldn't do but it returns absolutely random data...

Hope someone can help!

View Replies !

Referencing Tables In Nested Select Statements
I'm just wodnering if you have two select statements, one nested inside another, can you reference tables from the outer loop?

for example, say I would like to find all employees who have at least two clients and employees and clients have a one to many relationship.

select *
from Employee e
where
(
select count(*)
from Clients c
where c.EmployeeId = e.EmployeeId
) >= 2

This obviously doesn't work - but how would i go about doing something like this?

View Replies !

Select Statements And Nested Stored Procedures
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.

Is there any way to do either of those?

View Replies !

Nested Select Query Generating Syntax Error
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:

Oracle Version:

Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)

SQL Version:

select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)

In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?

Thanks,
Lee

View Replies !

Nested SELECT Query That Also Returns COUNT From Related Table
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

CatID | Name | Description | No. Products

0001 | Cars | Blah blah blah | 5

etc etc

At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

Many thanks!

View Replies !

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian

View Replies !

How To Select The Top 1 Row In Each Set Of Group????
hai guys...am new to sql....plz help me out........ the below is my table structure.....Table Name : #temp pgm_main_category_id         pgm_sub_category_id                filepath                                                                                             17                                                       15                  photo/Writer/Content/Sports/Basketball/bb1.jpg                                                       17                                                       16                  photo/Writer/Content/Sports/Cricket/cricket1.jpg                                                     17                                                       17                  photo/Writer/Content/Sports/BaseBall/base1.jpg                                                       18                                                       18                  photo/Writer/Content/Nature/Forest/forest1.jpg                                                       18                                                       19                  photo/Writer/Content/Nature/Tree/tree1.jpg                                                           18                                                       20                  photo/Writer/Content/Nature/Flower/flower1.jpg                                                       19                                                       21                  photo/Writer/Content/Gadget/Laptop/laptop1.jpg                                                       19                                                       22                  photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg                                                 19                                                       23                  photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg                                                     17                                                       24                  photo/Writer/Content/Sports/Formula1/F1.jpg     from this table i need the query output as below......for example: This is my expected output pgm_main_category_id         pgm_sub_category_id
filepath

17
15
photo/Writer/Content/Sports/Basketball/bb1.jpg
18                                                       18                  photo/Writer/Content/Nature/Forest/forest1.jpg    19
21                  photo/Writer/Content/Gadget/Laptop/laptop1.jpg  17
16
photo/Writer/Content/Sports/Cricket/cricket1.jpg      18
19
photo/Writer/Content/Nature/Tree/tree1.jpg       19
22
photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg    17                                                       17                  photo/Writer/Content/Sports/BaseBall/base1.jpg 18                                                       20                  photo/Writer/Content/Nature/Flower/flower1.jpg 19                                                       23                  photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg              17                                                       24                  photo/Writer/Content/Sports/Formula1/F1.jpg      if anybody have an idea about this query plz send me...... i need this immediately guys.....thanks in advance......regards janu

View Replies !

Select Top, Group By
Hello!
This is my query:SELECT tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy, tblMessages.datePosted,
tblMessages.intMessGroup
FROM tblMessages INNER JOIN
tblUsersToGroups ON tblMessages.intMessGroup = tblUsersToGroups.belongsToGroup INNER JOIN
tblUsers ON tblUsersToGroups.userId = tblUsers.id
WHERE (tblUsers.userName = @strUserName)
GROUP BY tblMessages.intMessGroup, tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy,
tblMessages.datePosted
ORDER BY tblMessages.intMessGroupWhat I want to do is select the top 3 from each intMessGroup. In other cases where I'd want to Group By a computed column I could have used Rank, but since the column intMessGroup contains static values (message group id's) there should be some easy peasy way to do this, or?Thanks beforehand for any and all replies!Cheers!/Eskil

View Replies !

SELECT Using GROUP BY - Please Help Me, Please
hello,
i have two tables: Pictures and UserComments, both have PictureID column
in second table i store each comment made by users at a specific picture like so: CommentID, UserName, PictureID, Comment, Date
i am trying to make a stored procedure with @UserName input parameter witch returns Distinct Pictures whereon that user has commented (sorry for that whereon expression, is from dictionary and i don't know if it express what i want to mean) SELECT Pictures.OwnerName AS 'Owner', Pictures.Name AS 'Name of picture', COUNT(UserComments.PictureID) AS 'Comments made', Pictures.Image1
FROM Pictures INNER JOIN
UserComments ON Pictures.PictureID = UserComments.PictureID
WHERE (UserComments.UserName = @UserName)
GROUP BY UserComments.UserName, Pictures.Name, Pictures.OwnerName, Pictures.Image1
if i use this statement it shows me the picture details whereon that user has commented and if he commented more than once on the same picture the result isn't duplicated
but with that statement i can't order by UserComments.Date because i have to use GROUP BY UserComments.Date and the results will not be shown in pairs
How can i order the results by Date Desc?
sorry for my bad english, if you don't understand please tell me and i'll try to explain by examples
please help me, thanks

View Replies !

Select, Group By, Sum...
Hello,I have two tables:Student (containing columns: name, id, status, amount)Uplate (containing columns: id, student_id as foreign key, date, payement)Students make payment from time to time and that payment goes to Uplate.How to list (What is SELECT statement) to getevery studentits idstatusamounttotal payment made till today! (like sum all payement for this student)Thanks in advance.

View Replies !

How To Select The First Row For Each Group?
Hello, everyone:

There is a table as below that has three groups regarding to cus. How to select first cid for each group. Thanks.

zyt

cus cid
ALFKI10643
ALFKI10692
ALFKI10702
ALFKI10835
ALFKI10952
ALFKI11011
ANATR10308
ANATR10625
ANATR10759
ANATR10926
ANTON10365
ANTON10507
ANTON10535
ANTON10573
ANTON10677
ANTON10682
ANTON10856
AROUT10355
AROUT10383
AROUT10453
AROUT10558
AROUT10707
AROUT10741
AROUT10743
AROUT10768
AROUT10793
AROUT10864
AROUT10920
AROUT10953
AROUT11016

View Replies !

Select Top 3 By Group
I need to find the first 3 days that a product is scheduled in one of my
tables. It may be scheduled on 12 different days, but I only want the first 3.

So, based on some sample data and this thread:

http://www.dbforums.com/t1115304.html

I was able to come up with a query that does what I need.
However, I have no idea how it works. For one, what
is Count(*) counting in the query?

create table #tmpSched (
sched_date datetime,
product char(3)
)

insert into #tmpsched
select '1/1/2001', 'abc'
union all
select '1/2/2001','abc'
union all
select '1/3/2001','abc'
union all
select '1/1/2001', 'def'
union all
select '1/2/2001','def'
union all
select '1/1/2001', 'ghi'
union all
select '1/1/2001', 'jkl'
union all
select '1/2/2001','jkl'
union all
select '1/3/2001','jkl'
union all
select '1/4/2001','jkl'

SELECT a.SCHED_DATE, a.PRODUCT
FROM #tmpSched a
INNER JOIN #tmpSched b
ON a.product = b.product
AND a.sched_date > = b.sched_date
GROUP BY a.product, a.sched_date
HAVING COUNT(*) <= 3
order by a.product, a.sched_date

drop table #tmpsched

Any explanation would be appreciated.

EDIT: Wanted first 3, not last 3. Changed "a.sched_date < = b.sched_date" to a.sched_date > = b.sched_date

View Replies !

Select With Group
Hi, on a table with two fields DATETIME and PLATAFORM I need to meka thefollowing queries

One return the total grouped by hour like

'1:00:00.000'    -    5    /* Registries from 0:00:00.000 to 1:00:00.000 from the day choose */

'2:00:00.000'    -    12/* Registries from 1:00:00.001 to 2:00:00.000 from the day choose */

'3:00:00.000'    -    5/* Registries from 2:00:00.001 to 3:00:00.000 from the day choose */

'4:00:00.000'    -    12/* Registries from 3:00:00.001 to 4:00:00.000 from the day choose */

and on....

the other is group by week, month(with the 30, 31 days of the month

Like

01/05/2006   -- 5

02/05/2006   -- 5

03/05/2006   -- 5

and year(with the 12 months)

january - 5

february   -- 12

What the easiest and best way to do that...

I tried with cursor but it takes too much time to complete.

View Replies !

SELECT, MAX(), Group By

Hi, I have two tables Employee and Department.
Employee                            Department

-EmployeeID                   -DepartmentID
-EmployeeName             -DepartmentName
-Salary
-DepartmentID

I want to SELECT each departments EmployeeName, who has max salary.

SELECT EmployeeName,MAX(Salary) FROM Employee GROUP BY DepartmentID

I know it won't work. I use CURSOR to solve this. But is there any easy way to do this. Using SELECTs
.

View Replies !

SQL Server 2005 Reporting Svcs - How To Use Dataset 1 Info To Drive Nested Table Select
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!

industry         Customer      Year        OCT    NOV   DEC
001 - Signposts
                     M12345
                        Part 1
                                         2006        5          6         2
                                         2007        0          3         1

    Part 2
                                         2006        4          3         0
                                         2007        1          0         7

            Customer M12345 totals
                                        2006         9         9         2
                                        2007         1         3         8

M45678
Part 3
                                        2007        8          4         7
                                        2008        3          4         8

Part 4
                                        2006        3          8         7
                                        2007        5          6         6

Customer M45678 totals
                                       2006         3         8         7
                                       2007        13       10       13
                                       2008          3         4        8
Industry 001 totals

2006       12      17         9
2007       14      13        21
2008         3       4          8

View Replies !

Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View Replies !

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys,
I have the query below (running okay):

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:

Field01           Field02

-------------          ----------------------

192473           8461760

192474           22810

Because other reasons. I need to modify that query to:

Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810

Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View Replies !

Select Query By Group
Hello All
I have table with 3 columns and Datas like this
LRNo              LRAmount   LRType
L001                100                TP
L002                250                PA
L003                150                AC
L004                275               TP
L005                400                PA
L006                125                TP

I need a query to get output like this

LRtype      Count           TotalAmount
TP               3                    500
PA               2                   650
AC              1                   150

Where count is total no of records grouped for LR Type and totalAmount is Sum of TotalAmount grouped by LRType

please help me
Thanks in Advance

View Replies !

Select Max() And Group By Problem
I have this query.
SELECT ForeignKey, MAX(Version) AS Latest_VersionFROM tblContentWHERE (Published = 'True')GROUP BY ForeignKeyORDER BY ForeignKey
It works perfectly just like it is above. It returns 20 records choosing the maximum value in the version column for each value in the ForeignKey column. Here is a sample of the result set.
ForeignKey     Latest_Version
1                    2
2                    2
3                    1
Then when I try to add other fields to the select and group by clauses I hit a problem. Here is the new query
SELECT ForeignKey, MAX(Version) AS Latest_Version, TitleFROM tblContentWHERE (Published = 'True')GROUP BY ForeignKey, TitleORDER BY ForeignKey
Now it is returning 22 records in the result set Here are the relevant records.
ForeignKey     Latest_Version Title
1                    1                    Title1
1                     2                     Title2
2                    1                       Title3
2                    2                       Title4
3                    1                      Title5
I know it is necessary to add each column in the select clause to the group by clause but that seems make group by not work at all. Am I going about this the wrong way. Basically I want to return only the ForeignKey records that have the highest version. This table does have a foreign key relationship with another table. Should I be doing this with a join instead somehow?
Thanks, Laura

View Replies !

Select And Display The Max Value From A Group In Sql?
hi
i have a database that has multiple records under the same id (each time a record is updated, it's assigned a date_value), i'm wanting one row to be generated using the data from the most recent date_value field.
a sample of the database looks something like this:
ID date_value data1 24 march info11 25 march info2
my statement looks like this:select max(date_value), data from table_name where data is not null group by id
this seems to bring up the following error for all the fields not in the group by part of the code:Column 'COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
what am i doing wrong?
TIA!

View Replies !

SELECT COUNT Of MAX (GROUP BY)
Hello

I want to get the COUNT of

SELECT MAX(id) AS ids, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

SELECT COUNT(MAX(id) AS ids) AS countIds, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

doesnt work

thank you

View Replies !

SQL Select Last Record Per Group
I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.

TABLE1 info
ID1 fname1 lname1 DOB1
ID2 fname2 lname2 DOB2
ID3 fname3 lname3 DOB3

TABLE2 info
ID1 Visit2
ID1 Visit3
ID1 Visit1
ID1 Visit4
ID2 Visit1
ID2 Visit2
ID3 Visit1

I need a view or SP to return the following:

ID1 fname1 lname1 dob1 visit4
ID2 fname2 lname2 dob2 visit2
ID3 fname3 lname3 dob3 visit1

It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!
thx

View Replies !

Select Top N, Group By Query
Hi All,

I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.

I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?

SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]

TIA.

View Replies !

Select Top N, Group By Query
Hi All,

I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.

I have been able to create the results I want using UNION, but this is not practical when there is more than a few groups. I think it should be possible using a derived table, but I am not sure how to do it! Below is the code the returns the results I am after, any ideas how to achieve this another way?

SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]

TIA.

View Replies !