Can we build Web UIs with SSRS?

If all one needs is a bunch of reports, then, instead of going the
whole hog writing a UI in a bespoke ASP.NET application, can one take
the sole refuge of SQL Server 2005 Reporting Services to do a neat UI
along with the reports?
As in, one of the parameters for a report may be a period range. Can
SSRS help me build a form with two date pickers that I can:
1. program the events of, package the date ranges, pass it to the
report and call my report file from?
2. publish the form as a Web form?
Or, does one have to rely on a bespoke ASP.NET app to do it? What are
the limits of SSRS?SSRS has its limitations.
SSRS relies greatly on how the SQL code is written.
For example, if you are using date ranges - reporting services is there
to be the middle man between presentation and data. Nothing more. All
reporting services will require is 2 parameters(start date and end
date).
Meaning SSRS will have to TEXTBOX's where the user will just enter 2
dates and it will run the report between the 2 dates. In this case
there is no validation of the paramters but i can all be handleled on
the DB side. So reporting services doesnt have built in controls where
the user will enter dates, its only there to GET parameters. These
parameters are provided in a text box.
SSRS is not a web application where the user has easy to read UI to
enter paramters. It does have a VERY nice way of displaying the
results. Its buily to display a result of a DB call, not to present the
user with a web form.
regards,
Stas K.|||Thanks, Sorcerdon. I understood what you're saying. Now let me present
a broader picture of what I'm thinking might just be possible.
I have to build a bespoke ASP.NET app, which mostly requires to use
SSRS as a backend for reporting. So, in effect, this bespoke Web app is
just to provide UI forms and call SSRS with parameters.
I was considering if it might be possible to use any of the SQL Server
2005 services (Integration, Application, Reporting, Anything Else) to
get the UI functionality also as it is, out-of-the-box.
After all, what I'll be building a bespoke application is to get a UI
to call my reports. Surely, Microsoft, with its DSI and software
factories initiative might have as well provided with some tool to
automate that as well out-of-the-box.
Thoughts?|||Actually, in SSRS 2005, there is a better implementation of the Date type
parameters, with a calendar control and input validation controls.
Thiago
--
Regards,
"Sorcerdon" wrote:
> SSRS has its limitations.
> SSRS relies greatly on how the SQL code is written.
> For example, if you are using date ranges - reporting services is there
> to be the middle man between presentation and data. Nothing more. All
> reporting services will require is 2 parameters(start date and end
> date).
> Meaning SSRS will have to TEXTBOX's where the user will just enter 2
> dates and it will run the report between the 2 dates. In this case
> there is no validation of the paramters but i can all be handleled on
> the DB side. So reporting services doesnt have built in controls where
> the user will enter dates, its only there to GET parameters. These
> parameters are provided in a text box.
> SSRS is not a web application where the user has easy to read UI to
> enter paramters. It does have a VERY nice way of displaying the
> results. Its buily to display a result of a DB call, not to present the
> user with a web form.
> regards,
> Stas K.
>|||I was solely speaking of Reporting Services 2000. My bad.
There is a UI for reporting that is built in for reporting
services(report manager). People can log in subsrcibe to report and get
it in their mail every morning. All of that is built in. And they can
run the reports at will.
The problem is that in RS2000 (I have not yet started working with 2005
until very recently) it wasnt as nice of a UI. So, perhaps a RS 2005
PRO can answer this?
As for out-of-the-box - there is always report manager. The users input
their parameters - or they are defualted to defualt parameters. It DOES
have it built in, yes!
Here are some screenies of what the report manager looks like:
http://www.windowsitpro.com/Files/09/40529/Figure_07.gif
http://msdn.microsoft.com/library/en-us/dnhcvs04/html/408dobson5.jpg
http://www.codeproject.com/books/MSReportingServices/img005.jpg
In that last screenie you can see how the parameters are inputed - in
those text boxes.
regards,
Stas K.|||Hi,
Ofcourse RS is meant for reporting and not giving some fancy UI. But yes in
2005 they have provided what is called "Report Viewer" (comes with VS2005)
which you can use it in your Asp.Net along with your UI. Want to explore ?
search for "ReportViewer Controls (Visual Studio)" in VS 2005 documentation.
Amarnath
"Water Cooler v2" wrote:
> Thanks, Sorcerdon. I understood what you're saying. Now let me present
> a broader picture of what I'm thinking might just be possible.
> I have to build a bespoke ASP.NET app, which mostly requires to use
> SSRS as a backend for reporting. So, in effect, this bespoke Web app is
> just to provide UI forms and call SSRS with parameters.
> I was considering if it might be possible to use any of the SQL Server
> 2005 services (Integration, Application, Reporting, Anything Else) to
> get the UI functionality also as it is, out-of-the-box.
> After all, what I'll be building a bespoke application is to get a UI
> to call my reports. Surely, Microsoft, with its DSI and software
> factories initiative might have as well provided with some tool to
> automate that as well out-of-the-box.
> Thoughts?
>|||Thanks everyone.
Sorcerdon and Amarnath,
Where do I find these "Report Manager" and "Report Viewer"? I cannot
see "ReportViewer Controls" in my toolbox. Do I have to import them (as
in set a reference to some assembly containing them)?
Or, is it because I do not have the complete Visual Studio 2005
Enterprise Architect Edition. I only have SQL Server 2005 Business
Intelligence Development Studio (which is basically a stripped down
version of VS .NET 2005 with only data-centric project templates).|||This is so wrong on so many levels.
I don't usually say this sort of thing but please until you learn more about
the product DO NOT jump in and answer questions.
Let's start with the most glaring problem. You state that the date would be
text with no validation. In RS 2000 there was not a date picker. In RS 2005
there is a date picker. BUT, even assuming you are on RS 2000 you are still
totally wrong. If you go to the menu Report->Report Parameters you can set
the data type for each parameter. So, even in RS 2000 there is validation
for a date field (or an integer field etc), they cannot put in a non-date if
the field should be a date field.
To say RS is not a web app that the user can enter parameters in easily is
also so totally wrong. Yes, some people prefer to have more control over
placement of controls and other things but RS comes with a totally usable
portal called Report Manager. You can have the parameters be based on a
query (so you that pick from a dropdown list). You can have cascading
parameters where the second parameter list is based on the previous
parameter. You can set defaults for the parameters, etc etc. Each report you
can place a description with it so when the user sees the list of reports
each report has a brief description. In RS 2005 you can have multi-select
parameter lists.
So in short, your answer has set this guy off on a totally wrong tangent. It
could be that the existing portal that ships with RS would fullfill all his
needs.
I know you were trying to be helpfull but you are lacking in knowledge about
the product.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1144686176.448071.239430@.i40g2000cwc.googlegroups.com...
> SSRS has its limitations.
> SSRS relies greatly on how the SQL code is written.
> For example, if you are using date ranges - reporting services is there
> to be the middle man between presentation and data. Nothing more. All
> reporting services will require is 2 parameters(start date and end
> date).
> Meaning SSRS will have to TEXTBOX's where the user will just enter 2
> dates and it will run the report between the 2 dates. In this case
> there is no validation of the paramters but i can all be handleled on
> the DB side. So reporting services doesnt have built in controls where
> the user will enter dates, its only there to GET parameters. These
> parameters are provided in a text box.
> SSRS is not a web application where the user has easy to read UI to
> enter paramters. It does have a VERY nice way of displaying the
> results. Its buily to display a result of a DB call, not to present the
> user with a web form.
> regards,
> Stas K.
>|||Hey Bruce,
Thanks for that RS2000 validation control re-answer. I seem to have
never known it existed - perhaps because I am running all my reports of
a web app.
Same for the second answer.
regards,
Stas K.|||I have noticed other posts of yours after my harsh response here and you
have good answers in other areas. I got a little excitable on my post.
You obviously do know other areas of the product, just not Report Manager.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1144787538.172402.57350@.g10g2000cwb.googlegroups.com...
> Hey Bruce,
> Thanks for that RS2000 validation control re-answer. I seem to have
> never known it existed - perhaps because I am running all my reports of
> a web app.
> Same for the second answer.
> regards,
> Stas K.
>|||Bruce,
I am actually a software developer not a report writer although I do
both.
So I really should go answer questions in other forums. In these forums
I hope to help myself learn and perfect my technique when writing
reports.
Although some questions here come from N-Users... So I am able to think
of solutions.
As for Report Manager, in this case I am the N-user ^_^ - I dont like
how it looks as a matter of fact. I want a whole new look for the whole
thing. I like a sharepoint type of look(if your familiar).
regards,
Stas K.

can u guide me writing a simple query

I am trying to do following thing:

i got a table which contains some words...all i need to do is whenever i see a word starting with 'R', i need to remove the starting 'R' and update it in the same table...

could anyone help me out here....thanks a lot in advance.......

I'm assuming by the title that you want to do it in the sql query. Here is one way to do it:

Select
Case WhenWordlike 'r%' Then right(Word, len(Word)-1) end
From Table1

|||

Can you provide some sample data and what you are trying to update it to so we can get a better idea?

sql

Can this be done using TSQL ?!

Hello
im writing an inventory application for a customer that needs to calculate
item cost by Moving Average method which requires calculating the cost after
each operation, i have a good experience with TSQL but so far i failed to
write the statement that can do this WITHOUT writing cursors
im trying to avoid calculating cost after each transaction to the inventory
. and by writing a stored procedure , get a list showing transactions and
item avg in a period
here is a description of moving avg method , also available here for those
who cant read html
http://www.fms.indiana.edu/auxiliary/inventory.asp
Moving Average--Perpetual
Continuous or moving average assigns a unit value to cost of goods available
for sale. In this scenario, the average cost determines cost of goods sold
at the time of each sale. This method requires a calculation of average unit
cost after each purchase as illustrated below.
# of Units Cost per Unit Total Cost Moving Avg. Cost
Beginning inventory, 7/1 200
$5,000
$25.00
Purchase, 8/10 100
$26.00
2,600
Inv. Balance 300
7,600
25.33
Sale, 9/15 (100)
25.33
(2,533)
Inv. Balance 200
5,067
Purchase, 12/7 600
27.00
16,200
Inv. Balance 800
21,267
26.58
Sale, 12/18 (300)
26.58
(7,975)
Inv. Balance 500
13,292
Sale, 2/22 (250)
26.58
(6,645
Inv. Balance 250
6,647
Purchase, 3/20 300
28.00
8,400
Inv. Balance 550
15,047
27.36
Sale, 5/15 (150)
27.36
(4,104)
Inv. Balance 400
27.36
10,943
Ending Inventory 400
10,943
Cost of Goods Sold 100
2,533
300
7,975
250
6,645
150
4,104
800
$21,257
Regards
Bassamcan you post DDL and some data..and also the example..pasted correctly..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Bassam" <egbas@.yahoo.com> wrote in message
news:ORrEOZODFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Hello
> im writing an inventory application for a customer that needs to calculate
> item cost by Moving Average method which requires calculating the cost
after
> each operation, i have a good experience with TSQL but so far i failed to
> write the statement that can do this WITHOUT writing cursors
> im trying to avoid calculating cost after each transaction to the
inventory
> . and by writing a stored procedure , get a list showing transactions and
> item avg in a period
> here is a description of moving avg method , also available here for those
> who cant read html
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> Moving Average--Perpetual
> Continuous or moving average assigns a unit value to cost of goods
available
> for sale. In this scenario, the average cost determines cost of goods sold
> at the time of each sale. This method requires a calculation of average
unit
> cost after each purchase as illustrated below.
> # of Units Cost per Unit Total Cost Moving Avg. Cost
> Beginning inventory, 7/1 200
> $5,000
> $25.00
> Purchase, 8/10 100
> $26.00
> 2,600
>
> Inv. Balance 300
> 7,600
> 25.33
> Sale, 9/15 (100)
> 25.33
> (2,533)
>
> Inv. Balance 200
> 5,067
>
> Purchase, 12/7 600
> 27.00
> 16,200
>
> Inv. Balance 800
> 21,267
> 26.58
> Sale, 12/18 (300)
> 26.58
> (7,975)
>
> Inv. Balance 500
> 13,292
>
> Sale, 2/22 (250)
> 26.58
> (6,645
>
> Inv. Balance 250
> 6,647
>
> Purchase, 3/20 300
> 28.00
> 8,400
>
> Inv. Balance 550
> 15,047
> 27.36
> Sale, 5/15 (150)
> 27.36
> (4,104)
>
> Inv. Balance 400
> 27.36
> 10,943
>
> Ending Inventory 400
> 10,943
>
> Cost of Goods Sold 100
> 2,533
>
> 300
> 7,975
>
> 250
> 6,645
>
> 150
> 4,104
>
> 800
> $21,257
>
>
> Regards
> Bassam
>|||Please post DDL and some INSERT statements of your sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:

>im writing an inventory application for a customer that needs to calculate
>item cost by Moving Average method which requires calculating the cost afte
r
>each operation, i have a good experience with TSQL but so far i failed to
>write the statement that can do this WITHOUT writing cursors
Hi Bassam,
I think you can get the moving average by a simple self-join with group
by. Check the following example:
-- First, create a table to hold all transactions
-- Opening balance is considered a transaction in this simplified example
CREATE TABLE Operations
(OpDate smalldatetime not null primary key,
Amount int not null, -- >0 purchase <0 sale
UnitPrice money not null)
go
-- Insert all data (same as on web page you mentioned)
INSERT Operations (OpDate, Amount, UnitPrice)
SELECT '20040701', 200, 25
UNION ALL
SELECT '20040810', 100, 26
UNION ALL
SELECT '20040915', -100, 25.33
UNION ALL
SELECT '20041207', 600, 27
UNION ALL
SELECT '20041218', -300, 26.58
UNION ALL
SELECT '20050222', -250, 26.58
UNION ALL
SELECT '20050320', 300, 28
UNION ALL
SELECT '20050515', -150, 27.36
go
-- Here's the statement that will calculate amount, value and moving
-- average after each of the transaction.
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
go
-- Done. Now clean up the mess.
DROP TABLE Operations
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your input but result of your statement will calculate
"Weighted Average" not "Moving Average"
difference is shown in examples in this link
http://www.fms.indiana.edu/auxiliary/inventory.asp
if you open this page and search for weighted average you fill find the
example which works with your statement but the just below example which is
for moving average won't work
i will post DDL and some data here to clear the case
Regards
Bassam
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:
>
calculate
after
> Hi Bassam,
> I think you can get the moving average by a simple self-join with group
> by. Check the following example:
> -- First, create a table to hold all transactions
> -- Opening balance is considered a transaction in this simplified example
> CREATE TABLE Operations
> (OpDate smalldatetime not null primary key,
> Amount int not null, -- >0 purchase <0 sale
> UnitPrice money not null)
> go
> -- Insert all data (same as on web page you mentioned)
> INSERT Operations (OpDate, Amount, UnitPrice)
> SELECT '20040701', 200, 25
> UNION ALL
> SELECT '20040810', 100, 26
> UNION ALL
> SELECT '20040915', -100, 25.33
> UNION ALL
> SELECT '20041207', 600, 27
> UNION ALL
> SELECT '20041218', -300, 26.58
> UNION ALL
> SELECT '20050222', -250, 26.58
> UNION ALL
> SELECT '20050320', 300, 28
> UNION ALL
> SELECT '20050515', -150, 27.36
> go
> -- Here's the statement that will calculate amount, value and moving
> -- average after each of the transaction.
> SELECT a.OpDate AS InvDate,
> SUM(b.Amount) AS Amount,
> SUM(b.Amount * b.UnitPrice) AS Value,
> SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
> FROM Operations AS a
> INNER JOIN Operations AS b
> ON b.OpDate <= a.OpDate
> GROUP BY a.OpDate
> ORDER BY a.OpDate
> go
> -- Done. Now clean up the mess.
> DROP TABLE Operations
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 7 Feb 2005 14:32:20 +0200, Bassam wrote:

>Hello Hugo,
>Thank you for your input but result of your statement will calculate
>"Weighted Average" not "Moving Average"
>difference is shown in examples in this link
>http://www.fms.indiana.edu/auxiliary/inventory.asp
>if you open this page and search for weighted average you fill find the
>example which works with your statement but the just below example which is
>for moving average won't work
Hi Bassam,
I did check that page, and the results of my query were equal to the
moving average quoted on that page (the table directly after the heading
"Moving Average--Perpetual").

>i will post DDL and some data here to clear the case
Excellent idea!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
I tested your statement, great , it works to a great detail !! , i found a
problem in the moving avg in date 2/22/05 , it should be exactly as the one
done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
put 20 sales transactions and see the result
but your statement gave max accurate to date to table.do you know a way to
overcome this small shift ?
thank you and welcome to any comments
Bassam|||On Mon, 7 Feb 2005 15:24:41 +0200, Bassam wrote:

>Hello Hugo,
>I tested your statement, great , it works to a great detail !! , i found a
>problem in the moving avg in date 2/22/05 , it should be exactly as the one
>done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
>26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
>put 20 sales transactions and see the result
Hi Bassam,
I noted the difference as well. This is caused by rounding errors.
Consider the first few rows in the sample data. The beginning inventory
shows 200 units at a total cost of $ 5,000 - exactle $ 25.00 on average.
After the first purchase, there are 300 units on stock and the total cost
is equal to $ 7,600. The average price is $ 25.333333333333333 (etc), but
it is rounded down to $ 25.33. This would mean that if the following sale
would not be for 100 units (as listed in the example), but for 300 units,
the total sale price would be $ 7,599 and the remaining stock would be 0
units, for a total price of $ 1.
The example on the web page graciously avoids this anomaly by only
including a new price after each purchase. It doesn't list the moving avg
cost after a sale, so I could not verify if the values given by my query
are correct or not.
If you need the moving average cost to reflect the situation after the
last purchase instead of after the last sale, try this (slightly more
complicated) query:
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
(Note: if you only need the date and the moving average, not the amount
and value of the inventory, you can remove the group by and the join to
"Operations AS b" - IOW, you can simplify to:
SELECT a.OpDate AS InvDate,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
ORDER BY a.OpDate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Bassam,
Thanks to Hugo for posting the DDL for the table, I will assume it is
correct. Here is a try, but do not compare to the table in the link, there i
s
an error there.
Error in the link:
Sale, 12/18 (300) 26.58 (7,975)
well, -300 * 26.58 should be 7,974.
select
a.OpDate,
sum(b.Amount) as number_of_units,
sum(b.Amount * b.UnitPrice) as total_cost,
(
select
sum(c.Amount * c.UnitPrice) / sum(c.Amount)
from
Operations as c
where
c.OpDate <= (
select
max(d.OpDate)
from
Operations as d
where
sign(d.Amount) >= 0 and d.OpDate <= a.OpDate
)
) as moving_avg_cost
from
Operations as a
inner join
Operations as b
on a.OpDate >= b.OpDate
group by
a.OpDate
order by
a.OpDate
go
AMB
"Bassam" wrote:

> Hello Hugo,
> Thank you for your input but result of your statement will calculate
> "Weighted Average" not "Moving Average"
> difference is shown in examples in this link
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> if you open this page and search for weighted average you fill find the
> example which works with your statement but the just below example which i
s
> for moving average won't work
> i will post DDL and some data here to clear the case
> Regards
> Bassam
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> calculate
> after
>
>|||Hello Hugo,
Thank you, Clear and efficient ! , only last problem , what if a user need
to delete a purchasing happened at beginning of month - or adjust its unit
price value, that means all next averages used in next sales is wrong and
need to be recalculated, is there a way to recalculate unit price for sales
transactions ' i need to adjust that before using your statement again or
result will be wrong
to make situation more complicated is that it might also be more purchasing
down there with sales, i mean suppose user deleted 1 of 5 purchasing done at
beginning of the month , on day 1 , while other purchasing happened on day 5
, 8 , 12 , 14 remains , and there are sales in between. , how then i can
recalculate unit price (which is the moving average) for sales
transactions.in between ?
Thank you
Bassam

Can someone please help with this problem, this is my first time at this forum

Need Help in getting started on a SP in SQL SERVER2000
I need help in writing a stored procedure on SQL Server 2000. Basically the stored procedure's primary task is to generate invoice records and insert the records in a invoice table. In order to generate the invoice records, I have an initial table which are basically Day Records. The task that I would like to complete is to select all records from that initial table and I guess put them into a temp table. Now that i have my temp table, I would like to loop thru the table record by record, and do inserts in the invoice table. I cant seem to figure out the syntax since I am somewhat weak in TSQL Programming. I would appreciate any assistance on this forum or to my email. Also If you need some pseudocode for the process or the DDL for the initial table and the invoice table, I can definitely post that in the forum possibly in the next thread. Please advise, I would need a full written syntax to get me started since i have some other processes that I would need to build using the template. Thanks again.Why do you want to loop through the table instead of running a set-based SQL query against the entire dataset? What you are talking about doing is setting up an SQL Cursor which will step through the data one record at a time, but this is an ineffecient method of handling database data, and is only used when there is no other alternative (very rare) or by VB programmers who don't know TSQL (unfortunately very common).
The task you describe is probably very simple for someone with some knowledge of SQL, but if you are trying to wing-it you are heading into trouble. You need to get some expertise, either in the form of a good SQL Programming turorial or a good SQL consultant.

blindman

Can someone helpl me write this query to create a crosstab(pivot t

Hi. I am trying to write a crosstab or pivot table, but I don't think the
syntax that I'm writing is very efficient. It is using a union statement to
add an overall total, but I think this is the problem that is inefficient.
Does anyone know of a better way to write this query to cut down on time?
I'm using this type syntax on a much larger scale(searching 200k record
creating a pivot table with 100 columns and between 100-200 rows).
drop table testme
Create Table Testme ( ID int PRIMARY KEY, City NVARCHAR(255), Country
NVARCHAR(255) )
Insert Into Testme (ID, City, Country) Values (1, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (2, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (3, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (4, N'Chicago', 'USA')
Insert Into Testme (ID, City, Country) Values (5, N'Chicago', 'USA')
select * from testme
select
[country], [total], [nyc], [chicago]
from
(
select
[country], [myorder] = 0, count([id]) as total, count(case when [city]
like 'nyc' THEN [id] else null END) as [nyc], count(case when [city] like
'Chicago' THEN [id] else null END) as [chicago]
from
testme
where
city in ('nyc', 'Chicago')
group by
country
union
select
[country] = 'Total', [myorder] = 1, count([id]) as total, count(case
when [city] like 'nyc' THEN [id] else null END) as [nyc], count(case when
[city] like 'Chicago' THEN [id] else null END) as [chicago]
from
testme
where
city in ('nyc', 'Chicago')
) as source order by [myorder], [country]Presumably you're going to display your results somewhere, such as in
ReportingServices, or a web page, etc... So why not add them up there instea
d
if you're worried?
Personally, I'm not too keen on the PivotTable concept of SQL. I would
rather return all the data, and then use something at the presentation layer
to turn it into a PivotTable.

Can seem to get delete and exist to work right

Hi all,

I am writing a test result database in SQL 2K5 and one of the features I want to implement is a stored procedure that deletes the oldrecords while preserving a set number of records, the following is what my SP looks like

Procedure [dbo].[CleanResults]

@.RecsToKeep bigInt,

@.Output Int output

as

Declare @.Date Datetime

Declare @.Count bigint

set @.Date = getdate()

Select @.Count = Count(UniqueID) from [Main]

Print 'THE COUNT IS'

print @.Count

if ( @.Count > @.RecsToKeep) begin

set @.Count= @.Count - @.RecsToKeep

Print 'THE Number to delete is'

print @.Count

select TOP(@.Count) UniqueID from Main order By [Main].[TestDateTime] desc

Delete from [Main] where exists (select TOP(@.Count) * from Main order By [Main].[TestDateTime] desc);

set @.Output = @.Count

end

else begin

set @.Output = -1

end

whats odd is that the select staement will evaluate correctly and return the oldest record @.Count record, however the delete stament removes all the records. An advice would be appriciated.

Thanks Christopher

PS Any advice for using TOP with variables in MSDE 2k (as opposed to 2k5) would be appreciated

The WHERE EXISTS is not what you are wanting. Try this instead

Code Snippet

Delete from [Main] where UniqueID IN (select TOP(@.Count) UniqueID from Main order By [Main].[TestDateTime] desc);

This will just grab the first set of uniqueIDs and delete those records, which will be the correct number of records.|||

Thanks it worked

|||

You can try:

;with cte

as

(

select *, row_number() over(order by TestDateTime DESC) as rn

from Main

)

delete cte

where rn <= @.Count;

AMB