Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


need help in a stored procedure


need help in a stored procedure

Author
Message
$t@r
$t@r
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 628
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..
Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4485 Visits: 4549
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
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
$t@r
$t@r
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 628
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
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
So what output you want?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7466 Visits: 15142
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search