BRAINTEASER For All You SQL Guys!

Apr 19, 1999

Hi all!

Steve suggested I post my question here because this is where all the "experts" reside who like a little challenge. I'll simplify my problem down to two example tables with two fields each for the purpose of solving this "hard part". My only requirement for the solution is that it has to be a SQL only solution (no stored procs) as my client can only run Access - yuck...

Here's the deal. There are two tables, PEOPLE and JOBS. They look like this:

PEOPLE Table:

ID NAME
------ ------
1 Adam
2 Bill
3 Carl
4 Dave

JOBS Table:

DESC ASSIGNED_TO
----- -----------
Clean 1
Wash 2,3
Dry 2
Polish 1,3,4

I want to report which people are assigned to which jobs as shown. One line per job with the names concatenated with commas in between:

Clean Adam
Wash Bill,Carl
Dry Bill
Polish Adam,Carl,Dave

All four fields are TEXT fields in this design, but they don't have to be. I have full control over the database, fields, types, etc. If there's a better way then just suggest it. As you can see, the "ASSIGNED_TO" field is a concatenation of the IDs. Goal is just to report the DESC and people's NAMEs.

How about it? Any slick way of doing this? Played around with using SubQueries, IIF, LIKE on ID and ASSIGNED_TO, and came close but never got it all.

I appreciate any tips or suggestions. Thanks a bunch!

John

View 2 Replies


ADVERTISEMENT

Guys Atleast Help Me In This One

May 15, 2008

I have a table with values

id diff
1 5
2 10
5 5
6 15
7 20
8 10
10 5
11 10
12 15

I need a sum of such records whose id are continous.I mean the output should be

id sum
2 15
8 50
12 25

Id 1 & 2 are continous so it is added.But after 2 the next number is not 3 so it wont be in the sum of 1 & 2.From 5 to 8 numbers are continous so those diff are added & so on.

View 12 Replies View Related

Hey Guys (QUESTION)

Jan 10, 2007

My question is can anybody explain Cascade Update and Deletes and plz tell me like i am your student. Thanks in Advance
Danny D

View 3 Replies View Related

Help With Query Guys..

Nov 3, 2007

I have these queries

SELECT sum(cantidad) AS suma FROM Movimiento WHERE ID=[puntp] AND id_sorteo=[sorteo] AND tipo="Entrada";
SELECT sum(cantidad) AS resta FROM Movimiento WHERE ID=[puntp] AND id_sorteo=[sorteo] AND tipo="Salida";


I want to join in one simple query and i want to display the result of suma-resta

Thnk you ! i hope u can help me, I'm going to explode my brain hehe

View 4 Replies View Related

Reeeeeally Easy For You Guys!

Jan 5, 2004

can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance

View 4 Replies View Related

Guys Please Please Help! Saving Cyrillic In My SQL DB HOW????????

Oct 25, 2007

Ok i've tried everything setting table collation to cyrillic setting the whole table language settings to Russian nothing works-i can only save ?????? instead of Russian symvols.(yes datatype is ntext not just text) I can directly input Russian letters from EMS SQL manager, and it will be ok, and i can even read it on my pages. But even if i try to execute SQL command through EMS manager (as opposite to just typing in the data) it will give me ????? again. If I save my ASP.net page in unicode it will give me ?? with some weird ` types symbols-but 70% of symbols would still be ????. I have already spend 45 hours googling and browsing forums but i still cannot solve it! I am so frustrated!
MS SQL Server 2005 Framework 2 ASP.net code looks like Win XP sp2


using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.Odbc;

using System.Data.SqlClient;

public partial class registration : System.Web.UI.Page

{



private const string ConnStr = "server=(local);database=main;Trusted_Connection=yes";

private const string ConnStr = "server=(local);database=main;Trusted_Connection=yes";

private void Page_Load(object sender, System.EventArgs e)

{



SqlConnection myConnection = new SqlConnection("server=(local);database=main;Trusted_Connection=yes;");

///ыуаыпвараеоеаореавп is russian random letters

SqlCommand myCommand = new SqlCommand("UPDATE quest SET Name='ыуаыпвараеоеаореавп' WHERE id=1", myConnection);

myConnection.Open();





SqlDataReader dr = myCommand.ExecuteReader();

myConnection.Close();


if i manually stop the script here and look at my dr variable i will see exactly sql command that i wrote (with no ????) but after execution in my database it is still ??????????????????????????????
Best Regards

View 6 Replies View Related

Storing SMS To My SQL Database..help Guys!!!

May 8, 2007

i use a teltonika t-modem gsm modem..

it's quite new actually and it is connected to my PC thru a usb port..



my problem now is that

i can,t seem to get the hang in storing received messages in

my SQL database..

the code goes like this..

a receive button is clicked to generate an instance

where all SMS sent to my sim card would then be accessed by my gsm modem..



private void btnReceive_Click(object sender, EventArgs e)

{

ListViewItem Component;

Cursor c = Cursor.Current;

Cursor.Current = Cursors.WaitCursor;

listView1.Items.Clear();

btnReceive.Enabled = false;

objGsmIn.Device = cmbDevice.SelectedItem.ToString();

objGsmIn.LogFile = txtLogging.Text;

if (cmbDeviceSpeed.SelectedIndex == 0)

{

objGsmIn.DeviceSpeed = 0;

}

else

{

objGsmIn.DeviceSpeed = System.Int32.Parse(cmbDeviceSpeed.Text);

}

objGsmIn.Storage = fMsgStore.MsgStorage;

if (fMsgStore.DelMsg == true)

{//deletes your message after receiving

objGsmIn.DeleteAfterReceive = 1;

}

objGsmIn.Receive(); //this checks your new incoming messages

if (objGsmIn.LastError == 0)

{

objGsmIn.GetFirstMessage();

while (objGsmIn.LastError == 0)

{

//continously receive all messages..

Component = listView1.Items.Add(objGsmIn.MessageSender);

Component.SubItems.Add(objGsmIn.MessageData);

objGsmIn.GetNextMessage();

}

txtResults.Text = "Messages Successfully Received..";

}

else

{

//error in message receiving process..

txtResults.Text = "Error " + objGsmIn.LastError + "(" + objGsmIn.GetErrorDescription(objGsmIn.LastError) + ")";

}

btnReceive.Enabled = true;

Cursor.Current = c;

}



now my problem is that i cant figure out where i would place my stored procedure

in storing those messages to my database..



the problem is that..

if i place my stored procedure on the 1st "IF" statement..

it will only save my 1st incoming message..



but another thing is that if i put it under my "WHILE" statement..

dont you think that it will continiously loop?

meaning to say that the next tym i tried to access all my received messages

it would definitely store all the previously read messages again to my database..

which then creates multiple copies of my previously stored messages again and again



well i hope you get the gist of what i mean..

its quite complicated to explain..

but then again thanks a lot for taking notice on my post regarding this..

thanks!!^_^



another thing..i used the active xperts c# application in order to

create this application..

View 2 Replies View Related

I Need Desperate Attention........please Guys

Mar 29, 2007

hello ppl.......

've installed sql 2005 xpress with advanced services......also installed the toolkit that came along..

all 'm interested in is to perform certain data mining operations on the adventureworks db..

;ve also installed the advn works db.........

now here is the prob.......

jus like how the sql online book instructed me i first started off with the datamining tutorial..where i was told to first OPEN BIDS select new PROJECT then make sure that the analysis Services template was selected and then go ahead with selection of the particular sln file....

the problem is that I DON'T SEE ANY ANALYSIS SERVICE TEMPLATE....all i c is a REPORT template..

i really need help guys..... 'm new to .net really feeling helpless...

please hurry guys....

thx a ton..

hanish

View 5 Replies View Related

How Do I Insert These Data Into Database Guys???

Jan 3, 2008

 
Hii Folks
This is my Table Order(OrderNo, CartID, TotalAmount, Name, City, Email, Zip, Date), Then I have my code which I need to insert data into database, but OrderNo is automatically inserted
this is my code, but when I run it it shows the error page, if I remove the direction to my error page, it does not show anything and I don't see any error, could any one check for it please
Imports System
Imports System.Data.SqlClientPartial Class Checkout
Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadAmountLabel.Text = Session("OrderTotal").ToString()
SessionLabel.Text = Session.SessionID.ToString()
End SubProtected Sub ContinueButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ContinueButton.Click
Dim shopp As New SqlDataSource()shopp.ConnectionString = ConfigurationManager.ConnectionStrings("SHOESConnectionString").ConnectionString
shopp.InsertCommandType = SqlDataSourceCommandType.Text
shopp.InsertCommand = "INSERT INTO Order(CartID, TotalAmount, Name, City, Email, Zip, Date) VALUES (@CartID, @TotalAmount, @Name, @City, @Email, @Zip, @Date)"shopp.InsertParameters.Add("CartID", SessionLabel.Text)
shopp.InsertParameters.Add("TotalAmount", AmountLabel.Text)shopp.InsertParameters.Add("Name", NameTextBox.Text)
shopp.InsertParameters.Add("City", CityTextBox.Text)shopp.InsertParameters.Add("Email", EmailTextBox.Text)
shopp.InsertParameters.Add("Zip", ZipTextBox.Text)shopp.InsertParameters.Add("Date", DateTime.Now()) Dim rowaffected As Integer = 0
Try
rowaffected = shopp.Insert()Catch ex As Exception Server.Transfer("ErrorPage.aspx")
End Try
shopp = Nothing
If rowaffected <> 1 ThenServer.Transfer("ErrorPage.aspx")
ElseServer.Transfer("success_shopping.aspx")
End IfEnd Sub
End Class

View 6 Replies View Related

Parent Package Configurations...or Not...What Do You Guys Do?

Jan 7, 2008



Finding myself in a bit of a lull at work, I'm trying to create some template packages / solutions, encapsulating some of the best practices that get discussed on here.


I'm thinking about passing configurations from Parent to Child packages, something we all must do on a regular basis. I've always done this a particular way, without thinking too much about it, but I'm not sure if this way is the best. I'd be interested in your opinions.

Scenario:

Parent.dtsx calls Child1.dtsx, Child2.dtsx etc, using Execute Package Task.
Parent has variable Source, and variable Destination, populated from a configuration (my preference is SQL table, but whatever).
These variables are ultimately used to populate the ConnectionString property of the source and destination Connection Managers of the Child packages.


Here's the question: Is it better for the Child packages to look up to their parent, and inherit the values of these variables through Parent Package Configurations, or to look directly to the configuration, and pick them up from there? Or does it not matter?

I tend to assign lots and lots of variables in Parent.dtsx, and pull them down into Child.dtsx with Parent Package Configs, but I'm thinking "is this good/bad/unimportant?"

View 7 Replies View Related

NOLOCK/ROWLOCK (good Or Bad Guys?)

Nov 9, 2007

Reading about avoiding deadlocks in SQL, I have found different opnions about whether using or not NOLOCK/ROWLOCK hints on SELECT/UPDATE/DELETE statements. I have several applications executing transactions on the same databases and tables, including inserts, updates and deletes. I wonder if by using NOLOCK/ROWLOCK I could decrease the chances of having deadlocks. At least using ROWLOCK on my update statements?. I just need some advice here.

Regards.

View 2 Replies View Related

Can Xp_sendmail Be Made Into A Stored Procedure (was Hi Guys)

Jul 21, 2004

I have another question guys I need to send reports to different departments using sql. would this be the xp_sendmail function in sql. Can this be made into a stored procedure? What I'm going to do is make some reports which will constantly be updated with a query. These reports need to be emailed to differnt departments every 2 weeks. Is there a trigger or stored procedure I can setup through sql to do this? Does that make any sense??

View 8 Replies View Related

Minimum Date In DateStamp Field Is Not 1-Jan-100 (Silly By SQLServer Guys)

Sep 14, 2004

Minimum Date in DateStamp field is not 1-Jan-100. Wouldn't we expect that.

In SQLServer2000 is it "1-Jan-100", if not WHY ??,

In the previous versions it is "01-Jan-1753"

bikramjeet

View 3 Replies View Related

Sql Server Express - Shared Hosting - Connection String - What Do You Guys Do?

Feb 2, 2007

Hey,

I have a asp.net 2.0 app that has a sql server express backend. I have been scratching my head for weeks trying to find a good wasy to encrypt my connection string. I store the string in web.config (which in itself is in theory "safe" because it's not servable) and I have scoured the web to try to find a simple way to encrypt the connection string. Even using user store would require running aspreg_iis.exe on the host with a command prompt (which clearly I cannot do). It seems that everyone's solution requires running a command prompt executable on the server.

So I am left to wonder what everyone else does. I am not comfortable using just the web.config because a determined hacker could get to that info. Then again, a determined hacker could get into any system , I guess. There are no credit card #'s stored in this app, but in theory there must be a way. I run all access to the db through stored procedures with permissions, for whatever it's worth.

Thanks in advance!
David

View 3 Replies View Related

Why Don't You Guys Provide A Way To Find Out The Dependancies Of Any Object (SProc, Table, View And Etc...) Within SQL Server...

Nov 15, 2007



Why don't you folks (SQL SERVER Management Studio Team or SQL SERVER Database Engine Team) provide an option by using which I would be in a position to know what all the other objects are depended on a selected object and vice versa. That means:



When I select a Table within the SQL SERVER Management Studio if there is an option for understanding "Which all the objects (tables or views) it is depended on (I mean Foreign key and etc... kind of relations) and also Which all the objects (Tables, Views, Stored Procedures and etc...) are using this table", It would be of great help for all developers.



I can be contacted over kotis@microsoft.com

I BLOG @ http://blogs.msdn.com/kreddy

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved