How to do this in SQL Server Stored Procedure

Here is my problem:

I am designing Support System. I have a stored procedure for storing new Support Ticket. This stored procedure internally gets next ticket number and inserts Support Ticket

CREATE PROCEDURE [sp_SaveSupportTicket]
(
@.pid int,
@.uidGen int,
@.status VarChar (100),
@.probDes text,
@.probSol text,
@.guestName VarChar (100),
@.os VarChar (100),
@.roomNum VarChar (100)
)
AS
DECLARE @.ticNum int
SELECT @.ticNum = MAX(ticNum) + 1 FROM sup_TicDetails
INSERT INTO sup_TicDetails ( ticNum, pid, uidGen, status, probDes, probSol, guestName, os, roomNum,dateofsub)
VALUES (@.ticNum, @.pid, @.uidGen, @.status, @.probDes, @.probSol, @.guestName, @.os, @.roomNum, CONVERT(VARCHAR,GETDATE(),101))
GO

Now... before this happens, on my ASP.NET Page I have a label Ticket# . This label displays next ticket number

CREATE PROCEDURE [sp_GetNextTicketNumber] AS
SELECT max (ticNum) + 1
FROM sup_TicDetails
GO

Now.. how can I have only 1 stored Procedure so that I can obtain next ticket number and display it on ASP.NET page and when I hit "Submit Ticket" sp_SaveSupportTicket gets executed ??

I hope I have made my problem clear !! If not let me know......What you are doing will fail under load. BEtween the time you get the ticket number and insert, another thread could have inserted that same number. Why not use an IDENTITY column? Is it really essential to have the ticket number before you actually save it?|||I see what you are saying !!! no its not necessary to get ticket number before I save it.. it is just to display purpose.

There are problems with identity column ( I read in couple of aticles) so, I am not using Identity column..

Ok, if I don't display ticket number before saving it on my ASP.NET page.. will it still fail while load ??

How to handle thread part !!!!|||I have not heard of the problems you describe with Identity columns. They are certainly more reliable than what you have done here. Even if you do not display the number first, what you are doing will be prone to failure if multiple users are hitting the stored procedure at once. Trust me, Identity is the answer, and then use Scope_Identity() to get the new ID created right after an insert.|||Read this...

http://www.sqlteam.com/item.asp?ItemID=102

Note that if you delete row/s in a table and start adding row/s, the identity column will pick up where it left off.

If I delete a ticket, or try to rollback, I will in problem with Identity column...|||Here is link on MS Website describing Identity Column problem

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3iex.asp

In our case, I am not sure, but we might have to delete some tickets and than I will be in trouble when I run Reporting Query !!!!!|||After talking to you I me and my friend tried ...

We both saw Ticket# 617 on our ASP.NET display page...

After entering data and hitting "submit", we went into Database to see what happend.

We saw 2 tickets Ticket# 617 created by me and 618 created by him.

So I guess concurrent access didn't gave us problem for insertion.. it displays wrong ticket number on screen ....

I guess I should remove Ticket# from screen than !!!!!!!!!!!

Any other idea ...|||The problem is that you may not be able to duplicate it at will, but the code you are using CAN fail if the two request come in at EXACTLY the same moment.|||Ok.. douglas

I gave you reason why I am not using Identity Column and I told you what happened when we submitted tickets.

I am understanding what you are saying.. .so what is the solution ? What are the chances that both tickets arriave to database at exactly same time ? What happens in that case ?

How can I handle this issue ? Everyone who is developing Web Applications might be running into same issues, how they are handling this or how are you handling such problem ?|||I use Identity columns as meaningless keys to link various rows in a database. It would not bother me if a row was deleted, because I would never rely on these numbers to know how many tickets were outstanding. I would use a SQL Query, not rely upon the ID number of the row. This matches what you would do in a system of paper tickets, because if a ticket is discarded, you would void the ticket and not reuse that number.

How often will folks have a problem with duplicate ticket numbers? Hard to say. You could use some sorts of locking inside of transactions. This is not something I would normally do, and it will serialize this section of code, meaning that only a single user at a time will be able to get to this section of code.|||hummm... Thats interesting !!!! If that is the case how will you set up primary key in your table ?

Here is structure of my table:

ticNum int 4 (primary key) (not auto generated, not identity)
pid int 4 (Property ID Referes to Property Table Primary Key)
uidGen int 4 (UserID Referes to User table Primary Key)
status varchar100
probDes text 16
probSol text 16
guestName varchar100
os varchar100
roomNum varchar100
dateofsub smalldatetime4|||Here is what I found out.. I have total 614 tickets, but when I do SELECT COUNT(*) from sup_TicDetails I see 614. I scanned my records and found that Tic# 100 and 472 are missing.

So I guess my previous developer might be using Identity. I removed Identity column after reding 2 articles i mentioned.

if I try convert primary key into Identity column now, will I be having any problem ?

How can I reset my Identity column every once in a while to fill out gaps or is it Ok to Have Column as identity & primary key and have gap in it ??|||It causes NO PROBLEM AT ALL to have gaps in the numbers.|||So, how you do testing of your code ?Everytime I Add/delete I am going to create gaps and when I transfer that on my production system, I am going to have gap tooo.....

How do I handle database on local system and on production system ?|||This is never something that has been an issue for me...

How to do this in OLAP report?

I am using SQL 2005 Reporting Services. I need to create an OLAP report. The suitation is similiar to a sub-query in Store Procedure. I am not sure how to do this in an OLAP dataset. The OLAP cube has been prepared using SQL 2005 AS. I believe that this report requires two datasets and it is based on the same OLAP cube. The report itself is based on the second dataset. The first dataset require the user to input two parameters. The second dataset uses two of the fields from the first dataset to filter out the second dataset. I can create the two datasets but I dont know how to assign the two parameters on the second dataset which is depends on the result of the first dataset. Any samples or steps or procedures using SQL 2005 RS would be great. Thanks a million.

If I am not mistaken, the first dataset is nothing but a query which contains two fields from the dimension that you will use it as a parameter and the second dataset is a query that should be filtered based on the two parameters.

So, first create the dataset which retrieve the two fields

then, click the layout tab and go to the report parameter dialog box and create the parameters, and make sure that the avialable values comes from the query (to do this, from the Available values column select the "From query" radio button and select the dataset name that you have created earlier.)

Finally, when you are creating the second dataset, filter your query based on those parameters.

I hope this will help.

Sincerely,

--Amde

How to do this in a report?

I have to write a report in SQL that takes the following data structure:
CREATE TABLE [dbo].[Sales_Customer_List] (
[cust_no] [char] (10) NOT NULL ,
[cust_name] [char] (35) NULL ,
[distr_channel] [char] (2) NOT NULL ,
[sold_to_sales_grp] [char] (3) NULL ,
[ship_to_sales_grp] [char] (3) NULL ,
[sold_to_sales_rep_cd] [char] (10) NULL ,
[ship_to_sales_rep_cd] [char] (10) NULL ,
[sold_to_sales_rep] [varchar] (30) NULL ,
[ship_to_sales_rep] [varchar] (30) NULL ,
[csr] [char] (35) NOT NULL ,
[csr_email] [char] (60) NOT NULL ,
[credit_mgr] [char] (35) NOT NULL ,
[sales_region] [char] (20) NULL ,
[BusArea01] [decimal](18, 2) NULL ,
[BusArea02] [decimal](18, 2) NULL ,
[BusArea03] [decimal](18, 2) NULL
) ON [PRIMARY]
--GO
and gives me a listing by customer number, customer name,
sold_to_sales_grp, etc thru the Sales region field.
The data itself can have multiple distr_channel values but the other
fields (excluding the busarea01, 02 and 03 fields) will not change
between customers.
The data would be like:
custno = 1234
custname= Customer1
distr_channel = DS
etc etc etc and the BusArea fields would be
BusArea01 = 0
BusArea02 = 137
BusArea03 = 984
A second record would be:
custno = 1234
custname= Customer1
distr_channel = GM
etc etc etc and the BusArea fields would be
BusArea01 = 855
BusArea02 = 0
BusArea03 = 211
A Third record would be:
custno = 6543
Custname = Customer2
distr_channel = CH
etc etc etc and the BusArea fields would be
BusArea01 = 1250
BusArea02 = 0
BusArea03 = 335
A fourth record would be
Custno = 8998
Custname = Customer3
distr_channel = DL
etc etc etc and the BusArea fields would be
BusArea01 = 25000
BusArea02 = 0
BusArea03 = 550
A fifth record would be
Custno = 8998
Custname = Customer3
distr_channel = WA
etc etc etc and the BusArea fields would be
BusArea01 = 0
BusArea02 = 15000
BusArea03 = 0
The line I need to have on my report would be:
C No C Name BA01 BA02 BA03
1234 Customer1 etc etc etc GM 855 DS 137 DS & GM 211 + 984
6543 Customer2 etc etc etc CH 1250 CH 0 CH 335
8998 Customer3 etc etc etc DL 25000 WA 15000 DL 550
Some customers would have 1 Distr_channel, some 4 or 5.
How would you do this in SQL?
Thanks,
SCBetter if you do this in the client app / reporting tool and not in sql serv
er.
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Blasting Cap" wrote:

> I have to write a report in SQL that takes the following data structure:
> CREATE TABLE [dbo].[Sales_Customer_List] (
> [cust_no] [char] (10) NOT NULL ,
> [cust_name] [char] (35) NULL ,
> [distr_channel] [char] (2) NOT NULL ,
> [sold_to_sales_grp] [char] (3) NULL ,
> [ship_to_sales_grp] [char] (3) NULL ,
> [sold_to_sales_rep_cd] [char] (10) NULL ,
> [ship_to_sales_rep_cd] [char] (10) NULL ,
> [sold_to_sales_rep] [varchar] (30) NULL ,
> [ship_to_sales_rep] [varchar] (30) NULL ,
> [csr] [char] (35) NOT NULL ,
> [csr_email] [char] (60) NOT NULL ,
> [credit_mgr] [char] (35) NOT NULL ,
> [sales_region] [char] (20) NULL ,
> [BusArea01] [decimal](18, 2) NULL ,
> [BusArea02] [decimal](18, 2) NULL ,
> [BusArea03] [decimal](18, 2) NULL
> ) ON [PRIMARY]
> --GO
> and gives me a listing by customer number, customer name,
> sold_to_sales_grp, etc thru the Sales region field.
> The data itself can have multiple distr_channel values but the other
> fields (excluding the busarea01, 02 and 03 fields) will not change
> between customers.
> The data would be like:
> custno = 1234
> custname= Customer1
> distr_channel = DS
> etc etc etc and the BusArea fields would be
> BusArea01 = 0
> BusArea02 = 137
> BusArea03 = 984
> A second record would be:
> custno = 1234
> custname= Customer1
> distr_channel = GM
> etc etc etc and the BusArea fields would be
> BusArea01 = 855
> BusArea02 = 0
> BusArea03 = 211
> A Third record would be:
> custno = 6543
> Custname = Customer2
> distr_channel = CH
> etc etc etc and the BusArea fields would be
> BusArea01 = 1250
> BusArea02 = 0
> BusArea03 = 335
> A fourth record would be
> Custno = 8998
> Custname = Customer3
> distr_channel = DL
> etc etc etc and the BusArea fields would be
> BusArea01 = 25000
> BusArea02 = 0
> BusArea03 = 550
> A fifth record would be
> Custno = 8998
> Custname = Customer3
> distr_channel = WA
> etc etc etc and the BusArea fields would be
> BusArea01 = 0
> BusArea02 = 15000
> BusArea03 = 0
>
> The line I need to have on my report would be:
> C No C Name BA01 BA02 BA03
> 1234 Customer1 etc etc etc GM 855 DS 137 DS & GM 211 + 984
> 6543 Customer2 etc etc etc CH 1250 CH 0 CH 335
> 8998 Customer3 etc etc etc DL 25000 WA 15000 DL 550
> Some customers would have 1 Distr_channel, some 4 or 5.
> How would you do this in SQL?
>
> Thanks,
> SC
>

How to do this conditional(maybe?) query

I'm trying to figure out how do a particular query.

Given that I know how to get the following results from a query (details below):

client1, MT
client2, WA
client3, MT
client3, WA
client3, ID
...

How do I fashion a query that, when a client is listed in more than one state (like client3), the query will only return a single record (instead of three) but print 'MULTIPLE' instead of listing the specific states.

<details>
Here's the set up. I have a table of clients (tblClients), a table of US states (tblStates) and 'linking' table (tblClientState) to id which states a particular client operates in. The reason for the linking table is that a client can operate in one or many different states.

The tables look like this:

tblClients:
client_id, client_name

tblStates:
state_abbrv, state_name

tblClientState:
client_id, state_abbrv

An example query I'm working with is to return a list with the client_name and the state_abbrv.

SELECT client_name, state
FROM tblClients JOIN tblClientState ON tblClients.client_id = tblClientState.client_id

This would return a result like this:

client1, MT
client2, WA
client3, MT
client3, WA
client3, ID
...

Again, here's my question:

How do I fashion a query that, when a client is in more than one state (like client3), the query will only return a single record (instead of three) but have it say 'MULTIPLE' instead of listing the specific states.

It seems like I might be able to use some conditions in my SELECT statement but I can't figure out how to make it all happen.
</details>

Thanks!

Eric LundTry using CASE:

SELECT client_name,
CASE WHEN COUNT(*) = 1 THEN MAX(state) ELSE 'MULTIPLE' END
FROM ...
GROUP BY client_name;|||Wow! Perfect!

That's just the kind of simple, sweet answer I was hoping I would get out of this group!

I was able to take your suggestion and build on it a little bit (because of course, my example was a little over simplified) and I got just EXACTLY what I was looking for.

Thanks a LOT. I really appreciate it!

Eric

How to do this calculation?

I have 3 tables , these table contain field as follow

- Dim_Product

- DecVolume

- Dim_Category

- DecVolume

- Fact_SalesTrx

- DecUnitPrice

If i would like to calculate a Price Per Unit with this formula, how should i do it?

Price Per Unit for specific Unit of Measurement (UOM)= (Unit Price/ UOM for that product) * UOM for that Category

I had try to do in the Calculation tab of the cube, but the result generate is weird (= Infinity).

This is the script that i had try

--

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Price Per Unit]

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume])*[Dim Category].[Dec Volume],

FORMAT_STRING = "Standard",

NON_EMPTY_BEHAVIOR = { [Dec Unit Price] },

VISIBLE = 1 ;

--

Appreciate some one can leave some clue here for me to go on...

Thanks

Woon How

You can do that inside datasourceview as named calculation!

NamedCalculation_PricePerUnit = (UnitPrice/DecVolume)*DecVolume

Try it.

Helped?

regards!

|||

Assuming that the "Value Column" Source and DataType have been correctly configured for the [Dim Product].[Dec Volume] and [Dim Category].[Dec Volume] attributes, you could try .MemberValue, like:

Code Snippet

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume].MemberValue)

* [Dim Category].[Dec Volume].MemberValue,

|||

Thanks for your hlp

Do you mean:

"Value Column" - mean insert some data in? (I'm had put some value for this column)

"Data Type: - mean set the data type ? )(I'm created it in design and set it to decimal)

I try had you approach and result return "#VALUE!". and i highlight the cell, it mention type mismatch.

Any idea?

Thanks In Advance Deepak Puri..

|||

Thanks PedroCGD,

If doing in this way, i would need to create another 2 columns into the Fact_SalesTrx Tables (decVolume Of Product,DecVolume Of Category)

Would that be a redundacy? since i had the columns in the Dim_Product, Dim_Category. And what should be the best way to design this, sorry im new in SSAS, so a lot of stuff im not sure.

|||

It's easier to illustrate this with a sample Adventure Works query - note that the .MemberValue of [Promotion].[Min Quantity] and [Product].[Reorder Point] attributes is only meaningful at the appropriate granularity of those dimensions:

Code Snippet

With

Member [Measures].[WeightedQuantity] as

iif(IsEmpty([Measures].[Order Quantity]) or

[Promotion].[Min Quantity].MemberValue = 0,

Null,

([Measures].[Order Quantity] /

[Product].[Reorder Point].MemberValue)

* [Promotion].[Min Quantity].MemberValue),

FORMAT_STRING = '#,0.0'

select

{[Measures].[WeightedQuantity]} on 0,

Non Empty [Product].[Product].[Product]

* [Promotion].[Promotion].[Promotion] on 1

from [Adventure Works]

How to do this ?

I want to do a win app or web app that has many reports, I do not want to use CR Service, I want to try SQL Server 2005 Reports, please is anyone who can tell me how to do this, or can send me any link telling me anything in this directon ?

I really appreciate your help !

Sincerely

The first place to start is the Microsoft site.

http://www.microsoft.com/sql/technologies/reporting/default.mspx

|||Hi,
Thnx Brad
Can u suggest me any book speaks about creating reports in VS.NET (2003 or 2005) using SQL Report Services (SQL 2000 or 2005) .
I value your help!

Best regards|||For RS 2000 the book "Microsoft Reporting Services in Action" by Teo Lachev is very good.

how to do this "between dates" query?

Hi,

I have a query as follows:

SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @.Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded')
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)

the results look like this

WeekRetailTotal

MonthRetailOrderNo

SalesWeek

SalesYear

￡397.55

8

3

2002

etc etc for each week in a year and then it goes onto the next year.

What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.

I've tried to do a WHERE DATEPART(wk, OrderDate) > @.StartDate AND DATEPART(wk, OrderDate)
< @.EndDate AND YEar(OrderDate) > @.StartYear AND YEAR(OrderDate) < @.EndYear

But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.

I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.

Any ideas?

Thanks

It is common for Date query questions to receive the suggestion of using a Calendar table to assist. In this case it would help as you could turn your Year/Week pairs into dates and then do a simple date range comparison.

Try this approach, using only date functions:

Where DateDiff( wk, DateAdd(yy, @.StartYear - 1900 , '1/1/1900' ), OrderDate ) >= @.StartWeek

And DateDiff(wk, DateAdd(yy, @.EndYear - 1900, '1/1/1900' ), OrderDate ) < @.EndWeek

The first comparison needs to be >= because the first day of the year is in the first week of the year -- the comparison gets off by one. The end should be okay. I used DateAdd to get the first day of each year, you could use another approach like string concatenation.

|||

There is native BETWEEN-AND syntax support in SQL Server. The bigger issue here is you need to consider day of week, i.e. you want the date range to be between the first day of the start year/week and the last day of the end year/week (inclusive on both ends):

Here is my approach (working sample against Northwind database):

DECLARE @.StartWeek INT, @.EndWeek INT, @.StartYear INT, @.EndYear INT

SET @.StartWeek = 50;

SET @.EndWeek = 2;

SET @.StartYear = 1996;

SET @.EndYear = 1997;

DECLARE @.StartDate DATETIME, @.EndDate DATETIME;

SET @.StartDate = DATEADD(week, @.StartWeek-1, '1/1/' + CAST(@.StartYear AS VARCHAR(4))) -- get into the @.StartWeek-th week

SET @.StartDate = DATEADD(day, 1-DATEPART(weekday, @.StartDate), @.StartDate) -- get the First Day (Sunday by default) of that week

SET @.EndDate = DATEADD(week, @.EndWeek-1, '1/1/' + CAST(@.EndYear AS VARCHAR(4))) -- get into the @.EndWeek-th week

SET @.EndDate = DATEADD(day, 7-DATEPART(weekday, @.EndDate), @.EndDate) -- get the Last Day (Saturday by default) of that week

--SELECT @.StartDate, @.EndDate

SELECT orderdate, DATEPART(year, orderdate) AS Year, DATEPART(week, orderdate) AS Week, DATEPART(weekday, orderdate) AS DayOfWeek FROM orders

WHERE OrderDate BETWEEN @.StartDate AND @.EndDate

|||thanks guys! - I'll give it a go!!