Creating Records based on Quant Field - Database Discussion Boards

sarang_k13-Nov-09 5:36

13-Nov-09 5:36

Hi all,

I am using a SQL Server 2008 and created a procedure,but can any one tell me how to use Case in it.

Thanks in advance.

Re: How to use the Case in stored procedure

Abhishek Sur13-Nov-09 5:58

Abhishek Sur

13-Nov-09 5:58

Case is used to implement IF - Else construct in a query. Use like this

Case <expression>
when 0 then <expression>
when 1 then <expression>
ELSE <expression> 
END

Or use
CASE WHEN <expression> = 0 THEN <expression> WHEN <expression> = 1 THEN <expression> ELSE <expression> END

Therefore you can easily incorporate your logic around the select query using CASE structure.

For instance :

Select CASE WHEN AGE > 50 THEN 'AGED' ELSE 'YOUNG' END FROM RECORDS

This will produce AGED if age is greater than 50 otherwise YOUNG.

For detail see this :
http://msdn.microsoft.com/en-us/library/aa258235(SQL.80).aspx[^]

Cheers
Rose | [Rose]

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

Re: How to use the Case in stored procedure

Abhishek Sur13-Nov-09 6:00

Abhishek Sur

13-Nov-09 6:00

If you dont want this in Select query.. inside your stored procedure.. rather you want to place a logic use

If - Else instead. Thumbs Up | :thumbsup:

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

Re: How to use the Case in stored procedure

David Skelly13-Nov-09 6:34

David Skelly

13-Nov-09 6:34

Am I the only person in the world that uses Google when I'm trying to find information? 2 seconds on Google will give you approximately 14 million search results about this including some very good explanations of what CASE is and what it does and how to use it. Just type "sql server case" and away you go.

Re: How to use the Case in stored procedure

Mycroft Holmes14-Nov-09 1:59

Mycroft Holmes

14-Nov-09 1:59

Some people seem to find it easier to type in a question rather than trawl through 14 million results! Laugh | :laugh:

Ah I crack me up. Just because they can find excellent examples in the first 2-3 results is no reson not to be spoon fed. Send me the codz.

Never underestimate the power of human stupidity
RAH

Re: How to use the Case in stored procedure

Andy_L_J14-Nov-09 2:38

Andy_L_J

14-Nov-09 2:38

Mycroft Holmes wrote:
Send me the codz.

When you get them can you forward them to me? My homework is soooo important! Big Grin | :-D

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: How to use the Case in stored procedure

Niladri_Biswas14-Nov-09 17:56

Niladri_Biswas

14-Nov-09 17:56

Hi, don't know what is ur actual doubt because the problem stated

sarang_k wrote:
How to use the Case in stored procedure

is a normal thing.

If you are looking for syntax there is plenty in google with examples too.

However, I have given 2 sample for the same program

Sample 1:

Create procedure TestCaseStatement
as

Begin
	declare @tbl table(id int)
	insert into @tbl 
	select 0 union all select 1 union all
	select 2 union all select 1 union all
	select 1
		
	select 
		id
		,case when id = 1 then 'One' else 'Other Number' end as Value	
	 from @tbl
End

Sample 2:

Create procedure TestCaseStatement
as

Begin
	declare @tbl table(id int)
	insert into @tbl 
	select 0 union all select 1 union all
	select 2 union all select 1 union all
	select 1
		
	select 
		id
		,Value = case when id = 1 then 'One' else 'Other Number' end 
	 from @tbl
End

The output

id Value

0	Other Number
1	One
2	Other Number
1	One
1	One

As you can see that I have shown 2 ways of holding the case result into a pseudo column.

And as Mr. Mycroft stated, if you have any specific doubt, please don't hesitate to share it here. We all would love to help you.

Hope you understand and ur doubt as per the question title has been answered.

Have a nice day
Smile | :)

Niladri Biswas

Creating Records based on Quant Field

ffowler12-Nov-09 7:57

ffowler

12-Nov-09 7:57

Hello All,

Sorry to bother you but I am hitting up against a wall trying to figure something out so I am turning to you for assistance (if possible.).

I have a table of let's say widgets. And for each entry there is a Quantity column. What I am needing to do is to write a Store Procedure for SQL Server that will pull all widgets for a certain customer and create a record in another table for each widget. No problem there. But I need to create a record for each widget based on it's quantity column.

So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces.

What I need to end up with is a new table with 9 entries. 1 for each widget piece.

Again I am sorry to bother you guys, but I am really at a lost.

I thank you in advance for any help you can direct me with.

Fred

Re: Creating Records based on Quant Field

Mycroft Holmes12-Nov-09 11:35

Mycroft Holmes

12-Nov-09 11:35

So break it up into smaller bits.

open management studio and connect to the database - this is where you should be doing your test/creating

select *
From Widgets
Where CustomerID = @CustID

That should get you the list of widgets for the customer
Now you want to count the record for each widget type

select count(*),WidgetTypeID
From Widgets
Where CustomerID = @CustID
GroupBy WidgetTypeID

Now add the insert statement around the select

Insert OtherTable(CustID,WidgetTypeID, Qty)
select CustID, WidgetTypeID, count(*)
From Widgets
Where CustomerID = @CustID
GroupBy CustID, WidgetTypeID

Here endeth the lesson

Never underestimate the power of human stupidity
RAH

Re: Creating Records based on Quant Field

David Skelly12-Nov-09 22:41

David Skelly

12-Nov-09 22:41

According to the OP, this part of it is not a problem. There seems to be some sort of additional requirement which is the sticking point. I'm not entirely sure I understand the problem, but it seems to involve creating more rows in the target table than exist in the source table. 1 customer with 3 widgets should give 9 rows. That's the bit I don't quite get.

Re: Creating Records based on Quant Field

Mycroft Holmes12-Nov-09 23:05

Mycroft Holmes

12-Nov-09 23:05

Seems like we are all struggling with this convoluted logic. I'll wait till the OP comes back with some clarification

Never underestimate the power of human stupidity
RAH

Re: Creating Records based on Quant Field

Niladri_Biswas12-Nov-09 16:16

Niladri_Biswas

12-Nov-09 16:16

Are you looking for this

declare @tblCustomers table(CustID int,CustomerName varchar(50))
declare @tblWidgets table(CustID int,WidgetTypeID int, Qty int)
declare @tblTarget table(CustID int,WidgetTypeID int, Qty int)
insert into @tblCustomers values(101,'Cust1'),(102,'Cust2'),(103,'Cust3')
insert into @tblWidgets values(101,1,5),(101,2,1),(101,3,3),(102,10,5),(103,4,1)

--Program starts

insert into @tblTarget
select CustID,WidgetTypeID,Qty
from
(
	select w.CustID,w.WidgetTypeID,w.Qty from @tblWidgets w
	inner join @tblCustomers c
	on c.CustID = w.CustID
)X where X.CustID =101
select * from @tblTarget

The output being

CustID	WidgetTypeID	Qty
101	1	5
101	2	1
101	3	3

Niladri Biswas

Re: Creating Records based on Quant Field

David Skelly12-Nov-09 22:38

David Skelly

12-Nov-09 22:38

ffowler wrote:
So for example customer A brought 3 widget items: Widget1 5 pieces, Widget2 1 piece, and Widget3 3 pieces.

What I need to end up with is a new table with 9 entries. 1 for each widget piece.

9 entries? How do you get 9 entries from this? I can't quite follow that logic.

Re: Creating Records based on Quant Field

David Skelly13-Nov-09 0:15

David Skelly

13-Nov-09 0:15

Flash of insight. The number of rows to create in the target table depends on the quantity in the source table.

So, customer A buys 3 widget items, Widget1 5 pieces, Widget2 1 piece and Widget3 3 pieces, what you want in the final result table is:

CustId  WidgetID
A       W1
A       W1
A       W1
A       W1
A       W1
A       W2
A       W3
A       W3
A       W3

In other words, 9 rows. Is that right?

Re: Creating Records based on Quant Field

ffowler13-Nov-09 1:50

ffowler

13-Nov-09 1:50

That is correct David. I need a row created based on what is in the Quantity field of the original table. You have outlined it perfectly. Sorry I didn't make it clearer to you others originially.

Now, can it be done, or do I have do something externally?

Re: Creating Records based on Quant Field

David Skelly13-Nov-09 2:31

David Skelly

13-Nov-09 2:31

Others may have a better suggestion, but I would select customer, widget, quantity and then use a cursor to fetch each row in turn. Then, for each row I would have a loop which repeats q times (where q is the quantity value for that row) and do an insert into the target table inside the loop. Does that make sense?

I'm not sure how you are planning to make each of the new rows unique in the target table, but at the simplest you could stick an identity column on there to handle the uniqueness. It seems a slightly odd requirement but I guess there is a reason behind it.

Sorry, no code because I don't use cursors that often, so I'm a bit rusty on the exact syntax. But Google is your friend when it comes to things like that.

Re: Creating Records based on Quant Field

ffowler13-Nov-09 2:50

ffowler

13-Nov-09 2:50

David,
I am required to create a report whereby each item quantity has it's own line (for jotting notes or whatever next to each. I love my Sales Dept!!!)

In any event, I see where you are going with that, but I won't use a cursor. There is a niffty loop that I found that takes the place of using cursors which I have found is alot lighter.

I think that is a plan and I'll give it a go.

Thanks to all of you for your time with my issue, which is certainly not yours. I appreciate it.

Problem with Datepart

Vimalsoft(Pty) Ltd12-Nov-09 3:10

Vimalsoft(Pty) Ltd

12-Nov-09 3:10

Good Day all

i have the Following Query

DECLARE @CurrentTime DATETIME
SET @CurrentTime = CURRENT_TIMESTAMP
select tr.Descr [Room], tb.Purpose [Purpose], tb.Description [Description],
convert(varchar,datepart(hour,tb.starttime))+':'+convert(varchar,datepart(minute,tb.starttime)) [Start Time],
convert(varchar,datepart(hour,tb.endtime))+':'+convert(varchar,datepart(minute,tb.endtime)) [End Time],
tu.name [Requested by] from tbl_booking tb inner join tbl_resource tr
on tb.resources = tr.id
inner join tbl_user tu on tu.id = tb.RequestedByUser
where (day(startdate) = day(@CurrentTime))and(month(startdate)=month(@CurrentTime))and(year(startdate)=year(@CurrentTime))and(tb.status=1)
order by [Room],[Start Time]

and in the [Start Time]and [End Time] it gives me time that is not Complete

it Gives this

14:0

instead of

14:00

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: Problem with Datepart

J4amieC12-Nov-09 3:52

J4amieC

12-Nov-09 3:52

Why in the world would you be formatting your time in SQL? SQL Is for extracting data (and it seems your decord is already of type DateTime) and a client application should be responsible for formatting that data.

As an aside, using spaces in fields or field alias' is generally bad form.

Re: Problem with Datepart

Not Active12-Nov-09 5:36

Not Active

12-Nov-09 5:36

Convert with style 108 will return the format as hh:mm:ss. If you don't want seconds, just strip it off.

I know the language. I've read a book. - _Madmatt

Re: Problem with Datepart

David Skelly12-Nov-09 5:42

David Skelly

12-Nov-09 5:42

Damn, pipped to the post again! Note to self: must type faster.

Re: Problem with Datepart

Not Active12-Nov-09 5:44

Not Active

12-Nov-09 5:44

Maybe you need to upgrade the 14.4 modem to broadband Poke tongue | ;-P

I know the language. I've read a book. - _Madmatt

Re: Problem with Datepart

David Skelly12-Nov-09 5:42

David Skelly

12-Nov-09 5:42

The reason you are seeing this is because the minute part of 14:00 is 0. So, you end up getting 14 for the hour part and 0 for the minute part. Hence a result of 14 Blush | :O

.

I agree with the previous poster, SQL is not the best place to reformat time, but sometimes the requirement is there and you have to do it. You should look into the format options of CONVERT. I think one option for you would be to convert starttime and endtime to strings using format 108 and then trim off the seconds if you don't want them.

http://msdn.microsoft.com/en-us/library/ms187928.aspx[^]

I'm also not convinced by your WHERE clause at the end. That looks to me like a potential performance killer, converting dates to do a selection like that. I may be wrong but I doubt if it will use an index for that so you will end up scanning over every row in the table.

Re: Problem with Datepart

Vimalsoft(Pty) Ltd12-Nov-09 22:52

Vimalsoft(Pty) Ltd

12-Nov-09 22:52

Good Day

in have Resolved it by adding

right('0'+convert(varchar(2),datepart(hour,tb.starttime)),2) + ':' + right('0'+convert(varchar(2),datepart(minute,tb.starttime)),2) AS[Start Time],
right('0'+convert(varchar(2),datepart(hour,tb.endtime)),2) + ':' + right('0'+convert(varchar(2),datepart(minute,tb.endtime)),2) AS  [End Time],

Thanks

Changing a column to IDENTITY

garfield18511-Nov-09 22:34

garfield185

11-Nov-09 22:34

Hi everybody.

SQL Server 2005

I've got a table "Center (ID,Name)", it has some records registered.

I need to change the ID field, and make it IDENTITY = true.
I have been searching, and only found information about how to add a new identity column to an existing table, but I have to turn an existing field on a table to Identity.

I cannot create a new identity field and then delete the old one, because the registered IDs are not sequential...

Any ideas? Thanks a lot.

Time to come clean...

Vive y deja vivir / Live and let live

Javier

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.