MS SQL Server :: How To Get Records In Groups

How To Get Unique Records When There Are More Groups

Oct 7, 2005

Steve writes "I need a query to show all the medicare patients only. I want only one kind of insurance for each patient. I am getting records where each patient has more than one insurance like medicare and something else.
Each patient has one,two, three or more insurances like
medicare,medical,blue cross,tricare etc.

The result should look like this:
Sam pick Medicare
John white Medicare
Ann Richmond Medicare

My query retrieves patients with more than one insurance.

select patient_first_name f_name,patient_last_name l_name,patient_zip zip,
count(distinct payer_id) payer_id
from claims
where payer_id ='abcdefhj'
group by patient_first_name, patient_last_name,patient_zip
having count(distinct payer_id)= 1
order by patient_zip asc;

please help me. Greatly appreciate your response."

View 1 Replies View Related

Selcting Records In Groups Of 5000

May 18, 2001

Hi,

I have a table with 1.6 million records with a foreign key called "ums_id" (int). I would like to select the ums_id's in groups of 5000. is there a simpler way to break these id's up instead of

select count(*)
from newsletter_opt_in
where ums_id between 493525 and 510075
and opt_in_Id = 1 and opt_in_status = 'Y'

I would like to break up the whole 1.5 million in one sql statement if I can.
Thanks, Mark

View 1 Replies View Related

SQL Server 2014 :: Getting Unique Number For Groups Of Records?

Aug 6, 2014

I'm trying to do the following and haven't been able to figure it out.

Say there's a table with these records:

Col1 Col2 Col3
a b c
a b c
a b d
e f g
e f g

I want to generate a number that represents the groups of columns like this:

Col1 Col2 Col3 MyNumber
a b c 1
a b c 1
a b d 2
e f g 3
e f g 3

So that each grouping gets its own identifier. I've tried this:

SELECT Col1, Col2, Col3
row_number() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3) AS MyNumber
FROM MyTable

But I get this:

Col1 Col2 Col3 MyNumber
a b c 1
a b c 2
a b d 1
e f g 1
e f g 2

View 9 Replies View Related

Transact SQL :: Optimize Count Distinct For Multiple Groups Of Records?

May 21, 2015

I have a CTE returning a recordset which contains a column SRC. SRC is a number which I use later to get counts and sums for the records in a distinct list.

declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as
(
SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row,
u.SRC,

[Code] ....

-- If Referral start date is between our requested dates

ref.Referral_Start_Date between @startdate and @enddate

OR

-- Include referrals which started before our requested date, but are still active during our date range.

(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
)
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
)
SELECT
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,

[code]....

View 5 Replies View Related

Integration Services :: Assigning Records To Groups In SSIS Like NTILE?

Aug 18, 2015

I am in middle of my transformation where I have to assign records equally among 3 different groups. I can do that in SQL using NTILE() Over() function. How do I do that in SSIS package. I have applied different business rules during transformation to get unique records and now I have to assign those records to 3 group in and generate excel report.Basically, I will need to have another column which will have those group numbers.

View 6 Replies View Related

Problem With Matrix (in Subreport, Multiple Groups), Groups Repeating First Row Data

Jan 25, 2008

I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.

I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:

ID1
ID2
DisplayData

1
1
A

1
2
B

1
3
C

2
1
A

2
2
B

2
3
C

Parent group is on ID1, child group is on ID2, report would show:

1
1
A

2
A

3
A

2
1
A

2
A

3
A

Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...

View 2 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Counts By Groups

Jan 8, 2007

I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID
2 from EvalAnswers
3 where CoID=@CoID
4 and EvaluatorID=@EvaluatorID
5 and (Scr0=0 and Sugg0 is NULL)
6 --group by TeamID
7

View 4 Replies View Related

Groups In SQLServer2000

Jan 31, 2002

Hi,
I would like to create groups in SQLServer2000.Do i need to do this by roles or can i create groups for each database.I read some where that there where
no groups for SQLServer7.0. Is this option available for the latest versions.
Any help will be appreciated.

Thanks in Advance.
Ran.

View 3 Replies View Related

SAN's And File Groups

Jan 8, 2001

If you have a SAN, is there any real benefit to breaking out large tables into file groups?

View 1 Replies View Related

File Groups

Nov 3, 2007

I am looking to find out when to use file groups when backing up. When should you use this, what's the benefit over just doing a full db backup? Is it better when you are dealing with large db's?

Also this question has been on my mind for a while. Why shouldn't you shrink the db after every full backup? What is the negative in doing so?

Thanks

View 1 Replies View Related

OT? Mysql Groups

Jul 20, 2005

I realize the irony of asking this here, but does anyone know of any MySQLgroups where I can ask my questions?Thanks.

View 1 Replies View Related

Getting Sum Of Certain Groups Using Inscope

May 16, 2008

In my report I have 3 row groups and two column groups as follows, where measure is number of hours for a given project.

2008/05 2008/06 ........
Demand | Allocated Demand | Allocated
- Project 1 1500 1000

- Department1 500 400

- Employee1 200
- Employee2 200

- Department2 1000 600

- Employee3 300
- Employee4 300

+ Project 2 1200 1000
-------------------------------------------------------------------------------------------------------------------------

Grand Total 2700 2000

My question is, is this possible using a Matrix? I have used matrix in some of my reports but have always used automatic/generic SUM feature it provides.

Note carefully that there are no values for Employees in Demand Column Group. Basically I would have to SUM Department groupings to get the Project level total and then SUM all Project Groupings to get the grand total. The Allocation column is okay since the Employees have values in that column. Can this be done using InScope( ) or some other function rather than simply doing the Generic SUM of Rows and Columns of the matrix which would probably throw error when trying to sum a blank value in demand column.

Thanks in advance.

View 2 Replies View Related

Headers And Groups

Apr 13, 2007

How do I put information only at the top of a page and have it change whenever a group occurs.

I want to do something like this. Have this on the top of every page:

Country: current country

State: current state

City: current city

There is a group on country, state and city. Whenever city changes the next page will display a new city. Whenever state changes the next page will display the new state in the header.

I have already put Country, State, and City into their own group headers. However, if either of them change in the middle of the page then the group header repeats. I have group header repeat on so I can see it on each page. If I choose the Page break at start then it breaks but I don't want it to break. I want it to run on the same page if it passes a group and then have the next page display relevant information for the group.

Putting it in the table header works as far as keeping them all on the top of the page but it doesn't change when a grouping ends.

Please assist me in anyway you can.

Thanks,

Sarah

View 9 Replies View Related

Groups Question

Mar 17, 2008

I have my first SSRS report going, after years on Crystal and Access. It has several groups, each nested within the next...I think. I'm having a little trouble with the groups...well, grouping the way I thought I had it set up. The groups come out looking like my primary group is second instead of first, although when I look at the groups in "edit groups" it appears I have them set up correctly.
In Crystal, on the preview page you could see the groups idendified so if there was a problem in a report you could tell what group it was coming from. You could also select and change objects in preview, which made it nice to see the impact of changes. I cannot see either of these features in SSRS. I'm hoping that there's a setting I need to turn on, and also hoping that this product has these user friendly features. Any ideas on that?

Also, in Crystal, when you set up a group or a formula, it became available in the field list within the formula builder, so you could use them by picking them instead of typing the whole thing in again, and that's not available either. Or I can't find it!

Since I'm new to SSRS maybe there are features and settings I haven't found yet, but would appreciate any comments from more experienced users about the overall usability of this tool.

Thanks!

View 9 Replies View Related

Table Groups Keep Together

Apr 20, 2007

Is there any way to keep a table group together. MSFT says no but I can't believe this limitation exists. It's a basic reporting function in any reporting software I've used.

I have one group in a table. I don't want the detail rows to be split when it gets to the bottom of the page.

View 6 Replies View Related

Missing Groups

Nov 3, 2007

Server:
Sql 2005 SP2 running on Win2003 Ent. SP1

I have a 3rd party app that needs me to add a user to the SQLServer2005SQLAgentUser group for the instance I placed the DB in. This group doesn't exist on my server for any of my instances. Is there a simple way to generate it or a document that can tell me how to manually create it?

View 4 Replies View Related

File Groups

Jun 28, 2006

Hi everyone,
When I do the following, did I put the files in Test1FG1 file group to the default file group(Primary) ?

ALTER DATABASE Test1

MODIFY FILEGROUP Test1FG1 DEFAULT
GO

Thanks

View 8 Replies View Related

Custom Groups

Jul 18, 2006

I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.

View 6 Replies View Related

Ceilings On Groups

Apr 24, 2008

Hi All,

I have a challenging report to create. I need to create a report where I'll have groups then I need to have a ceiling on the group to print only 10 records (if there are more than 10 records) then go to the next page and print the final records for that group. I can do the grouping and I can do the ceiling, but I am having a hard time doing both. I'm open for any suggestions.

Thanks

View 1 Replies View Related

File Groups

Jun 26, 2006

Hi everyone,
While creating our database in only one disc(C or D), suppose that we create more than one file group in order to group our data files. However, in this situation; I wonder that whether it brings any benefit or advantage to us.

Also, I wonder that why we always have to put our data file into separate file group if we use separate discs for data files. Is not it allowed to use only one file group even if we use separate dics ?

Would you explain these to me ?

Thanks

View 13 Replies View Related

Top Count For Groups Possible

Apr 7, 2007

Hello,
in a report in Reporting Services 2005 I want to create a top count 100 for groups.
I have a report sheet with a table containing some groups.
Under the groupings there are other groupings and so on.
Now I want that only the top 100, ordered by a value on group level of the 1st group are contained in the report.

Any ideas?

Best regards,

Stefoon

View 1 Replies View Related

SQL Server Groups

Feb 7, 2007

Is it possible that i can create a SQL Server Group in sql 2005. Eg. in 2K when u right click (in Enterprise manager) on Microsoft Sql servers you can see "New Sql server Group". I am talking about this group. I have many servers in NYC and Dallas and in VA. I want to group them in the group names NYC DAL VA. is it possible.

View 12 Replies View Related

Conversation Groups

Jun 28, 2006

I am thinking of updating my SQL monitoring application to use Service Broker.

Right now I loop through my list of servers performing various checks on each server. Things like 'check last database backup', 'check for new databases', 'check for server restart'. I loop through, one server at a time, doing one check at a time. The more servers I have the longer it is taking.

So, I want to multi-thread the servers, but single-thread the checks on each individual server. This way I can check say, 5 servers at a time, but on each server I will only do one check at a time. This way I won't flood an individual server with multiple checks.

Is this possible? It looks like Conversation groups might be the way to go but I'm not sure.

View 2 Replies View Related

Conversation Groups

Dec 20, 2005

I'm having some troubles with conversation groups. I need to send two messages on the same conversation group so I have the following in my SP....

BEGIN DIALOG CONVERSATION @providerConversationHandle
FROM SERVICE [ProviderDataService]
TO SERVICE 'CalculatedDataService'
ON CONTRACT [ProviderDataContract]
WITH ENCRYPTION = OFF
, LIFETIME = 600;

BEGIN DIALOG CONVERSATION @curveConversationHandle
FROM SERVICE [ProviderDataService]
TO SERVICE 'CalculatedDataService'
ON CONTRACT [ProviderDataContract]
WITH RELATED_CONVERSATION = @providerConversationHandle
, ENCRYPTION = OFF
, LIFETIME = 600;

SEND ON CONVERSATION @providerConversationHandle
MESSAGE TYPE [ProviderDataMessage] ( @providerMessage );

SEND ON CONVERSATION @curveConversationHandle
MESSAGE TYPE [ProviderCurveMessage] ( @curveMessage );

When I query the queue I see two messages, but they don't have the same conversation_group_id.

Any ideas?

Thanks.

View 9 Replies View Related

Filter Top N Groups

Apr 19, 2008

Using: SSRS 2005, SSAS 2005Question: Can we use Filter Top N on Groups?
Detail:I have a table in RS with data sourced from an AS cube that has a structure like this:

Category Date Quantity
======== ======== ========
+ Cat1
2008-01-01 3,000
2008-01-02 5,000
2008-01-03 7,000
2008-01-04 9,000
2008-01-05 4,000
2008-01-06 2,000
2008-01-07 5,000
Cat1Total 35,000

+ Cat2
2008-01-01 2,000
2008-01-02 5,000
2008-01-03 6,000
2008-01-04 8,000
2008-01-05 3,000
2008-01-06 1,000
2008-01-07 5,000
Cat2Total 30,000

+ Cat n...

I want to return only the top 10 categories by descending quantity.
I can't get the filter to work on either the table or group level properties.

FILTER:Expression: =sum(Fields!Quantity.Value)Operator: TopNValue: 10ERROR:Failed to evaluate the FilterValue

Do the Top and Bottom functions only work on the detail level?
Is there some other method I should be using?

I've tried to do this with the query using the MDX AGGREGATE function but having trouble getting it to return proper results. (Details in this thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3203246&SiteID=17 )

So as a workaround to get the project delivered on time, I'm reluctantly bringing in the date level and trying to do the aggregations and filtering in the RS table.

Any help would be appreciated.

-Michael

View 4 Replies View Related

Hiding Groups

May 1, 2008

I know how to hide groups using Expressions that use parameters that can be passed in, but can you hide groups that are inside other groups. Here is my situation. Our company has different organization levels. On reports the users want to select which organization levels they want to see the #s for. So I set up groups for each org level like so....

Org Level 1
Org Level 2
Org Level 3

The data results are like so

Org Level 1 Org Level 2 Org Level 3 Category $ Amount
USA Midwest Kansas 1 500.00
USA Northeast Maine 1 200.00

And I sum the $ Amount Per Category for each Org Level using grouping in the report.

Then based on the selections they make it hides the org levels they don't need to see. The issue is that the report will not show org level 2 or org level 3 if org level 1 is not visible, and org level 3 can't be visible if org level 2 isn't visible, etc.....Anyone know a solution to this situation, I know this has had to have come up.

Thanks,
Adam

View 1 Replies View Related

SQL Using 2003 AD Groups

Apr 6, 2007

We have began to use Windows AUthentication for our SQL 2000 Server. When we llok atthe groups in AD we do not see any Domain Local groups? Is there a reason for that? Can I just add the group without browsing? I assume DLG will work with SQL for security.

Thanks

Brent

View 1 Replies View Related

Sorting Groups

Sep 24, 2007

I am having a bit of trouble with the sorting of grouped data.

I have a data set that returns sales data for stock items between a user specified date range. As a result I have to group by the stock items (description or code depending on a parameter value) to prevent displaying duplicate stock items and to get aggregate sales data. However I also need to be able to sort this group by three other aggregates but have not been successful. I have done the following:

The group expression is as follows:

Function Group4(fields As Fields) As String
Dim Group As String
If( Report.Parameters!ReportSetting_ReportType.Value <> "1" AndAlso Report.Parameters!ReportSetting_DetailSummary.Value = "1" ) Then
Group = fields!Group2Desc.Value
Else
If Report.Parameters!ReportSetting_SortType.Value = "1" Then
Group = fields!StockDesc.Value
Else
Group = fields!StockCode.Value
End If
End If
Return Group
End Function

The sort expression for this group is as follows:
=Sum(Code.SortExpressionNumeric(Fields), "table1_Group4")

where SortExpressionNumeric is as follows:

Function SortExpressionNumeric(fields As Fields) As Decimal
Select Case Report.Parameters!ReportSetting_SortType.Value
Case "3"
Return fields!TillSalesValue.Value
Case "4"
Return fields!TillSalesQty.Value
Case "5"
Return GrossProfit(fields)
Case Else
Return 0
End Select
End Function

where GrossProfit is another function that returns a decimal value.

When I preview my report with ReportSetting_SortType = 1 or 2 everything displays as expected. However when I set ReportSetting_SortType = 3,4 or 5 the data does not sort how I need it to.

Can anybody see what I might be doing wrong?

View 1 Replies View Related

Counting And Displaying Groups

Apr 3, 2008

Lets suppose I have a db which looks like:District        FundVAE            AVAW           AVAE            BDC              CDC              ANYS            BMD             CVAW           AI would like a query which displays the following dynamically.   I don't know each unique member of the Fund field, they could be added by the end user. It might include M,J,S etc. I need the columns dynamically created. I can do it statically if I know the distinct members of fund. I would like to be able to do it dynamically. Any ideas? The static version with multiple queries is at the end.District     Total       A         B      CVAE            2         1         1             VAW           2         2         DC              3         2                  1 NYS            1                    1                          MD              1                            1
Query Name =GroupByDistSELECT     district, COUNT(district) AS dcountFROM         dbo.mydbGROUP BY districtQuery Name=DCountASELECT     district, COUNT(district) AS dcountFROM         dbo.mydbWHERE     (fund = N'A')GROUP BY district
Query Name=DCountBSELECT     district, COUNT(district) AS dcountFROM         dbo.mydbWHERE     (fund = N'B')GROUP BY district
Query Name=DCountCSELECT     district, COUNT(district) AS dcountFROM         dbo.mydbWHERE     (fund = N'C')GROUP BY district
SELECT GroupByDist.district, GroupByDist.dcount AS Total, DCountA.dcount AS A, DCountB.dcount AS B, DCountC.dcount AS C From    GroupByDist LEFT OUTER JOIN                      DCountC ON GroupByDist.district = DCountC.district LEFT OUTER JOIN                      DCountB ON GroupByDist.district = DCountB.district LEFT OUTER JOIN                      DCountA ON GroupByDist.district = DCountA.district

View 8 Replies View Related

How To Count Number Of Groups In MS-SQL ?

Nov 30, 2005

The following SQL works on Access and Oracle to return the number of
Groups(Rows) of the SQL. In MS-SQL this SQL is not valid. What is the
equivalent in MS-SQL?

Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight]
from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion )

Thanks,

Frankk

View 2 Replies View Related

Removing Server Groups

Sep 29, 1999

In SQL 6.5 enterprise mgr I Could easily delete by right clicking on a server or group. In 7
this functionality is gone. Anybody done this.
Thanks

View 1 Replies View Related