Sql Query
Hi All, I have a requirement. want output data like below screen print CREATE TABLE [dbo].[Source_Table]( [Product] [varchar](10) NULL, [Trans_type] [varchar](10) NULL, [SalesCode] [varchar](10) NULL, [Profitamount] [int] NULL ) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type1', N'A', 7) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product2', N'Type1', N'B', 3) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type2', N'C', 4) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product2', N'Type2', N'A', 5) GO INSERT [dbo].[Source_Table] ([Product], [Trans_type], [SalesCode], [Profitamount]) VALUES (N'Product1', N'Type1', N'D', 9) GO Thanks in Advance...
Sub query
I want to use a query to pick out the latest record for a user. A user may have 5 records in the database and I want to pick the latest one. The latest one can be determined by the user Id and max mod id as there is a new id created every time a user registers for one. Is this the most efficient query to pick the latest record SELECT MC.USERID ,OG.OrganisationID , MC.Id AS ModID , MC.TYPE AS ResID ,CAST(MC.EndDateTime AS DATETIME2) AS EndDate ,CAST(MC.ExpiryDate AS DATETIME2) ExpiryDate ,CASE WHEN ExpiryDate >= GETDATE() AND IsDeleted = 0 THEN 1 ELSE 0 END AS Compliance FROM MC LEFT JOIN OG ON MC.USERID = OG. USERID INNER JOIN ( SELECT MC.USERID ,MAX(MC.Id) AS Max_Record FROM MC GROUP BY MC.USERID) Latest ON MC.USERID = Latest.USERID AND MC.Id = Latest.Max_Record
How to create category based on 1 column
Hi all, I am new to SQL and I am finding a way to categorise 1 column in my table. Currently I have 1 column called ID and I want to add 1 column that determine the category of that ID. My desired out is as following: ID Category 1 a 2 b 3 a 4 a 5 b The thing is, for 1 category alone, I have like 50+ ID involved, and I have 20+ categories. How do I create a query to simplify this? Many thanks.
