Problem grouping records under single PK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem grouping records under single PK

Author

Topic

mharry
Starting Member

5 Posts

Posted - 2006-02-09 : 14:48:03

I am more accustomed to FoxPro and the way it allows you to group by any column no matter the number of (un-aggregated columns) in the select list. MS SQL Server does not allow this. I am not getting desired results the way the help file suggest doing it. I can get what I want only selecting two fields, one aggregate and grouping the others… For example I’m selecting patient claims data that have a primary key of claim no. and claim line (ea. Claim can have multiple lines) I want all those lines grouped under one result with a sum to the total paid of the claim from each line.
Sample data:
[claimno] [memberno] [chkno] [svcdat] [pidate] [to_pay]
123456 34513 3434 01/01/2000 01/01/2000 125.00
123456 34513 3434 01/01/2000 01/01/2000 13.45

& keep in mind line no is the other PK that makes the records valid, in the example the first record would be line 1 and the second line 2

Here is one sample I’ve been tinkering with pulling claims records….

**
drop table clmstemp
declare @paiddate as char(10),

set dateformat mdy
set @paiddate= '01/24/2006'

select
claimno,
sum(to_pay)as total
into clmstemp
from claimlin
where pidate= @paiddate
group by claimno
order by claimno

drop table tempcl
select distinct
t.claimno,
c.chknum,
C.grpnum,
C.provno,
c.membno,
C.svcdat,
C.pidate,
C.poscod,
t.total
into tempcl
from clmstemp AS t left join claimlin as c
ON t.claimno = c.claimno
where pidate= @paiddate

The first part of the query does pull only distinct claims records but everything I’ve tried returns multiple claim numbers?? Please help

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-09 : 15:57:31

Can you post what the expect result is suppose to be?

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mharry
Starting Member

5 Posts

Posted - 2006-02-09 : 16:12:15

I’m getting duplicate claim records; it looks like when ever one of the fields from my original table is different from my (select claimno, sum(to_pay)as total into clmstemp) …if the svcdat is different in line 1 & 2 of that claim they will both be displayed instead of grouping them together with a sum of the total paid.

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-09 : 16:17:12

I really need to you to read the hint link in my sig and post what it asks for, and we can help, because I really don't see what you're saying

Your statement has nothing to do with svcdat

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mharry
Starting Member

5 Posts

Posted - 2006-02-09 : 16:32:25

Okay you ask for it

********************************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[claimlin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[claimlin]
GO

CREATE TABLE [dbo].[claimlin] (
[claimno] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lineno] [smallint] NOT NULL ,
[recdate] [datetime] NULL ,
[authno] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[compno] [tinyint] NULL ,
[vendor] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chknum] [int] NULL ,
[grpnum] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[utlgrp] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prodcd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[formcd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[paycod] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ovrcod] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prccod] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[athtyp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tiercd] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[benpkg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bencod] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[feecod] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[feengt] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[feesrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ntooth] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[clmcon] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[athlin] [tinyint] NULL ,
[fndtyp] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[riskcd] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[edtcod] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dencod] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deneop] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[statcd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[provno] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[membno] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[member] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[svcdat] [datetime] NULL ,
[svccod] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modcod] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pcpcod] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prvorg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prvtyp] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prvcpy] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[spccod] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[poscod] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[toscod] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[clatyp] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[diagn1] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[diagn2] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[diagn3] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[enddat] [datetime] NULL ,
[unitct] [smallint] NULL ,
[alwunt] [smallint] NULL ,
[claamt] [decimal](11, 2) NULL ,
[disalw] [decimal](11, 2) NULL ,
[alwamt] [decimal](11, 2) NULL ,
[copamt] [decimal](9, 2) NULL ,
[coiamt] [decimal](9, 2) NULL ,
[dedamt] [decimal](9, 2) NULL ,
[admded] [decimal](9, 2) NULL ,
[nonamt] [decimal](11, 2) NULL ,
[preamt] [decimal](9, 2) NULL ,
[accamt] [decimal](11, 2) NULL ,
[cobamt] [decimal](9, 2) NULL ,
[cobalw] [decimal](11, 2) NULL ,
[rcvamt] [decimal](11, 2) NULL ,
[elgchg] [decimal](11, 2) NULL ,
[whdamt] [decimal](9, 2) NULL ,
[dscamt] [decimal](9, 2) NULL ,
[emppy1] [decimal](9, 2) NULL ,
[emppy2] [decimal](9, 2) NULL ,
[emppay] [decimal](9, 2) NULL ,
[prvpay] [decimal](11, 2) NULL ,
[to_pay] [decimal](11, 2) NULL ,
[inscod] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[userid] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sysusr] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lstchg] [datetime] NULL ,
[adjusr] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[verify] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[edtdat] [datetime] NULL ,
[infdat] [datetime] NULL ,
[reicsq] [smallint] NULL ,
[reimth] [int] NULL ,
[reiexb] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reiexc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reiexm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[clmcnv] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prfeen] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[airdat] [datetime] NULL ,
[reflin] [smallint] NULL ,
[adjcnt] [tinyint] NULL ,
[fincng] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[gcrcod] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bprcod] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[areacd] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cntalw] [decimal](11, 2) NULL ,
[cntfee] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mrktop] [decimal](11, 2) NULL ,
[cpysrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cntsrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[orgsrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modsrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rsksrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[copsrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[coptyp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prepad] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[elgdsc] [decimal](11, 2) NULL ,
[cntdsc] [decimal](11, 2) NULL ,
[echkno] [int] NULL ,
[pidate] [datetime] NULL ,
[apttrans] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sysdat] [datetime] NULL ,
[cobpad] [datetime] NULL ,
[causno] [smallint] NULL ,
[morgpr] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[morgsc] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[baspkg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pkgsrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[duplin] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rvnflg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[postdt] [datetime] NULL ,
[benset] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mapben] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[clbind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pyrnum] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[grpcod] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prcmth] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cntmth] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[clmcls] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cntcls] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cccgrp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cntcgp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mthgrp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cntmgp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modcd2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modcd3] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[modcd4] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[agrtyp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cntagt] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[folhld] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adjtop] [decimal](11, 2) NULL ,
[spltfm] [smallint] NULL ,
[causrc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[causet] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dedgp2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dedgp3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dedgp4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[enbseq] [decimal](14, 0) NULL ,
[enpseq] [decimal](14, 0) NULL ,
[disstat] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[authent] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[patnum] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[altprv] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fmtcod] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[covdys] [smallint] NULL ,
[estflg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[daycnt] [smallint] NULL ,
[daytyp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[accdat] [datetime] NULL ,
[accnum] [smallint] NULL ,
[nopflg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[biltyp] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prvasg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[recpnd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[venpy1] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[venpy2] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fileid] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[altclm] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adrseq] [tinyint] NULL ,
[clmlvl] [tinyint] NULL ,
[mltlvl] [smallint] NULL ,
[linlvl] [smallint] NULL ,
[linelv] [smallint] NULL ,
[mgpuse] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ctgcmp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prclvl] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[attflg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wrcdoi] [datetime] NULL ,
[wrcath] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[altcm2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[altcm3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[altcm4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prcset] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prcagr] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cobtot] [decimal](11, 2) NULL ,
[cntset] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cntagr] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asprat] [decimal](9, 2) NULL ,
[pndflg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rprdat] [datetime] NULL ,
[accind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wrkrel] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stayno] [int] NULL ,
[jodatetime] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[claimlin] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[claimno],
[lineno]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[claimlin] ADD
CONSTRAINT [DF__claimlin__authno__7E25DDB9] DEFAULT (' ') FOR [authno],
CONSTRAINT [DF__claimlin__compno__7F1A01F2] DEFAULT (0) FOR [compno],
CONSTRAINT [DF__claimlin__vendor__000E262B] DEFAULT (' ') FOR [vendor],
CONSTRAINT [DF__claimlin__chknum__01024A64] DEFAULT (0) FOR [chknum],
CONSTRAINT [DF__claimlin__grpnum__01F66E9D] DEFAULT (' ') FOR [grpnum],
CONSTRAINT [DF__claimlin__utlgrp__02EA92D6] DEFAULT (' ') FOR [utlgrp],
CONSTRAINT [DF__claimlin__prodcd__03DEB70F] DEFAULT (' ') FOR [prodcd],
CONSTRAINT [DF__claimlin__formcd__04D2DB48] DEFAULT (' ') FOR [formcd],
CONSTRAINT [DF__claimlin__paycod__05C6FF81] DEFAULT (' ') FOR [paycod],
CONSTRAINT [DF__claimlin__ovrcod__06BB23BA] DEFAULT (' ') FOR [ovrcod],
CONSTRAINT [DF__claimlin__prccod__07AF47F3] DEFAULT (' ') FOR [prccod],
CONSTRAINT [DF__claimlin__athtyp__08A36C2C] DEFAULT (' ') FOR [athtyp],
CONSTRAINT [DF__claimlin__tiercd__09979065] DEFAULT (' ') FOR [tiercd],
CONSTRAINT [DF__claimlin__benpkg__0A8BB49E] DEFAULT (' ') FOR [benpkg],
CONSTRAINT [DF__claimlin__bencod__0B7FD8D7] DEFAULT (' ') FOR [bencod],
CONSTRAINT [DF__claimlin__feecod__0C73FD10] DEFAULT (' ') FOR [feecod],
CONSTRAINT [DF__claimlin__feengt__0D682149] DEFAULT (' ') FOR [feengt],
CONSTRAINT [DF__claimlin__feesrc__0E5C4582] DEFAULT (' ') FOR [feesrc],
CONSTRAINT [DF__claimlin__ntooth__0F5069BB] DEFAULT (' ') FOR [ntooth],
CONSTRAINT [DF__claimlin__clmcon__10448DF4] DEFAULT (' ') FOR [clmcon],
CONSTRAINT [DF__claimlin__athlin__1138B22D] DEFAULT (0) FOR [athlin],
CONSTRAINT [DF__claimlin__fndtyp__122CD666] DEFAULT (' ') FOR [fndtyp],
CONSTRAINT [DF__claimlin__riskcd__1320FA9F] DEFAULT (' ') FOR [riskcd],
CONSTRAINT [DF__claimlin__edtcod__14151ED8] DEFAULT (' ') FOR [edtcod],
CONSTRAINT [DF__claimlin__dencod__15094311] DEFAULT (' ') FOR [dencod],
CONSTRAINT [DF__claimlin__deneop__15FD674A] DEFAULT (' ') FOR [deneop],
CONSTRAINT [DF__claimlin__statcd__16F18B83] DEFAULT (' ') FOR [statcd],
CONSTRAINT [DF__claimlin__provno__17E5AFBC] DEFAULT (' ') FOR [provno],
CONSTRAINT [DF__claimlin__membno__18D9D3F5] DEFAULT (' ') FOR [membno],
CONSTRAINT [DF__claimlin__member__19CDF82E] DEFAULT (' ') FOR [member],
CONSTRAINT [DF__claimlin__svccod__1AC21C67] DEFAULT (' ') FOR [svccod],
CONSTRAINT [DF__claimlin__modcod__1BB640A0] DEFAULT (' ') FOR [modcod],
CONSTRAINT [DF__claimlin__pcpcod__1CAA64D9] DEFAULT (' ') FOR [pcpcod],
CONSTRAINT [DF__claimlin__prvorg__1D9E8912] DEFAULT (' ') FOR [prvorg],
CONSTRAINT [DF__claimlin__prvtyp__1E92AD4B] DEFAULT (' ') FOR [prvtyp],
CONSTRAINT [DF__claimlin__prvcpy__1F86D184] DEFAULT (' ') FOR [prvcpy],
CONSTRAINT [DF__claimlin__spccod__207AF5BD] DEFAULT (' ') FOR [spccod],
CONSTRAINT [DF__claimlin__poscod__216F19F6] DEFAULT (' ') FOR [poscod],
CONSTRAINT [DF__claimlin__toscod__22633E2F] DEFAULT (' ') FOR [toscod],
CONSTRAINT [DF__claimlin__clatyp__23576268] DEFAULT (' ') FOR [clatyp],
CONSTRAINT [DF__claimlin__diagn1__244B86A1] DEFAULT (' ') FOR [diagn1],
CONSTRAINT [DF__claimlin__diagn2__253FAADA] DEFAULT (' ') FOR [diagn2],
CONSTRAINT [DF__claimlin__diagn3__2633CF13] DEFAULT (' ') FOR [diagn3],
CONSTRAINT [DF__claimlin__unitct__2727F34C] DEFAULT (0) FOR [unitct],
CONSTRAINT [DF__claimlin__alwunt__281C1785] DEFAULT (0) FOR [alwunt],
CONSTRAINT [DF__claimlin__claamt__29103BBE] DEFAULT (0) FOR [claamt],
CONSTRAINT [DF__claimlin__disalw__2A045FF7] DEFAULT (0) FOR [disalw],
CONSTRAINT [DF__claimlin__alwamt__2AF88430] DEFAULT (0) FOR [alwamt],
CONSTRAINT [DF__claimlin__copamt__2BECA869] DEFAULT (0) FOR [copamt],
CONSTRAINT [DF__claimlin__coiamt__2CE0CCA2] DEFAULT (0) FOR [coiamt],
CONSTRAINT [DF__claimlin__dedamt__2DD4F0DB] DEFAULT (0) FOR [dedamt],
CONSTRAINT [DF__claimlin__admded__2EC91514] DEFAULT (0) FOR [admded],
CONSTRAINT [DF__claimlin__nonamt__2FBD394D] DEFAULT (0) FOR [nonamt],
CONSTRAINT [DF__claimlin__preamt__30B15D86] DEFAULT (0) FOR [preamt],
CONSTRAINT [DF__claimlin__accamt__31A581BF] DEFAULT (0) FOR [accamt],
CONSTRAINT [DF__claimlin__cobamt__3299A5F8] DEFAULT (0) FOR [cobamt],
CONSTRAINT [DF__claimlin__cobalw__338DCA31] DEFAULT (0) FOR [cobalw],
CONSTRAINT [DF__claimlin__rcvamt__3481EE6A] DEFAULT (0) FOR [rcvamt],
CONSTRAINT [DF__claimlin__elgchg__357612A3] DEFAULT (0) FOR [elgchg],
CONSTRAINT [DF__claimlin__whdamt__366A36DC] DEFAULT (0) FOR [whdamt],
CONSTRAINT [DF__claimlin__dscamt__375E5B15] DEFAULT (0) FOR [dscamt],
CONSTRAINT [DF__claimlin__emppy1__38527F4E] DEFAULT (0) FOR [emppy1],
CONSTRAINT [DF__claimlin__emppy2__3946A387] DEFAULT (0) FOR [emppy2],
CONSTRAINT [DF__claimlin__emppay__3A3AC7C0] DEFAULT (0) FOR [emppay],
CONSTRAINT [DF__claimlin__prvpay__3B2EEBF9] DEFAULT (0) FOR [prvpay],
CONSTRAINT [DF__claimlin__to_pay__3C231032] DEFAULT (0) FOR [to_pay],
CONSTRAINT [DF__claimlin__inscod__3D17346B] DEFAULT (' ') FOR [inscod],
CONSTRAINT [DF__claimlin__userid__3E0B58A4] DEFAULT (' ') FOR [userid],
CONSTRAINT [DF__claimlin__sysusr__3EFF7CDD] DEFAULT (' ') FOR [sysusr],
CONSTRAINT [DF__claimlin__adjusr__3FF3A116] DEFAULT (' ') FOR [adjusr],
CONSTRAINT [DF__claimlin__verify__40E7C54F] DEFAULT (' ') FOR [verify],
CONSTRAINT [DF__claimlin__reicsq__41DBE988] DEFAULT (0) FOR [reicsq],
CONSTRAINT [DF__claimlin__reimth__42D00DC1] DEFAULT (0) FOR [reimth],
CONSTRAINT [DF__claimlin__reiexb__43C431FA] DEFAULT (' ') FOR [reiexb],
CONSTRAINT [DF__claimlin__reiexc__44B85633] DEFAULT (' ') FOR [reiexc],
CONSTRAINT [DF__claimlin__reiexm__45AC7A6C] DEFAULT (' ') FOR [reiexm],
CONSTRAINT [DF__claimlin__clmcnv__46A09EA5] DEFAULT (' ') FOR [clmcnv],
CONSTRAINT [DF__claimlin__prfeen__4794C2DE] DEFAULT (' ') FOR [prfeen],
CONSTRAINT [DF__claimlin__reflin__4888E717] DEFAULT (0) FOR [reflin],
CONSTRAINT [DF__claimlin__adjcnt__497D0B50] DEFAULT (0) FOR [adjcnt],
CONSTRAINT [DF__claimlin__fincng__4A712F89] DEFAULT (' ') FOR [fincng],
CONSTRAINT [DF__claimlin__gcrcod__4B6553C2] DEFAULT (' ') FOR [gcrcod],
CONSTRAINT [DF__claimlin__bprcod__4C5977FB] DEFAULT (' ') FOR [bprcod],
CONSTRAINT [DF__claimlin__areacd__4D4D9C34] DEFAULT (' ') FOR [areacd],
CONSTRAINT [DF__claimlin__cntalw__4E41C06D] DEFAULT (0) FOR [cntalw],
CONSTRAINT [DF__claimlin__cntfee__4F35E4A6] DEFAULT (' ') FOR [cntfee],
CONSTRAINT [DF__claimlin__mrktop__502A08DF] DEFAULT (0) FOR [mrktop],
CONSTRAINT [DF__claimlin__cpysrc__511E2D18] DEFAULT (' ') FOR [cpysrc],
CONSTRAINT [DF__claimlin__cntsrc__52125151] DEFAULT (' ') FOR [cntsrc],
CONSTRAINT [DF__claimlin__orgsrc__5306758A] DEFAULT (' ') FOR [orgsrc],
CONSTRAINT [DF__claimlin__modsrc__53FA99C3] DEFAULT (' ') FOR [modsrc],
CONSTRAINT [DF__claimlin__rsksrc__54EEBDFC] DEFAULT (' ') FOR [rsksrc],
CONSTRAINT [DF__claimlin__copsrc__55E2E235] DEFAULT (' ') FOR [copsrc],
CONSTRAINT [DF__claimlin__coptyp__56D7066E] DEFAULT (' ') FOR [coptyp],
CONSTRAINT [DF__claimlin__prepad__57CB2AA7] DEFAULT (' ') FOR [prepad],
CONSTRAINT [DF__claimlin__elgdsc__58BF4EE0] DEFAULT (0) FOR [elgdsc],
CONSTRAINT [DF__claimlin__cntdsc__59B37319] DEFAULT (0) FOR [cntdsc],
CONSTRAINT [DF__claimlin__echkno__5AA79752] DEFAULT (0) FOR [echkno],
CONSTRAINT [DF__claimlin__apttra__5B9BBB8B] DEFAULT (' ') FOR [apttrans],
CONSTRAINT [DF__claimlin__causno__5C8FDFC4] DEFAULT (0) FOR [causno],
CONSTRAINT [DF__claimlin__morgpr__5D8403FD] DEFAULT (' ') FOR [morgpr],
CONSTRAINT [DF__claimlin__morgsc__5E782836] DEFAULT (' ') FOR [morgsc],
CONSTRAINT [DF__claimlin__baspkg__5F6C4C6F] DEFAULT (' ') FOR [baspkg],
CONSTRAINT [DF__claimlin__pkgsrc__606070A8] DEFAULT (' ') FOR [pkgsrc],
CONSTRAINT [DF__claimlin__duplin__615494E1] DEFAULT (' ') FOR [duplin],
CONSTRAINT [DF__claimlin__rvnflg__6248B91A] DEFAULT (' ') FOR [rvnflg],
CONSTRAINT [DF__claimlin__benset__633CDD53] DEFAULT (' ') FOR [benset],
CONSTRAINT [DF__claimlin__mapben__6431018C] DEFAULT (' ') FOR [mapben],
CONSTRAINT [DF__claimlin__clbind__652525C5] DEFAULT (' ') FOR [clbind],
CONSTRAINT [DF__claimlin__pyrnum__661949FE] DEFAULT (' ') FOR [pyrnum],
CONSTRAINT [DF__claimlin__grpcod__670D6E37] DEFAULT (' ') FOR [grpcod],
CONSTRAINT [DF__claimlin__prcmth__68019270] DEFAULT (' ') FOR [prcmth],
CONSTRAINT [DF__claimlin__cntmth__68F5B6A9] DEFAULT (' ') FOR [cntmth],
CONSTRAINT [DF__claimlin__clmcls__69E9DAE2] DEFAULT (' ') FOR [clmcls],
CONSTRAINT [DF__claimlin__cntcls__6ADDFF1B] DEFAULT (' ') FOR [cntcls],
CONSTRAINT [DF__claimlin__cccgrp__6BD22354] DEFAULT (' ') FOR [cccgrp],
CONSTRAINT [DF__claimlin__cntcgp__6CC6478D] DEFAULT (' ') FOR [cntcgp],
CONSTRAINT [DF__claimlin__mthgrp__6DBA6BC6] DEFAULT (' ') FOR [mthgrp],
CONSTRAINT [DF__claimlin__cntmgp__6EAE8FFF] DEFAULT (' ') FOR [cntmgp],
CONSTRAINT [DF__claimlin__modcd2__6FA2B438] DEFAULT (' ') FOR [modcd2],
CONSTRAINT [DF__claimlin__modcd3__7096D871] DEFAULT (' ') FOR [modcd3],
CONSTRAINT [DF__claimlin__modcd4__718AFCAA] DEFAULT (' ') FOR [modcd4],
CONSTRAINT [DF__claimlin__agrtyp__727F20E3] DEFAULT (' ') FOR [agrtyp],
CONSTRAINT [DF__claimlin__cntagt__7373451C] DEFAULT (' ') FOR [cntagt],
CONSTRAINT [DF__claimlin__folhld__74676955] DEFAULT (' ') FOR [folhld],
CONSTRAINT [DF__claimlin__adjtop__755B8D8E] DEFAULT (0) FOR [adjtop],
CONSTRAINT [DF__claimlin__spltfm__764FB1C7] DEFAULT (0) FOR [spltfm],
CONSTRAINT [DF__claimlin__causrc__7743D600] DEFAULT (' ') FOR [causrc],
CONSTRAINT [DF__claimlin__causet__7837FA39] DEFAULT (' ') FOR [causet],
CONSTRAINT [DF__claimlin__dedgp2__792C1E72] DEFAULT (' ') FOR [dedgp2],
CONSTRAINT [DF__claimlin__dedgp3__7A2042AB] DEFAULT (' ') FOR [dedgp3],
CONSTRAINT [DF__claimlin__dedgp4__7B1466E4] DEFAULT (' ') FOR [dedgp4],
CONSTRAINT [DF__claimlin__enbseq__7C088B1D] DEFAULT (0) FOR [enbseq],
CONSTRAINT [DF__claimlin__enpseq__7CFCAF56] DEFAULT (0) FOR [enpseq],
CONSTRAINT [DF__claimlin__dissta__7DF0D38F] DEFAULT (' ') FOR [disstat],
CONSTRAINT [DF__claimlin__authen__7EE4F7C8] DEFAULT (' ') FOR [authent],
CONSTRAINT [DF__claimlin__patnum__7FD91C01] DEFAULT (' ') FOR [patnum],
CONSTRAINT [DF__claimlin__altprv__00CD403A] DEFAULT (' ') FOR [altprv],
CONSTRAINT [DF__claimlin__fmtcod__01C16473] DEFAULT (' ') FOR [fmtcod],
CONSTRAINT [DF__claimlin__covdys__02B588AC] DEFAULT (0) FOR [covdys],
CONSTRAINT [DF__claimlin__estflg__03A9ACE5] DEFAULT (' ') FOR [estflg],
CONSTRAINT [DF__claimlin__daycnt__049DD11E] DEFAULT (0) FOR [daycnt],
CONSTRAINT [DF__claimlin__daytyp__0591F557] DEFAULT (' ') FOR [daytyp],
CONSTRAINT [DF__claimlin__accnum__06861990] DEFAULT (0) FOR [accnum],
CONSTRAINT [DF__claimlin__nopflg__077A3DC9] DEFAULT (' ') FOR [nopflg],
CONSTRAINT [DF__claimlin__biltyp__086E6202] DEFAULT (' ') FOR [biltyp],
CONSTRAINT [DF__claimlin__prvasg__0962863B] DEFAULT (' ') FOR [prvasg],
CONSTRAINT [DF__claimlin__recpnd__0A56AA74] DEFAULT (' ') FOR [recpnd],
CONSTRAINT [DF__claimlin__venpy1__0B4ACEAD] DEFAULT (' ') FOR [venpy1],
CONSTRAINT [DF__claimlin__venpy2__0C3EF2E6] DEFAULT (' ') FOR [venpy2],
CONSTRAINT [DF__claimlin__fileid__0D33171F] DEFAULT (' ') FOR [fileid],
CONSTRAINT [DF__claimlin__altclm__0E273B58] DEFAULT (' ') FOR [altclm],
CONSTRAINT [DF__claimlin__adrseq__0F1B5F91] DEFAULT (0) FOR [adrseq],
CONSTRAINT [DF__claimlin__clmlvl__100F83CA] DEFAULT (0) FOR [clmlvl],
CONSTRAINT [DF__claimlin__mltlvl__1103A803] DEFAULT (0) FOR [mltlvl],
CONSTRAINT [DF__claimlin__linlvl__11F7CC3C] DEFAULT (0) FOR [linlvl],
CONSTRAINT [DF__claimlin__linelv__12EBF075] DEFAULT (0) FOR [linelv],
CONSTRAINT [DF__claimlin__mgpuse__13E014AE] DEFAULT (' ') FOR [mgpuse],
CONSTRAINT [DF__claimlin__ctgcmp__14D438E7] DEFAULT (' ') FOR [ctgcmp],
CONSTRAINT [DF__claimlin__prclvl__15C85D20] DEFAULT (' ') FOR [prclvl],
CONSTRAINT [DF__claimlin__attflg__16BC8159] DEFAULT (' ') FOR [attflg],
CONSTRAINT [DF__claimlin__wrcath__17B0A592] DEFAULT (' ') FOR [wrcath],
CONSTRAINT [DF__claimlin__altcm2__18A4C9CB] DEFAULT (' ') FOR [altcm2],
CONSTRAINT [DF__claimlin__altcm3__1998EE04] DEFAULT (' ') FOR [altcm3],
CONSTRAINT [DF__claimlin__altcm4__1A8D123D] DEFAULT (' ') FOR [altcm4],
CONSTRAINT [DF__claimlin__prcset__1B813676] DEFAULT (' ') FOR [prcset],
CONSTRAINT [DF__claimlin__prcagr__1C755AAF] DEFAULT (' ') FOR [prcagr],
CONSTRAINT [DF__claimlin__cobtot__1D697EE8] DEFAULT (0) FOR [cobtot],
CONSTRAINT [DF__claimlin__cntset__1E5DA321] DEFAULT (' ') FOR [cntset],
CONSTRAINT [DF__claimlin__cntagr__1F51C75A] DEFAULT (' ') FOR [cntagr],
CONSTRAINT [DF__claimlin__asprat__2045EB93] DEFAULT (0) FOR [asprat],
CONSTRAINT [DF__claimlin__pndflg__213A0FCC] DEFAULT (' ') FOR [pndflg],
CONSTRAINT [DF__claimlin__accind__222E3405] DEFAULT (' ') FOR [accind],
CONSTRAINT [DF__claimlin__wrkrel__2322583E] DEFAULT (' ') FOR [wrkrel],
CONSTRAINT [DF__claimlin__stayno__24167C77] DEFAULT (0) FOR [stayno]
GO

The svcdat and the poscod are affecting my results I have run many test and when I eliminate those fields from my query I get what I’m looking for.

This gives me (2156 row(s) affected) in the second result set I looking for it to be the same as the first result set ((2039 row(s) affected))
**
drop table clmstemp
declare @paiddate as char(10),
@begchknum as int,
@endchknum as int

set dateformat mdy
set @paiddate= '01/24/2006'

select
claimno,
sum(to_pay)as total
into clmstemp
from claimlin
where pidate= @paiddate
group by claimno
order by claimno

drop table tempcl
select distinct
t.claimno,
c.chknum,
C.grpnum,
C.provno,
c.membno,
C.svcdat,
C.pidate,
C.poscod,
t.total
into tempcl
from clmstemp AS t left join claimlin as c
ON t.claimno = c.claimno
where pidate= @paiddate
****
If I comment out poscod and svcdat I get ((2039 row(s) affected))on both result sets

drop table clmstemp
declare @paiddate as char(10),
@begchknum as int,
@endchknum as int

set dateformat mdy
set @paiddate= '01/24/2006'

select
claimno,
sum(to_pay)as total
into clmstemp
from claimlin
where pidate= @paiddate
group by claimno
order by claimno

drop table tempcl
select distinct
t.claimno,
c.chknum,
C.grpnum,
C.provno,
c.membno,
-- C.svcdat,
C.pidate,
-- C.poscod,
t.total
into tempcl
from clmstemp AS t left join claimlin as c
ON t.claimno = c.claimno
where pidate= @paiddate

mharry
Starting Member

5 Posts

Posted - 2006-02-09 : 17:08:26

I’m guessing it does this because it must return something for that record and if a value is different (svcdat for the same claim) it will return them both claimlins. This is just not the way it worked in my FoxPro program.

mharry
Starting Member

5 Posts

Posted - 2006-02-09 : 17:15:46

I think I got it….I just forced aggregate functions…added max to the other fields and it looks pretty good now …but thanks.
*********************************************

declare @paiddate as char(10),
@begchknum as int,
@endchknum as int

set dateformat mdy
set @paiddate= '01/24/2006'

select claimno,sum(to_pay)as total
from claimlin
where pidate= @paiddate
group by claimno
order by claimno

select distinct t.claimno,
max(c.chknum)as chknum,
max(C.grpnum) as grpnum,
max(C.provno)as provno,
max(c.membno) as membno,
max(C.svcdat) as svcdat,
max(C.pidate)as paiddate,
max(C.poscod)as poscod,
max(t.total)as totalfrom clmstemp AS t inner join claimlin as c
ON t.claimno = c.claimno
group by t.claimno
********************************

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources