Hi,
Need help please.
I need to write a query that will sum the salary and rateforworker group by policyno,clientid. I need to return min(budgetid).
Also I need to return the comments. If there are duplicate rows with same policyno and clientid and one of the rows has a value for the field comment I need to return the value written in comment whether it belongs to the budgetid or not.
If all the rows have null for the value in comments then i will just return null.
The output needs to be as follows:
budgetid rateforworker salary clientid policyno comments
1 57 2400 1234 4567hj comments for this row
3 34 43 1234 567j9 NULL
The table structure isn't the real structure. Just did something quick now.
USE [TestDB]
GO
/****** Object: Table [dbo].[budget] Script Date: 11/03/2016 10:14:45 /
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[budget]') AND type in (N'U'))
DROP TABLE [dbo].[budget]
GO
/ Object: Table [dbo].[budget] Script Date: 11/03/2016 10:14:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[budget]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[budget](
[budgetid] [int] IDENTITY(1,1) NOT NULL,
[salary] [decimal](18, 0) NULL,
[rateforworker] [decimal](18, 0) NULL,
[policyno] nchar NULL,
[clientid] [int] NULL,
[comments] nvarchar NULL,
CONSTRAINT [PK_budget] PRIMARY KEY CLUSTERED
(
[budgetid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[budget] ON
INSERT [dbo].[budget] ([budgetid], [salary], [rateforworker], [policyno], [clientid], [comments]) VALUES (1, CAST(2400 AS Decimal(18, 0)), CAST(23 AS Decimal(18, 0)), N'4567hj ', 1234, NULL)
INSERT [dbo].[budget] ([budgetid], [salary], [rateforworker], [policyno], [clientid], [comments]) VALUES (2, NULL, CAST(34 AS Decimal(18, 0)), N'4567hj ', 1234, N'comments for this row')
INSERT [dbo].[budget] ([budgetid], [salary], [rateforworker], [policyno], [clientid], [comments]) VALUES (3, CAST(45 AS Decimal(18, 0)), CAST(34 AS Decimal(18, 0)), N'567j9 ', 1234, NULL)
SET IDENTITY_INSERT [dbo].[budget] OFF