combining two tables

hi,

does anyone have any good insight to this problem? I will have two tables which contains the same number of columns for same data types, they are related together by a key book_id. I need to combine them together and create some extra totalling data in the new datatable for a report. Here is an example

table1:
book_id new_words cost_of_change
1 3000 2
1 4000 4
2 500 4

table2
book_id old_words cost_of_change
1 1500 1
3 2500 5

I need to combine them into a table like this:

book_id new_words cost_of_change old_words cost_of_change total_cost
1 7000 6 1500 1 7
2 500 4 0 0 4
3 2500 5 0 0 5

whats the best way to do this?

I have been trying to use full outer joins to do this but I find this a difficult way to create new rows in the new combined table, like what will be an easy way for me to say in SQL that only one row should be used for book_id 1, as it's is present in the two source tables 3 times? I think i will be able to find out from using left and right inner joins, before i make the new combined table but this seems like a very ineligant way of doing this, as it seems to require lots of temp tables.

thxIs table1 the only place where there can be duplicate book IDs? I'm going to assume so, but if table2 can have duplicates you'll need to modify this a bit. But the basic idea should work.

You can use an aggregate subquery for table1 that you then join on table2. The subquery looks something like this (all of this is untested code; you may need to tweak):

SELECT SUM(new_words), SUM(cost_of_change) FROM table1 GROUP BY book_id

That sums the two fields for each id and eliminates the dupes. That subquery becomes one of the derived tables in the outer select. Something like this:

SELECT B.book_id, A.new_words, A.new_cost, B.old_words, B.cost_of_change AS old_cost, total_cost FROM table2 AS B
INNER JOIN (SELECT SUM(new_words) AS new_words, SUM(cost_of_change) AS new_cost
FROM table1 GROUP BY book_id) AS A
ON A.book_id = B.book_id

This query doesn't yet aggregate the totals from the two tables, so that will be another outer query, but the idea is the same. And there are almost certainly ways to simplify this query.

One way is to use table variables in SS2K. Then you can do three more straightforward joins.

Is this helpful? Or have I confused things more?

Don|||Something like this should work:

Select
IsNull(A.book_id,B.book_id) as book_id,
IsNull(A.new_words,0.0) as New_Words,
IsNull(A.Cost_of_change,0.0) ACost_of_Change,
IsNull(B.old_words,0.0) as Old_Words,
IsNull(B.Cost_of_change,0.0) BCost_of_Change,
IsNull(A.Cost_of_change,0.0)+IsNull(B.Cost_of_change,0.0) as Cost_of_change
From
(Select book_id, Sum(new_words) New_Words,Sum(Cost_of_change) Cost_of_change FROM Table1 Group By book_id) A
FULL OUTER JOIN
(Select book_id, Sum(old_words) Old_Words,Sum(Cost_of_change) Cost_of_change FROM Table2 Group By book_id) B
ON A.book_id=B.book_id

|||Thanks Guys, that solved my problem. The second method is lot more readable, but which would be the most efficient method?|||Both methods are basically the same thing. The second method could be made clearer by using Table variables as mentioned in the first method. But I don't think that would affect efficiency. You could test this using the Sql Query Analyzer and compare the execution plans and execution times for each.

Combining two rows into one

I am trying to combine two rows of data into one row for comparison
purposes and I can do it using a number of steps but thought that there
had to be a way to do it in one SQL statement. Any help would be
appreciated...
Instead of having two rows with the different period end dates, I would
like the query to return the results as follows:
Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
OpCashFlowLastYear... All in one row.
Table:
CREATE TABLE [dbo].[CompanyRatios_3Y] (
[Name] [varchar] (160),
[Ticker] [varchar] (10),
[CIK] [varchar] (10),
[PeriodEndDate] [datetime],
[DurationType] [varchar] (3),
[NetIncome] [decimal](38, 6) NULL ,
[OperatingCashFlow] [decimal](38, 6) NULL ,
[TotalAssets] [decimal](38, 6) NULL ,
[TotalRevenue] [decimal](38, 6) NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
*** Sent via Developersdex http://www.examnotes.net ***try this.
select
a.Name, a.Ticker, a.CIK, a.PeriodEndDate, b.PeriodEndDate as
PeriodEndDateLastYear,
a.Netincome as NetIncomeCurrentYear, b.Netincome as NetIncomeLastYear,
a.operatingcashflow as OpCashFlowCurrentYear,
b.operatingcashflow as OpCashFlowLastYear
from CompanyRatios_3 a
,CompanyRatios_3 b
where a.name = b.name
and a.ticker = b.ticker
and a.cik = b.cik
and year(a.PeriodEndDate) = year(b.periodEndDate) + 1|||Here is one approach, but it makes some assumptions about that data
that I am not comforatble with:
SELECT C.Name,
C.Ticker,
C.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM CompanyRatios_3 as C -- as in Current
JOIN CompanyRatios_3 as P -- as in Prior
ON C.Ticker = P.Ticker
AND C.CIK = P.CIK
WHERE C.PeriodEndDate = '20051231'
AND P.PeriodEndDate = '20041231'
The problem is that there must be EXACTLY the same Ticker and CIK
values for both years, or you don't get any data for either year.
This can be allowed for, at the price of some complication:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy Harvey
Beacon Falls, CT
On Tue, 02 May 2006 13:25:39 -0700, Jason . <jrp210@.yahoo.com> wrote:

>I am trying to combine two rows of data into one row for comparison
>purposes and I can do it using a number of steps but thought that there
>had to be a way to do it in one SQL statement. Any help would be
>appreciated...
>Instead of having two rows with the different period end dates, I would
>like the query to return the results as follows:
>Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
>NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
>OpCashFlowLastYear... All in one row.
>Table:
>CREATE TABLE [dbo].[CompanyRatios_3Y] (
> [Name] [varchar] (160),
> [Ticker] [varchar] (10),
> [CIK] [varchar] (10),
> [PeriodEndDate] [datetime],
> [DurationType] [varchar] (3),
> [NetIncome] [decimal](38, 6) NULL ,
> [OperatingCashFlow] [decimal](38, 6) NULL ,
> [TotalAssets] [decimal](38, 6) NULL ,
> [TotalRevenue] [decimal](38, 6) NULL
> ) ON [PRIMARY]
>GO
>Sample Data:
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
>
>
>
>*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 16:59:16 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:

>SELECT K.Name,
> K.Ticker,
> K.CIK,
> C.PeriodEndDate,
> PeriodEndDateLastYear = P.PeriodEndDate,
> NetIncomeCurrentYear = C.NetIncome,
> NetIncomeLastYear = P.NetIncome,
> OpCashFlowCurrentYear = C.OperatingCashFlow,
> OpCashFlowLastYear = P.OperatingCashFlow
> FROM (select distinct Name, Ticker, CIK
> from CompanyRatios_3) as K -- for Key
> LEFT OUTER
> JOIN CompanyRatios_3 as C -- as in Current
> ON K.Ticker = C.Ticker
> AND K.CIK = C.CIK
> AND C.PeriodEndDate = '20051231'
> JOIN CompanyRatios_3 as P -- as in Prior
> ON K.Ticker = P.Ticker
> AND K.CIK = P.CIK
> AND P.PeriodEndDate = '20041231'
I missed the second LEFT OUTER:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
LEFT OUTER
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy|||Thanks for both solutions. There will always be a CIK, Ticker, and name
but the dates will not always be 12/31/2005 and 12/31/2004. I was using
those dates as examples.
*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 18:29:13 -0700, Jason . <jrp210@.yahoo.com> wrote:

>Thanks for both solutions. There will always be a CIK, Ticker, and name
>but the dates will not always be 12/31/2005 and 12/31/2004. I was using
>those dates as examples.
That should be easily corrected. Just change the date tests:
AND datepart(year, C.PeriodEndDate) = datepart(year,getdate())
AND datepart(year, P.PeriodEndDate) = datepart(year,getdate()) - 1
Roy|||Thanks! There could be more than two dates per CIK so I am guessing I
would have to add the following to get the latest two dates:
WHERE (a.PeriodEndDate =
(SELECT MAX(c.PeriodEndDate)
FROM CompanyRatios_3 c
WHERE a.CIK = c.CIK))
*** Sent via Developersdex http://www.examnotes.net ***|||yeah.. I guess that should do the trick.
You will have to find the max of both this year and the previous year
--
"Jason ." wrote:

> Thanks! There could be more than two dates per CIK so I am guessing I
> would have to add the following to get the latest two dates:
> WHERE (a.PeriodEndDate =
> (SELECT MAX(c.PeriodEndDate)
> FROM CompanyRatios_3 c
> WHERE a.CIK = c.CIK))
>
> *** Sent via Developersdex http://www.developersdex

Combining the dates

Hi Guys

I have got two columns- one is the year and the other is the month number. I need to combine these two columns so that they form a date

For Ex

Year Month CombinedColumn

2000 11 2000/11

2003 01 2003/01

I am using SQL Server 2005

Thanks

I'll assume that Year and Month are integers and CombinedColumn is of data type DATETIME. If other data types are used, this solution might not apply.

Code Block

CREATE TABLE #Temp

(

[Year] INT NOT NULL,

[Month] INT NOT NULL,

[CombinedCol] DATETIME NULL

)

INSERT INTO #Temp VALUES (2000, 11, NULL)

INSERT INTO #Temp VALUES (2003, 01, NULL)

UPDATE #Temp

SET [CombinedCol] = DATEADD(month, [Month] - 1, DATEADD(year, [Year] - 1900, '1900-01-01 00:00:00.000'))

SELECT * FROM #Temp

|||

you can try this..

select cast( (cast(yearValue as varchar(4))+cast(monthValue as varchar(2)) + '01') as datetime)

you will get the date as the first day of your year and month values...

|||

Harish has the right idea, but it should be noted that 2000/11 is not a valid date. You can just concatenate them for display, but as for making them a date, can you explain more what you will do with the dates once you have them concatenated? (unless just making them the first day of the month suffices, of course

|||

My query would be like select cast('20001101' as datetime) which sql server implicitly converts to datetime value from varchar if it is in yyyymmdd format. The user needs to decide which value he needs for the dd value in the string

|||

Here it is,

Code Block

Create Table #sampledata (

[Year] varchar(4),

[Month] varchar(2)

);

Insert Into #sampledata Values('2000','11');

Insert Into #sampledata Values('2003','01');

DECLARE @.UserDay varchar(2);

Set @.UserDay = '5'

Select Convert(Datetime, Year + Month + substring(cast((cast(@.UserDay as int) + 100) as varchar),2,2),112) [Output] from #sampledata

|||

Another method with fewer keystrokes

Code Block

SELECT CAST(LTRIM([Year] * 10000 + [Month] * 100 + @.UserDay) AS DATETIME)

FROM #sampledata

|||Is it bad to use implicit conversion? I believe it will be faster than explicitly handling the conversion.|||

Well, I think it is better to use explicit conversion. That way you leave no room for anyone for interpretation and avoid any ambiguities.

|||

Just a note: "Partial Dates" like "January, 1968" are not really supported. Basically in SQL, if you really want to work with dates, you must store a full date...."January 1, 1968" for example. SQL will infer the time component as being Midnight and store it as "01 Jan 1968 00:00" (if you're using smalldatetime) and "01 Jan 1968 00:00:00.000" using DateTime.

So, for your query, let's add a day-of-the-month component:

Code Block

select convert(smalldatetime,'01' + '/' + Month + '/' + Year) as ThisIsTheDate from

or...if the values are stored as numbers (which is not suggested in your example of "01" which would probably be "1" if it were stored as a number:

Code Block

select convert(smalldatetime,"01/" + right('00',convert(varchar,month),2) + '/' + convert(varchar,year))

from

|||

Thanks .It works fine

Cheers

|||

Actually, here's one that I like better:

Note that DateAdd(year,50,0) = 'January 1, 1950' and DateAdd(month, 5, 0) = 'June 1, 1900' so....

Code Block

CREATE TABLE #Temp

(

[Year] INT NOT NULL,

[Month] INT NOT NULL,

[CombinedCol] DATETIME NULL

)

INSERT INTO #Temp VALUES (2000, 11, NULL)

INSERT INTO #Temp VALUES (2003, 01, NULL)

select dateadd(month, [Month]-1,dateadd(Year,[year]-1900,0))

from #Temp

|||

I came up with the same approach, but after you. One little thing...rather than specify the "base date" with a string of "1900-01-01 00:00:00.000") you can simply use the numeric 0

Code Block

select DateAdd(month, 10, 0)

select DateAdd(month, 10, "1900-01-01 00:00:00.000")

Combining Table and Matrix format in one Report in RS2005

Hello,

I am using RS 2005 trying to create the following report. My report consists of the following columns: Question, Sub Question, N as Number of Responses, All as Average for all responses per given question and sub question, and Ethnicity column which is presented here in a Matrix format with ethnic group as columns and average response as Data values. It looks like my challenge is to combine Matrix format report (Ethnicity column) with a data such as N and All columns which are more like a table format. Any input how I could tackle this is greatly appreciated.

Thank you!

--

1.How often have you done each of the following?

N All F M Asian Multi-cultural a. Worked on a paper or project that required integrating ideas or information from various sources 1134 3.96 3.95 3.99 3.54 4.50 b. Used library resources 1132 4.21 4.26 4.09 4.12 4.33 c. Prepared multiple drafts of a paper or assignment before turning it in 1130 3.90 3.97 3.76 3.80 4.50

-How the source data looks like?sqlsql

Combining rows in a table(again)

I've seen a number of questions on combining rows, but not one
exactly like this. I have a solution, but I'd like to know
if there are other ways.
I'd like to select and combine rows from a table. Here's a simplified
version of the table:
tab1
key date status
1 1/1/06 stat1
1 1/2/06 stat2
1 1/3/06 stat3
1 1/4/06 stat4
2 1/1/06 stat1
2 1/2/06 stat2

And the desired results:
key date status prevstatus
1 1/1/06 stat1 null
1 1/2/06 stat2 stat1
1 1/3/06 stat3 stat2
1 1/4/06 stat4 stat3
2 1/1/06 stat1 null
2 1/2/06 stat2 stat1

Here's the simplified version of the solution:
select
a.*,b.status prevstatus
from
tab1 a
left join
tab1 b
on a.key = b.key and
b.date =
(select max(date) from tab1 c
where
a.key = c.key and
a.date > c.date
)

Is there a better way?Your resultset doesn't make much sense. Can you explain it?|||Your resultset doesn't make much sense. Can you explain it?It's a "PeopleSoft" join.

No gams, I'm pretty sure that is optimal for the case you've presented.

-PatP|||Yes.
The idea is to get a row and the most recent previous status. The first row in a set will have no previous status.|||It's a "PeopleSoft" join.

No gams, I'm pretty sure that is optimal for the case you've presented.

-PatP
Does that make me a "PeopleSoft" joiner? What is a "PeopleSoft" join?|||The "PeopleSoft join" was a reference for Brett's information. Brett is quite familiar with the glories of PeopleSoft.

PeopleSoft is an ERP package. The PeopleSoft packages use a data representation that often needs to reference the "prior" row based on a presumed sequence.

-PatP

Combining multiple subreports into a single report

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!

Have you tried reducing the body width to landscape? We had a similar requrement which we implemented with linked reports pointing to standalone reports and I don't recall having extra blank pages with mixed layouts.|||

I did check the landscape width for the reports both individualy and in the master report. They all render fine independently. I also tested the report rendering as I added each subreport to the master report. The moment I added the Landscape one, all portrait reports (that rendered fine before) spilled over onto subsequent pages. The subreports are embedded in a main report and not linked. Can you tell me more about how you configured your reports to be linked?

Thanks!

|||

I appologize I meant reducing the body width to portrait regardless of the fact that you have reports set to landscape. I believe at runtime the report server will expand the body width as needed.

A linked report is essentially a smart pointer to the actual report. You can create a linked reportin in the Report Manager. Go to the report properties and click on the Create Linked Report button. The advantage of having this point of indirection is that if the standalone report is moved, the linked report will automatically be redirected to the new location. Also, a linked report can have its own security policies, etc.

|||

Hi,

I was able to find information at this link:

http://msdn2.microsoft.com/en-us/library/ms155993.aspx

"Reporting Services does not provide a way to combine landscape and portrait mode pages in the same report, nor does it provide a way to create a print-based layout that replaces or exists alongside the layout of a report as rendered in a browser or other application. For most exported reports, report printouts include everything that is visible on the report, as viewed by the user on a computer monitor."

Not what I wanted to hear. Also, I was unable to find the Linked Reports option within Report Designer Report Properties. We are using Visual Studio 2005. Someone on the team provided these links for combining PDFs. This seems like a lot of work to go thru because of a missing feature. Even Word allows you to insert section breaks where you can specigy Landscapre or Portrait.

http://www.codeproject.com/cs/library/giospdfnetlibrary.asp

https://secure.codeproject.com/csharp/giospdfsplittermerger.asp

|||

Yes, this is correct. I appologize for giving you wrong information. Upon looking at our report package implementation, the master report width is set to Landscape. The Create Linked Report button is on the report properties (General Tab) assuming you use the Report Manager and have rights to create linked reports.

|||

Hi,

The mechanism that we've used to achieve this is to write some code using a PDF library to combine the reports. It goes off and renders the reports and then adds them to a master document. That way we can add page numbers, table of contents etc. and its all dynamic.

Sanjay

|||

Hi, and thanks.

That is what we ended up doing and I am posting the code for the benefit of others. We used PDFSharp (there are several others) and I modified one of their samples into the class below. It worked good and we ended up with one report that could have both landscape and portrait pages and page numbers.

#region PDFsharp - A .NET library for processing PDF

//

// Copyright (c) 2005-2006 empira Software GmbH, Cologne (Germany)

//

// http://www.pdfsharp.com

//

// http://sourceforge.net/projects/pdfsharp

//

// Permission is hereby granted, free of charge, to any person obtaining a copy

// of this software and associated documentation files (the "Software"), to deal

// in the Software without restriction, including without limitation the rights

// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

// copies of the Software, and to permit persons to whom the Software is

// furnished to do so, subject to the following conditions:

//

// The above copyright notice and this permission notice shall be included in

// all copies or substantial portions of the Software.

//

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS.

// IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,

// DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR

// OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE

// USE OR OTHER DEALINGS IN THE SOFTWARE.

#endregion

using System;

using System.Diagnostics;

using System.IO;

using PdfSharp;

using PdfSharp.Pdf;

using PdfSharp.Pdf.IO;

using PdfSharp.Drawing;

namespace successionManagement

{

public class CombinePdfs

{

public static PdfDocument combine(Stream[] streams, String fileName)

{

PdfDocument outputDocument = new PdfDocument();

XFont font = new XFont("arial", 8, XFontStyle.Regular);

XStringFormat format = new XStringFormat();

format.Alignment = XStringAlignment.Center;

format.LineAlignment = XLineAlignment.Far;

XGraphics gfx;

XRect box;

int totalPages = 0;

int currentPage = 0;

PdfDocument[] pdfDocuments = new PdfDocument[streams.Length];

for (int i = 0; i < streams.Length; i++)

{

Stream stream = (Stream) streams;

PdfDocument inputDocument = PdfReader.Open(stream, PdfDocumentOpenMode.Import);

totalPages = totalPages + inputDocument.PageCount;

pdfDocuments = inputDocument;

}

String pageNbrFooter;

for (int i=0; i < pdfDocuments.Length; i++)

{

PdfDocument inputDocument = (PdfDocument) pdfDocuments;

for (int idx = 0; idx < inputDocument.PageCount; idx++)

{

PdfPage page = inputDocument.Pages[idx];

currentPage = currentPage + 1;

pageNbrFooter = "Page " + currentPage + " of " + totalPages;

page = outputDocument.AddPage(page);

//Write document file name and page number on each page

gfx = XGraphics.FromPdfPage(page);

box = page.MediaBox.ToXRect();

box.Inflate(20, -10);

gfx.DrawString(String.Format( pageNbrFooter,0 ),

font, XBrushes.Black, box, format);

}

}

outputDocument.Save(fileName);

return outputDocument;

}

}

}

To invoke the class you would supply your stream in place of the pdf and the relative path. Here is a simple hard-coded path example.

Stream[] streams = new Stream[5];

streams[0] = new FileStream(@."c:/Lynette/portrait1.pdf", FileMode.Open);

streams[1] = new FileStream(@."c:/Lynette/portrait2.pdf", FileMode.Open);

streams[2] = new FileStream(@."c:/Lynette/portrait3.pdf", FileMode.Open);

streams[3] = new FileStream(@."c:/Lynette/landscape1.pdf", FileMode.Open);

streams[4] = new FileStream(@."c:/Lynette/portrait4.pdf", FileMode.Open);

PdfDocument combinedPdf = CombinePdfs.combine(streams,"c:/Lynette/myCombined.pdf");

|||

The code sample above should have a subscript in brackets but I guess that is also the symbol for an idea. How funny!

|||

Great solution to this issue. Thank you for posting the code!

Combining multiple subreports into a single report

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!

Have you tried reducing the body width to landscape? We had a similar requrement which we implemented with linked reports pointing to standalone reports and I don't recall having extra blank pages with mixed layouts.|||

I did check the landscape width for the reports both individualy and in the master report. They all render fine independently. I also tested the report rendering as I added each subreport to the master report. The moment I added the Landscape one, all portrait reports (that rendered fine before) spilled over onto subsequent pages. The subreports are embedded in a main report and not linked. Can you tell me more about how you configured your reports to be linked?

Thanks!

|||

I appologize I meant reducing the body width to portrait regardless of the fact that you have reports set to landscape. I believe at runtime the report server will expand the body width as needed.

A linked report is essentially a smart pointer to the actual report. You can create a linked reportin in the Report Manager. Go to the report properties and click on the Create Linked Report button. The advantage of having this point of indirection is that if the standalone report is moved, the linked report will automatically be redirected to the new location. Also, a linked report can have its own security policies, etc.

|||

Hi,

I was able to find information at this link:

http://msdn2.microsoft.com/en-us/library/ms155993.aspx

"Reporting Services does not provide a way to combine landscape and portrait mode pages in the same report, nor does it provide a way to create a print-based layout that replaces or exists alongside the layout of a report as rendered in a browser or other application. For most exported reports, report printouts include everything that is visible on the report, as viewed by the user on a computer monitor."

Not what I wanted to hear. Also, I was unable to find the Linked Reports option within Report Designer Report Properties. We are using Visual Studio 2005. Someone on the team provided these links for combining PDFs. This seems like a lot of work to go thru because of a missing feature. Even Word allows you to insert section breaks where you can specigy Landscapre or Portrait.

http://www.codeproject.com/cs/library/giospdfnetlibrary.asp

https://secure.codeproject.com/csharp/giospdfsplittermerger.asp

|||

Yes, this is correct. I appologize for giving you wrong information. Upon looking at our report package implementation, the master report width is set to Landscape. The Create Linked Report button is on the report properties (General Tab) assuming you use the Report Manager and have rights to create linked reports.

|||

Hi,

The mechanism that we've used to achieve this is to write some code using a PDF library to combine the reports. It goes off and renders the reports and then adds them to a master document. That way we can add page numbers, table of contents etc. and its all dynamic.

Sanjay

|||

Hi, and thanks.

That is what we ended up doing and I am posting the code for the benefit of others. We used PDFSharp (there are several others) and I modified one of their samples into the class below. It worked good and we ended up with one report that could have both landscape and portrait pages and page numbers.

#region PDFsharp - A .NET library for processing PDF

//

// Copyright (c) 2005-2006 empira Software GmbH, Cologne (Germany)

//

// http://www.pdfsharp.com

//

// http://sourceforge.net/projects/pdfsharp

//

// Permission is hereby granted, free of charge, to any person obtaining a copy

// of this software and associated documentation files (the "Software"), to deal

// in the Software without restriction, including without limitation the rights

// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

// copies of the Software, and to permit persons to whom the Software is

// furnished to do so, subject to the following conditions:

//

// The above copyright notice and this permission notice shall be included in

// all copies or substantial portions of the Software.

//

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS.

// IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,

// DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR

// OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE

// USE OR OTHER DEALINGS IN THE SOFTWARE.

#endregion

using System;

using System.Diagnostics;

using System.IO;

using PdfSharp;

using PdfSharp.Pdf;

using PdfSharp.Pdf.IO;

using PdfSharp.Drawing;

namespace successionManagement

{

public class CombinePdfs

{

public static PdfDocument combine(Stream[] streams, String fileName)

{

PdfDocument outputDocument = new PdfDocument();

XFont font = new XFont("arial", 8, XFontStyle.Regular);

XStringFormat format = new XStringFormat();

format.Alignment = XStringAlignment.Center;

format.LineAlignment = XLineAlignment.Far;

XGraphics gfx;

XRect box;

int totalPages = 0;

int currentPage = 0;

PdfDocument[] pdfDocuments = new PdfDocument[streams.Length];

for (int i = 0; i < streams.Length; i++)

{

Stream stream = (Stream) streams;

PdfDocument inputDocument = PdfReader.Open(stream, PdfDocumentOpenMode.Import);

totalPages = totalPages + inputDocument.PageCount;

pdfDocuments = inputDocument;

}

String pageNbrFooter;

for (int i=0; i < pdfDocuments.Length; i++)

{

PdfDocument inputDocument = (PdfDocument) pdfDocuments;

for (int idx = 0; idx < inputDocument.PageCount; idx++)

{

PdfPage page = inputDocument.Pages[idx];

currentPage = currentPage + 1;

pageNbrFooter = "Page " + currentPage + " of " + totalPages;

page = outputDocument.AddPage(page);

//Write document file name and page number on each page

gfx = XGraphics.FromPdfPage(page);

box = page.MediaBox.ToXRect();

box.Inflate(20, -10);

gfx.DrawString(String.Format( pageNbrFooter,0 ),

font, XBrushes.Black, box, format);

}

}

outputDocument.Save(fileName);

return outputDocument;

}

}

}

To invoke the class you would supply your stream in place of the pdf and the relative path. Here is a simple hard-coded path example.

Stream[] streams = new Stream[5];

streams[0] = new FileStream(@."c:/Lynette/portrait1.pdf", FileMode.Open);

streams[1] = new FileStream(@."c:/Lynette/portrait2.pdf", FileMode.Open);

streams[2] = new FileStream(@."c:/Lynette/portrait3.pdf", FileMode.Open);

streams[3] = new FileStream(@."c:/Lynette/landscape1.pdf", FileMode.Open);

streams[4] = new FileStream(@."c:/Lynette/portrait4.pdf", FileMode.Open);

PdfDocument combinedPdf = CombinePdfs.combine(streams,"c:/Lynette/myCombined.pdf");

|||

The code sample above should have a subscript in brackets but I guess that is also the symbol for an idea. How funny!

|||

Great solution to this issue. Thank you for posting the code!

Combining multiple subreports into a single report

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!

Have you tried reducing the body width to landscape? We had a similar requrement which we implemented with linked reports pointing to standalone reports and I don't recall having extra blank pages with mixed layouts.|||

I did check the landscape width for the reports both individualy and in the master report. They all render fine independently. I also tested the report rendering as I added each subreport to the master report. The moment I added the Landscape one, all portrait reports (that rendered fine before) spilled over onto subsequent pages. The subreports are embedded in a main report and not linked. Can you tell me more about how you configured your reports to be linked?

Thanks!

|||

I appologize I meant reducing the body width to portrait regardless of the fact that you have reports set to landscape. I believe at runtime the report server will expand the body width as needed.

A linked report is essentially a smart pointer to the actual report. You can create a linked reportin in the Report Manager. Go to the report properties and click on the Create Linked Report button. The advantage of having this point of indirection is that if the standalone report is moved, the linked report will automatically be redirected to the new location. Also, a linked report can have its own security policies, etc.

|||

Hi,

I was able to find information at this link:

http://msdn2.microsoft.com/en-us/library/ms155993.aspx

"Reporting Services does not provide a way to combine landscape and portrait mode pages in the same report, nor does it provide a way to create a print-based layout that replaces or exists alongside the layout of a report as rendered in a browser or other application. For most exported reports, report printouts include everything that is visible on the report, as viewed by the user on a computer monitor."

Not what I wanted to hear. Also, I was unable to find the Linked Reports option within Report Designer Report Properties. We are using Visual Studio 2005. Someone on the team provided these links for combining PDFs. This seems like a lot of work to go thru because of a missing feature. Even Word allows you to insert section breaks where you can specigy Landscapre or Portrait.

http://www.codeproject.com/cs/library/giospdfnetlibrary.asp

https://secure.codeproject.com/csharp/giospdfsplittermerger.asp

|||

Yes, this is correct. I appologize for giving you wrong information. Upon looking at our report package implementation, the master report width is set to Landscape. The Create Linked Report button is on the report properties (General Tab) assuming you use the Report Manager and have rights to create linked reports.

|||

Hi,

The mechanism that we've used to achieve this is to write some code using a PDF library to combine the reports. It goes off and renders the reports and then adds them to a master document. That way we can add page numbers, table of contents etc. and its all dynamic.

Sanjay

|||

Hi, and thanks.

That is what we ended up doing and I am posting the code for the benefit of others. We used PDFSharp (there are several others) and I modified one of their samples into the class below. It worked good and we ended up with one report that could have both landscape and portrait pages and page numbers.

#region PDFsharp - A .NET library for processing PDF

//

// Copyright (c) 2005-2006 empira Software GmbH, Cologne (Germany)

//

// http://www.pdfsharp.com

//

// http://sourceforge.net/projects/pdfsharp

//

// Permission is hereby granted, free of charge, to any person obtaining a copy

// of this software and associated documentation files (the "Software"), to deal

// in the Software without restriction, including without limitation the rights

// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

// copies of the Software, and to permit persons to whom the Software is

// furnished to do so, subject to the following conditions:

//

// The above copyright notice and this permission notice shall be included in

// all copies or substantial portions of the Software.

//

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS.

// IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,

// DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR

// OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE

// USE OR OTHER DEALINGS IN THE SOFTWARE.

#endregion

using System;

using System.Diagnostics;

using System.IO;

using PdfSharp;

using PdfSharp.Pdf;

using PdfSharp.Pdf.IO;

using PdfSharp.Drawing;

namespace successionManagement

{

public class CombinePdfs

{

public static PdfDocument combine(Stream[] streams, String fileName)

{

PdfDocument outputDocument = new PdfDocument();

XFont font = new XFont("arial", 8, XFontStyle.Regular);

XStringFormat format = new XStringFormat();

format.Alignment = XStringAlignment.Center;

format.LineAlignment = XLineAlignment.Far;

XGraphics gfx;

XRect box;

int totalPages = 0;

int currentPage = 0;

PdfDocument[] pdfDocuments = new PdfDocument[streams.Length];

for (int i = 0; i < streams.Length; i++)

{

Stream stream = (Stream) streams;

PdfDocument inputDocument = PdfReader.Open(stream, PdfDocumentOpenMode.Import);

totalPages = totalPages + inputDocument.PageCount;

pdfDocuments = inputDocument;

}

String pageNbrFooter;

for (int i=0; i < pdfDocuments.Length; i++)

{

PdfDocument inputDocument = (PdfDocument) pdfDocuments;

for (int idx = 0; idx < inputDocument.PageCount; idx++)

{

PdfPage page = inputDocument.Pages[idx];

currentPage = currentPage + 1;

pageNbrFooter = "Page " + currentPage + " of " + totalPages;

page = outputDocument.AddPage(page);

//Write document file name and page number on each page

gfx = XGraphics.FromPdfPage(page);

box = page.MediaBox.ToXRect();

box.Inflate(20, -10);

gfx.DrawString(String.Format( pageNbrFooter,0 ),

font, XBrushes.Black, box, format);

}

}

outputDocument.Save(fileName);

return outputDocument;

}

}

}

To invoke the class you would supply your stream in place of the pdf and the relative path. Here is a simple hard-coded path example.

Stream[] streams = new Stream[5];

streams[0] = new FileStream(@."c:/Lynette/portrait1.pdf", FileMode.Open);

streams[1] = new FileStream(@."c:/Lynette/portrait2.pdf", FileMode.Open);

streams[2] = new FileStream(@."c:/Lynette/portrait3.pdf", FileMode.Open);

streams[3] = new FileStream(@."c:/Lynette/landscape1.pdf", FileMode.Open);

streams[4] = new FileStream(@."c:/Lynette/portrait4.pdf", FileMode.Open);

PdfDocument combinedPdf = CombinePdfs.combine(streams,"c:/Lynette/myCombined.pdf");

|||

The code sample above should have a subscript in brackets but I guess that is also the symbol for an idea. How funny!

|||

Great solution to this issue. Thank you for posting the code!

Combining Data from Variable number of tables

I have a requirment to take data from a large set of tables where the
total number of these tables may change on a regular baisis and
output into another table. All the tables willl have the same
columns. Frequency is being debated but it maybe as much as once per
hour.

Example
1) I need to choose all the following tables
select * from dbo.sysobjects where name like '_CPY%.

2) then I need the following
for each of the tables found above, I need the outfrom from each of
those tables to be inputted into another table. basically, I would
want the following output from each of the tables found in step 1

select machineid,name from _cpy_offermanager_678

3) In the end I would have something like dbo.ALLCPY with records
combined from all other _CPY tables

Ron SorrellTry something like:

SET NOCOUNT ON
CREATE TABLE ALLCPY
(
machineid int NOT NULL,
name varchar(30) NOT NULL

)
DECLARE @.InsertStatement nvarchar(4000)

DECLARE InsertStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'INSERT INTO ALLCPY
SELECT machineid,name FROM ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME LIKE '[_]CPY%'

OPEN InsertStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM InsertStatements INTO @.InsertStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.InsertStatement)
END
CLOSE InsertStatements
DEALLOCATE InsertStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Ron Sorrell" <ron.sorrell@.quaysys.com> wrote in message
news:4cgrmv0tnu355pk28jb7si938uq9pe6d85@.4ax.com...
> I have a requirment to take data from a large set of tables where the
> total number of these tables may change on a regular baisis and
> output into another table. All the tables willl have the same
> columns. Frequency is being debated but it maybe as much as once per
> hour.
> Example
> 1) I need to choose all the following tables
> select * from dbo.sysobjects where name like '_CPY%.
> 2) then I need the following
> for each of the tables found above, I need the outfrom from each of
> those tables to be inputted into another table. basically, I would
> want the following output from each of the tables found in step 1
> select machineid,name from _cpy_offermanager_678
> 3) In the end I would have something like dbo.ALLCPY with records
> combined from all other _CPY tables
> Ron Sorrell|||Perfect
Thank you very much

Ron Sorrell

On Sun, 21 Sep 2003 20:04:31 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:

>Try something like:
>
>SET NOCOUNT ON
>CREATE TABLE ALLCPY
> (
> machineid int NOT NULL,
> name varchar(30) NOT NULL
>)
>DECLARE @.InsertStatement nvarchar(4000)
>DECLARE InsertStatements CURSOR
>LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT
> N'INSERT INTO ALLCPY
> SELECT machineid,name FROM ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> TABLE_NAME LIKE '[_]CPY%'
>OPEN InsertStatements
>WHILE 1 = 1
>BEGIN
> FETCH NEXT FROM InsertStatements INTO @.InsertStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.InsertStatement)
>END
>CLOSE InsertStatements
>DEALLOCATE InsertStatementssqlsql

Combining data from several databases

Hello

I'm to familiar with Analysis Services. If my question is not related to this forum, please redirect me to somewhere else.

I have a number of databases of the same structure (for instance 5 databases). I would like to create some reports using data from all of them. For instance if there is a table called Customer I would like to get a report with a list of Customers with the addition of one column that contains the source database.

I'm also concerned about performance. I would prefer if the data of the original databases where copied somewhere else, since they are OLTP databases and it would be good not to be affected in terms of performance by the whole procedure.

Can you please give me some options on how to implement a solution?

Hello!

I would recommend you to build a data warehouse.

With SQL Server 2005 you have all tools you need for that.

SSIS(Integration Services) let you pump and transform data from the five source databases.

When you have a data warehouse finished you can build an Analysis Services(SSAS2005) cube on top of that or query the data warehouse tables directly with reporting services(SSRS2005).

All these poducts, the databas engine, SSIS2005, SSAS2005 and SSRS2005 are included in the same license.

HTH

Thomas Ivarsson

Combining Data from Multiple tables

I have a requirment to take data from a large set of tables where the total number of these tables may change on a regular baisis and output into another table. All the tables willl have the same columns. Frequency is being debated but it maybe as much as once per hour.

Example
1) I need to choose all the following tables
select * from dbo.sysobjects where name like '_CPY%.

2) then I need the following
for each of the tables found above, I need the outfrom from each of those tables to be inputted into another table. basically, I would want the following output from each of the tables found in step 1

select machineid,name from _cpy_offermanager_678

3) In the end I would have something like dbo.ALLCPY with records combined from all other _CPY tables

Ron SorrellWhat about this idea? This draft does not work properly because of filed name does not exists for all tables - but you modify for your case.

declare @.union varchar(8000)
set @.union='insert alltables'+char(13)
select @.union=@.union+' select name from '+name+char(13)+'union all'+char(13) from sysobjects where xtype='U'
select @.union=left(@.union,DATALENGTH(@.union)-10)
exec( @.union)

Combined into one select

Hi all,

I have the following:

select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1

Is there anyway to combined them into one query? So I get one result?

Thanks,

KenPlace UNION between the select statements.

Originally posted by GA_KEN
Hi all,

I have the following:

select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1

Is there anyway to combined them into one query? So I get one result?

Thanks,

Ken|||Union sort of worked, but I need the data in columns, union gave me rows.|||try this

select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) as UNDER25_12_SINGLE ,
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) as UNDER25_12_MULTIPLE
:
:|||Try this:

select * from
(select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1) as A,
(select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1) as B,
(select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1) as C,
(select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1) as D,
(select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1) as E,
(select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1) as F

Originally posted by GA_KEN
Union sort of worked, but I need the data in columns, union gave me rows.|||Originally posted by msieben
try this

select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) as UNDER25_12_SINGLE ,
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) as UNDER25_12_MULTIPLE
:
:

This works just like I wanted! I knew there had to be a way! I've been pulling my hair out trying to get it to work!|||You guys are awesome!!

Thanks for all your help!

Ken|||select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) AS UNDER25_12_SINGLE
,(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) AS UNDER25_12_MULTIPLE
,(select count([Account Number]) from OH_UNDER25_36MONTHS where AccountCount = 1) AS UNDER25_36_SINGLE
,(select count([Account Number]) from OH_UNDER25_36MONTHS where AccountCount > 1) AS UNDER25_36_MULTIPLE
,(select count([Account Number]) from OH_UNDER25_60MONTHS where AccountCount = 1) AS UNDER25_60_SINGLE
,(select count([Account Number]) from OH_UNDER25_60MONTHS where AccountCount > 1) AS UNDER25_60_MULTIPLE

Good luck !

Combine two queries - help please

I have a table that has two dates in it, a date opened and a date
closed. I would like to create one query to give me the number of
records that have been opened each month plus, and this is the hard
part the number of those records that have been closed each month. I
can get the result with two seperate queries but have been unable to
get it combined into one query with three values for each month, i.e.,
the month, the number opened and the number of those that were opened
in the month that have been subsequently closed.

Here's my two queries. If anyone can help I'd appreciate.

SELECT COUNT(*) AS [Number Closed], LEFT(DATENAME(m, DateOpened),
3) + '
' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]
FROM table
WHERE (DateClosed IS NOT NULL)
GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,
DateOpened), 3)
+ ' ' + CAST(YEAR(DateOpened) AS Char(5))
ORDER BY CONVERT(CHAR(7), DateOpened, 120)

SELECT COUNT(*) AS [Number Opened], LEFT(DATENAME(m, DateOpened),
3) + '
' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]
FROM table
GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,
DateOpened), 3)
+ ' ' + CAST(YEAR(DateOpened) AS Char(5))
ORDER BY CONVERT(CHAR(7), DateOpened, 120)

TIA

BillTry:

SELECT MIN(dateopened),
COUNT(*),
COUNT(dateclosed)
FROM YourTable
GROUP BY YEAR(dateopened), MONTH(dateopened)

--
David Portas
SQL Server MVP
--|||David;

Thank you very much that works just fine. I appreciate the help.

Cheers;

Bill

Combine record?

I want a string that consist full account number as follow:
account_num = 300.111000.10
But table only consist of the following:
-gbmcu : 300
-gbobj : 111000
-gbsub : 10
So how to combine gbmcu,gbobj and gbsub into account_num?
I had tried this T-SQL but fail :
update Bank_Account
set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
go
Error Message
--
Server: Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.
Please help.Sam wrote:
> I want a string that consist full account number as follow:
> account_num = 300.111000.10
> But table only consist of the following:
> -gbmcu : 300
> -gbobj : 111000
> -gbsub : 10
> So how to combine gbmcu,gbobj and gbsub into account_num?
> I had tried this T-SQL but fail :
> update Bank_Account
> set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
> go
> Error Message
> --
> Server: Msg 8152, Level 16, State 4, Line 2
> String or binary data would be truncated.
> The statement has been terminated.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You don't indicate the data types for the columns gbmcu, gbobj & gbsub.
If they are numeric you'll have to convert them to varchar:
set account_num = cast(gbmcu as varchar) + '.'
+ cast(gbobj as varchar) + '.'
+ cast(gbsub as varchar)
This assumes that columns gbmcu, gbobj & gbsub are in the table being
updated. It also assumes that column account_num is a varchar data type
column.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhqwS4echKqOuFEgEQJMPACfSUlNvfxcbuSt
iWy8RFVdCqRy43sAnj/Z
vlg9s7fQcepcdpIQ8bu8Bi1o
=LMc6
--END PGP SIGNATURE--|||The problem could be that your account_number definition is too short to
hold the resulting string. Else, yours and MGFoster's solution should work.
-oj
"Sam" <cybersam88@.hotmail.com> wrote in message
news:%23N4UkqIGFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I want a string that consist full account number as follow:
> account_num = 300.111000.10
> But table only consist of the following:
> -gbmcu : 300
> -gbobj : 111000
> -gbsub : 10
> So how to combine gbmcu,gbobj and gbsub into account_num?
> I had tried this T-SQL but fail :
> update Bank_Account
> set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
> go
> Error Message
> --
> Server: Msg 8152, Level 16, State 4, Line 2
> String or binary data would be truncated.
> The statement has been terminated.
> Please help.
>

columns count

Please any one can tell me.
How to defined Number of columns for tables?
For example I have one table in my database called “my_table” it has number of columns
What the functions or any thing I can use to get the number of columns for that table and assigned to local variable?
Declare @.P_count_int
Select @. P_count_int = ??
If this table (my_table have 5 columns )
The result should be if I print this variable 5
SELECT @.p_count_int = COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='my_table'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"mmsjed" <anonymous@.discussions.microsoft.com> wrote in message
news:D1CFBD6D-9872-4B36-89A7-944CB9A9B396@.microsoft.com...
> Please any one can tell me.
> How to defined Number of columns for tables?
> For example I have one table in my database called my_table it has
> number of columns
> What the functions or any thing I can use to get the number of columns for
> that table and assigned to local variable?
> Declare @.P_count_int
> Select @. P_count_int = ??
> If this table (my_table have 5 columns )
> The result should be if I print this variable 5
>

columns count

Please any one can tell me.
How to defined Number of columns for tables?
For example I have one table in my database called “my_table” it has num
ber of columns
What the functions or any thing I can use to get the number of columns for t
hat table and assigned to local variable?
Declare @.P_count_int
Select @. P_count_int = '?
If this table (my_table have 5 columns )
The result should be if I print this variable 5SELECT @.p_count_int = COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='my_table'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"mmsjed" <anonymous@.discussions.microsoft.com> wrote in message
news:D1CFBD6D-9872-4B36-89A7-944CB9A9B396@.microsoft.com...
> Please any one can tell me.
> How to defined Number of columns for tables?
> For example I have one table in my database called my_table it has
> number of columns
> What the functions or any thing I can use to get the number of columns for
> that table and assigned to local variable?
> Declare @.P_count_int
> Select @. P_count_int = '?
> If this table (my_table have 5 columns )
> The result should be if I print this variable 5
>

column with a number and 2 decimal like 10.10 or 150.30

Hello,

How should I create a column to save data with the folowing format 10.10 or 10.20 or 10.30 or 150.30 or 10 or 150.
It is basically process step in a diagram flow.
I tried with decimal but with 10.10 , it removes automatically the 0.

ThanksYou could set the precision to 2in the design view for the particular column. Alternatively, this kind of formatting is best done from your presentation layer. There are different varieties of DataFormatStrings available to preent the data in different ways.|||

Hi

You column type must be decimal(18, 0) .

change to decimal(18, 2) .

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.
The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.
|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.
|||Uytkownik Roger Wolter[MSFT] napisa:

> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||Uytkownik Roger Wolter[MSFT] napisa:

> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much mo
re
> efficient than converting a character column to integers which you're maki
ng
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn'
t
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||U¿ytkownik Roger Wolter[MSFT] napisa³:
> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.