August 26, 2005 at 12:28 pm
have to do sequencing on the query below
select orroomname, tblsortorder.orroomno,atime, dtime, caseid from tblcasedata inner join tblsortorder
on tblcasedata.orroomno= tblsortorder.orroomno
where dateofservice= '8/11/2005'
and tblsortorder.orroomno in ('611','554')
and qcstatus=4
order by orroomname, atime
i am getting out put as
Orromname orroonno atime dtime caseid
MRI 611 2005-08-11 12:00:00.000 2005-08-11 14:00:00.000 OMOR12232
MRI 611 2005-08-11 15:15:00.000 2005-08-11 17:45:00.000 OMOR12233
NPTC 554 2005-08-11 09:15:00.000 2005-08-11 10:26:00.000 OMOR12234
NPTC 554 2005-08-11 10:27:00.000 2005-08-11 11:06:00.000 OMOR12235
NPTC 554 2005-08-11 11:09:00.000 2005-08-11 11:39:00.000 OMOR12236
NPTC 554 2005-08-11 12:01:00.000 2005-08-11 12:44:00.000 OMOR12237
NPTC 554 2005-08-11 12:46:00.000 2005-08-11 13:50:00.000 OMOR12238
i want one more field as seqno and wanted 1,2 for orroomno 611 and 1,2,3,4 for orroom no 554
could anybody change my query and help
thanks
August 26, 2005 at 12:33 pm
Please do not cross-post.
Check this thread out :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=189202
August 26, 2005 at 1:17 pm
i cannot use update or create as this is on production server only i could use select then it would be useful
thankx
August 26, 2005 at 1:19 pm
Might I suggest you try the solution on a test server first???
Or at the very least on another db on the same server??
August 26, 2005 at 2:04 pm
Hi Remi
thanks for suggestion but i tried on development server but couldn't figure out how it could help in my scenario.
can you throw some light how can i use ur solution provided at the link above..
thanks
kalpan
August 26, 2005 at 2:07 pm
Can you (re)post :
- Table DDL
- Insert script (statements) for sample data
- Current select query
- Needed output
If you don't know how to do all that :
August 26, 2005 at 2:22 pm
thank you for support
TABLE DDL STATEMENTS
TBLCASEDATA
CREATE TABLE [tblCaseData] (
[CaseID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SaturnPatientID] [int] NULL ,
[MRN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MidName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateofBirth] [datetime] NULL ,
[Gender] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmissionTypeID] [int] NULL ,
[DateofService] [datetime] NOT NULL ,
[ORFormNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORCaseID] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORRoomNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmitDate] [datetime] NULL ,
[ASAClassID] [int] NULL ,
[ATime] [datetime] NULL ,
[BTime] [datetime] NULL ,
[CTime] [datetime] NULL ,
[DTime] [datetime] NULL ,
[QCStatus] [int] NULL CONSTRAINT [DF_tblCaseData_QCStatus] DEFAULT (0),
[SaturnCaseStatus] [int] NULL CONSTRAINT [DF_tblCaseData_SaturnCaseStatus] DEFAULT (0),
[ValidationStatus] [int] NULL CONSTRAINT [DF_tblCaseData_ValidationStatus] DEFAULT (0),
[AcceptRejectSaturnUpdates] [int] NULL CONSTRAINT [DF_tblCaseData_AcceptRejectSaturnUpdates] DEFAULT (0),
[QCPendingStatus] [int] NULL CONSTRAINT [DF_tblCaseData_QCPendingStatus] DEFAULT (0),
[QCErrorStatus] [int] NULL CONSTRAINT [DF_tblCaseData_QCErrorStatus] DEFAULT (0),
[LastCaseDataPullTime] [datetime] NULL ,
[Remarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedTime] [datetime] NOT NULL CONSTRAINT [DF_tblCaseData_CreatedTime] DEFAULT (getdate()),
[LastUpdatedTime] [datetime] NOT NULL CONSTRAINT [DF_tblCaseData_LastUpdatedTime] DEFAULT (getdate()),
[BillingArea] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tblCaseData_BillingArea] DEFAULT (20),
[CareFacility] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemBaseWeight] [float] NULL ,
[UserEnteredBaseWeight] [float] NULL ,
[patienttype] [int] NULL ,
[PendingComments] [varchar] (127) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApprovedTimeStamp] [datetime] NULL ,
[RejectedTimeStamp] [datetime] NULL ,
[PACEAccountNumber] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RevenueControlStatus] [int] NULL ,
[RevenueControlPrintTime] [datetime] NULL ,
[RevenueControlPrintedUserID] [int] NULL
) ON [PRIMARY]
GO
TBLSORTORDER
CREATE TABLE [tblsortorder] (
[OrRoomNo] [int] NULL ,
[OrRoomName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrRoomSortOrder] [int] NULL ,
[Active] [int] NULL
) ON [PRIMARY]
GO
SELECT STATEMENT
select orroomname, tblsortorder.orroomno,atime, dtime, caseid from tblcasedata inner join tblsortorder
on tblcasedata.orroomno= tblsortorder.orroomno
where dateofservice= '8/11/2005'
and tblsortorder.orroomno in ('611','554')
and qcstatus=4
order by orroomname, atime
CURRENT OUTPUT
Orromname orroonno atime dtime caseid
MRI 611 2005-08-11 12:00:00.000 2005-08-11 14:00:00.000 OMOR12232
MRI 611 2005-08-11 15:15:00.000 2005-08-11 17:45:00.000 OMOR12233
NPTC 554 2005-08-11 09:15:00.000 2005-08-11 10:26:00.000 OMOR12234
NPTC 554 2005-08-11 10:27:00.000 2005-08-11 11:06:00.000 OMOR12235
NPTC 554 2005-08-11 11:09:00.000 2005-08-11 11:39:00.000 OMOR12236
NPTC 554 2005-08-11 12:01:00.000 2005-08-11 12:44:00.000 OMOR12237
NPTC 554 2005-08-11 12:46:00.000 2005-08-11 13:50:00.000 OMOR12238
REQUIRED OUTPUT
WANT A SEQUENCE FIELD AT LAST SHOWING SEQID ORDER BY ORROOMNO AND ATIME SAY FOR ABOVE CASE FOR 611 ORROOM NO I WANT 1,2 AND FOR ORROOM NO 554 I WANT 1,2,3,4
and sorry if you could understood to my earlier query i understand you are master in sqlserver and i am novoice so..
thanks
kalpan
August 26, 2005 at 2:23 pm
There's no insert statements for the test data.
August 26, 2005 at 2:25 pm
i dont want to insert anything i just want to show that in output..
can i do that...
August 26, 2005 at 2:27 pm
insert statement for me to insert data in the table I'll be creating with your script(s).
August 26, 2005 at 2:50 pm
insert into tblsortorder values (554,"NPTC",307," ")
insert into tblsortorder values (611,"MRI",314," ")
insert into tblcasedata values ("OMOR12233"," ","2180961","CHRISTOPHE","POLLARD"," ",1951-12-10 00:00:00,"M",3,2005-08-11 00:00:00,"OMOR12233"," ","611"," ",3,2005-08-11 15:15:00,2005-08-11 16:00:00,2005-08-11 17:15:00,2005-08-11 17:45:00,4,6,1,0,0,1,," ",2005-08-12 08:53:26.310000000,2005-08-12 08:53:27.043000000,"21"," "," ",0," "," "," "," ","905650636",1,2005-08-22 10:13:04.463000000,7)
insert into tblcasedata values ("OMOR12234"," ","2180961","CHRIS","POLLARD"," ",1951-12-10 00:00:00,"M",3,2005-08-11 00:00:00,"OMOR12233"," ","611"," ",3,2005-08-11 15:15:00,2005-08-11 16:00:00,2005-08-11 17:15:00,2005-08-11 17:45:00,4,6,1,0,0,1,," ",2005-08-12 08:53:26.310000000,2005-08-12 08:53:27.043000000,"21"," "," ",0," "," "," "," ","905650636",1,2005-08-22 10:13:04.463000000,7)
insert into tblcasedata values ("OMOR12235"," ","2180961","TOPHE","POLLARD"," ",1951-12-10 00:00:00,"M",3,2005-08-11 00:00:00,"OMOR12233"," ","544"," ",3,2005-08-11 15:15:00,2005-08-11 16:00:00,2005-08-11 17:15:00,2005-08-11 17:45:00,4,6,1,0,0,1,," ",2005-08-12 08:53:26.310000000,2005-08-12 08:53:27.043000000,"21"," "," ",0," "," "," "," ","905650636",1,2005-08-22 10:13:04.463000000,7)
insert into tblcasedata values ("OMOR12236"," ","2180961","TOP","POLLARD"," ",1951-12-10 00:00:00,"M",3,2005-08-11 00:00:00,"OMOR12233"," ","544"," ",3,2005-08-11 15:15:00,2005-08-11 16:00:00,2005-08-11 17:15:00,2005-08-11 17:45:00,4,6,1,0,0,1,," ",2005-08-12 08:53:26.310000000,2005-08-12 08:53:27.043000000,"21"," "," ",0," "," "," "," ","905650636",1,2005-08-22 10:13:04.463000000,7)
insert into tblcasedata values ("OMOR12237"," ","2180961","HE","POLLARD"," ",1951-12-10 00:00:00,"M",3,2005-08-11 00:00:00,"OMOR12233"," ","544"," ",3,2005-08-11 15:15:00,2005-08-11 16:00:00,2005-08-11 17:15:00,2005-08-11 17:45:00,4,6,1,0,0,1,," ",2005-08-12 08:53:26.310000000,2005-08-12 08:53:27.043000000,"21"," "," ",0," "," "," "," ","905650636",1,2005-08-22 10:13:04.463000000,7)
insert into tblcasedata values ("OMOR12238"," ","2180961","STOPHE","POLLARD"," ",1951-12-10 00:00:00,"M",3,2005-08-11 00:00:00,"OMOR12233"," ","544"," ",3,2005-08-11 15:15:00,2005-08-11 16:00:00,2005-08-11 17:15:00,2005-08-11 17:45:00,4,6,1,0,0,1,," ",2005-08-12 08:53:26.310000000,2005-08-12 08:53:27.043000000,"21"," "," ",0," "," "," "," ","905650636",1,2005-08-22 10:13:04.463000000,7)
insert into tblcasedata values ("OMOR12239"," ","2180961","RISTOPHE","POLLARD"," ",1951-12-10 00:00:00,"M",3,2005-08-11 00:00:00,"OMOR12233"," ","611"," ",3,2005-08-11 15:15:00,2005-08-11 16:00:00,2005-08-11 17:15:00,2005-08-11 17:45:00,4,6,1,0,0,1,," ",2005-08-12 08:53:26.310000000,2005-08-12 08:53:27.043000000,"21"," "," ",0," "," "," "," ","905650636",1,2005-08-22 10:13:04.463000000,7)
insert into tblcasedata values ("OMOR12240"," ","2180961","PHE","POLLARD"," ",1951-12-10 00:00:00,"M",3,2005-08-11 00:00:00,"OMOR12233"," ","611"," ",3,2005-08-11 15:15:00,2005-08-11 16:00:00,2005-08-11 17:15:00,2005-08-11 17:45:00,4,6,1,0,0,1,," ",2005-08-12 08:53:26.310000000,2005-08-12 08:53:27.043000000,"21"," "," ",0," "," "," "," ","905650636",1,2005-08-22 10:13:04.463000000,7)
sorry again..
kalpan
August 26, 2005 at 4:07 pm
NP... I'll check it out on Monday when I have access to a server.
August 29, 2005 at 11:42 am
hi remi
did you got sometime to check the querry?????
August 29, 2005 at 1:00 pm
Sorry, just got back.
Actually re-reading this thread I feel I must add this :
1 - This is the sample code you missed in my link to another thread which is what you should be using.
Select O1.XType, O1.Name, Count(*) as Rank from dbo.SysObjects O1 inner join dbo.SysObjects O2 on O2.XType = O1.XType and O2.Name <= O1.name group by O1.XType, O1.Name order by O1.XType, O1.Name
2 - You should be doing this presentation stuff on the application side, not on the server. You'll be taxing it far too much for nothing (check the execution plan).
3- Next time you post some script, can't you TEST 'EM??? I don't have 30 minutes to make them work for every question I answer.
August 29, 2005 at 2:29 pm
hi remi
thanks for all your advice and helping..
kalpan
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply