hello,
i want to display the sorting arrow for a default sort order.
i tried to use the sorting functionality of the table control; the sorting is doing well but the "sorting arrow" doesn't display correctly on startup. anyone know a solution for getting the correct "arrow"? there should be a possibility to show the end-user the "default sort order"
I do not use groups...
thank you very much indeed
flo_nie
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
View 6 Replies View RelatedCreated a custom SSRS report. applied an interactive sort for a date field. I have the the formatting set to "MM/DD/YYYY" and it sorts everything correctly except for the year.
Example:
As you can see, the dates with 2014 aren't sorted correctly. I want it to sort the dates like:
11/06/2014
12/10/2014
01/07/2015 etc, etc.
Is there a way to get this to take into account the year? but also the month and day?
In SSRS 2014 std (on a win 2012 server) the report controls display incorrectly in IE11. The save and several other features display on multiple lines instead of one line unless you are in capability mode.Also works fine on SSRS 2014 entr on win 2012 server.
View 7 Replies View Related
I have the following query in an ExecuteSQL Task:
Insert Into Table2
Select * From Table1 Where Column1Val = '4'
As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.
Why is the above query failing with
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
I know that this may be a stupid question but I have yet to find the exact answer. Within the SSMS the servers are displayed in the registered servers. I register the server and then connect successfully. What I don't understand is although the server is running or the sql server agent is running the green dot with the white arrow is completely white. I can click on the server and look at the databases so I know that the server is running. This happens on remote servers and servers here at our location. Does anyone know why the white dot is not green with a white arrow? I'm just curious to know why this is like that. Thanks for any help you can provide.
I have a series of drill-through reports from a parent report. While in BIDS I get a blue arrow that allows we to get back to the parent report from the drill-through report. I do not see this feature available on the RS web interface. How are users expected to navigate back to the parent report?
View 3 Replies View RelatedI have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
Any suggestions would help.
Thanks
I know my problem is not directly associated w/ SSIS, so please forgive the post here. I figured this would be the "most" appropriate place to post this challenge.
We're attempting to edit a 2000 DTS package in Design mode from within the 2005 SQL Management Studio. To do this we downloaded/installed the following packages from the MS download site:
Microsoft SQL Server 2000 DTS Designer Components
Microsoft SQL Server 2005 Backward Compatibility Components
We're able to open the package in design mode from the Management Studio (Server > Management > Legacy > Data Transformation Services > DTS Package Name). But, when the DTS designer is open, the backspace and arrow keys won't respond in the Management Studio. They begin responding when we close the DTS designer. There are no error messages when we close the DTS designer.
Does anyone know of a fix or a workaround? Any help would be greatly appreciated.
Thanks, Mike
I would like to display a portion of report where there is data or no data
There is data subreport display
Product Name Latex Gloves
Product ID
xxxx5678
There NO data in the subReport
Product Name
Product ID
and (jobs.ApplicationsClosed > getdate() OR jobs.ApplicationsClosed = getdate())
The above statement does not pick jobs.ApplicationsClosed= 3/30/2004(that is today'sdate)
If I change 3/30/2004 to 3/31/2004 it picks it up. What is wrong with my syntax?
Thanks!
Hi guys,
I'm trying to use the bcp utility to transfer data from an SQL Server table (CUSTOMERS) to a text file (test.txt).
Here is what I have:
DECLARE @FileName varchar(50)
SET @FileName = 'C: est.txt'
EXEC bcp BAirwaysDB..CUSTOMERS out @FileName -S<servername> -T
only I keep getting the error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
and when running:
bcp BAirwaysDB..CUSTOMERS out C: est.txt -S<servername> -T
on the command line I get:
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'RIVERAMelanie'.
SQLState = 42000, NativeError = 4060
Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database "BAirways" requested by the login. The login failed.
Can you see where I'm going wrong with this? Any feedback would be really appreciated.
Thank you
Butterfly82
Hi,
I have the following simple SP..
There is a field which I'm doing a sUM() on to get a total for a QtyRecieved from one of the tbls in the SP..
However, the QtyRecieved field is set to be a two decimal place numeric field.
The sp works fine without issues until on the of the qty received is not a round number.
If it's a number with a decimal in it (i.e 5.5) then this is show as a seperate result and not SUM'd with the others.
Any ideas why?
SELECT PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID,
dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
PDS_Login.Supplier_Products.UOM, PDS_Login.Supplier_Products.Vatable, dbo.Suppliers_POs_Details.ProductID,
dbo.Suppliers_POs_Details.PODetailsStatusReason, SUM(dbo.Suppliers_POs_Details_Received.Qty) AS QtyReceived
FROM PDS_Login.Supplier_Products INNER JOIN
dbo.Suppliers_POs_Details ON PDS_Login.Supplier_Products.ProductID = dbo.Suppliers_POs_Details.ProductID RIGHT OUTER JOIN
dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID
GROUP BY dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, PDS_Login.Supplier_Products.ProductName,
PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PricePerItem,
dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived,
PDS_Login.Supplier_Products.UOM, PDS_Login.Supplier_Products.Vatable, dbo.Suppliers_POs_Details.ProductID,
dbo.Suppliers_POs_Details.PODetailsStatusReason, dbo.Suppliers_POs_Details_Received.Qty
HAVING (dbo.Suppliers_POs_Details.POID = @POID)
This is a subset question of one of my other threads, the gist is while using forms authentication, I am trying to enter additional information into another table on the same db as the aspnet_membership tables. I think I need an <asp:SqlDataSource> to do this because it is about the only thing left I haven't tried to get this code to work so any help is appreciated. Environment: sql server 2000, windows server 2003, asp.net 2.0, vb, no GUI<ASP:SqlDataSource id="addedInfo" ConnectionString="<%$ ConnectionStrings:SqlServerConnection %>" InsertCommand="INSERT INTO addInfo(username,email,company,firstname,lastname,phone) VALUES (@username,@email,@company,@firstname,@lastname,@phone)" runat="server" />looking over some of my books and online it says in the connectionStrings that it is supposed to be the database name that goes next to the : but when I try that I get an error that it is looking for the name of the connection string, so I enter the name of the connection string (as above) and nothing happens, which is basically the ongoing event with this whole problem, the form fields (username, email, password) that go into the aspnet_membership tables just fine, I don't have any problems with those, it is getting the additional information into another table that has been stalling me the whole time.So does this sqldatasource look right? Any help is appreciated.
View 12 Replies View RelatedHi,
View 6 Replies View RelatedHi everyone,
I'm having some problems importing a CSV to a db in SQL Server
Consider the following csv input
"1102","Spiderman, New York City",
"1103","Superman, Metropolis",
"1104","Batman, Gotham City",
When I select comma as delimiter, the columns will automatically
be offset since the location includes commas as well. How do I correctly
set the delimiter?
I tried using the following as delimiter → ","
but that'll strip the quotes which are required in this case.
Does anyone have an idea how to resolve this?
Call me an idiot as I'm quite new to asp.net. I'm working through a
book called 'ASP.NET Unleashed' where the chapter on how to create a
database connection to an sql database on MSDE called 'pubs' is
shown. Iv'e installed MSDE. When I try to run the example it says that
the login or authentication has failed. Iv'e tried different things but
I'm out of ideas. Another thing is that when I try to install sql
server 2000 on my Win XP it first says something about this being the
wrong OS or something like that. I can get past that but when I'm
presented with 3 installation options 1.client 2. client and
connectivity 3.connectivity I installed client because that's supposed
to create a stand alone installation.
However when I'm done installing the only new entry in the start->programs is
client connectivity. Where is the Enterprise services that I'm supposed
to be able to access according to the book? Is sql server 2000
compatible with XP and not just win 2000?
can somebody tell me whats wrong with this sp..................when i pass lname only or fname only it does not return any data, but when i passs both lname and fname it returns the correct data.....when i rearrange the structure to where it checks the lname only it works also but fname doesn't .........any help is greatlly appreciated
CREATE PROCEDURE [dbo].[temp_search] ( @lname varchar(50) = null, @fname varchar(50) = null, @date1 datetime = null, @date2 datetime = null)
/*set @lname = null -- 'aaa'set @fname = null --'ddd'set @date1 = '2/25/2003'set @date2 = '9/30/2004'*/
as
set nocount on
if not @lname is null or @lname = '' and not @fname is null or @fname = ''begin select temp_user_info.*, dateTime1 FROM temp_user_info INNER JOIN temp_seq ON temp_user_info.temp_seq = temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and temp_user_info.lname like @lname and temp_user_info.fname like @fnameendelse if not @lname is null or @lname = '' begin select temp_user_info.*, dateTime1 FROM temp_user_info INNER JOIN temp_seq ON cir_temp_user_info.temp_seq = temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and temp_user_info.lname like @lname end else if not @fname is null or @fname = '' begin select temp_user_info.*, dateTime1 FROM temp_user_info INNER JOIN temp_seq ON temp_user_info.temp_seq = temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and temp_user_info.fname like @fname end else if not @date1 = null and not @date2 = null begin select cir_temp_user_info.*, dateTime1 FROM cir_temp_user_info INNER JOIN temp_seq ON cir_temp_user_info.temp_seq = temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and dateTime1 between @date1 and @date2 end else if not @date1 = null begin select temp_user_info.*, dateTime1 FROM temp_user_info INNER JOIN temp_seq ON temp_user_info.temp_seq = cir_temp_seq.temp_seq WHERE temp_user_info.temp_seq = temp_seq.temp_seq and dateTime1 like @date1 endGO
I am very new to SQL server and I'm using stored procedures for my program. So far I wrote one tonight, that works just fine.
I haven't really written one before, but its kind of similar syntax since I know C++/C# and VB.
My question is, even though this works for what I need it do, is it written correctly? Can you see any problems with it, or would you have done it differently?
I want to make sure its done correctly, and it runs as fast as possible.
Thanks!
[pre]
CREATE PROCEDURE CreateNewUser
@UserID int out, @LoginID nvarchar(30), @Password nvarchar(30), @RegisterDate smalldatetime, @LoginIDExists nvarchar(30)AS /* Check to see if the loginID is already in use before attempting to save it. We MUST have a unique loginID for each user */ SELECT @LoginIDExists = loginID FROM users WHERE loginID = @LoginID
/* If we pulled a value from the database, then the loginID already exists, return with error code 1 */ IF (@LoginIDExists = @LoginID) BEGIN SELECT 1 RETURN END
ELSE BEGIN /* The loginID does not already exist, attemp to add the new user to the database. */ INSERT INTO users ( loginID, loginpassword, registerDate ) VALUES ( @LoginID, @Password, @RegisterDate )
/* Grab the UserID for the new user. */ SELECT @UserID = @@identity
/* return with error code 0 */ SELECT 0 RETURN ENDGO
[/pre]
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). What's going on here? Am I losing records somewhere?
Also, this is a cold fusion application. Thanks!!
I have a field called contact_id in a table. Datatype is integer and has an identity seed of 1 and an identity increment of 1. When new records are created, the increment sometimes skips numbers. (ex. 12,13,18,21). There is absolutely no deletion of any kind going on. (i.e. My application (cold fusion) has no code whatsoever that is deleting records.) What's going on here? Am I losing records somewhere?
View 1 Replies View RelatedI've recently just got started using sub queries. The sub query that I'm running is supposed to select distinct values out of a table. It doesn't seem to be working though.
SELECT id
FROM data
WHERE (issueID IN
(SELECT DISTINCT issueID
FROM data))
When I execute this query, it returns a list of id's, but they don't each have a unique issueID. In the data table, issueID can appear more than once (hence, I need to use distinct). However, id is the primary key, and is unique.
Any ideas?
I am new to SQL Server 2005. I have tried to be a good student and learn on my own about designing the database. However, I am facing a blockage of getting data into the database. I want to verify I have setup the relationships correctly. Here is the tables.
1. A Manufacture has an Address.
2. A Manufacturer has one or more Distribution Points.
3. A Distribution Point has an Address.
The Manufacturer --> Address is a one-to-one relationship.
The Distribution Point --> Address is a one-to-one relationship.
The Manufacturer --> Distribution Point is a one-to-many relationship.
The manufacturer table has a FK to the address id (PK).
The distribution table has a FK to the address id (PK).
The distribution table has a FK to the Manufacturer id (PK).
Address has several other FK, but not used in this scenario.
The INSERT and UPDATE Specification for the Delete Rule and Update Rule is set to "No Action" for the Manufacturer and Address tables. In the Distribution Point table, the relationship for the update rule involving the relationship with the Address and Manufacturer tables is set to CASCADE. What this tells me is SQL Server 2005 will update the table should changes occur in Address and Manufacturer table.
Is there anything else I can check to be sure the issue I am facing is NOT the deisgn of the database?
Thanks for taking the time to read my post.
Russ
Does anybody know how to work with two (or more) databases in SQL Server 2005; or where that information can be obtained? I searched online, in BOL and asked in this forum but with no success.
information in this posting does not work; results in invalid object name (source database) and/or database does not exist (destination database) errors:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=292125&SiteID=1
this post about the database does not exist has received no replies:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295742&SiteID=1
Of course, both databases exist and their names are valid. Both can be accessed individually thru SSMS and a VB app I am coding. The problem is when trying to work with both of them.
Any information on the subject of working with multiple datatabases and/or links to said subject would be appreciated.
Hi
Basicaly this is isituation: Software that our company developing (in Visual Basic 2005 .NET) is supposed to deliver files to sever and get them from it over Internet. In database those files are stored as image files. But the problem is that when Internet connection is slow or unstable the data transion seems to srew things up. Therefore file that is saved in database is corrupted and when i get it back from db it's not possible to open it.
Question is this: How to ensure safer delivery of large data (large meaning up to 4 MB). Is there any strategy that is recommended or proven to be best there is to this point.
Our first guess was to make MP5 based checksum in software and send it with data and then on serverside make a new checksum wiht MP5 and check if those two match. Since I've never done this I wanted to know (in case you dont know any better way)
*) what would be the code to use in order to make a MP5 checksum form Image and *) what the best best type of data for MP5 checksums (in tables and stuff)
I have a report in SQL Reporting Services that isn't displaying correctly. The report comprises a header and a simble table, typically consisting of 20 to 30 columns and 1-20 rows.
When I run the report IE, the table is truncated and only the first five columns are displayed. I can get the table to display correctly by doing one of the following:
Changing the report zoom - once the zoom has been changed, the report displays correctly even back at the default 100%
Moving to the next report page, then back to the first page
Refreshing the report by right-clicking and selecting 'Refresh' (not using the refresh button in the tool bar)
What seems strange is that if I copy the truncated table from IE to Excel, the full table is copied so it appears that what I'm seeing is some kind of display issue. I've trawled Google and the forums but to no avail - can anyone help?
Thanks!
I have a report which exports to Excel fine but it exports 100% as 1 to XML. Is this how it is supposed to work or is it supposed to put 100? The textbox has a format code of "P".
Has anybody have this problem before? Any Ideas?
Hi
In a stored procedure the following code snippet 1 checks against duplicate data being inserted. I've tested it with snippet 2 and it works as expected. However, when the procedure is called from ASP.NET the check seems ineffective. I still get the error msg. The application uses a SqlDataSource with the following parameters.
Any suggestions?
Thanks,
Bakis.
PS I want to ask a question on the ASP.NET forum .The login/pwd for this forum "get me in" to the .net forum in the sense that when I log in I see a logout link. I don't get an "ask a question" button though. Is there a separate screening for each forum?
<UpdateParameters>
<asp:Parameter Name="CatItemUID" Type="Int32" />
<asp:Parameter Name="CatName" Type="String" />
<asp:Parameter Name="Item" Type="String" />
<asp:Parameter Name="Quad" Type="Int16" />
<asp:Parameter Name="UID" Type="Int64" />
</UpdateParameters>
snippet 1 :
if (@CatItemComboExists > 0 )
BEGIN
--print @CatItemComboExists
return 0
END
snippet 2:
begin tran
declare @return_status int
EXECUTE @return_status = spUpdateCatItemRec 343, 'blah','blih', 2,3
print @return_status
rollback
error msg only if proc is called from app
Violation of UNIQUE KEY constraint 'IX_tblCatItemUID'. Cannot insert duplicate key in object 'tblCatItemUID'.
Hi Everyone!
I have a strange issue in my replicated databases.
I've a 5 merge subscribers replication schema. Our client discover a row that isn't matching with another database.
After some research it seam that the correct row is only at one database and all other database, have a the same row in a incoherent status.
Anyone have a idea on how this could happen?
I'm clue less right now
Thanks!
Hello,
because of the fact that the database sdf-file is stored on a cf-card, i want that all commit transactions will be flushed to the file immediately.
So i want to open the database with the above property. But i don't know the type and value i have to place into the variant. The following code fragment gave me the error "DB_S_ERRORSOCCURRED when i try to set the property with IDBInitialize::SetProperties() :
//Initialize property DBPROP_SSCE_TRANSACTION_COMMIT_MODE
dbprop_ssce_session[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
dbprop_ssce_session[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop_ssce_session[0].vValue.vt = VT_I4;
dbprop_ssce_session[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;
//Initialize property set DBPROPSET_SSCE_SESSION
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
dbpropset[1].rgProperties = dbprop_ssce_session;
dbpropset[1].cProperties = 1;
//(there is another property for the path not shown here)
I would appreciate it when someone out there knows the answer and could tell me.
Kind regards,
Andre
I have a conditional split where i check for First name ,Last name to see if they are empty.
this is the condition i specify in the conditional split
ISNULL(Column Name)
It dosent remove rows which are empty to a different output..
I tried trim the input column using derived column before sending the data to conditional split but nothing worked.
but after loading the data when i query in the database
select * from TBL_Name
Where Column Name =' '
I get so many records
How to resolve this??
Using the SSMS Import and Export Wizard, I am trying to export tables from one database into another within the same sql server instance but am getting the error message below. I tried exporting one table; still doesn't work. I copied the wizard generated CREATE TABLE sql statement and ran it in a query in SSMS without incident. I looked up ResultSet property in BOL but there is no entry for it; just ResultSets and ResultSetType. How does one set ResultSet? I have frequently exported to text files without this problem. Can anybody help resolve this?
- Execute the transfer with the TransferProvider. (Error)
Messages
ERROR : errorCode=-1073548784 description=Executing the query "" failed with the following error: "Retrieving the COM class factory for component with CLSID {7816B7A3-CD60-4539-BD38-C35AFC61F200} failed due to the following error: 80040153.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
ERROR : errorCode=-1073548784 description=Executing the query "" failed with the following error: "Retrieving the COM class factory for component with CLSID {7816B7A3-CD60-4539-BD38-C35AFC61F200} failed due to the following error: 80040153.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
Hi. I have a stored procedure that I'm interacting with through vb.net... the stored procedure logic, by itself, runs in query analyzer, but when I run it from the code side using the stored procedure, it dies. Now, that said, I'm not a pro at writing a stored procedure, so you might look at this and gasp in horror.
I'm executing a three-staged query, all of which are using temp tables, the final of which is what I'm using for my datagrid import. The temp table name I'm using doesn't work... it's not accepted in the SP and stops the execution of the query stating "invalid table name."
Not being a guru at this, I dont know why it runs in Query Analyzer but not w/i the SP. Can someone look at this and help?
(If its grossly written and there's a better way, I wouldn't be offended to be told that either. I"m looking for ways to write cleaner, faster code)
Here is the SP, any suggestions are very very appreciated.
Thanks so much!
CREATE PROCEDURE sp_PCPPanel_Summary
(@strWHEREMale as varchar(500), @strWHEREFemale as varchar(500))
AS
EXEC('
SELECT
Gender, RealAge, WeightedAge, FWeight, Mweight, AccountNum, PatLastName, PatFirstName, PatAddress, Patcity, PatState , PatZip, Tertiary, PatientStatusKey, InsClass, InsClassDesc, PCPDr, PCPClass, InsurancePlan, CarrierKey, CarrierName, PlanName, Age
INTO #tblTempPCP
FROM(
SELECT tblPCP.AccountNum, tblPCP.PatLastName, tblPCP.PatFirstName, tblPCP.PatAddress, tblPCP.PatCity, tblPCP.PatState, tblPCP.PatZip, tblPCP.PatPhone, tblPCP.DOB, tblPCP.Age, tblPCP.Gender, tblPCP.InsurancePlan, tblPCP.PCPClass, tblPCP.CarrierName, tblPCP.CarrierKey, tblPCP.PCPDr, tblPCP.PlanName, tblPCP.InsAddress, tblPCP.InsCity, tblPCP.InsState, tblPCP.InsZip, tblPCP.Tertiary, tblPCP.PatientStatusKey, dbo.tblPanelWeights.WeightedAge, dbo.tblPanelWeights.Category, 0 as MWeight, dbo.tblPanelWeights.Female as FWeight, dbo.tblPanelWeights.RealAge, tblPCP.InsClass, tblPCP.InsClassDesc, tblPCP.ApptDate FROM dbo.tblPanelWeights RIGHT OUTER JOIN dbo.[vwPCP+Continuity] tblPCP ON dbo.tblPanelWeights.RealAge = tblPCP.Age ' + @strWHEREFEMALE + '
UNION SELECT tblPCP.AccountNum, tblPCP.PatLastName, tblPCP.PatFirstName, tblPCP.PatAddress, tblPCP.PatCity, tblPCP.PatState, tblPCP.PatZip, tblPCP.PatPhone, tblPCP.DOB, tblPCP.Age, tblPCP.Gender, tblPCP.InsurancePlan, tblPCP.PCPClass, tblPCP.CarrierName, tblPCP.CarrierKey, tblPCP.PCPDr, tblPCP.PlanName, tblPCP.InsAddress, tblPCP.InsCity, tblPCP.InsState, tblPCP.InsZip, tblPCP.Tertiary, tblPCP.PatientStatusKey, dbo.tblPanelWeights.WeightedAge, dbo.tblPanelWeights.Category, dbo.tblPanelWeights.Male as MWeight, 0 as FWeight, dbo.tblPanelWeights.RealAge, tblPCP.InsClass, tblPCP.InsClassDesc, tblPCP.ApptDate FROM dbo.tblPanelWeights RIGHT OUTER JOIN dbo.[vwPCP+Continuity] tblPCP ON dbo.tblPanelWeights.RealAge = tblPCP.Age ' + @strWHEREMALE + '
) unionWEIGHTS
GROUP BY Gender, RealAge, WeightedAge, FWeight, Mweight, AccountNum, PatLastName, PatFirstName, PatAddress, patcity, patState , patZip, Tertiary, PatientStatusKey, InsClass, InsClassDesc, PCPDr, PCPClass, InsurancePlan, CarrierKey, CarrierName, PlanName, Age
ORDER BY realage
')
SELECT isnull(cast(RealAge as varchar), 'Unknown') as AgeRange, RealAge as AgeSort, gender, mweight, fweight, [Male] = sum(case when gender = 'M' then 1 else 0 end), [Female] = sum(case when gender = 'F' then 1 else 0 end), count(*) as Totalinto #tblTempPCP2
FROM #tblTempPCPgroup by realage, gender, mweight, fweight
Select AgeRange, AgeSort, [MALE], [FEMALE], [Male Weighted] = case when gender = 'M' then Sum(MWeight * [MALE]) end, [Female Weighted] = case when gender = 'F' then Sum(FWeight * [FEMALE]) endfrom #tblTempPCP2group by AgeRange, AgeSort, [MALE], [FEMALE], gender, totalorder by AgeSortGO