|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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?
|
|
|
|
|
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?
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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/
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Damn, pipped to the post again! Note to self: must type faster.
|
|
|
|
|
Maybe you need to upgrade the 14.4 modem to broadband 
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
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 .
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.
|
|
|
|
|
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
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/
|
|
|
|
|
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
|
|
|
|
|
Follow the steps
1)Go to the design view of your table
2)Select ID Column
3) In the down, you will find Column Properties
4)In that you will find Identity Specification whose first property is Identity Specification. The default will be set to "No".
Change that to yes.
5)Save the table.
Hope this helps
Niladri Biswas
|
|
|
|
|
Thanks Niladri, but I meant how to do it in SQL language...
|
|
|
|
|
Follow the steps
1)Go to the design view of your table
2)Select ID Column
3) In the down, you will find Column Properties
4)In that you will find Identity Specification whose first property is Identity Specification. The default will be set to "No".
Change that to yes.
5)Click on "save change script"
6) Save the script to a file or copy it to the clipboard.
Wout Louwers
|
|
|
|
|
|
high guys
i have a problem
when i press F5 on visual studio to start debugging of my project, it runs good no errors
but
when i press ctrl+F5 on visual studio to start without debugging of my project,it gets error message
the error message is:
Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immedediately.
the type initializer for 'System.Data.SqlClient.SqlConnection'threw an exception.
Details..
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.PlatformNotSupportedException: This operation is only supported on Windows 2000 SP3 or later operating systems.
at System.Security.Principal.SecurityIdentifier..ctor(WellKnownSidType sidType, SecurityIdentifier domainSid)
at System.Diagnostics.SharedUtils.EnterMutexWithoutGlobal(String mutexName, Mutex& mutex)
at System.Diagnostics.SharedPerformanceCounter.GetCounter(String counterName, String instanceName, Boolean enableReuse, PerformanceCounterInstanceLifetime lifetime)
at System.Diagnostics.SharedPerformanceCounter..ctor(String catName, String counterName, String instanceName, PerformanceCounterInstanceLifetime lifetime)
at System.Diagnostics.PerformanceCounter.Initialize()
at System.Diagnostics.PerformanceCounter.set_RawValue(Int64 value)
at System.Data.ProviderBase.DbConnectionPoolCounters.Counter..ctor(String categoryName, String instanceName, String counterName, PerformanceCounterType counterType)
at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp)
at System.Data.SqlClient.SqlPerformanceCounters..ctor()
at System.Data.SqlClient.SqlPerformanceCounters..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnectionFactory..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection..cctor()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection..ctor()
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at Computer_Sales_System.Form2.button1_Click(Object sender, EventArgs e) in D:\My first project\Computer Sales System\Computer Sales System\Form2.cs:line 178
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
Computer Sales System
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///D:/My%20first%20project/Computer%20Sales%20System/Computer%20Sales%20System/bin/Debug/Computer%20Sales%20System.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
PresentationCore
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_32/PresentationCore/3.0.0.0__31bf3856ad364e35/PresentationCore.dll
----------------------------------------
WindowsBase
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/WindowsBase/3.0.0.0__31bf3856ad364e35/WindowsBase.dll
----------------------------------------
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
WindowsFormsIntegration
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/WindowsFormsIntegration/3.0.0.0__31bf3856ad364e35/WindowsFormsIntegration.dll
----------------------------------------
PresentationFramework
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/PresentationFramework/3.0.0.0__31bf3856ad364e35/PresentationFramework.dll
----------------------------------------
PresentationFramework.Aero
Assembly Version: 3.0.0.0
Win32 Version: 3.0.6920.1500 built by: QFE
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/PresentationFramework.Aero/3.0.0.0__31bf3856ad364e35/PresentationFramework.Aero.dll
----------------------------------------
System.Management
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll
----------------------------------------
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
For example:
<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
the code is:
SqlConnection cn = new SqlConnection(@"Data Source='MOHAMEDEWEHISHY\SQLEXPRESS';Initial Catalog='Computer Sales System';integrated security=sspi");
try
{
SqlCommand cmd = new SqlCommand("backup_database", cn);
cmd.CommandType = CommandType.StoredProcedure;
if (System.IO.File.Exists(@"D:\SQLServerBackups\Computer Sales System.bak"))
{
System.IO.File.Delete(@"D:\SQLServerBackups\Computer Sales System.bak");
}
cn.Open();
int i = cmd.ExecuteNonQuery();
if (i < 0)
{
MessageBox.Show("Your database is backed up successfully");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("Sorry ... You can not back up your database");
}
finally
{
cn.Close();
}
so the problem is using SqlConnection with others not DML commands
i tried to use OLEDBConnection but it not gets result
So what is the solution????????
Thanks,
Mohamed El-Wehishy
|
|
|
|
|
You can learn more about SQL Server 2008 R2 in free developer training videos on MSDEV[^]. This on demand series will walk through the new features of SQL Server 2008 R2. Topics included: Report Builder 3.0, building your ISV ETL application, ISV Operational application, looking at what R2 edition is right for you and more.
See SQL Server 2008 R2 Developer Training Series[^]
|
|
|
|
|
Hi all,
Can any one advice on Data Transfer Between Same Databases On Seperate Servers ?
Database A on Server 197.23.xxx
Database B on server 197.23.xxxx
The run on the same network.
Any Information will be much appreciated.
Many thanks
modified on Wednesday, November 11, 2009 1:34 PM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
