August 30, 2013 at 12:38 am
Hi friends,
I have a table called op_registration where every visit of out patient(op) will be inserted.I need to check count of regular(up to 28 days from registration date) and irregular (more than 28 days) patients for each day in given month..
CREATE TABLE [dbo].[OP_Registration](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FacilityId] [int] NOT NULL,--branch id
[MRNO] [nvarchar](20) NOT NULL,--patient unique number
[OPNO] [nvarchar](20) NOT NULL,-- patient unique number per visit
[RegistrationDate] [datetime] NOT NULL,
[VisitNumber] [int] NULL,
CONSTRAINT [PK_OP_Registration] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
any help would be appreciated..
Thanks in advance..
August 30, 2013 at 1:03 am
Hi,
Have you got any sample data we can have a look at - also how far have you got?
Could you please post what you have so far? - You might not be too far away.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
August 30, 2013 at 1:59 am
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
August 30, 2013 at 3:02 am
So what output you want?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 30, 2013 at 3:34 am
First, you need a table with all those August dates in it. Do you already have one? If not, you can create a permanent one or use a CTE to make it on the fly. Second, cast the ResgistrationDate column as date in order to strip out the time portion. Third, left join your dates table to your registration table on dates.date < registration.date + 28 and patient = MR-02-0613-0001. Rows with NULL on the right hand side will be irregular; all others will be regular. Have a go at writing that and post back if you're struggling with something in particular.
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply