Showing posts with label row. Show all posts
Showing posts with label row. Show all posts

Wednesday, March 21, 2012

Help Needed: For Creating Synchronous Transform Component

Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.

So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.

Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.


Thanks Steve

There are a whole host of samples now out, as well as those that shipped in the box, one of them will help I'm sure-

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20SSIS%20Sample%20Component&DisplayLang=en

(Or call jamie and he'll tell you, even if he does ask me!)

|||

Just been talking with Steve about this offline.

Turns out that, based on his requirement, he needed an asynch component. However, the same number of rows will be coming in as are going out, its just that the "shape" of the output (i.e. the metadata) needs to change.

Steve doesn't need to cache all the data in memory which is usually what asynch components do. They don't have to though. ProcessInput() can push rows to the output as soon as they are encountered without storing internally - thus giving the "illusion" of it being a synch component.

-Jamie

|||

I would first like to clear up some confusion on asynchronous outputs (I have tried before but it persists :)). Having an async output does not mean that the component waits until all the data has been seen to output any data. All it means is that the data coming out of the component is a copy in a new buffer. The Union All transform has an async output but it clearly does not wait until it receives all the data from all its inputs before outputing any data. In fact, most of the stock components with async outputs start generating output data well before all the input data has been seen.

In specific anwer to your question you can do one of 2 things. The first is to add additional output columns to the match output and keep it in sync with the input. The 2nd is to have your match output be an async output. Normally the 1st is better if you can do it because you don't have a memory copy. However, since you stated you don't want to keep any data from the input data on a match then in your case making the output async is actually the better alternative because you will actually wind up saving memory by doing so (due to the internals of the buffering system that have to do with sync outputs with additional columns causing buffer row widening).

As for sample, as Darren pointed out there are plenty to choose from. Although there probably are none that have a component with one async and one sync output there will be very little difference with just looking at both a sync and async sample and mixing and matching the appropriate code that is needed.

Thanks,

Matt

|||

I had a look and could not see a sample with two synch outputs which is what I thought was required at first. Steve now has a simple example I wrote for him earlier, but for reference the only (public) asynch sample I know of is the RemoveDuplicates component which ships in the box. This does cache data, which is a good example of this style of transform, but since all he work is done in ProcessInput, although it would spane several invocations of this method, it is fairly easy to see how you can change this blocking nature, and start passing rows straight to the output buffer as soon as received from the input.

|||

Interesting thread. I feel a blog post coming on but in this case I'm gonna leave it to Steve as he is keen to share what he's learnt here. Keep a look out!

-Jamie

|||

I was hoping for a little bit more information about Asynchronous output, especially where the input is sent to the output right away instead of using blocking.

Is there any more information on developing Async components besides what has already listed? I've already looked at Remove Duplicates from the samples but find it a litte confusing without a document to go with it explaining the process and why certain techniques were used.

I'm trying to create an Async component with 1 input, 3 outputs. I basically want some processing to be done that will determine which of the three outputs to go to. I'm not concerned about the decision making part (which output to go to) but more so on how to move the data to the outputs right way.

|||

If all the rows that come in, and no more than those that come in, end up in one of the outputs, then this would be best done as a synch component.

You would create your 3 outputs in ProvideComponentProperties, setting the SynchronousInputID property to that of yuor input. Since you have several outputs you also need to set the ExclusionGroup property for each output, each should have a unique value to differentiate them. You then use the DirectRow method of the PipelineBuffer to send each row to the output you decide.

Lookup the ExclusionGroup property help topic for a simple example.

Help Needed: For Creating Synchronous Transform Component

Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.

So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.

Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.


Thanks Steve

There are a whole host of samples now out, as well as those that shipped in the box, one of them will help I'm sure-

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20SSIS%20Sample%20Component&DisplayLang=en

(Or call jamie and he'll tell you, even if he does ask me!)

|||

Just been talking with Steve about this offline.

Turns out that, based on his requirement, he needed an asynch component. However, the same number of rows will be coming in as are going out, its just that the "shape" of the output (i.e. the metadata) needs to change.

Steve doesn't need to cache all the data in memory which is usually what asynch components do. They don't have to though. ProcessInput() can push rows to the output as soon as they are encountered without storing internally - thus giving the "illusion" of it being a synch component.

-Jamie

|||

I would first like to clear up some confusion on asynchronous outputs (I have tried before but it persists :)). Having an async output does not mean that the component waits until all the data has been seen to output any data. All it means is that the data coming out of the component is a copy in a new buffer. The Union All transform has an async output but it clearly does not wait until it receives all the data from all its inputs before outputing any data. In fact, most of the stock components with async outputs start generating output data well before all the input data has been seen.

In specific anwer to your question you can do one of 2 things. The first is to add additional output columns to the match output and keep it in sync with the input. The 2nd is to have your match output be an async output. Normally the 1st is better if you can do it because you don't have a memory copy. However, since you stated you don't want to keep any data from the input data on a match then in your case making the output async is actually the better alternative because you will actually wind up saving memory by doing so (due to the internals of the buffering system that have to do with sync outputs with additional columns causing buffer row widening).

As for sample, as Darren pointed out there are plenty to choose from. Although there probably are none that have a component with one async and one sync output there will be very little difference with just looking at both a sync and async sample and mixing and matching the appropriate code that is needed.

Thanks,

Matt

|||

I had a look and could not see a sample with two synch outputs which is what I thought was required at first. Steve now has a simple example I wrote for him earlier, but for reference the only (public) asynch sample I know of is the RemoveDuplicates component which ships in the box. This does cache data, which is a good example of this style of transform, but since all he work is done in ProcessInput, although it would spane several invocations of this method, it is fairly easy to see how you can change this blocking nature, and start passing rows straight to the output buffer as soon as received from the input.

|||

Interesting thread. I feel a blog post coming on but in this case I'm gonna leave it to Steve as he is keen to share what he's learnt here. Keep a look out!

-Jamie

|||

I was hoping for a little bit more information about Asynchronous output, especially where the input is sent to the output right away instead of using blocking.

Is there any more information on developing Async components besides what has already listed? I've already looked at Remove Duplicates from the samples but find it a litte confusing without a document to go with it explaining the process and why certain techniques were used.

I'm trying to create an Async component with 1 input, 3 outputs. I basically want some processing to be done that will determine which of the three outputs to go to. I'm not concerned about the decision making part (which output to go to) but more so on how to move the data to the outputs right way.

|||

If all the rows that come in, and no more than those that come in, end up in one of the outputs, then this would be best done as a synch component.

You would create your 3 outputs in ProvideComponentProperties, setting the SynchronousInputID property to that of yuor input. Since you have several outputs you also need to set the ExclusionGroup property for each output, each should have a unique value to differentiate them. You then use the DirectRow method of the PipelineBuffer to send each row to the output you decide.

Lookup the ExclusionGroup property help topic for a simple example.

sql

Monday, March 19, 2012

Help needed!

Hello guys,
I'm new to the forum and to MS SQL 2K.
I'm trying to a merge similar rows in a table into a single row and put them in a new table.

Example:-
This is my input table
TableA
ID A B C
--------
1 jk kl bj
2 sd we op
3 io po kl
1 ui gh ew
2 kl re op
1 qw kj nn

My output table should look like this
TableB
ID A1 B1 C1 A2 B2 C2 A3 B3 C3
----------------
1 jk kl bj ui gh ew qw kj nn
2 sd we op kl re op
3 io po kl

Please help me on how to create my output.
Thanks in advance,

Sid.You want help violating the rules of normalization? Should I buy you a carton of cigarettes while I'm at it? Neither activity is healthy.

Seriously, if you must do this it is important to know whether the number of records for an ID is fixed or not. If it can be any number then you are not going to be able to define the columns on your output table ahead of time and you are left with a messy dynamic query task. If there is a limit on the number of records per ID then your problem is merely a moderately difficult cross-tab query.|||normalization or not
this is a good exercise to displace data
kinda like playing scales before you actually play a song on an instrument

i will be working on this tonight|||I'll check this query today ...|||giving up

I don't understand to purpose of this query|||i now have a headache|||There's not much point in pursuing this without further clarification from coolhandsid, so save the Tylenol.|||I've got

Table1
ID | A | B | C
1 | 2 | 3 | 4
2 | 9 | 4 | 5
3 | 22| 53 94

I want this result

Table1
ID X Y Z
1 | 81 | April | NULL
Y | 12 | Dog | Sheep
12.3 | Cherry | Spain | 3|||I've got broccolli and I want lobster. I can't make one out of the other either.|||Remeber the MASH episode (when they used to be good) when they made the spam lamb for the turkish troops?|||Originally posted by blindman
You want help violating the rules of normalization? Should I buy you a carton of cigarettes while I'm at it? Neither activity is healthy.

I would not want you doing the first ... but you can certainly buy that carton of cigs for me ...|||Thanks for all your help :),

I figured it out , it can be down by a DTS package or a cross-tab query.

Sid.

Originally posted by Karolyn
I've got

Table1
ID | A | B | C
1 | 2 | 3 | 4
2 | 9 | 4 | 5
3 | 22| 53 94

I want this result

Table1
ID X Y Z
1 | 81 | April | NULL
Y | 12 | Dog | Sheep
12.3 | Cherry | Spain | 3

Monday, March 12, 2012

Help needed- regarding datediff functio

Hi,
I have to compare every row in month column in xyz table with current month. If there is mismatch, then error message must be displayed.
I have used DATEDIFF (MM, Select getdate(), select month from xyz ), but I am getting error at MM. Can anybody fix this issue.

The syntax you have is incorrect. Try something like this:

SELECT [Month], Mismatch=CaseWHENMonth(Getdate())= [Month]then 0ELSE 1END

FROM MyTable

|||

This will work:

1SELECT MonthColumn,2 IsMatchColumn =3CaseWHENDatePart(Month,GetDate()) = MonthColumn4Then 0-- Yes (Matched)5Else 1-- No (Not Matched)6END78FROM YourTableName9

Good luck.

Friday, February 24, 2012

Help me solve this.

When I collect data from a form, I recieved this message:

"Cannot create a row of size 8822 which is greater than the allowable maximum of 8060. The statement has been terminated."

Can any body tell me how to solve this, my data field is varchar size 8000 bytes, and I posted only ~ 6000 byte.Show the entire table definition, and perhaps we can help.|||There are several data types that store a fixed amount of data. For example, a char (10) field will store 10 bytes even if you never assign a value to it. So, if you have 4000 bytes worth of fixed-length fields, and you put 6000 bytes into a varchar(8000) field, you would end up with a record that's 10000 bytes and the update wil fail.

Sunday, February 19, 2012

Help me join data from 2 common tables into 1 row. I'm outta ideas!

Common table: PrimaryClaims

Facility

ClaimNumber

opsd

8

088270301

06/26/2007

8

088270701

06/26/2007

8

088270801

06/26/2007

8

088477201

06/26/2007

8

088477701

06/26/2007

Common table: SecondaryClaims

Facility

ClaimNumber

ossd

8

069294101

06/26/2007

8

069633701

06/26/2007

8

073847301

06/26/2007

8

075659901

06/26/2007

8

077253001

06/26/2007

8

081933301

06/26/2007

8

082344301

06/26/2007

To make it easier to visualize my example, the above are the data for the common tables. Here’s what I got so far:

Code Snippet

WITH PrimaryClaims AS

(

SELECT DISTINCT Facility, ClaimNumber, OriginalPrimarySubmissionDate AS opsd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalPrimarySubmissionDate = '6/26/07'

),

SecondaryClaims AS

(

SELECT DISTINCT Facility, ClaimNumber, OriginalSecondarySubmissionDate AS ossd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalSecondarySubmissionDate = '6/26/07'

)

SELECT pc.Facility, COUNT(pc.opsd) AS PCount, sc.Facility, COUNT(sc.ossd) AS SCount

FROM PrimaryClaims pc FULL OUTER JOIN SecondaryClaims sc ON pc.ClaimNumber = sc.ClaimNumber

GROUP BY pc.Facility, sc.Facility

My current results:

Facility

PCount

Facility

SCount

8

5

NULL

0

NULL

0

8

7

I’ve played with INNER JOIN, all OUTER JOINs, and CROSS JOIN to no avail. I’ve added different conditions into the joins and still won’t work. I’ve done many other attempts as well. Too many to list and still won’t work. I’m thinking I’m missing something so simple.

What I’m trying to do is to get all the data on one row. The query basically counts the number of primary and secondary claims. I’d like to get them in a row so I can make a report out of it. Any help is greatly appreciated!

Why do you need to JOIN the tables together? Can you just write a CTE or inline sub queries to get the count of each table?

Code Snippet

SELECT (SELECT COUNT(*) FROM PrimaryClains WHERE ....) AS PCount

,(SELECT COUNT(*) FROM SecondaryClaimms WHERE ...) AS SCount

|||

Using your cte values here some code, and then a modification to your code to achieve the same from the source table (which obviously I haven't tested)

Code Snippet

drop table #PrimaryCLaims

drop table #SecondaryClaims

create table #PrimaryClaims ( Facility int, ClaimNumber int, opsd datetime)

insert into #PrimaryClaims values (8, 088270301, '06/26/2007')

insert into #PrimaryClaims values (8, 088270701, '06/26/2007')

insert into #PrimaryClaims values (8, 088270801, '06/26/2007')

insert into #PrimaryClaims values (8, 088477201, '06/26/2007')

insert into #PrimaryClaims values (8, 088477701, '06/26/2007')

create table #SecondaryClaims( Facility int, ClaimNumber int, ossd datetime)

insert into #SecondaryClaims values (8, 069294101, '06/26/2007')

insert into #SecondaryClaims values (8, 069633701, '06/26/2007')

insert into #SecondaryClaims values (8, 073847301, '06/26/2007')

insert into #SecondaryClaims values (8, 075659901, '06/26/2007')

insert into #SecondaryClaims values (8, 077253001, '06/26/2007')

insert into #SecondaryClaims values (8, 081933301, '06/26/2007')

insert into #SecondaryClaims values (8, 082344301, '06/26/2007')

select p.Facility, p.pcount, s.scount

from

( select Facility, count(*) pcount from #PrimaryClaims group by Facility ) as p

inner join

( select Facility, count(*) scount from #SecondaryClaims group by Facility) as s

on p.Facility = s.Facility

WITH PrimaryClaims AS

(

Select Facility, count(*) as pcount from

(

SELECT DISTINCT Facility, ClaimNumber, OriginalPrimarySubmissionDate AS opsd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalPrimarySubmissionDate = '6/26/07'

) as p1

Group by Facility

),

SecondaryClaims AS

(

Select Facility, count(*) as scount from

(

SELECT DISTINCT Facility, ClaimNumber, OriginalSecondarySubmissionDate AS ossd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalSecondarySubmissionDate = '6/26/07'

) as s1

Group by Facility

)

SELECT pc.Facility, pc.pcount as PCount, sc.scount AS SCount

FROM PrimaryClaims pc

INNER JOIN SecondaryClaims sc

ON pc.ClaimNumber = sc.ClaimNumber

|||

Unless I'm totally mis-reading your question, it seems like both -it can be done without a JOIN and it can be done a lot simpler.

Does this come close?

Code Snippet


DECLARE @.Claims table
( Facility int,
ClaimNumber int,
OriginalPrimarySubmissionDate datetime,
OriginalSecondarySubmissionDate datetime,
ClaimSubmitted int
)


INSERT INTO @.Claims VALUES ( 8, 088270301, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088270701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088270801, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477201, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 069294101, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 069633701, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 073847301, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 075659901, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 077253001, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 081933301, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/26/2007', 1 )
-- Data to test criteria
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/27/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/27/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 082344302, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 088477703, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/26/2007', NULL, 0 )
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/26/2007', 0 )
INSERT INTO @.Claims VALUES ( 7, 088477701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 9, 082344301, NULL, '06/26/2007', 1 )


SELECT
Facility,
PCount = count( OriginalPrimarySubmissionDate ),
SCount = count( OriginalSecondarySubmissionDate )
FROM @.Claims
WHERE ( ClaimSubmitted = 1
AND ClaimNumber LIKE '%01'
AND Facility = 8
AND ( OriginalPrimarySubmissionDate = '2007/06/26'
OR OriginalSecondarySubmissionDate = '2007/06/26'
)
)
GROUP BY Facility


Facility PCount SCount
-- -- --
8 5 7

|||hi you can also try this one, but this is with the assumption that all primary claim may or may not have a secondary claim, that's why a left join was used

create table #primaryclaims ( Facility int, ClaimNumber int, opsd datetime)
insert into #primaryclaims values (8, 088270301, '06/26/2007')
insert into #primaryclaims values (8, 088270701, '06/26/2007')
insert into #primaryclaims values (8, 088270801, '06/26/2007')
insert into #primaryclaims values (8, 088477201, '06/26/2007')
insert into #primaryclaims values (8, 088477701, '06/26/2007')

create table #secondaryclaims( Facility int, ClaimNumber int, ossd datetime)
insert into #secondaryclaims values (8, 069294101, '06/26/2007')
insert into #secondaryclaims values (8, 069633701, '06/26/2007')
insert into #secondaryclaims values (8, 073847301, '06/26/2007')
insert into #secondaryclaims values (8, 075659901, '06/26/2007')
insert into #secondaryclaims values (8, 077253001, '06/26/2007')
insert into #secondaryclaims values (8, 081933301, '06/26/2007')
insert into #secondaryclaims values (8, 082344301, '06/26/2007')

select p.facility
, p.pcount
, s.facility
, s.scount
from ( select p.facility
, count(*) as pcount
from #primaryclaims p
group by
p.facility
) p left outer join
( select s.facility
, count(*) as scount
from #secondaryclaims s
group by
s.facility
) s on p.facility = s.facility

drop table #primaryclaims
drop table #secondaryclaims|||

THANKS FOR ALL YOUR HELP GUYS! Some famaliar faces in the replies! Thanks so much...

Although I didn't go with any of your possible solutions, your examples combined with further research got me what i was looking for. I understand it's very difficult to explain the whole picture when asking for help because the tables we're working with can span infinite space and time. I always try to give the most information with the least amount of data to get my question across so I don't waste anyone's time too much.

I ended up just using a simple COUNT(DISTINCT...) transact in the CTE. My count was based on claims with the primary and secondary submission dates, which were static amongst all the records in the result set. With that in mind, I just counted the claim numbers. For anyone who's reading this and wants to know how i did it, here's the new query:

Code Snippet

WITH PrimaryClaims AS

(

SELECT FacilityName, COUNT(DISTINCT ClaimNumber) AS PCount

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalPrimarySubmissionDate = '6/26/07'

GROUP BY FacilityName

),

SecondaryClaims AS

(

SELECT FacilityName, COUNT(DISTINCT ClaimNumber) AS SCount

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalSecondarySubmissionDate = '6/26/07'

GROUP BY FacilityName

)

SELECT pc.FacilityName, pc.PCount, sc.SCount

FROM PrimaryClaims pc

LEFT OUTER JOIN SecondaryClaims sc ON pc.FacilityName = sc.FacilityName

This gives me what i wanted in one row. Thanks again for all the help and inspiration!

Help me join data from 2 common tables into 1 row. I'm outta ideas!

Common table: PrimaryClaims

Facility

ClaimNumber

opsd

8

088270301

06/26/2007

8

088270701

06/26/2007

8

088270801

06/26/2007

8

088477201

06/26/2007

8

088477701

06/26/2007

Common table: SecondaryClaims

Facility

ClaimNumber

ossd

8

069294101

06/26/2007

8

069633701

06/26/2007

8

073847301

06/26/2007

8

075659901

06/26/2007

8

077253001

06/26/2007

8

081933301

06/26/2007

8

082344301

06/26/2007

To make it easier to visualize my example, the above are the data for the common tables. Here’s what I got so far:

Code Snippet

WITH PrimaryClaims AS

(

SELECT DISTINCT Facility, ClaimNumber, OriginalPrimarySubmissionDate AS opsd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalPrimarySubmissionDate = '6/26/07'

),

SecondaryClaims AS

(

SELECT DISTINCT Facility, ClaimNumber, OriginalSecondarySubmissionDate AS ossd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE '%01' AND Facility = 8 AND OriginalSecondarySubmissionDate = '6/26/07'

)

SELECT pc.Facility, COUNT(pc.opsd) AS PCount, sc.Facility, COUNT(sc.ossd) AS SCount

FROM PrimaryClaims pc FULL OUTER JOIN SecondaryClaims sc ON pc.ClaimNumber = sc.ClaimNumber

GROUP BY pc.Facility, sc.Facility

My current results:

Facility

PCount

Facility

SCount

8

5

NULL

0

NULL

0

8

7

I’ve played with INNER JOIN, all OUTER JOINs, and CROSS JOIN to no avail. I’ve added different conditions into the joins and still won’t work. I’ve done many other attempts as well. Too many to list and still won’t work. I’m thinking I’m missing something so simple.

What I’m trying to do is to get all the data on one row. The query basically counts the number of primary and secondary claims. I’d like to get them in a row so I can make a report out of it. Any help is greatly appreciated!

Why do you need to JOIN the tables together? Can you just write a CTE or inline sub queries to get the count of each table?

Code Snippet

SELECT (SELECT COUNT(*) FROM PrimaryClains WHERE ....) AS PCount

,(SELECT COUNT(*) FROM SecondaryClaimms WHERE ...) AS SCount

|||

Using your cte values here some code, and then a modification to your code to achieve the same from the source table (which obviously I haven't tested)

Code Snippet

droptable #PrimaryCLaims

droptable #SecondaryClaims

createtable #PrimaryClaims ( Facility int, ClaimNumber int, opsd datetime)

insertinto #PrimaryClaims values(8, 088270301,'06/26/2007')

insertinto #PrimaryClaims values(8, 088270701,'06/26/2007')

insertinto #PrimaryClaims values(8, 088270801,'06/26/2007')

insertinto #PrimaryClaims values(8, 088477201,'06/26/2007')

insertinto #PrimaryClaims values(8, 088477701,'06/26/2007')

createtable #SecondaryClaims( Facility int, ClaimNumber int, ossd datetime)

insertinto #SecondaryClaims values(8, 069294101,'06/26/2007')

insertinto #SecondaryClaims values(8, 069633701,'06/26/2007')

insertinto #SecondaryClaims values(8, 073847301,'06/26/2007')

insertinto #SecondaryClaims values(8, 075659901,'06/26/2007')

insertinto #SecondaryClaims values(8, 077253001,'06/26/2007')

insertinto #SecondaryClaims values(8, 081933301,'06/26/2007')

insertinto #SecondaryClaims values(8, 082344301,'06/26/2007')

select p.Facility, p.pcount, s.scount

from

(select Facility,count(*) pcount from #PrimaryClaims groupby Facility )as p

innerjoin

(select Facility,count(*) scount from #SecondaryClaims groupby Facility)as s

on p.Facility = s.Facility

WITH PrimaryClaims AS

(

Select Facility,count(*)as pcount from

(

SELECTDISTINCT Facility, ClaimNumber, OriginalPrimarySubmissionDate AS opsd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE'%01'AND Facility = 8 AND OriginalPrimarySubmissionDate ='6/26/07'

)as p1

Groupby Facility

),

SecondaryClaims AS

(

Select Facility,count(*)as scount from

(

SELECTDISTINCT Facility, ClaimNumber, OriginalSecondarySubmissionDate AS ossd

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE'%01'AND Facility = 8 AND OriginalSecondarySubmissionDate ='6/26/07'

)as s1

Groupby Facility

)

SELECT pc.Facility, pc.pcount as PCount, sc.scount AS SCount

FROM PrimaryClaims pc

INNERJOIN SecondaryClaims sc

ON pc.ClaimNumber = sc.ClaimNumber

|||

Unless I'm totally mis-reading your question, it seems like both -it can be done without a JOIN and it can be done a lot simpler.

Does this come close?

Code Snippet


DECLARE @.Claims table
( Facility int,
ClaimNumber int,
OriginalPrimarySubmissionDate datetime,
OriginalSecondarySubmissionDate datetime,
ClaimSubmitted int
)


INSERT INTO @.Claims VALUES ( 8, 088270301, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088270701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088270801, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477201, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 069294101, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 069633701, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 073847301, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 075659901, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 077253001, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 081933301, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/26/2007', 1 )
-- Data to test criteria
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/27/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/27/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 082344302, NULL, '06/26/2007', 1 )
INSERT INTO @.Claims VALUES ( 8, 088477703, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 8, 088477701, '06/26/2007', NULL, 0 )
INSERT INTO @.Claims VALUES ( 8, 082344301, NULL, '06/26/2007', 0 )
INSERT INTO @.Claims VALUES ( 7, 088477701, '06/26/2007', NULL, 1 )
INSERT INTO @.Claims VALUES ( 9, 082344301, NULL, '06/26/2007', 1 )


SELECT
Facility,
PCount = count( OriginalPrimarySubmissionDate ),
SCount = count( OriginalSecondarySubmissionDate )
FROM @.Claims
WHERE ( ClaimSubmitted = 1
AND ClaimNumber LIKE '%01'
AND Facility = 8
AND ( OriginalPrimarySubmissionDate = '2007/06/26'
OR OriginalSecondarySubmissionDate = '2007/06/26'
)
)
GROUP BY Facility


Facility PCount SCount
-- -- --
8 5 7

|||hi you can also try this one, but this is with the assumption that all primary claim may or may not have a secondary claim, that's why a left join was used

create table #primaryclaims ( Facility int, ClaimNumber int, opsd datetime)
insert into #primaryclaims values (8, 088270301, '06/26/2007')
insert into #primaryclaims values (8, 088270701, '06/26/2007')
insert into #primaryclaims values (8, 088270801, '06/26/2007')
insert into #primaryclaims values (8, 088477201, '06/26/2007')
insert into #primaryclaims values (8, 088477701, '06/26/2007')

create table #secondaryclaims( Facility int, ClaimNumber int, ossd datetime)
insert into #secondaryclaims values (8, 069294101, '06/26/2007')
insert into #secondaryclaims values (8, 069633701, '06/26/2007')
insert into #secondaryclaims values (8, 073847301, '06/26/2007')
insert into #secondaryclaims values (8, 075659901, '06/26/2007')
insert into #secondaryclaims values (8, 077253001, '06/26/2007')
insert into #secondaryclaims values (8, 081933301, '06/26/2007')
insert into #secondaryclaims values (8, 082344301, '06/26/2007')

select p.facility
, p.pcount
, s.facility
, s.scount
from ( select p.facility
, count(*) as pcount
from #primaryclaims p
group by
p.facility
) p left outer join
( select s.facility
, count(*) as scount
from #secondaryclaims s
group by
s.facility
) s on p.facility = s.facility

drop table #primaryclaims
drop table #secondaryclaims|||

THANKS FOR ALL YOUR HELP GUYS! Some famaliar faces in the replies! Thanks so much...

Although I didn't go with any of your possible solutions, your examples combined with further research got me what i was looking for. I understand it's very difficult to explain the whole picture when asking for help because the tables we're working with can span infinite space and time. I always try to give the most information with the least amount of data to get my question across so I don't waste anyone's time too much.

I ended up just using a simple COUNT(DISTINCT...) transact in the CTE. My count was based on claims with the primary and secondary submission dates, which were static amongst all the records in the result set. With that in mind, I just counted the claim numbers. For anyone who's reading this and wants to know how i did it, here's the new query:

Code Snippet

WITH PrimaryClaims AS

(

SELECT FacilityName,COUNT(DISTINCT ClaimNumber)AS PCount

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE'%01'AND Facility = 8 AND OriginalPrimarySubmissionDate ='6/26/07'

GROUPBY FacilityName

),

SecondaryClaims AS

(

SELECT FacilityName,COUNT(DISTINCT ClaimNumber)AS SCount

FROM Claims

WHERE ClaimSubmitted = 1 AND ClaimNumber LIKE'%01'AND Facility = 8 AND OriginalSecondarySubmissionDate ='6/26/07'

GROUPBY FacilityName

)

SELECT pc.FacilityName, pc.PCount, sc.SCount

FROM PrimaryClaims pc

LEFTOUTERJOIN SecondaryClaims sc ON pc.FacilityName = sc.FacilityName

This gives me what i wanted in one row. Thanks again for all the help and inspiration!