Combining Groups In Report
Jun 24, 2008
Is there a way to combine groups that you've set up in a report?
For example:
I am pulling information that is entered by a form. The 4 possibilities are (blank), Y, N, and P, which has been made into a group to uniformly sort these options with a sum of how many users have each answer:
(blank) 10
P 20
Y 35
N 100
However, (blank) and N are pretty much the same thing...so I'd like to combine those results like this:
N 110
P 20
Y 35
Any help is appreciated!
View 6 Replies
ADVERTISEMENT
Jun 22, 2015
We are trying to build HA and DR environment, combining AG and FCI. So we have 3 servers added to a cluster, 2 of them sharing the same storage and working as ACTIVE-PASSIVE cluster instance. The other server has a stand alone instance with its local storage, with the same drivers letters as the cluster instance.
Between the Active FCI and the Stand Alone, we set up an Availability Group. They are running fine, except by the listener. When the stand alone instance owns the Primary Replica, I am able to access the SQL Server instance by the listener, but when the Primary Replica is owned by the FCI, I can't reach the database by the listener (name or IP).
I've opened the same ports in both servers, it hasn't worked though.
View 14 Replies
View Related
Jul 30, 2007
In old school COBOL Reports you'd often see a header row that would be something like
Line1: CustomerID
Line2: Customer Name
Detail1: 0156784613
Detail2: Jane, Doe
Can that be done with reporting services?
If I have a group in a table, can I make that group cover two rows?
View 9 Replies
View Related
Oct 2, 2006
The goal is to produce a single PDF consisting of a number of subreports. Some are landscape, others are portrait. The subreports may also be run as independent reports. The master report that contains them defaults to the width of the widest subreport, which is landscape. This causes all portrait subreports to spill over producing blank pages. Are there any work-arounds to concatenate multiple, single report PDFs into a single PDF and have page numbering too?
Thanks!
View 11 Replies
View Related
Jun 6, 2007
Please, i need a very urgent help, for this question... tkssss :-)
I need to create a report with dynamic columns. I try use Matrix, but in this time, i dont konw how to calcutate the subtotal e groups in the correct way.
The data for generate the report ( http://xs116.xs.to/xs116/07233/DataReport.JPG )
This is the actual report ( 2 rows groups and 1 columns group ) ok, but how group dates now ? ( http://xs116.xs.to/xs116/07233/ReportInvalid.JPG
The correct report i need( if is possible?) (http://xs116.xs.to/xs116/07233/ReportOK.JPG)
I need group by Filial (idFilial) Dates (Dta) and inside dates the Dispositivos(NomeDispositivo or IdDispositivo) of the date.... and finally total all by group Filial (idFilial)...
Code of my actual report....
Code Snippet
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="INTEGRAReport">
<ConnectionProperties>
<ConnectString>server=111.11.1.111GPS;Integrated Security=False;User Id=integraapp;Password=asasasas;initial catalog=INTEGRA_DESENV;Persist Security Info=True;Connection Timeout=360</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>637ed8b0-ea9b-4da1-b6b8-6710cf62db84</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1.25in</Width>
</MatrixColumn>
<MatrixColumn>
<Width>1.25in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1.25in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeFilial.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_GroupFilial">
<GroupExpressions>
<GroupExpression>=Fields!IdFilial.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.25in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Dta.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup3">
<GroupExpressions>
<GroupExpression>=Fields!IdProduto.Value</GroupExpression>
<GroupExpression>=Fields!Dta.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.25in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeDipositivo.Value</Value>
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="NomeProduto">
<rd:DefaultName>NomeProduto</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!NomeProduto.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_NomeProduto">
<GroupExpressions>
<GroupExpression>=Fields!NomeProduto.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.375in</Height>
</ColumnGrouping>
<ColumnGrouping>
<Height>0.375in</Height>
<StaticColumns>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Qtd</Value>
</Textbox>
</ReportItems>
</StaticColumn>
<StaticColumn>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Saldo</Value>
</Textbox>
</ReportItems>
</StaticColumn>
</StaticColumns>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>ConsolidadoEntregasDataSet_RelatorioConsolidadoEntregas</DataSetName>
<Width>6.25in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Filial
/
Dispositivo</Value>
</Textbox>
</ReportItems>
</Corner>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Height>1.125in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.375in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Qtd">
<rd:DefaultName>Qtd</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Qtd.Value</Value>
</Textbox>
</ReportItems>
</MatrixCell>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Saldo.Value</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.875in</Height>
</Body>
<PageHeader>
<Height>0.25in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>872304d3-bc47-4430-8d0b-ea8c9d927f42</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="ConsolidadoEntregasDataSet_RelatorioConsolidadoEntregas">
<rd:DataSetInfo>
<rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod>
<rd:DataSetName>ConsolidadoEntregasDataSet</rd:DataSetName>
<rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>
<rd:TableAdapterName>RelatorioConsolidadoEntregasTableAdapter</rd:TableAdapterName>
<rd:TableName>RelatorioConsolidadoEntregas</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>dbo.RelatorioConsolidadoEntregas</CommandText>
<DataSourceName>INTEGRAReport</DataSourceName>
</Query>
<Fields>
<Field Name="IdFilial">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdFilial</DataField>
</Field>
<Field Name="IdProduto">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdProduto</DataField>
</Field>
<Field Name="NomeProduto">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeProduto</DataField>
</Field>
<Field Name="NomeFilial">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeFilial</DataField>
</Field>
<Field Name="NomeDipositivo">
<rd:TypeName>System.String</rd:TypeName>
<DataField>NomeDipositivo</DataField>
</Field>
<Field Name="Dta">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Dta</DataField>
</Field>
<Field Name="Qtd">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Qtd</DataField>
</Field>
<Field Name="Saldo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Saldo</DataField>
</Field>
<Field Name="IdDispositivo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>IdDispositivo</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>11.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
View 3 Replies
View Related
Feb 29, 2008
Hello all,
I have a report with some groups which can all be expanded/collapsed by clicking on a textbox with an action attached to it.
The example I used can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=600583&SiteId=1
But I want to hide the row containing this textbox (or the textbox itself) on the actual printed report.
Is there any report item that can tell me if the report is in preview phase, or can it be solved in any other way?
Kind regards,
Dirk Holland
View 1 Replies
View Related
Aug 1, 2015
I have a matrix table with row groups of a date and addresses and also a column group which produces 3 columns and values with in these column.I have a "Total" column based on a Count of the column groups which gives a number.How do you hide all rows where the "Total" column is less then 1? There will be results which still needs the date and addresses groups.
View 2 Replies
View Related
Jun 14, 2015
Let's say I have an rdlc report for customers, and it has three sections with subreports:
CUSTOMER_INFO (name, phone, e-mail)
ITEMS
OBSERVATIONS
As the page only has 21cm and the report grows horizontally, I need to repeat the WHOLE report with all of its sections after every 4 customers. So if I have 5 customers, there would be three pages(let's say every subreport takes 1 page) for the first four customers, and then another three pages(with every section once again) just for one customer.
Is it possible? It doesn't seem somehing out of the ordinary, but I can't figure how to make it work. Everytime there is more than 4 customers, the report breaks its layout trying to accommodate the records in a new table below the tables of each subreport.
The expected result would be to bring all of exceeding data to new pages.
View 5 Replies
View Related
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
Mar 2, 2015
I have a report in SSRS2012. It was copied from SSRS2005 quite recently, as we have upgraded. The SQL developer said that it didn't need any code changes, as he had some kind of tool to test code problems between the two versions. The report has a table, with 5 row groups, and one detail row. The table is setup as follows:
Name             Sales         Costs      Profit
Group1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
  Group2
     Group3
        Group4
           Group5
              DetailsRow
When I run this report using the Report Manager website from SSRS2005, and then export to Excel 2013, I get the 6 row groups in Excel, no problem whatsoever. I can drill into each group in Excel, using the group drilldown column on the left hand side. This works the same as the Report Manager website.
If I run the exact same report from the Report Manager website in SSRS2012, the Report Manager result is the same as SSRS2005 - all the row groups are present, and the drilldown works fine. However, the export to Excel has issues with the groups. There are only 3 row groups showing in Excel, along with a column group added for no apparent reason.
Drilling into each group shows inconsistent details, and there is no apparent logic as to what is shown within each group. I should also mention, that this issue with groups is seen on all my reports which have groups, not just this report.
View 7 Replies
View Related
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Jan 11, 2008
Hello. I am trying to write a report that pulls information in from Active Directory. I have a view created that gets a listing of users and a view that creates a listing of user groups, but I can't seem to figure out how to get all user groups that are associated with the users. This is what I have.
SELECT TOP 100 PERCENT *
FROM OPENQUERY(ADSI, 'SELECT cn, groupMembershipSAM FROM ''LDAP://wmdomain.local'' WHERE objectcategory=''group''') Rowset_1
SELECT *
FROM OPENQUERY(ADSI,
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn
FROM ''LDAP://wmdomain.local''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
Rowset_1
Let me know if you have any suggestions!
BJ
View 1 Replies
View Related
Jan 2, 2007
Can we do this?
Adding more columns in a matrix report that don€™t
belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I
would add some ratios, that is, Units Sold/Months (sold per month) and other that
is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some
columns should be precalculated prior to the report so do not get into it, the
real problem I don€™t see how to solve is adding one or two columns for showing
these calculated column that doesn€™t depend on the column groups but they do
for the rows groups€¦
Any guidance
on that?
The only
way I am seeing by now is to set it as two different reports, and that is not
what my client wants€¦
Many
thanks,
Jose
View 4 Replies
View Related
Jun 5, 2007
Dear Developres,
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps.
at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)???
Thanks in advance.
View 5 Replies
View Related
Oct 22, 2007
Hi all,
I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ?
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1
FROM dbo.Orders
WHERE (ShipVia = 1)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 1 Results
EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29
SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2
FROM dbo.Orders
WHERE (ShipVia = 2)
GROUP BY EmployeeID
ORDER BY EmployeeID
Table 2 results
EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19
Table 3 the desired result:
EmployeeID CountShipVia1 CountShipVia2
1
82 44
2
71 36
3
81 45
4
116 70
5
29 15
6
48 25
7
44 24
8
75 48
9
29 19
thanksrobby
View 5 Replies
View Related
Nov 22, 2003
hello everyone
there is a smalllll problem facing mee...well i want to combine the result of 2 queries together
, the queries are :
select x1,x2,x3 from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z where table1.anything = 5
and the other query
select x1, x2 from Table1 where table1.anything = 5
is there anyway????
Thank you
View 2 Replies
View Related
May 9, 2004
Hello everyone,
I'm having problems transfering data. I don't even know if this is even possible, but this is what I'm trying to do. I have two tables: ZipRegionUps, ZipRegionUsps. Both tables have the same two columns: Zip, Region.
I want to combine the two. Having one table ZipRegion with three columns: Zip, UpsRegion, Usps Region. I've tried everything I can think of, but no luck. Here's the most sensible Stored Procedure I have tried:
If I wasn't very clear with my explanation, I'm hoping the procedure will clear things up:
CREATE PROCEDURE CMRC_Databases_DataTransfer
AS
DELETE FROM CMRC_ZipRegionTest
INSERT INTO CMRC_ZipRegionTest
(
Zip,
UpsRegion,
UspsRegion
)
SELECT
CMRC_ZipRegionUps.Zip,
CMRC_ZipRegionUps.UpsRegion,
CMRC_ZipRegionUsps.UspsRegion
FROM
CMRC_ZipRegion,
CMRC_ZipRegionUsps
GO
Is there any way to do this? Or do I have to manually enter all the entries?
Any help would be great. Thank you.
-Alec
View 1 Replies
View Related
Jan 23, 2006
I have a transactions table that stores prices for products bought and sold.
If I want average buying prices I use:
SELECT AVG(price), product FROM transactions WHERE transactiontype=1 GROUP BY product
and for selling prices:
SELECT AVG(price), product FROM transactions WHERE transactiontype=2 GROUP BY product
Is there a way to combine this into one SQL query, to create one bindable dataset ?
View 2 Replies
View Related
Mar 7, 2006
I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).
insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName"
insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)"
I have tried:
Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName = @uName VALUES (uID, @eventID, @comment)
Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!
Thanks in advance.
View 2 Replies
View Related
Mar 3, 2000
I have 2 columns in a table and would like to combine
the 2 columns into 1 column separates by a delimiter.
Do anyone know the syntax??
Thanks, Vic
View 1 Replies
View Related
Aug 27, 2007
Hi,
Can anybody please tell how can I combing all records in field into one field.
For example
If my table is like
Final
_______
aaa
bbb
ccc
ddd
and i want result as
final1
_____
aaabbbcccddd
I do not want to use cursors for this. Please let me know if somebody knows the answer
Thanks
View 2 Replies
View Related
Dec 18, 2006
I have a database table tblobjects like this:
object_name, reference_id
a 1
a 2
a 3
a 4
b 2
b 3
b 1
b 4
c 2
c 4
c 5
c 6
d 2
d 4
d 5
d 6
I now would like to have a SQL query which gives me the number of
unique object and reference combinations, like this:
a
b
c
d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is
also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.
Any ideas how can I do this?
Thanks!
View 1 Replies
View Related
Dec 13, 2004
Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:
View 12 Replies
View Related
Apr 12, 2006
These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.
I hope that's clear; if not, maybe the SQL will make it clearer:
SELECT
[tblTickets].[id] AS TicketId,
SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblTickets].[id]
SELECT
[tblCombinations_Tickets].[ticket_id] AS cTicketId,
SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblCombinations_Tickets].[ticket_id]
I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.
I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.
This has got to be laughably simple. But I'm stuck :( Can anyone help?
View 3 Replies
View Related
Jul 12, 2006
Not concatenation, more... err.. I don't know what you'd call it.
SELECT
DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
[C01241 Internal Data].[Col15] AS InternalDataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]
(Apologies for the table/col names, this is all very temporary)
So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.
At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.
What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.
Does that make sense?
View 3 Replies
View Related
May 15, 2007
If I have two tables with the following data:
Table_A
A
B
C
Table_B
1
2
3
is there a way to make a select the gives me this result(in separate columns):
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
View 2 Replies
View Related
Jul 23, 2014
I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.
Personstartdateenddate Money
7d3397/1/201412/31/2014 1000
7d3391/1/20145/23/2014 355
View 2 Replies
View Related
Apr 3, 2008
Hi,
I was wondering if there's a way to combine last, first and middle name together in one field instead of three different ones?
View 9 Replies
View Related
May 13, 2008
Hello,
I was wondering what is the best way to have multiple joins?
Here are the two statements I've been trying to combine
--------------------------------------------------------------------
SELECT CASE when
t1.Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(t1.Cust_DB_Shipment_Key,1,5,''))
End,
t1.Airway_Bill_No,
t1.Shipper_Reference,
t1.External_Product_Cd,
t1.Chargeable_Weight,
dt.CountTrackingNumber,
dt.SumProductCharge,
t1.Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping t1
Inner Join
(
SELECT Cust_DB_Shipment_Key,
sum(PRODUCT_CHARGE_AMOUNT) as [SumProductCharge],
count(Airway_Bill_No) as [CountTrackingNumber]
FROM Shipping
Where Ship_DT = '2008-05-12' and status != 'voided'
GROUP BY Cust_DB_Shipment_Key
) dt
ON (stuff(t1.Cust_DB_Shipment_Key,1,5,'')) =
(stuff(dt.Cust_DB_Shipment_Key,1,5,''))
Where Ship_DT = '2008-05-12' and status != 'voided'
----------------------------------------------------------------------
SELECT CASE when
Cust_DB_Shipment_Key = 'Used:' Then Description_1
Else (stuff(Cust_DB_Shipment_Key,1,5,''))
End,
Airway_Bill_No,
Shipper_Reference,
Service_Name,
Chargeable_Weight,
Consignee_Company_Name,
CONVERT(CHAR(8),Ship_DT,112)
FROM Shipping
s JOIN Shipping..Distinct_Service_by_SAS_Code d ON s.External_Product_CD = d.SAS_Code
Where Ship_DT = '2008-05-12' and status != 'voided'
Thanks,
Stephen
View 4 Replies
View Related
Jun 19, 2008
Hello everybody,
I have the following problem.
I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.
I would like to make a query that retreives al send emails, its recipients and message_size.
SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles
Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55
But i'd like to get;
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55
Can anyone help me with this query?
Best Regards,
Sidney
View 3 Replies
View Related
Jun 19, 2008
I need to combine two queries into one.
Query 1 (main query)
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
Result
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26 (material qty)
1569.26 (production qty)
(where 1314.26 is sum material and 1569.26 is production)
Query 2
selectsum(EmployeeLaborEvent.Hours) as hours
fromdbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid
Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
Result:
1647.50 (which are sum of hours, this figure is correct)
Now I try to merge query 2 into Query 1 like this:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production,
sum(EmployeeLaborEvent.Hours) as hours
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
When I run the query the result is:
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26(material)
1569.26 (production)
NULL (hours)
The material and production stay the same (and is correct). Hours are wrong.
Any clues? Thank you.
View 4 Replies
View Related
Aug 29, 2005
Hi
What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??
Thanks
View 8 Replies
View Related
Feb 2, 2006
Hello,
I have two tables that I would like to combine but with some rules.
Table 1 has two columns with Actual Time and Actual People
Table 2 has two columns also with Planned Time and Planned People
I would like to combine these two tables if the actual time is not more than 30 minutes from a planned time
If I use one of the planned times, i would not want to show it again, even if there is another actual time that is not more than 30 minutes of it.
For ex.
Planned Time has 20:00,23:00
Planned People has 2,5
Actual Time has 19:00,19:30,19:45,21:15
Actual People has 5,2,5,3
Output should be like
Planned_Time,Actual_Time,Planned_People,Actual_People
,19:00,,5
20:00,19:30,2,2
,19:45,,5
,21:15,,3
23:00,,5,
I can do this in asp but if this can be done in sql it would be better.
Any ideas appreciated.
Thanks.
View 4 Replies
View Related
