getting day values by month

[canleveent]

CREATE TABLE [dbo].[Persons](
[Id] [int] NOT NULL,
[Name] [nvarchar](15) NULL,
[Lastname] [nvarchar](15) NULL,
[SpecialCode] [nvarchar](15) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
CONSTRAINT [PK_dbo.Persons] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--
INSERT INTO [dbo].[Persons]
([Name]
,[Lastname]
,[SpecialCode]
,[StartDate]
,[EndDate])
VALUES
('Adam','xxxx',12345678911,'2019-09-01','2019-09-10'),
('Adrian','xxxx',12435678910,'2019-09-01','2019-09-10'),
('Bart','xxxx',13246257811,'2019-09-01','2019-09-10');
GO


--

CREATE TABLE [dbo].[WorkingHours](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PersonId] [int] NOT NULL,
[WrDate] [datetime] NULL,
[DWorking] [decimal](2, 1) NULL,
[OwerTime] [decimal](2, 1) NULL,
CONSTRAINT [PK_dbo.WorkingHours] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--

INSERT INTO [dbo].[WorkingHours]
([PersonId]
,[WrDate]
,[DWorking]
,[OwerTime])
VALUES
(1,'2019-09-01',1.0,2.0),
(2,'2019-09-01',1.0,2.0),
(3,'2019-09-01',1.0,1.0),
(1,'2019-09-02',1.0,2.0),
(2,'2019-09-02',1.0,2.0),
(3,'2019-09-02',1.0,2.0),
(1,'2019-09-04',1.0,2.0),
(2,'2019-09-04',1.0,2.0),
(3,'2019-09-04',1.0,2.0),
(1,'2019-09-05',1.0,2.0),
(2,'2019-09-05',1.0,2.0),
(3,'2019-09-05',1.0,2.0),
(1,'2019-09-06',1.0,2.0),
(2,'2019-09-06',1.0,2.0),
(3,'2019-09-06',1.0,2.0),
(1,'2019-09-09',1.0,2.0),
(2,'2019-09-09',1.0,2.0),
(3,'2019-09-09',1.0,2.0),
GO

DataTable tbl = new DataTable();
new SqlDataAdapter(@"SELECT * FROM (
SELECT DAY([WrDate]) as [Day1], Dworking
FROM [dbo].[WorkingHours]
WHERE [PersonId] = 1
) AS D
PIVOT(
SUM(DWorking) FOR [Day1] IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS P;",
@"server=.\SQLExpress;Database=dbName;Trusted_Connection=yes").Fill(tbl);
Hi
i couldn't do this code as ef or linq, i need your help,
Thank you in advance for your trouble,

Continue reading...