Group totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Group totals

Author

Topic

Plaice
Starting Member

20 Posts

Posted - 2010-02-10 : 08:55:16

Ok, think I have quite a simple problem but can't seem to get my head around it.


SELECT        mrpout.qty, mrpout.duedate, stock.desc1, stock.desc2, stock.desc3, stock.desc4, mrpout.partnumber
FROM            mrpout, stock
WHERE        mrpout.partnumber = stock.partnumber

Very basic query pulling out some lines which are then in a table grouped by Part Number.

Then from the date I'm using an expression:


=IIf(((Fields!duedate.Value) <= (Globals!ExecutionTime) OR (Month(Fields!duedate.Value)) = (Month(Globals!ExecutionTime))),"ASAP",(MonthName(Month(Fields!duedate.Value),False)))

This changes the date to a month or ASAP depending on the expression.

Now there's multiple lines for each part number and i need to group these so that the qty is shown for ASAP (anything in the current month or before) or for each following month after.

Tried a few things but can't make it work. Should I be doing this in the query or via the table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 09:15:01

have you applied any grouping in your table now?

------------------------------------------------------------------------------------------------------
SQL Server MVP

Plaice
Starting Member

20 Posts

Posted - 2010-02-10 : 09:16:21

Yes, the table is grouped by Part Number.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 09:32:20

so you want totals of each months as different columns? like

ASAP Mar Apr...

------------------------------------------------------------------------------------------------------
SQL Server MVP

Plaice
Starting Member

20 Posts

Posted - 2010-02-10 : 09:46:59

So far I have 5 columns. Snap shot below:

Where there is the same Part Number and Month Required values I'd like to Sum the Qtys together to give one line instead of multiple lines.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 10:18:31

in that case you wont be able to display all the dates. In such case what date you want to show against the single line?

------------------------------------------------------------------------------------------------------
SQL Server MVP

Plaice
Starting Member

20 Posts

Posted - 2010-02-10 : 10:25:31

I don't really need to see the Req Date column but I do need to see the Month Required column.

I just left the Req Date column in whilst building the report for sense checking.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 10:35:28

ok then its just a matter to apply grouping on composite set PartNumber,Description,Monthrequired and applying SUM() over QtyReq in expression

------------------------------------------------------------------------------------------------------
SQL Server MVP

Plaice
Starting Member

20 Posts

Posted - 2010-02-10 : 11:33:21

Excellent, got it working now.

Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 11:38:44

great
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources