Using Rollup - 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

General SQL Server Forums

New to SQL Server Programming

Using Rollup

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-03 : 09:49:03

Hey guys,

I'm working on this query in order to sort of the category based on their values. However, when I was using roll up function it's not sort out for me. This is the query.

SELECT TOP 5

((([P21].[dbo].p21_view_inventory_value_report.qty_on_hand - [P21].[dbo].p21_view_inventory_value_report.special_layer_qty) * [P21].[dbo].[p21_view_inventory_value_report].[cost]) + [P21].[dbo].p21_view_inventory_value_report.special_layer_qty) AS extended_value
,CASE
WHEN (SUM (CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6)THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) = 0) THEN 'Hardcore'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) THEN 'Good'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM (CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -4, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM,0, getdate()),112),6) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) THEN 'NNU'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) > SUM(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM,0, getdate()),112),6) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END)
AND ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty)<= SUM(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6)THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END)THEN 'Overstock'
ELSE 'Surplus' END 'Status'

FROM
[P21].[dbo].[p21_view_inventory_value_report]
LEFT OUTER JOIN [P21].[dbo].p21_view_inv_loc ON ([P21].[dbo].p21_view_inv_loc.inv_mast_uid = [P21].[dbo].p21_view_inventory_value_report.inv_mast_uid) AND
([P21].[dbo].p21_view_inv_loc.location_id = [P21].[dbo].p21_view_inventory_value_report.location_id)
LEFT OUTER JOIN [P21].[dbo].[p21_inventory_usage_all_view] ON ([p21_view_inventory_value_report].inv_mast_uid = [p21_inventory_usage_all_view].inv_mast_uid AND
[p21_view_inventory_value_report].location_id = [p21_inventory_usage_all_view].location_id)

Group BY

([P21].[dbo].[p21_view_inventory_value_report].[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty)
,[P21].[dbo].[p21_view_inventory_value_report].[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty -[P21].[dbo].p21_view_inv_loc.qty_allocated
,(([P21].[dbo].[p21_view_inventory_value_report].qty_on_hand - [P21].[dbo].p21_view_inventory_value_report.special_layer_qty) * [P21].[dbo].[p21_view_inventory_value_report].[cost]) + [P21].[dbo].p21_view_inventory_value_report.special_layer_qty WITH ROLLUP

The result came out like this.

Extended Value Status
0.045000 Hardcore
NULL Hardcore
NULL Hardcore
495.250000 Hardcore
631.500000 NNU

How can I sort them out as one sum extended value for Hardcore and NNU as two column. Because the data is very big to retrieve therefore I only use top 5. Assuming that I have total of 50000 data and only 5 status. I want to retrieve it as 5 rows with total on extended value.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-03 : 19:09:23

then why not group by status and take sum(extended value field)?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-05 : 09:03:50

That's not gonna give you the correct value, however, the status contains the aggregation function so that it's not working with group by.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 09:40:29

why? as per your sample data you were asking for one row per status isnt it? then it should be grouped on status. please elaborate with some sample data if your requirement was different from this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources