Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Aggregation With Condition Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 3:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:22 AM
Points: 1, Visits: 28
Below is the scenario in which I have a question:

Fact/Measures Table with the following columns:

Fact_ID - Key - bigint
Document_ID - bigint
Version_ID - bigint
Item_ID - bigint
Amount1 - money
Amount2 - money
Document_Key - (Foreign Key - Document)

Dimensions:

Documents

Document_Key - Key- bigint
Document_ID - bigint
Version_ID - bigint
Payment_Date_Key - (Foreign Key - Date_Key)


Date

Date_Key - Key

The Fact Table contains multiple Version_IDs for a certain Document_ID. Each Document_ID->Version_ID combination points to a specific Document_Key in Documents.

I want to sum the Amount columns in the Fact Table in such a way that the Maximum Version_ID of a Document_ID is selected for a given Date Range.

How do I do that?

Thanks in advance for the help.

Afaq
Post #1511033
Posted Monday, November 4, 2013 8:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 13,007, Visits: 12,421
Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1511135
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse