Database

Re: Reading xml inside stored procedure

29-Apr-08 4:36

Lot of informations [^] just by simple google search.

I Love T-SQL

saurabhmind29-Apr-08 22:33

saurabhmind

29-Apr-08 22:33

i have tried google but i dont find the appropriate result

by the way

thankx

Reporting Best Practises (Are there any what do you do?)

GDMFSOB29-Apr-08 0:10

GDMFSOB

29-Apr-08 0:10

Hi,

I am in the process of trying to find out what is the best practise for generating reports from an sql database useing different tables, that would be usefull to user on the Software access the DB, how do you do it? I come from a web design background and are now having to design a reporting engine for an ancient VB 6 application i have been lumped with....

I am used to having to build each report manually in html line for line and writting the formatting out in code, using sql querys for getting the data I need. but this is hard crap work and I would like to know if I am the only one doing it this way or do you have another way of doing it using some kind of report builder like crystal reports.

Its just there must be a better and faster way to get usefull reports from a db without the hard work building every line in the report from different querys and functions that jump all over the place in your application and db.

your insiteful comments will be greatly appreciated...

Re: Reporting Best Practises (Are there any what do you do?)

Youtea1-May-08 16:57

Youtea

1-May-08 16:57

If you're programming under .NET, I would suggest to use Microsoft's Reporting Technology (I think Crystal Report is a little more complicated, but that's just my personal opinion). It's easy--all you have to do is to design a report (Add new item -> Report), create an instance with parameters and attach it to your program. You don't have to worry about print margins or layout. They can be set by changing some variables, unlike HTML, which you'd have to mess with CSS. This is especially useful when you have to print labels.

Plus, The Reporting Technology and Crystal Report comes with VS2005. If you're programming .NET you should have them installed already.

Selecting the latest records

Kevin Brydon28-Apr-08 23:27

Kevin Brydon

28-Apr-08 23:27

Hi, I have a table that lists the incomes and expenditures of various organizations.

<br />
Organization | Report Year | Income | Expenditure<br />
Org 1 |         1/4/2008 |     £20 |     £30<br />
Org 1 |         1/4/2007 |     £20 |     £30<br />
Org 2 |         1/4/2003 |     £20 |     £30<br />
Org 2 |         1/4/2002 |     £20 |     £30<br />
Org 2 |         1/4/2001 |     £20 |     £30<br />
Org 3 |         1/4/2006 |     £20 |     £30<br />
Org 4 |         1/4/2002 |     £20 |     £30<br />

I have been trying to write a query that will return the latest record for each organization. So the result would be

<br />
Org 1 |         1/4/2008 |     £20 |     £30<br />
Org 2 |         1/4/2003 |     £20 |     £30<br />
Org 3 |         1/4/2006 |     £20 |     £30<br />
Org 4 |         1/4/2002 |     £20 |     £30<br />

Tried searching but came up with no uselful answers. Can someone help? It needs to be compatible with Microsoft Access Dead | X|

Blue_Boy28-Apr-08 23:55

28-Apr-08 23:55

here it is :

select distinct organisation,<br />
(select top 1 t1.rerportyear from myTable as t1 where t1.organisation = myTable.organisation order by rerportyear desc) as reportYear,income,expenditure<br />
from myTable

I Love T-SQL

Harini N K29-Apr-08 0:13

Harini N K

29-Apr-08 0:13

Hi

Try this:

SELECT<br />
	Organization,<br />
	[Report Year],<br />
	[Income],<br />
	[Expenditure]<br />
FROM <br />
	Table1 a <br />
WHERE [Report Year] >=<br />
		(<br />
		SELECT TOP 1 <br />
			[Report Year]<br />
		FROM <br />
			Table1 b <br />
		WHERE <br />
			a.Organization = b.Organization <br />
		ORDER BY [Report Year] DESC<br />
		)<br />
ORDER BY <br />
	Organization

Hope you are getting results as expected.

Harini

Kevin Brydon30-Apr-08 6:45

Kevin Brydon

30-Apr-08 6:45

Thanks for replying blue_boy and harini. I used blue_boys solution in the end and it works as i want it. it actually fits into another much longer sql statement.

Blue_Boy30-Apr-08 10:16

Selecting 3 columns and merging???

30-Apr-08 10:16

Glad to help you... Wink | ;)

I Love T-SQL

Limit Rows in DataTable

Brady Kelly28-Apr-08 23:15

Brady Kelly

28-Apr-08 23:15

I have an application under test, where I iteratively reset an export batch and then run the export for different configurations. For major testing, I wish to test each config over the full monty, 70k documents exported, but for dev testing I like to keep the rows in each section down to 500. Last night in the interests of not breaking code, I was popping ‘TOP 500’ into each query, but I’m sure there is a better way. Any suggestions will be appreciated. I’m using a SqlDataAdapter to fill my DataTable objects.

Karan_TN28-Apr-08 21:46

Karan_TN

28-Apr-08 21:46

hai,
i m using SQL server 2000.
i have 3 columns A,B,C respectively. the columns consist of the following values.

Col-A: a,b,c,d,e,f,g,h,i,i,i,i,i
Col-B: j,k,l,m,n,o,p,q,r,i,r,r,r
Col-C: s,t,u,v,w,x,y,z,a,b,c,n,s

I need the output as

Col-D: a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z

Note : Commas are not needed. just in row-wise

i.e, i want to combine 3 columns and want to remove the REPEAT values,NULL and EMPTY

how to achieve it? - KARAN

Blue_Boy28-Apr-08 22:07

28-Apr-08 22:07

here it is my quick solution:

select distinct cola <br />
from myTable<br />
union all<br />
select distinct colb from myTable where colb <> (select top 1 cola from myTable order by cola desc)<br />
union all<br />
select distinct t3.colc from myTable as t3 where t3.colc <='z' and t3.colc >(select top 1 colb from myTable order by colb desc)

I Love T-SQL

Karan_TN28-Apr-08 22:32

Karan_TN

28-Apr-08 22:32

thanks blue_boy.
but all the three columns is in the same table and i dt know the last value whether the last value is "z" or anyother (since, i cant understand {t3.colc <='z'} statement)

Blue_Boy28-Apr-08 22:37

28-Apr-08 22:37

Instead harcoded 'z' condition then modified query looks like this:

select distinct cola <br />
from myTable<br />
union all<br />
select distinct colb from myTable where colb <> (select top 1 cola from myTable order by cola desc)<br />
union all<br />
select distinct t3.colc from myTable as t3 <br />
where t3.colc <=(select top 1 colc from myTable order by colc desc)<br />
and t3.colc >(select top 1 colb from myTable order by colb desc)

result of this query comes from one table in my case named myTable

I Love T-SQL