none
getting day values by month RRS feed

  • Question

  • 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,


    Thursday, December 26, 2019 7:12 AM

Answers

  • Hi canleveent,

    Thank you for posting here.

    According to your description, you seem to want to use Linq to achieve similar functions to the SQL statement in the article.

    If so, please refer to the code below.

            static void Main(string[] args)
            {
                String connString = @"ConnString";
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("select * from WorkingHours",connection))
                    {
                        DataTable dataTable = new DataTable();
                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                        sqlDataAdapter.SelectCommand = command;
                        sqlDataAdapter.Fill(dataTable);
    
                        var result = from row in dataTable.AsEnumerable()
                                     group row by row["WrDate"] into g
                                     select new
                                     {
                                         Day = DateTime.Parse(g.Key.ToString()).Day,
                                         Value = g.Max(x=>x["Dworking"])
                                     };
    
                        foreach (var item in result)
                        {
                            Console.WriteLine(item.Day+" "+item.Value);
                        }
                    }
                    Console.WriteLine("Press any key to continue...");
                    Console.ReadKey();
                }
            }

    If not, please describe your problem in more detail so that we can understand your thoughts better.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Friday, December 27, 2019 3:22 AM

All replies

  • I cannot tell if you're trying to do this in SQL or .NET.

    For SQL put your query into SSMS or Azure Data Studio and get it working there. Questions related to SQL can be posted in the SQL forums.

    On the .NET Side you'd just read the day as an int but since you're using a SqlDataAdapter it would handle that automatically for you.


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, December 26, 2019 2:57 PM
    Moderator
  • Hi canleveent,

    Thank you for posting here.

    According to your description, you seem to want to use Linq to achieve similar functions to the SQL statement in the article.

    If so, please refer to the code below.

            static void Main(string[] args)
            {
                String connString = @"ConnString";
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("select * from WorkingHours",connection))
                    {
                        DataTable dataTable = new DataTable();
                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                        sqlDataAdapter.SelectCommand = command;
                        sqlDataAdapter.Fill(dataTable);
    
                        var result = from row in dataTable.AsEnumerable()
                                     group row by row["WrDate"] into g
                                     select new
                                     {
                                         Day = DateTime.Parse(g.Key.ToString()).Day,
                                         Value = g.Max(x=>x["Dworking"])
                                     };
    
                        foreach (var item in result)
                        {
                            Console.WriteLine(item.Day+" "+item.Value);
                        }
                    }
                    Console.WriteLine("Press any key to continue...");
                    Console.ReadKey();
                }
            }

    If not, please describe your problem in more detail so that we can understand your thoughts better.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Friday, December 27, 2019 3:22 AM
  • Hi canleveent,

    Thank you for posting here.

    According to your description, you seem to want to use Linq to achieve similar functions to the SQL statement in the article.

    If so, please refer to the code below.

            static void Main(string[] args)
            {
                String connString = @"ConnString";
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("select * from WorkingHours",connection))
                    {
                        DataTable dataTable = new DataTable();
                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                        sqlDataAdapter.SelectCommand = command;
                        sqlDataAdapter.Fill(dataTable);
    
                        var result = from row in dataTable.AsEnumerable()
                                     group row by row["WrDate"] into g
                                     select new
                                     {
                                         Day = DateTime.Parse(g.Key.ToString()).Day,
                                         Value = g.Max(x=>x["Dworking"])
                                     };
    
                        foreach (var item in result)
                        {
                            Console.WriteLine(item.Day+" "+item.Value);
                        }
                    }
                    Console.WriteLine("Press any key to continue...");
                    Console.ReadKey();
                }
            }

    If not, please describe your problem in more detail so that we can understand your thoughts better.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    hi Timon Yang,

    thank you so much,

    I've shared the sql data to be a sample data.
    I don't want to use "DataTable" because my project is linked to sql with "app.config". Is it possible?

    Best Regards,

    can

    Friday, December 27, 2019 12:04 PM
  • Note that you can rewrite your sum as

        SUM(DWorking) FOR Day1 BETWEEN 1 AND 31

    but since Day1 is the result of the DAY function, it's silly.  It's impossible for Day1 to have any value outside of that range.  Just use SUM(DWorking).


    Tim Roberts | Driver MVP Emeritus | Providenza & Boekelheide, Inc.

    Friday, December 27, 2019 8:53 PM
  • Hi canleveent,

    Thank you for posting here.

    According to your description, you seem to want to use Linq to achieve similar functions to the SQL statement in the article.

    If so, please refer to the code below.

            static void Main(string[] args)
            {
                String connString = @"ConnString";
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("select * from WorkingHours",connection))
                    {
                        DataTable dataTable = new DataTable();
                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                        sqlDataAdapter.SelectCommand = command;
                        sqlDataAdapter.Fill(dataTable);
    
                        var result = from row in dataTable.AsEnumerable()
                                     group row by row["WrDate"] into g
                                     select new
                                     {
                                         Day = DateTime.Parse(g.Key.ToString()).Day,
                                         Value = g.Max(x=>x["Dworking"])
                                     };
    
                        foreach (var item in result)
                        {
                            Console.WriteLine(item.Day+" "+item.Value);
                        }
                    }
                    Console.WriteLine("Press any key to continue...");
                    Console.ReadKey();
                }
            }

    If not, please describe your problem in more detail so that we can understand your thoughts better.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.




    Hi again,

    I've shared the sql data as a sample data.
    My project is linked to "app.config" with sql
    String connString = @ "ConnString"; the connection name from "app.config"?
    because I'm working with entity framwork.
    I'm not very good at connection matters, please forgive me if my question is inappropriate.

    Saturday, December 28, 2019 2:09 PM
  • Let's cut to the chase, what do you want from us? Are you trying to convert the SQL to LINQ? Are you trying to get the data from the database using LINQ, DataTable or anything? What does a config file have anything to do with this? 

    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, December 28, 2019 4:01 PM
    Moderator
  • Let's cut to the chase, what do you want from us? Are you trying to convert the SQL to LINQ? Are you trying to get the data from the database using LINQ, DataTable or anything? What does a config file have anything to do with this? 

    Michael Taylor http://www.michaeltaylorp3.net

    yes you're right, you'd get it with linq.  because the DataTable will need a separate connection address.

    Can you help me?

    Please

    Saturday, December 28, 2019 7:34 PM
  • Not sure how we got onto a DataTable discussion because LINQ doesn't care. So how are you reading that data from your DB into memory? Are you using SqlCommand, EntityFramework, etc? While this has little to do with the actual problem it does impact the structure of the data which is important to LINQ so if you can post the data access code you're currently running to get the data from the DB into your app we can then apply Timon's LINQ logic to it.

    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, December 28, 2019 8:38 PM
    Moderator
  • Hi canleveent,

    Thanks for your feedback.

    If you just don't know how to get the connection string written in App.config, you can use the following code. Then use Linq as I replied before.

    App.config:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
        </startup>
      <connectionStrings>
        <add name="Test" connectionString="Data Source=.;Initial Catalog=OmidPayamak;Integrated Security=True" 
             providerName="System.Data.SqlClient" />
      </connectionStrings>
    </configuration>

    C# code:

     string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;

    And like Michael said, it will be better for us to deal with this issue if you could provide more information.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 30, 2019 8:06 AM