Regarding Grouping - 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

Regarding Grouping

Author

Topic

niraj
Starting Member

2 Posts

Posted - 2009-08-28 : 03:49:20

Hi All

Please Help me I Am writing a query which have Column MonthNAme,Dept,Vendor Name and another column are [MTDAmount],MTDMargin,[MTD_Net_Sales],[YTDAmount],YTDMargin,[YTD_Net_Sales]

I want to group these MTD and YTD amount and Mrgin etc for each
vendor and dept wise for each month when I Run The Below Query It Gives me result only grouping month wise and dept wise and vendor wise but i want Year wise also ..that is for a particular vendor_Name the amount should be clubed for each dept ......how to do please help

SELECT DateName( MONTH,SS.Date)AS MonthName,
Left(SKU.[Item Catagory],2) AS Dept,
V.[Name] as Vendor_Name
,SUM([Amount]) AS [MTDAmount]
,SUM(CASE WHEN ISNULL(Fixed_Perc_Margin,0)=0 THEN [Net Sales Amount] - [Net Cost Amount]
ELSE [Net Sales Amount] * Fixed_Perc_Margin/100 END) AS MTDMargin
,SUM([Net Sales Amount]) AS [MTD_Net_Sales]
-- ,SUM([Amount]) AS [YTDAmount]
-- ,SUM(CASE WHEN ISNULL(Fixed_Perc_Margin,0)=0 THEN [Net Sales Amount] - [Net Cost Amount]
-- ELSE [Net Sales Amount] * Fixed_Perc_Margin/100 END) AS YTDMargin
-- ,SUM([Net Sales Amount]) AS [YTD_Net_Sales]
FROM [dbo].[Sales_Summary] SS (Nolock)
INNER JOIN dbo.StockkeepingUnit SKU
ON SKU.[Item No_]=SS.Item_No
LEFT JOIN Vendor_details V
ON V.Vendor_No=SKU.[Vendor No_]
LEFT OUTER JOIN (
SELECT Item_No, Fixed_Perc_Margin FROM [HM_Integrated].dbo.Fixed_Margin_Items
WHERE [Location_Code]='2GJVD101'
)FMI
ON SS.Item_No= FMI.Item_No
WHERE SS.Date>='04/01/2009'
AND Left(SKU.[Item Catagory],2) NOT IN ('88','99')
GROUP BY DateName(MONTH,SS.Date),Left(SKU.[Item Catagory],2),V.Name
ORDER BY DateName(MONTH,SS.Date),Left(SKU.[Item Catagory],2),V.Name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 06:50:48

SELECT DateName( MONTH,SS.Date)AS MonthName,Datepart( year,SS.Date)AS [Year],
Left(SKU.[Item Catagory],2) AS Dept,
V.[Name] as Vendor_Name
,SUM([Amount]) AS [MTDAmount]
,SUM(CASE WHEN ISNULL(Fixed_Perc_Margin,0)=0 THEN [Net Sales Amount] - [Net Cost Amount]
ELSE [Net Sales Amount] * Fixed_Perc_Margin/100 END) AS MTDMargin
,SUM([Net Sales Amount]) AS [MTD_Net_Sales]
-- ,SUM([Amount]) AS [YTDAmount]
-- ,SUM(CASE WHEN ISNULL(Fixed_Perc_Margin,0)=0 THEN [Net Sales Amount] - [Net Cost Amount]
-- ELSE [Net Sales Amount] * Fixed_Perc_Margin/100 END) AS YTDMargin
-- ,SUM([Net Sales Amount]) AS [YTD_Net_Sales]
FROM [dbo].[Sales_Summary] SS (Nolock)
INNER JOIN dbo.StockkeepingUnit SKU
ON SKU.[Item No_]=SS.Item_No
LEFT JOIN Vendor_details V
ON V.Vendor_No=SKU.[Vendor No_]
LEFT OUTER JOIN (
SELECT Item_No, Fixed_Perc_Margin FROM [HM_Integrated].dbo.Fixed_Margin_Items
WHERE [Location_Code]='2GJVD101'
)FMI
ON SS.Item_No= FMI.Item_No
WHERE SS.Date>='04/01/2009'
AND Left(SKU.[Item Catagory],2) NOT IN ('88','99')
GROUP BY DateName(MONTH,SS.Date),Datepart( year,SS.Date),Left(SKU.[Item Catagory],2),V.Name
ORDER BY DateName(MONTH,SS.Date),Left(SKU.[Item Catagory],2),V.Name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources