Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

need help in a stored procedure Expand / Collapse
Author
Message
Posted Friday, August 30, 2013 12:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 10:24 PM
Points: 48, Visits: 370
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..
Post #1490010
Posted Friday, August 30, 2013 1:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 3,002, Visits: 3,178
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
Post #1490015
Posted Friday, August 30, 2013 1:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 10:24 PM
Points: 48, Visits: 370
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
Post #1490021
Posted Friday, August 30, 2013 3:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
So what output you want?


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1490033
Posted Friday, August 30, 2013 3:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 5,229, Visits: 9,448
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
Post #1490043
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse