Denormalizing Data Query
Jan 24, 2008
Please help, I've used all of my capacities...
I need to denormalize some data...
I'll give an example.
Source table: (bold €“ PK)
ID_Company Data (yyyy-mm-dd) Type Quantity
FORD 2000-01-01 A 100
FORD 2000-06-01 A 200
FORD 2000-07-01 B 150
FORD 2002-01-01 A 400
FIAT 1999-01-01 A 300
FIAT 2000-06-01 B 700
FIAT 2001-07-01 B 750
FIAT 2002-01-01 C 250
Needed result set:
ID_Company Year A B C
FORD 2000 200 150 0
FORD 2002 400 150 0
FIAT 1999 300 0 0
FIAT 2000 300 700 0
FIAT 2001 300 750 0
FIAT 2002 300 750 250
A business rules:
· if there are more records for the same year and the same type, I extract the later date.
If more explanations are needed, please ask€¦I appreciate every help I can get with this query.
View 9 Replies
ADVERTISEMENT
Jul 10, 2006
Hello,I am currently working with a data mart. The business wants a view ofthe data that shows columns from the main table along with up to 6codes from a related table (not related by the full PK of the maintable though). They don't care about the order of the codes or how theyare assigned to their columns. Here is an example, which will hopefullymake this clearer:CREATE TABLE dbo.Main_Table (my_id INT NOT NULL,line_number SMALLINT NOT NULL,some_other_columns VARCHAR(20) NULL )GOALTER TABLE dbo.Main_TableADD CONSTRAINT PK_Main_Table PRIMARY KEY CLUSTERED (my_id, line_number)GOCREATE TABLE dbo.Related_Table (my_id INT NOT NULL,my_code CHAR(6) NOT NULL )GOALTER TABLE dbo.Related_TableADD CONSTRAINT PK_Related_Table PRIMARY KEY CLUSTERED (my_id, my_code)GO-- The two tables above are related by my_idINSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 1)INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 2)INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 3)INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (2, 1)INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (2, 2)INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '22.63')INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '73.09')INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '51.23')INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (2, '26.42')GOThe results that they would want to see are:my_id line_number my_code_1 my_code_2 my_code_3 my_code_41 1 22.63 73.09 51.23 NULL1 2 22.63 73.09 51.23 NULL1 3 22.63 73.09 51.23 NULL2 1 26.42 NULL NULL NULL2 2 26.42 NULL NULL NULLI'm pretty sure that I will need to load a table with the data in thisformat (or something similar) since generating this output on the flyfor the users will not work performance-wise (the main table is apartitioned table with 6 partitions, each containting 35M+ rows, whilethe related table is a single table that contains about 2M rows. Thereis additional logic that has to be done with a similar table of 90Mrows. So, I will try to load a table with this denormalized view of theworld. I could probably add some sort of sequence number toRelated_Table if you think that might help (then it just becomes asimple series of left outer joins).Any thoughts?Thanks!-Tom.
View 8 Replies
View Related
Jul 20, 2005
I have a database with over 450 tables, that until a short while agohas been strictly 3NF. In cases where complicated permutations ofdata are required quickly for certain parts of the system, sometimesit is not possible to write a query fast enough, due to the complexityof deriving many nested levels of data.Therefore for the few instances where this has been needed, I havecreated carefully named summary tables, having one-to-onerelationships to real tables (same primary key), and containing onlythe values which must be computed.This works fine. The question now is when to compute them.Much as I strongly dislike triggers, I have created triggers for thetables in question, which themselves recalculate the summary values,and populate the summary tables.Good news, loading is much faster.Bad news, now that I'm taking the hit on the save, the saves are fartoo slow.The only way I can see that my saves could be sped up, is to perhapsspread the work a bit, such that maybe if I am adding fifty records toa table, that the trigger is somehow disabled before the rows areadded, and re-enabled afterwards, to calculate the lot as a set-basedoperation (rather than calling the trigger 50 times), but I don't seea way to do that.Any help of any nature on this matter would be greatly appreciatedCheersBrian McGee
View 10 Replies
View Related
Sep 2, 2007
I have the following:
Demograph Table (PK MemberID)
-MemberID
-FirstName
-LastName
InsuranceSegments Table (PK = all columns combined)
-MemberID
-ProgramCode
-StatusCode
-BeginDate
-EndDate
I need to get the data into this table (i'll call the table MyTest)
-MemberID
-FirstName
-LastName
-BeginDate
-EndDate
-ProgramCode
-StatusCode
The catch:
For each record in the demograph table I need only one segment and that segment is to be based on the greatest EndDate.
What I need:
Give me the last segment for each demograph record and put each into it table MyTest
In my pseodo thinking
INSERT INTO MyTest (MemberID, FirstName, LastName, BeginDate, EndDate, ProgramCode, StatusCode)
SELECT
Demograph.MemberID, Demograph.FirstName, Demograph.LastName,
InsuranceSegments.BeginDate, InsuranceSegments.EndDate, InsuranceSegments.ProgramCode, InsuranceSegments.StatusCode
FROM
Demograph
INNER JOIN
InsuranceSegments
ON
Demograph.MemberID = InsuranceSegments.MemberID
WHERE { Need help :-) }
Need help with the where clause to get the segment with the 'highest/latest' EndDate
Sorry for the wall of text. I hope this is enough information to receive help and would like to thank you in advance for your time :-)
J
View 7 Replies
View Related
Apr 19, 2007
I have two tables related many-to-many so there's an intersection table in the middle. The first table is dbo.Event. It has a list of gigs (events) my company has booked to provide services for. The second table is dbo.Employee which has a list of employees. The intersection table dbo.EventEmployee contains which employee(s) have been scheduled to work. The intersection table has some status information whether the employee asked to work, is scheduled to work, etc. The data type is TINYINT
Here's what I want to do. Create a SELECT statement that basically denormalizes these three tables so the recordset looks like this:
Event.Name, Event.StartDate, Event.Location, Ed, Mary, Tom, Steve, Sally
The idea is to get the list of employees to become columns in the result set. If an employee is scheduled for the event there is a non-null value (Status), if an employee isn't scheduled there is a NULL in their column. For example:
CDC, 2/12/07, Chicago, NULL, 0, 0, NULL, NULL
NYC, 11/04/07, New York, 0, 0, 0, 0, NULL
The first record Mary & Tom are scheduled to work the CDC. The second row everyone except Sally is scheduled to work.
I've thought of cursor based solutions but performance will be rather poor. There are multiple places I want to use this type of solution so I'm hoping there's a more elegant way to accomplish this. SQL 2000 with latest SP. Help. This is beyond my SQL abilities.
harrier
View 9 Replies
View Related
Jun 15, 2007
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
View 1 Replies
View Related
Jul 19, 2015
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
  at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
  at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
View 3 Replies
View Related
Sep 3, 2007
Hi ,
I am having 2 data store .
1. Oracle 10g
2 SQL server 2000
My requirement is that , i need to insert some data from sql server database table to oracle database using sql server query analyser or interface.
If there is any way ,plz let me know it
Thanks
Abraham
View 3 Replies
View Related
Sep 28, 2006
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
View 4 Replies
View Related
Jun 8, 2015
I have  a column in which Email data is available likeÂ
clicuanan@aspenms.com(M)
jteply@mac.com(M)
How to extract in the below format
clicuanan@aspenms.com
jteply@mac.com
tjones@jpmc.com
View 4 Replies
View Related
Nov 25, 2015
I have a view that give me the data of all the batched. Now I am using a query on view to get a single batched data. when I am using direct query it was taking 0 sec but when I am using Through view "select * from myView where batched=2" then its taking 30 mnt.
View 3 Replies
View Related
Jul 6, 2015
getting result as given below.
Input:
id Name Data
101 AA PQ102BBRAJAKIRANBUBLU
102 BB RS101AAEROJUCHALABAGUNDI
103 CC TU104GGANDICANKILLANYONE
OUTPUT:
id Name Data
101 AA 101AAEROJUCHALABAGUNDI
102 BB PQ102BBRAJAKIRANBUBLU
103 CC
View 4 Replies
View Related
Apr 4, 2007
i someone had teach me how to write a query in datatable. however i need to get the data out from my database rather than the data table. can someone teach me how should i do it?esp at the first like.... like DataTable dt = GetFilledTable() since i already have set of data in my preset table i should be getting data from SqlDataSource1 right ( however i am writing this in my background code or within <script></script> so can anyone help me? protected void lnkRadius_Click(object sender, EventArgs e) { DataTable dt = GetFilledTable(); double radius = Convert.ToDouble(txtRadius.Text); decimal checkX = (decimal)dt.Rows[0]["Latitude"]; decimal checkY = (decimal)dt.Rows[0]["Longitude"]; // expect dt[0] to pass - as this is our check point // We use for rather than fopreach because the later does not allow DELETE during loop execution for(int index=0; index < dt.Rows.Count; index++) { DataRow dr = dt.Rows[index]; decimal testX = (decimal)dr["Latitude"]; decimal testY = (decimal)dr["Longitude"]; double testXzeroed = Convert.ToDouble(testX -= checkX); double testYzeroed = Convert.ToDouble(testY -= checkY); double distance = Math.Sqrt((testXzeroed * testXzeroed) + (testYzeroed * testYzeroed)); // mark for delete (not allowed in a foreach - so we use "for") if (distance > radius) dr.Delete(); } // accept deletes dt.AcceptChanges(); GridView1.DataSource = dt.DefaultView; GridView1.DataBind(); }
View 2 Replies
View Related
Apr 21, 2008
Hello,
I am very new to SQL and just getting to learn this stuff. To make this question easier I will scale down the fields dramatically.
I have about 8000 records close to 2000 records for the last 4 years
and I would like to create a query that will create a table on my SQL server. I need to bind the data based on two items the Year and the Name and average several records. However, one record needs it's own calculation.
Here are my field names:
[year] ***4 choices 2007, 2006, 2005, 2004***
[name]
[rush_no] ***integer***
[rush_net] ***integer***
[YPC] *** This field needs to be calculated by [rush_net] divided by [rush_no]***decimal***
I also need to create the same table that will "total/sum" the same records.
View 7 Replies
View Related
Jul 8, 2013
I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
So I have 2 tables for products in the system
products
+-----------+------------+
|productid |productname |
|Int |varchar(50) |
+-----------+------------+
productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int |Int |Text |Money |Int |
| |FK_From_ | | | |
| |productid_| | | |
| |products | | | |
+---------+----------+------------+------+------+
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock]
AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock
FROM tab_productdetails INNER JOIN
tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
View 1 Replies
View Related
Jul 22, 2014
I have created a table in a database for a football that I want to update as the season progresses. Is there any way I can add the new data to the old data in my columns through SQL rather than searching for the old data, doing the maths in my head and doing a simple update query? I know it doesn't save much extra time but I can get awful lazy when it comes to extra maths!
Example would be:
Player Minutes
Adam 287
Adam has played 287 minutes prior to the most recent match in which he played 67 more. I would like to know if its possible to do an update where I can just simply add 67 onto the existing numbers with one query rather than find Adam's minutes, use a calculator, do a table update.
View 4 Replies
View Related
Jan 4, 2007
I am creating an application which uses logic similar to J.D.Edwards (for those of you are familiar with its wildcarding data structure).
Basically, a customer purchases a particular house, with a particular elevation, in a particular community. This data is stored in a Customer table. From the customer’s selection (community, plan, elevation) criteria, I need to create a unique list of options from data stored in an optionmaster table.
A customer’s data looks similar to this in the customer table:
CustomerID
CommunityID
Community Name
PlanID
Plan Name
ElevationID
ElevationName
1234567
7
Hickory Hills
25
Allen
3
C
The Optionmaster table is structured like this (there are actually about 1000 records):
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
0
+
0
+
0
+
100
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optional Dormer
0
+
25
Allen
2
B
50
9125
Optional Tub
8
Smithville
0
+
0
+
800
9125
Optional Kitchen
0
+
0
Lori
0
+
2500
First. Based on the customer’s table I need to first select all options = to their community, as well as those available to communities everywhere (+). In some cases the same option is maintained for both at both levels. In that case the community specific option must be selected.
To do this I think I need an if else statement to select those records:
In this case, if CommID =7, select record, else select 0. This eliminates the other Smithville community and pulls the correct option # 1234.
The result would look like this:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
9125
Optinal Kitchen
0
+
0
Lori
0
+
2500
Second, from this dataset, I need to select all options equal to the plan ID, and those which apply to all plans (+). So, the Lori Plan data goes away.
Something like, If the plan ID = 25, select record, else 0. the result would be:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
Third and finally, based on that dataset, I would have to select any elevation specific options for that plan or options for all elevations of that plan (+).
Something like this: If the Elevation ID = 2, select record, else. 0. Note the elevation B data goes away.
The final result would be for this customer is:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
Any assistance anyone can provide in coming up with a sql statement to do this would be appreciated.
View 5 Replies
View Related
Oct 21, 2007
Hi! I have a sql query in stored procedure: SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname
Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.
View 4 Replies
View Related
Nov 14, 2007
I need to combine the data into ONE Row. Any Ideas ?
here is what the Data looks like now
FirstQuery
WorkOrder
CID
Address1
Address2
Open24
NicePeople
NicePlace
33333
6666
Yes
33333
6666
Green Street
67666
7777
No
67666
7777
No
67666
7777
Mad Ave
I need it to look like this
FirstQuery
WorkOrder
CID
Address1
Address2
Open24
NicePeople
NicePlace
33333
6666
Green Street
Yes
67666
7777
Mad Ave
No
No
Here is my Query
SELECT RouteCustomer.WorkOrder, RouteCustomer.CID, IIf([ServiceHistorydetails].[FieldName]="Address1",[ServiceHistoryDetails].[ToValue],"") AS Address1, IIf([ServiceHistorydetails].[FieldName]="Address2",[ServiceHistoryDetails].[ToValue],"") AS Address2, IIf([ServiceHistorydetails].[FieldName]="Open24",[ServiceHistoryDetails].[ToValue],"") AS Open24, IIf([ServiceHistorydetails].[FieldName]="NicePeople",[ServiceHistoryDetails].[ToValue],"") AS NicePeople, IIf([ServiceHistorydetails].[FieldName]="NicePlace",[ServiceHistoryDetails].[ToValue],"") AS NicePlaceFROM RouteCustomer RIGHT JOIN ServiceHistoryDetails ON RouteCustomer.WorkOrder = ServiceHistoryDetails.WorkOrderGROUP BY RouteCustomer.WorkOrder, RouteCustomer.CID, IIf([ServiceHistorydetails].[FieldName]="Address1",[ServiceHistoryDetails].[ToValue],""), IIf([ServiceHistorydetails].[FieldName]="Address2",[ServiceHistoryDetails].[ToValue],""), IIf([ServiceHistorydetails].[FieldName]="Open24",[ServiceHistoryDetails].[ToValue],""), IIf([ServiceHistorydetails].[FieldName]="NicePeople",[ServiceHistoryDetails].[ToValue],""), IIf([ServiceHistorydetails].[FieldName]="NicePlace",[ServiceHistoryDetails].[ToValue],"");
If anyone has any ideas how I can Parse this data correctly please help me out.
Thank you
View 2 Replies
View Related
Dec 18, 2007
Dear All,
i need your help,
i had created a table student, studentid column with alpha numeric primary key with varchar datatype
now my problem: i want to sort the student id accroding to studentid like
STU1
STU2
STU3
STU4
STU5
.
.
.
STU9
STU10
STU11
but i’m getting the sorted result like this, how to overcome this problem,guide me PLEASE
STU1
STU10
STU11
STU12
.
.
.
STU100
.
.
.
STU1000
STU10000
STU2
STU20
STU200
STU2000
STU20000
STU20001
Thank's In Advances
View 9 Replies
View Related
Apr 7, 2008
Hi All. How do i loop over to extract data out of a xml parameter in order to insert that into a table along with other input. For example if the xml is <Id>1</Id> <Id>2</Id> <Id>3</Id> I want to input values into table (1,data2,data3) and (2,data2,data3) and (3,data2,data3). How do i do that?
View 6 Replies
View Related
Apr 29, 2008
Select A.SNO , A.Name , B.Picfrom Student A Left Join Picture BWhere A.SNo = B.PNoNo SNo Name Pic1 000 Andy /Doc/andy.jpg2 001 Andy /Doc/andy2.jpg3 002 VIVI /Doc/vivi.jpg4 003 VIVI /Doc/Vivi2.jpg The same person will only show 1 pic Pathlike this resultNo SNo Name Pic1 000 Andy /Doc/andy.jpg3 002 VIVI /Doc/vivi.jpgcan you please help? I trid use Group By but it not works..thank you
View 10 Replies
View Related
May 7, 2008
I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT
SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE)
SELECT NAME,
POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$,
LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLE INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME
WHERE POST_PD = @LASTPD
AND TABLE2.NUM= 539
GROUP BY NAME) AS LASTMONTH
INTO #TEMP_SAFROM TABLE
INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLEWHERE TABLE2.NUM = 539
GROUP BY NAME, POST_PDORDER BY NAME, POST_PD
SELECT NAME,
LAST_MONTH,
CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$
FROM #TEMP_SA
GROUP BY NAME
ORDER BY NAME
View 13 Replies
View Related
May 17, 2005
Hi.,
Can anyone please assist me in getting the following SQL Query to work. It returns no errors but also returns no records even though there is actually data in my database for that period.
Dim DCriteria as StringDCriteria = "StartDate <= " & EDateDCriteria = DCriteria & " AND EndDate >= " & SDatestrSQL = "SELECT * FROM vwLeavePlan WHERE " & DCriteria
This results in the follwoing SQL Statement:
strSQL = SELECT * FROM vwLeavePlan WHERE StartDate <= 31/05/2005 AND EndDate >= 01/05/2005
Regards.
Peter
View 14 Replies
View Related
Aug 11, 2005
Hi:
I have three tables in my db. One is clients, other calls, and other visits.
I want to get all the calls and visits of all clients in my db every row in each table separate in one row in the results table.
How can I do it?
View 3 Replies
View Related
Mar 13, 2006
I'm writting a stored proc that has to query 2 tables. One table is a table of "jobs" and the other table contains jobs that have been invoiced (2 tables are jobs and invoicedJobs). The invoiced table only contains records for jobs that have an invoice and not jobs that do not have an invoice.
My dilemma is that I need to write a query that can retrieve all un-invoiced jobs in my stored proc. You can't rightly join a table that does not have a relationship with another table (can you?). So in my query for jobs with an invoice, I simply join my jobs table and invoice table based on a job id that both tables contain. But how could I perform a query for jobs that do not exist in my invoice table inside my stored proc? Any help would be greatly appreciated.
View 2 Replies
View Related
May 1, 2006
I want to query XML datas.
I have one table T.this T has two columns,id int , datasXML xml.
the datasXML has some XML datas, there into one data is:
<root> <item val="true"></item> <others ... /></root>
Now,I want to query this table's datasXML column,and root/item value is true.
How to design this SQL string?
SELECT * FROM T WHERE datasXML = "<root>.." -- or other?
Please give me some samples.thanks!
Ray Lynn
View 2 Replies
View Related
Sep 22, 2000
Hi,
Can anybody help me with this? I have tried this and got confused big time.
Can you point me in the right direction please?
TIA.
Barath
View 2 Replies
View Related
Aug 25, 2000
hi,
I have a question. how do query data between the two servers.
thanks
kumar
View 2 Replies
View Related
Jan 12, 2001
Is there any way in SQL 7/2000 to export a query result to a csv or xls via t-sql?
Thanks a lot for your help,
Yelena
View 2 Replies
View Related
Apr 19, 2000
Hello;
I need to create a data driven query task where the destination is a view. The option to select a view is only available for the source.
I have created a view because the first attempts to create a data driven query againt the required table (db is over 4000 tables, 4 GB of data)resulted in workstation lockups.
Thank you.
Barb
View 1 Replies
View Related
Sep 30, 2006
I'm quite new to using t-sql, so hopefully the answer to this should be fairly simple... I have the following basic stored procedure:
Quote: CREATE PROCEDURE dbo.SP_Check_Login (
@arg_UserEmail VARCHAR(255),
@arg_UserPassword VARCHAR(255))
AS
BEGIN
SELECT a.userArchived,a.userPasswordDate
FROM app_users a
WHERE a.userEmail = @arg_UserEmail
AND a.userPassword = @arg_UserPassword
END;
GO
What I want to do is some conditional statements on the query results.
i.e:
IF (userArchived = 1)
RETURN "Archived"
ELSE IF (userPasswordDate < dateadd(month,-3,getdate())
RETURN "UpdatePassword"
ELSE
RETURN "OK"
So firstly how to I reference the data returned by the query, and secondly am I on the right track with the conditional code?
Thanks, Mike
View 2 Replies
View Related
Mar 30, 2006
I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.
In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.
I'm using the Bush family in this example, these are the relationships:
- George SR
--- George JR
------ Jenna
------ Barbara
--- Jeb
------ Jeb JR
------ Noelle
These statements will create and populate the tables for the example with the above relationships:
SET NOCOUNT ON
DROP TABLE KK_Kids, DD_Dads, GG_Grandpas
CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20))
CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20))
CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))
ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Dad_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT PK_KK PRIMARY KEY (KK_Kid_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT FK_KK FOREIGN KEY (KK_Dad_Key) REFERENCES DD_Dads (DD_Dad_Key)
INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR')
INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR')
INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' )
INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' )
INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' )
INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )
So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads.
I am getting this, which is not what I want:
- George SR
--- George JR
--- Jeb
------ Jenna
------ Barbara
------ Jeb JR
------ Noelle
SELECT 1 as Tag,
NULL as Parent,
GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName],
GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id],
NULL as [DD_Dads!2!DD_DadName],
NULL as [DD_Dads!2!DD_Dad_Key!id],
NULL as [DD_Dads!2!DD_Grandpa_Key!idref],
NULL as [KK_Kids!3!KK_KidName],
NULL as [KK_Kids!3!KK_Dad_Key!idref]
FROM GG_Grandpas
UNION ALL
SELECT 2 ,
1 ,
NULL ,
GG_Grandpa_Key ,
DD_DadName ,
DD_Dad_Key ,
DD_Grandpa_Key ,
NULL ,
NULL
FROM GG_Grandpas, DD_Dads
WHERE GG_Grandpa_Key = DD_Grandpa_Key
UNION ALL
SELECT 3 ,
2 ,
NULL ,
GG_Grandpa_Key ,
NULL ,
DD_Dad_Key ,
NULL ,
KK_KidName ,
KK_Dad_Key
FROM GG_Grandpas, DD_Dads , KK_Kids
WHERE GG_Grandpa_Key = DD_Grandpa_Key
AND DD_Dad_Key = KK_Dad_Key
FOR XML EXPLICIT
I've tried it all different ways, but no luck so far.
Any ideas?
View 5 Replies
View Related
