I want row wise and column wise Total
Create table #Sections (SecID int, Secnam varchar(50))
create table #ItemMasterFile(CodeItem int, Descriptionitem varchar(max),SecID int,CID int)
create table #Probale (BID int, CodeItem int, prdqtyint,EntryDate date)
insert into #Sections
values
(1, 'HR'),
(2, 'Baby'),
(3, 'Ladies'),
(4, 'Mix Rammage'),
insert into #ItemMasterFile
values
(1, 'A', '1'),
(2, 'B', '2'),
(3, 'C', '3'),
(4, 'D' ,'4')
insert into #Probale
values
(1, '1', '1', '5/01/2019 '),
(2, '2', '1', '5/01/2019 '),
(3, '3', '1', '5/01/2019 '),
(4, '2', '1', '5/02/2019 '),
(5, '3', '1', '5/02/2019 '),
(6, '4', '1', '5/02/2019 '),
(6, '1', '1', '5/03/2019 '),
(7, '2', '1', '5/04/2019 '),
(8, '4', '1', '5/05/2019 '),
| Item Name | 01/05/2019(Vertical) | 02/05/2019 | 03/05/2019 | 4/05/2019 | 5/05/2019 | Total |
|---|---|---|---|---|---|---|
| A | 1 | 0 | 1 | 0 | 0 | 2 |
| B | 1 | 1 | 0 | 1 | 0 | 3 |
| C | 1 | 1 | 0 | 0 | 0 | 2 |
| D | 0 | 1 | 0 | 0 | 1 | 2 |
| Total | 3 | 3 | 1 | 1 | 1 | 9 |
here is my store procedure in which data is getting display but i want to display row and column wise total.
**ALTER Procedure [dbo].[Pivot_Item1] @StartDate Date,
@Enddate Date
AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT Distinct Entrydate INTO #Dates26 FROM Probale WHERE EntryDate BETWEEN @StartDate AND @Enddate ORDER BY EntryDate
SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(2),EntryDate) )
FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate
--SET @cols = STUFF(@cols, 1, 1, '')
SET @query =
N'SELECT *
FROM (SELECT Descriptionitem,Probale.prdqty,
DATEPART(DAY, Probale.EntryDate)as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat'
exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate
END**