ALTER Procedure Chart_daily1
(
@DateD VARCHAR(50)
,@Department VARCHAR(50)
)
AS
Begin
IF @Department = 'ALL'
BEGIN
SELECT
CONVERT(char(7), date, 120) AS 'Month',
DATEPART(DAY, Date) AS 'Day',
SUM(Price) AS 'Total' ,Department
FROM [dbo].[Expenses]
where CONVERT(char(7), date, 120) = @DateD
GROUP BY DATEPART(DAY, Date),
CONVERT(char(7), date, 120) ,
department
ORDER BY
Department,
'Month',
'Day'
END
ELSE
SELECT
CONVERT(char(7), date, 120) AS 'Month',
DATEPART(DAY, Date) AS 'Day',
SUM(Price) AS 'Total'
,Department
FROM [dbo].[Expenses]
where CONVERT(char(7), date, 120) = @DateD and Department =@Department
GROUP BY DATEPART(DAY, Date),
CONVERT(char(7), date, 120) ,
department
ORDER BY
Department,
'Month',
'Day'
End