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
Sql query
Hi Everyone, I have one sql query to be solved as practice, have attached the ss Output: need to select the src and dest that appears only ones Delhi Mum Delhi Kolkata Mum Nagpur I have solved it using lead function over 1 row But if there are more such records not necessary to the very next row how should I query the output Please help Thanks
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.
Please anyone help me to write my desired query as per Sample Output
I have a table in MS access (2016 version) name Transaction Table, given following: Transaction Table ID Transaction Date Receive Payment 1 01-Mar-20 50 1 02-Mar-20 40 70 2 03-Mar-20 400 100 2 04-Mar-20 300 400 3 05-Mar-20 500 0 3 06-Mar-20 200 0 1 07-Mar-20 476 300 2 08-Mar-20 390 300 1 09-Mar-20 0 500 2 10-Mar-20 300 0 2 11-Mar-20 0 500 3 07-Mar-20 500 400 3 08-Mar-20 200 Now I want to write a query (SQL view window) or want to design a query through MS Access query design wizard, which will generate a report containing ID wise Sub total, Running total and grand total from the above table( sample output is given below). I know that It can be done through MS Access Report wizard but I do not want that, I just want to write a query (using rollup, cube, running total query etc ) like the way it is usually done in Oracle / MS sql server/My sql . Please anyone help me to write my desired query as following: Customer Ledger Statement Balance Report ID Transaction Date Receive Payment Closing Balance 1 01-Mar-20 50 50 02-Mar-20 40 70 20 07-Mar-20 476 300 196 09-Mar-20 0 500 -304 Total 566 870 -304 2 03-Mar-20 400 100 300 04-Mar-20 300 400 200 08-Mar-20 390 300 290 10-Mar-20 300 0 590 11-Mar-20 0 500 90 Total 1390 1300 90 3 05-Mar-20 500 0 500 06-Mar-20 200 0 700 07-Mar-20 500 400 800 08-Mar-20 200 600 Total 1200 600 600 Grand Total 2656 2170 486 Thanks In Advance !!
