Donate

Converting Rows To Columns With DateTime Column In SQL Server

-
Good evening!
An issue was brought up by a developer on how to convert rows of DateTime values into columns using MS Access DB with functionality similar to pivoting of MSSQL.The problem has an added complexity since specific portions of the DateTime will be extracted too. See original post here: Converting Columns to Rows in MS Access. The data presented is similar to a Timesheet entry wherein an employee has login/logout records. The solution presented in the thread involves usage of subquery and joins designed for MS Access Db. I modified the accepted answer to a T-SQL query using Group By, Max() function, Coalesce() and without subquery. 
SELECT 
  LogInfo.fldMachineId, 
  Year(LogInfo.fldLogDate) as [Year], 
  Month(LogInfo.fldLogDate) as [Month], 
  Day(LogInfo.fldLogDate) as [Day],
  Max(Coalesce(Convert(varchar(5), T2.fldLogDate, 108),'')) as TimeIn1,
  Max(Coalesce(Convert(varchar(5), T3.fldLogDate, 108),'')) as TimeOut1, 
  Max(Coalesce(Convert(varchar(5), T4.fldLogDate, 108),'')) as TimeIn2, 
  Max(Coalesce(Convert(varchar(5), T5.fldLogDate, 108),'')) as TimeOut2
FROM LogInLogOut as LogInfo
  LEFT JOIN LogInLogOut as T2 ON (LogInfo.fldMachineId = T2.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T2.fldLogDate) AND T2.LogStatus = 1 AND DatePart(Hour, T2.fldLogDate) < 11)
  LEFT JOIN LogInLogOut as T3 ON (LogInfo.fldMachineId = T3.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T3.fldLogDate) AND T3.LogStatus = 2 AND DatePart(Hour, T3.fldLogDate) < 15)
  LEFT JOIN LogInLogOut as T4 ON (LogInfo.fldMachineId = T4.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T4.fldLogDate) AND T4.LogStatus = 1 AND DatePart(Hour, T4.fldLogDate) >= 11)
  LEFT JOIN LogInLogOut as T5 ON (LogInfo.fldMachineId = T5.fldMachineId AND Convert(date, LogInfo.fldLogDate) = Convert(date, T5.fldLogDate) AND T5.LogStatus = 2 AND DatePart(Hour, T5.fldLogDate) >= 15)
Group By LogInfo.fldMachineId, 
         Year(LogInfo.fldLogDate), Month(LogInfo.fldLogDate), Day(LogInfo.fldLogDate)
Another approach that I was thinking of will be using the partitioning logic of T-SQL. :-)

Comments

Post a Comment

Donate

Popular Posts From This Blog

WPF CRUD Application Using DataGrid, MVVM Pattern, Entity Framework, And C#.NET

-
Image
Good day to all! Here's an example of a WPF CRUD (Create,Update and Delete) project using the DataGrid control, ADO.NET Entity Framework 6.x, C#.NET and Model–View–Viewmodel(MVVM) architectural pattern. This post was based from this tutorial WPF CRUD With DataGrid, Entity Framework And C#.NET except that we are now using the MVVM framework. The steps below are pretty straightforward and easy to follow. I. Project Setup 1. Add a table called Students in you database. The complete script is found in this post WPF CRUD With DataGrid, Entity Framework And C#.NET 2. Create a WPF Project and add four folders called DataAccess, Model, View and ViewModel. 3. Your project structure may look similar with the screenshot provided below. II. Coding The Model and Repository Class 1. Inside the Model folder, add an ADO.NET Entity Data Model that connects to the Students table in your database. On my part, I named it StudentModel . 2. For the connectionstring name in App.config file, I
Read more

How To Insert Or Add Emojis In Microsoft Teams Status Message

-
Image
Good day Gents! I was trying to update my Microsoft Teams status and was wondering if I can add or insert emoji. For editing or adding new teams status, only text fields are available and there's no emoji icon similar to writing a teams message. After googling for a few minutes, I found this link Emoji's in status which is a feature request in Microsoft Teams User's Voice Forum. So far this upgrade request only has thirty votes which is relatively small and may be considered a low priority for the product team to implement. Given the limitations at hand, I tried several approach including copying an emoji in a chat message which didn't work and later found a solution using Microsoft Outlook 365. First is to create a new email message in Microsoft Outlook and press (Window Key + .). The emoji dropdown control will appear. Next is to select the emoji that you wanted to add to Teams. Copy the emoji from the newly created email message template and paste that to Micr
Read more

Pass GUID As Parameter To Action Using ASP.NET MVC ContribGrid

-
Hi, In a scenario where in you want to pass a GUID object to an Action Parameter, the parameter might contain a null value. Since Guid is not nullable, you can't simply just pass it to an action parameter because Action parameters require nullable and reference types. You might encounter an error like this: An optional parameter must be a reference type, a nullable type, or be declared as an optional parameter Assuming you have an aspx view below: <div> <% Html.Grid(Model).Columns (c => { c.For(m => m.Price); c.For(m => m.ProductName); c.For(m => m.ProductID); c.For( "View Person" ).Named( "" ).Action(m => { %> <td> <%= Html.ActionLink( "View Person" , "ProductDetail" , "Home" , new { @id = m.ProductID.ToString() }) %> </td> <% }); %> <% }).Render(); %> </div> and an action
Read more