Need help to get appropriate result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help to get appropriate result

Author

Topic

pankaj2910
Starting Member

31 Posts

Posted - 2014-12-08 : 13:45:39

USE [dummy]
GO
/****** Object: Table [dbo].[Type4] Script Date: 12/09/2014 00:13:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Type4](
[id] [int] NOT NULL,
[claimid] [varchar](10) NOT NULL,
[dummycustomernumber] [varchar](50) NOT NULL,
[batchid] [nchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Type4] ([id], [claimid], [dummycustomernumber], [batchid]) VALUES (132, N'98761234', N'1600149395', N'9021201 ')
/****** Object: Table [dbo].[Type3] Script Date: 12/09/2014 00:13:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Type3](
[id] [int] NULL,
[claimid] [varchar](10) NULL,
[number] [varchar](50) NULL,
[code] [varchar](2) NULL,
[batchid] [nchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Type3] ([id], [claimid], [number], [code], [batchid]) VALUES (123, N'98765434', N'9876543212', N'12', N'9021201 ')
INSERT [dbo].[Type3] ([id], [claimid], [number], [code], [batchid]) VALUES (132, N'98761234', N'9876543212', N'12', N'9021201 ')
INSERT [dbo].[Type3] ([id], [claimid], [number], [code], [batchid]) VALUES (143, N'98767862', N'9876543212', N'12', N'9021201 ')
INSERT [dbo].[Type3] ([id], [claimid], [number], [code], [batchid]) VALUES (1234, N'98763421', N'1234412341', N'12', N'9021201 ')
/****** Object: Table [dbo].[type2] Script Date: 12/09/2014 00:13:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[type2](
[id] [int] NOT NULL,
[claimid] [varchar](10) NOT NULL,
[customernumber] [varchar](50) NOT NULL,
[startdate] [datetime] NOT NULL,
[enddate] [datetime] NOT NULL,
[batchid] [nchar](10) NOT NULL,
[UploadDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[type2] ([id], [claimid], [customernumber], [startdate], [enddate], [batchid], [UploadDate]) VALUES (123, N'98765434', N'1600149395', CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A3B600000000 AS DateTime), N'9021201 ', CAST(0x0000A22E001AA450 AS DateTime))
INSERT [dbo].[type2] ([id], [claimid], [customernumber], [startdate], [enddate], [batchid], [UploadDate]) VALUES (132, N'98761234', N'1600149395', CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A3B600000000 AS DateTime), N'9021201 ', CAST(0x0000A22E001944C0 AS DateTime))
INSERT [dbo].[type2] ([id], [claimid], [customernumber], [startdate], [enddate], [batchid], [UploadDate]) VALUES (143, N'98767862', N'1600149395', CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A3B600000000 AS DateTime), N'9021201 ', CAST(0x0000A22E00110760 AS DateTime))
INSERT [dbo].[type2] ([id], [claimid], [customernumber], [startdate], [enddate], [batchid], [UploadDate]) VALUES (1234, N'98763421', N'1600149395', CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A3B600000000 AS DateTime), N'9021201 ', CAST(0x0000A22E00179EE0 AS DateTime))
INSERT [dbo].[type2] ([id], [claimid], [customernumber], [startdate], [enddate], [batchid], [UploadDate]) VALUES (132, N'98761234', N'1600149395', CAST(0x0000A24A00000000 AS DateTime), CAST(0x0000A3B600000000 AS DateTime), N'9021201 ', CAST(0x0000A22E0023FAF0 AS DateTime))
/****** Object: Table [dbo].[Type1] Script Date: 12/09/2014 00:13:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Type1](
[id] [int] NOT NULL,
[claimid] [varchar](10) NOT NULL,
[name] [varchar](50) NULL,
[batchid] [nchar](10) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Type1] ([id], [claimid], [name], [batchid]) VALUES (123, N'98765434', N'PETER', N'9021201 ')
INSERT [dbo].[Type1] ([id], [claimid], [name], [batchid]) VALUES (132, N'98761234', N'BCD', N'9021201 ')
INSERT [dbo].[Type1] ([id], [claimid], [name], [batchid]) VALUES (143, N'98767862', N'CDA', N'9021201 ')
INSERT [dbo].[Type1] ([id], [claimid], [name], [batchid]) VALUES (123, N'98765434', N'PETER', N'9021201 ')
INSERT [dbo].[Type1] ([id], [claimid], [name], [batchid]) VALUES (132, N'98761234', N'BCD', N'9021201 ')
INSERT [dbo].[Type1] ([id], [claimid], [name], [batchid]) VALUES (1234, N'98763421', N'MANU', N'9021201 ')

I need the result as group by
([std], [local], [number], [code], [startdate], [enddate], [count(total claimid)], [count(distinct claimid)])
(98, 76, 9876543212, 12, 2013-10-01 00:00:00.000,2014-09-30 00:00:00.000, 5, 4)
the result baSED ON THE ABOVE FOUR TABLES IN WHICH id, claimid and batchid is common in each table but in some tables rows having duplicate records.
Please help me to get the result

pankajrocks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-08 : 14:33:17

What have you got so far? That is, please take a shot at building a query, play with it until you get stuck, the post what you have (including partial results).

pankaj2910
Starting Member

31 Posts

Posted - 2014-12-09 : 02:25:44

Dear gbritton,

actually I need the count(total claimid) and count(distinct claimid) by joining all 4 tables using claimid, id, batchid as common in each table so these columns use to join & extract the report as required.

pankajrocks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-09 : 08:53:33

OK -- so let's see the query you have built so far...

Note: You really shouldn't post a problem without trying to solve it yourself, first.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources