June 11, 2004 at 4:57 am
i had 2 queryies written , 1 in the main page and another in the details
main page has a counter showing no. of messages or issues pending
and inside is the detailed content list.
so i need a count query to show the exact number of details present in the inside screen
but my query have joins and having cluase, and having clause requires group by.
but what do i do about this.
my inside query is like this
--------------
SELECT RMCPL_PACK_SIZEApr,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_NAME AS sample,
RMCPL_EXPECTED_DT,ss.RMCPL_SAMPLE_NAME as APPROVEDSAMP,
BALQTY=(RMCPL_REQUISITION.RMCPL_QTY_APPROVED-sum(isnull(DispatchQty,0))),
RMCPL_REQUISITION.RMCPL_QTY_APPROVED,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD,
RMCPL_EMP_MASTER.RMCPL_EMP_NAME AS Emp,RMCPL_REQUISITION.RMCPL_PACK_SIZE,
RMCPL_REQUISITION.RMCPL_QTY_REQUIRED, RMCPL_REQUISITION.RMCPL_DATE,RMCPL_REQUISITION.RMCPL_TID
FROM RMCPL_REQUISITION INNER JOIN RMCPL_SAMPLE_MASTER
ON RMCPL_REQUISITION.RMCPL_SAMPLE_CD = RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD
INNER JOIN RMCPL_EMP_MASTER ON RMCPL_REQUISITION.RMCPL_EMP_CD = RMCPL_EMP_MASTER.RMCPL_EMP_CD
inner join rmcpl_sample_master ss on RMCPL_REQUISITION.RMCPL_SAMPLE_CDApr =ss.RMCPL_SAMPLE_CD
left join SampleDispatchDetails dd on RMCPL_REQUISITION.RMCPL_TID=dd.RMCPL_TID
where RMCPL_REQUISITION.RMCPL_QTY_APPROVED IS not NULL AND RMCPL_REQUISITION.RMCPL_EXPECTED_DT is not null
group by
RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_NAME ,
RMCPL_EXPECTED_DT,ss.RMCPL_SAMPLE_NAME ,
RMCPL_REQUISITION.RMCPL_QTY_APPROVED,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD,
RMCPL_EMP_MASTER.RMCPL_EMP_NAME ,RMCPL_REQUISITION.RMCPL_PACK_SIZE,RMCPL_PACK_SIZEApr,
RMCPL_REQUISITION.RMCPL_QTY_REQUIRED, RMCPL_REQUISITION.RMCPL_DATE,RMCPL_REQUISITION.RMCPL_TID
having sum(isnull(DispatchQty,0))< RMCPL_REQUISITION.RMCPL_QTY_APPROVED
-----------------------------------
and my count qwery is
SELECT count(*)
FROM RMCPL_REQUISITION rr
left join SampleDispatchDetails dd on RR.RMCPL_TID=dd.RMCPL_TID
where RR.RMCPL_QTY_APPROVED IS not NULL AND RR.RMCPL_EXPECTED_DT is not null
but its not showing the exact record count because of the missing having cluase, i need to put having cluase for correct resulat as my inside query has.
how do i do this.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
June 14, 2004 at 8:00 am
This was removed by the editor as SPAM
June 15, 2004 at 3:44 am
brains a bit sluggish this morning, but can't you just do a:
select count(*)
from (
.... first query
) t
June 15, 2004 at 4:10 am
I guess this should work. Try it out!! Since I dont have the actual data, I am not able to test it out. Please do let us know if it works.
SELECT
COUNT(*)
FROM
RMCPL_REQUISITION rr
LEFT JOIN
SampleDispatchDetails dd
ON
RR.RMCPL_TID=dd.RMCPL_TID
WHERE
RR.RMCPL_QTY_APPROVED IS NOT NULL
AND
RR.RMCPL_EXPECTED_DT IS NOT NULL
HAVING
sum(isnull(DispatchQty,0))< RMCPL_REQUISITION.RMCPL_QTY_APPROVED
Regards,
Beulah Kingsly
June 15, 2004 at 5:38 am
having needs a group by clause, thats what iam trying to tell, u.
pls check it again
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
June 15, 2004 at 5:49 am
below is database schema
--------------------|||------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RMCPL_REQUISITION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RMCPL_REQUISITION]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SampleDispatchDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SampleDispatchDetails]
GO
CREATE TABLE [dbo].[RMCPL_REQUISITION] (
[RMCPL_TID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[RMCPL_DATE] [datetime] NULL ,
[RMCPL_PACK_SIZE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RMCPL_PACK_SIZEApr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RMCPL_QTY_REQUIRED] [numeric](18, 0) NULL ,
[RMCPL_QTY_APPROVED] [numeric](18, 0) NULL ,
[RMCPL_QTY_PENDING] [numeric](18, 0) NULL ,
[RMCPL_EMP_CD] [numeric](18, 0) NULL ,
[RMCPL_SAMPLE_CD] [numeric](18, 0) NULL ,
[RMCPL_SAMPLE_CDApr] [numeric](18, 0) NULL ,
[Rmcpl_approved_date] [datetime] NULL ,
[RMCPL_EXPECTED_DT] [datetime] NULL ,
[ApprovedBy] [numeric](18, 0) NULL ,
[inserteddate] [datetime] NOT NULL ,
[Remarks] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AprRemarks] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SampleDispatchDetails] (
[Dispatchid] [numeric](18, 0) IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[RMCPL_TID] [numeric](18, 0) NULL ,
[ScreenDispatchDate] [datetime] NOT NULL ,
[DispatchDate] [datetime] NULL ,
[DispatchQty] [numeric](18, 0) NULL ,
[CourierDetails] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dispatchdetails] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
June 15, 2004 at 6:45 am
Hi, as I am not familiar with your table size or the performance issues that may arise from this, But, When I have had to count the number of children and have had to display that with the parents I have found a function works for me.
something like:
function dbo.CountDetails(@dd.RMCPL_TID [numeric](18, 0) )
returns int
as
Begin
Return(
SELECT count(*)
from SampleDispatchDetails Where RR.RMCPL_TID=dd.RMCPL_TID and RR.RMCPL_QTY_APPROVED IS not NULL AND RR.RMCPL_EXPECTED_DT is not null )
end
HTH
Tal McMahon
June 15, 2004 at 6:48 am
I am sorry I forgot to add now that you have the function you can refer to it in your parent query right in the select statement. like so:
SELECT dbo.CountDetails(RMCPL_TID) as MyCount, RMCPL_PACK_SIZEApr,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_NAME AS sample,
RMCPL_EXPECTED_DT,ss.RMCPL_SAMPLE_NAME as APPROVEDSAMP,
BALQTY=(RMCPL_REQUISITION.RMCPL_QTY_APPROVED-sum(isnull(DispatchQty,0))),
RMCPL_REQUISITION.RMCPL_QTY_APPROVED,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD,
RMCPL_EMP_MASTER.RMCPL_EMP_NAME AS Emp,RMCPL_REQUISITION.RMCPL_PACK_SIZE,
RMCPL_REQUISITION.RMCPL_QTY_REQUIRED, RMCPL_REQUISITION.RMCPL_DATE,RMCPL_REQUISITION.RMCPL_TID
FROM RMCPL_REQUISITION INNER JOIN RMCPL_SAMPLE_MASTER
ON RMCPL_REQUISITION.RMCPL_SAMPLE_CD = RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD
INNER JOIN RMCPL_EMP_MASTER ON RMCPL_REQUISITION.RMCPL_EMP_CD = RMCPL_EMP_MASTER.RMCPL_EMP_CD
inner join rmcpl_sample_master ss on RMCPL_REQUISITION.RMCPL_SAMPLE_CDApr =ss.RMCPL_SAMPLE_CD
left join SampleDispatchDetails dd on RMCPL_REQUISITION.RMCPL_TID=dd.RMCPL_TID
where RMCPL_REQUISITION.RMCPL_QTY_APPROVED IS not NULL AND RMCPL_REQUISITION.RMCPL_EXPECTED_DT is not null
group by
RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_NAME ,
RMCPL_EXPECTED_DT,ss.RMCPL_SAMPLE_NAME ,
RMCPL_REQUISITION.RMCPL_QTY_APPROVED,RMCPL_SAMPLE_MASTER.RMCPL_SAMPLE_CD,
RMCPL_EMP_MASTER.RMCPL_EMP_NAME ,RMCPL_REQUISITION.RMCPL_PACK_SIZE,RMCPL_PACK_SIZEApr,
RMCPL_REQUISITION.RMCPL_QTY_REQUIRED, RMCPL_REQUISITION.RMCPL_DATE,RMCPL_REQUISITION.RMCPL_TID
having sum(isnull(DispatchQty,0))< RMCPL_REQUISITION.RMCPL_QTY_APPROVED
there thats better
tal mcmahon
June 16, 2004 at 3:40 am
that was not a very proper solution i guess i
need to write a inside query and do recordcount
to get the count figure
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply