op_registration data

SET IDENTITY_INSERT [dbo].[OP_Registration111] ON

INSERT INTO [dbo].[OP_Registration111] ([Id], [FacilityId], [MRNO], [OPNO], [RegistrationDate], [VisitNumber])

SELECT 31, 2, N'MR-02-0613-0001', N'OP-02-0613-0001', '2013-06-01 09:48:00.000', 1

UNION ALL

SELECT 32, 2, N'MR-02-0613-0001', N'OP-02-0713-0002', '2013-07-07 09:51:00.000', 1

UNION ALL

SELECT 35, 2, N'MR-02-0613-0003', N'OP-02-0613-0010', '2013-08-06 11:19:00.000', 1

UNION ALL

SELECT 36, 2, N'MR-02-0613-0005', N'OP-02-0613-0053', '2013-07-16 10:09:00.000', 1

UNION ALL

SELECT 40, 2, N'MR-02-0613-0005', N'OP-02-0813-0329', '2013-08-28 12:15:00.000', 1

UNION ALL

SELECT 41, 2, N'MR-02-0613-0005', N'OP-02-0813-0329', '2013-08-08 12:15:00.000', 1

UNION ALL

SELECT 42, 2, N'MR-02-0613-0001', N'OP-02-0613-0001', '2013-08-15 09:48:00.000', 1

SET IDENTITY_INSERT [dbo].[OP_Registration111] OFF

here 'MR-02-0613-0001' visited 3 times so far

the result set would be for this patient in August month

Date Regular Irregular

01/08/2013 1 0

02/08/2013 1 0

03/08/2013 1 0

04/08/2013 1 0

05/08/2013 1 0 --regular till 2013-07-07 + 28 days

06/08/2013 0 1

07/08/2013 0 1

08/08/2013 0 1

09/08/2013 0 1

10/08/2013 0 1

11/08/2013 0 1

12/08/2013 0 1

13/08/2013 0 1

14/08/2013 0 1

15/08/2013 1 0 -- visted again on 15/08/2013 so he will be regular for the next 28 days

16/08/2013 1 0

17/08/2013 1 0

18/08/2013 1 0

19/08/2013 1 0

20/08/2013 1 0

21/08/2013 1 0

22/08/2013 1 0

23/08/2013 1 0

.

.

.

.

31/08/2013 1 0