Distinct Values Problem With SP5a

Aug 10, 1999

Hi,

Has anyone encountered this?

After applying SP 5a I found out that my SQL strings with sums and groups produced double rows. I found out that grouping one column (type CHAR(6)) produced two distinct rows of the equal values.

All the values in that column have 4 characters, but it seemed like some distinct values were grouped by the whole field length i.e 6 characters. I haven't seen earlier that those blanks after field value change the grouping results.

When I transferred the table and data to old SQL Server without any SPs, query showed distinct values correctly.

The sort order and other settings are the same. Can anyone tell where to look for help?

TIA, Al

View 1 Replies


ADVERTISEMENT

Distinct Values Among Distinct Column Values

Jan 9, 2015

Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.

Here's an example of what I'm hoping to accomplish:

*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)

===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555

==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3

View 1 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related

Distinct Values

Nov 17, 2006

Hi, i hope i'm asking this on the right forum.
My problem is:
I need to select only distinct columns from a table, for this i can use: "select distinct username from table_user" but this is not working each time .... someone told me that i can use "alias" ... what does this means and how can i use alias for geting only distinct usernames from my table???

hope that is here someone that can help me ... !
10x

David Palmer SQL Manager

View 4 Replies View Related

DISTINCT Values From A Table

May 4, 2007

Hi,I am trying to output a list of data from a table, showing only one record of each TypeID.So, for instance, I have a simple SQL query that says:SELECT DISTINCT AlbumTypeID FROM Album
ORDER BY AlbumTypeID DESCThis works correctly, and gives a list of 1,2,3. But I need more information than that, I want the Description field output with the ID, but how can I do this without assigning that to be Distinc also?When I try:   SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESCThe output is completely wrong.Many thanks   

View 11 Replies View Related

How To Calculate Sum For Distinct Values In MDX

Jul 27, 2007

Hi ALL,
I need help in calculating sum of market value based on property_id. The sum should be calculated by finding the average market value for a given property and then sum the individual average of the property to get the Distinct sum.

I have a very little knowlegde in Cubes and analysis services. I need to perform this distinct sum by using calculated members using MDX on SQL server 2000.

Data example

P_Code Cus Prpty_IdMrkt Val

3000 1234 1111 $10,000 $10,000
3000 1234 2222 $20,000
3000 1234 3333 $30,000 $20,000
3000 5678 1111 $10,000
3000 5678 2222 $20,000 $30,000
3000 5678 3333 $30,000
3000 1020 1111 $10,000
3000 1020 3333 $30,000

Distinct Sum $60,000

Thanks in Advance

Brijesh

View 2 Replies View Related

Grouping On Distinct Values

Aug 30, 2013

I want to get the summation of the amount column against all the distinct values of Description fields. May be the "Where" (applied on Tdate Column) Clause unable to contains all the Unique(Distinct) values from the single Column (Description).

Table Structure :
CREATE TABLE [dbo].[TransLine](
[TransID] [int] NULL,
[No] [int] NULL,
[Description] [varchar](50) NULL,

[Code] ....

Sample data : [URL] ....

View 5 Replies View Related

Select Distinct Values

Jun 25, 2014

I have a problem when selecting distinct values from a table. I want to select those distinct numbers with a code that I want. To make it more clear i`ll show it on an example.I have the following table:

tel_no | code | id_res
+-----------+--------------+-----------
0742062141 | a-not answer | 1
0742062141 | a-not answer | 2
0742062141 | c-answer | 3
0754231456 | a-not answer | 4
0754231456 | a-not answer | 5

When I use the following query it returns the distinct number but with the first code it finds:

select a.* from test_funnel as a
inner join
( select distinct tel_no,MIN(id_res) as id_res
from test_funnel

[code]....

View 3 Replies View Related

Help In Coalescing Distinct Values

Sep 21, 2007

Hi all,

I want to know how to coalesce distinct values as comma seperated into a variable which is used elsewhere. Here are my ddl and the query I tried.
My Expected result is
[Java],[MySQL],[.Net]





Code Snippet

CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO

CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO

INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO

INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO


DECLARE @listSkills nvarchar(max)
SELECT DISTINCT @listSkills= COALESCE(@listSkills+',','')+'['+ #Lkp_SkillCategory.Skill+']'
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
SET @listSkills=(SELECT Skill = @listSkills)
SELECT @listSkills

DROP TABLE #Lkp_SkillCategory,#Tbl_Request

View 7 Replies View Related

Getting Distinct Values On One Column ?

Sep 11, 2007

We have a query in which there are 20,000 rows and 90 distinct ID's

If we say

SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8

from #TEMPX

we get the 90 distinct values, but if we say


SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8, ts1avg, ts2avg, ts3avg, ts4avg, ts5avg, ts6avg, ts7avg, ts8avg from #TEMPX

TS1 contains 90 distinct values, whily ts1avg has 2,000 disitinct rows

Is there a way to get Distinct to work against only one column, i.e. SiteID ?

View 2 Replies View Related

Return DISTINCT Values

Aug 22, 2007

Hi,

How do I ensure that DISTINCT values of r.GPositionID are returned from the below??



Code Snippet
SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;




Thanks.

View 11 Replies View Related

How To Insert Distinct Values In Database

Apr 11, 2008

Hello,
 I have three columns in my database named FirstName, LastName, StudentID. i want to insert only the distinct values and avoid the values which are already in the database. can anybody please help me with the logic? is there any possibility of checking for the redudant values in the stored procedure?
 below is my code:protected void Page_Load(object sender, EventArgs e)
{Label1.Visible = false;GridView1.Visible = false;
}protected void Button1_Click(object sender, EventArgs e)
{
 
loaddata();TextBox1.Visible = false;
TextBox2.Visible = false;TextBox3.Visible = false;
Button1.Visible = false;Label1.Visible = true;
Label2.Visible = false;Label3.Visible = false;Label4.Visible = false;
loadGrid();GridView1.Visible = true;
 
}private void loaddata()
{string str = ConfigurationManager.ConnectionStrings["adventure"].ConnectionString;
SqlConnection conn = new SqlConnection(str);SqlCommand cmd = new SqlCommand("Sp_Student", conn);
conn.Open();cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text;cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = TextBox2.Text;cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = TextBox3.Text;
cmd.ExecuteNonQuery();
conn.Close();
}
 private void loadGrid()
{string strn = ConfigurationManager.ConnectionStrings["adventure"].ConnectionString;
SqlConnection connect = new SqlConnection(strn);string select = "select * from Student";
SqlCommand comm = new SqlCommand(select, connect);SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();da.Fill(ds, "Student");GridView1.DataSource = ds.Tables["Student"];
GridView1.DataBind();
 
 
}
Thanks
Sandeep

View 1 Replies View Related

Distinct Values And Join Query

Feb 24, 2015

SELECT first.name,first.country, second.name, second.string
FROM
first LEFT OUTER JOIN second
ON
first.name = second.name
WHERE
first.date='2015/02/24'

This query means all record from second table and matching record from first table. Now my question is that on 24 Feb 2015 I have duplicate names in second table and I want distinct names from second table and then its matching values from first table. Now my query is showing all duplicate values from second table and its matching record from first table.

View 5 Replies View Related

Distinct Values In Select Statements

Jul 6, 2006

Hello,

I have a table, which contains well over 30'000 rows. One of the Columns is for ransaction type, of which there are about 20-25 different types.

I am trying to pull a list of each type value, using a select distinct TSQL command...but thus far am unsuccessful.

Has anyone had a similar situation?? Am I wrong in using a Select Distinct?? Many thanks to all who reply!

View 3 Replies View Related

How To Conditionally Count Distinct Values?

Mar 17, 2008

Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.

departmentId classId barcode orderId isDiscountedItem
----------------------------------------------------------------------------------------------------------------------
1 1 123 1 True
2 7 456 1 False
1 1 123 2 False
1 1 123 3 True
1 1 789 3 True
2 7 456 3 False
... ... ... ... ...

I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.

My report would produce the following output considering the above dataset:






Merchandise Number of customers who used a coupon
--------------------------------------------------------------------------------------------------------------
Department 1 2

Class 1 2

Barcode 123 2
Barcode 789 1
Department 2 0

Class 7 0

Barcode 456 0


I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.





View 3 Replies View Related

DB Design :: How To Get Distinct Count Of Values

Sep 21, 2015

I have the below query,

select 
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column

[Code] ....

o/p: is :

LastEnforcementState NumberOfComputers PComputers

Compliant 7056 91.54
Downloading update(s) 39 0.51

[code]....

I want to add these two rows information into single row,

Failed to Download Updates(s)    131(127+8)       1.66(1.65+0.1)

how to do this in my sql query?

View 12 Replies View Related

Return Distinct Values From Stored Procedures

Aug 17, 2006

I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!

View 3 Replies View Related

Distinct Record Equal To 2 Values From Same Column

Dec 6, 2013

Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:

name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1

View 2 Replies View Related

Select Distinct On Field With Values Seperated By

Apr 5, 2006

I have a db that contains a column named DSCODES. Values in DSCODES are seperated by a comma.

If I run the following command.

select distinct(DSCODES) from DB

The following is returned.

S100,S102,S103
S100,S103,S105

What I would like returned is the following

S100
S102
S103
S105

Is this possible?

Thank you in advance

Graham

View 2 Replies View Related

How To Insert Only Distinct Values From A Flat File

Sep 11, 2007



I have to insert the Values from the Flat Files , My table structures have Primary keys , how do i insert only the distinct values into the table without the ERROR VIOLATION OF Primary Key already exists a record.

Dropping and Adding Relationships after insert is a way but doesnt serve the whole purpose is there a way we can eliminate duplicate records based on their Primary key before inserting them into the Database.

View 10 Replies View Related

How To Assign Distinct Data Values For Attribute

Nov 17, 2015

I need the Rate to be of a value from 1-5 to indicate the stars for Rating attribute in my database.

View 2 Replies View Related

Can We Create Distinct Values For Document Map On A Particular Level?

Apr 16, 2007

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about document map on SQL Server 2005 Reporting Services. I found one problem with the values retrived for the document level. That is, the values are not distinct, they are duplicate for the document level. Is it possible for us to get the distinct values for a particular document level? Hope my question is clear for your help.

Thank you very much in advance for your help and advices. I am looking forward to hearing from you.

With best regards,

Yours sincerely,

View 3 Replies View Related

Transact SQL :: Distinct Values From Multiple Tables?

Nov 2, 2015

best way to go about this. My query works great and displays the information I require.

EG all demographic for ED, IP & OP. However if a patient has a record in each of the tables then I get the same info multiple times.

What I'm trying to do is a Select Distint against the entire query (below).

eg Select Distinct * from all below.

DECLARE @StartDate datetime = '01 oct 2015';
DECLARE @EndDate datetime = '30 OCT 2015';
--***OUTPATIENT DQ QUERY***--
SELECT distinct
HEYNo,
NHSNo2,
NHSNo1,

[code]....

View 3 Replies View Related

Select Distinct Column Data With Other Values From The Table

Dec 16, 2004

I have a table 'wRelated' with the following columns

[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.

[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'

I have tried Unions and temporary table inserts.

View 1 Replies View Related

Distinct Count With Null Values (grand Total)

May 9, 2007

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
--------------
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 1 Replies View Related

Distinct Count With Null Values (grand Total)

May 10, 2007

Hello,

I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 2 Replies View Related

SQL Server 2014 :: Inserting Distinct Values From One To Another Table

May 28, 2015

I have the following table

Table A

Col1

0118F
0118R
5678
0118F
0118R
5678
5678
5678
0118F
0118R

I want to insert only distinct values of Col1 from table A to another table

Insert into TableB

(
UserName, ProjectName, processdate, Recordprocess, Comments, RecordProcessExt
)
values(@UserName, 'Test Project', getDate(), distinct Col1 from Table1, 'Test Comments', distinct col1+ 'TR' from TableA)

How can I accomplish the above. I need to insert distinct column from TableA to RecordProcess and col1+'Tr' to recordprocessExt.I can do it with cursor. I don't know any other way.

Also, there are other columns in Table A. I am using sql server 2005.

View 1 Replies View Related

Transact SQL :: How To Get All Distinct Values From 10 Different Tables That Exist In The Field

Aug 19, 2015

I need to get all distinct values from 10 different tables that exist in the field [favoritesport]  And each table holds close to 50K records so I am looking at 500,000 records to get distinct values for.  Would the fastest, less intrusive way of achieving this be to just create a UNION ALL so run 

Select Distinct([favoritesport]) from table1
Union
Select Distinct([favoritesport]) from table2
Union
Select Distinct([favoritesport]) from table3
etc etc etc

View 2 Replies View Related

Insert Rows Based On Number Of Distinct Values In Another Table?

May 21, 2014

I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.

Example:

Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40

Table 1 :

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40

Updated Table 1:

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40

how can I update Table 1 to get desired results?

View 2 Replies View Related

SQL Server 2012 :: Comma Separated List Of Distinct Values

Oct 14, 2015

I am trying to create a comma delimited list of InvNo along with the JobNo .

CREATE TABLE #ListString
(
JobNo VARCHAR(10),
InvNo VARCHAR(MAX)
)
INSERT INTO #ListString ( JobNo, InvNo )
SELECT '3079', 'abc'

[Code] ....

View 6 Replies View Related

How To Bring Back The Distinct Values In Single Column From Two Tables

Jul 20, 2005

12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim

View 1 Replies View Related

Reporting Services :: How To Get Distinct Values Of Sharepoint Column Using SSRS

May 13, 2009

I have integrated sharepoint list data to SQL Server reporting services. I am using the below to query sharepoint list data using sql reporting services.

<Query>   <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>  
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">     
<Parameters> <Parameter Name="listName">       

[Code] .....

By using this query, I am getting a dataset which includes all the columns of sharepoint list. Among these columns, I wanted to display only 2 columns (i.e Region and Sales type) using chart. I have created a Region parameter but when I click preview, the drop down box is giving me all the repeatative values of region like

RG1,RG1,RG1,RG2,RG2,RG2,RG2,RG3..........

I wanted to display only distinct values of Region parameter so that whenever end user select region from the parameter drop down, it will display the respective value of Sales type column.Also when I select only RG1 parameter, it is giving me a chart including the sales type of all the Regions. (it should display me only the sales type of RG1) How can I link these 2 columns so that they will display the values respectively.  

View 36 Replies View Related

SQL Server 2012 :: Row Counts Based On Distinct Values From Multiple Tables

Jan 15, 2015

I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now

Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1

Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.

Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1

I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved