Hello, I am new to Transact SQL and am not sure how to get the proper result set. If there is OE 1 and only EP 1 for the same sysprojn (see 3000021), I only want to select the row with the OE 1. If there are more EP rows for the same sysprojn (see 3000024) then the sum(bookd) of the EP rows equals the bookd of the OE. In that case, I need to select all the EP rows and the OE row. Any help would be greatly appreciated. Thank you.
sysprojn type no bookd
3000020 OE 1 201684
3000021 EP 1 1633842
3000021 OE 1 1633842
3000022 OE 1 850731
3000023 EP 1 201684
3000023 EP 2 0
3000023 OE 1 201684
3000024 EP 1 1152501
3000024 EP 2 481341
3000024 OE 1 1633842
3000025 OE 1 7655823
3000026 EP 1 1152501
3000026 EP 2 481551
3000026 EP 3 -210
3000026 OE 1 1633842
Difficult Query
Hot Topic
- Watermark Generation is Invalid Error
- Creating DTS
- CPU to slow for SQL Server 2005 express???
- Data source column discovery at runtime? Help much appreciated
- Simultaneous DB access
- Problem during the process database
- Problem set up connection string to SQL server (Using ADO in VB)
- ReportViewer with Authentication
- Custom Parameter Page with Report Manager
- Problem extracting data from multiple list boxes
SQL Server
- Audit task
- Attach Database problem
- Foreach Loop Container in SSIS
- Generate Scripts option in SQL 2005 Management Studio
- How to identify which version we have?
- Reporting Services error
- Cannot open a connection with SQL Server 2005 Developer Edition.
- External Stylesheet in a database
- Creating report based on parent-child dimension
- Page or File Restore - Warning Message
Difficult Query
Mu0n
GregQuinn
Brandon Taylor
Do you need to perform the calculation, or just limit the rows My interpretation was the latter, in which case you just need to remove any EP rows where there aren't at least 2 EP records. If so:
SELECT *
FROM <some_table>
MINUS
SELECT *
FROM <some_table>
WHERE type='EP'
AND sysprojn in
(SELECT sysprojn, COUNT(sysprojn)
FROM <some_table>
WHERE type='EP'
GROUP BY sysprojn
HAVING COUNT(sysprojn)<2)
GO
Hope this helps,
Josh