Transact SQL :: How To Do Pagination When Multiple Table Used By Joining
Sep 22, 2015
I know people use ROW_NUMBER() function to do the pagination but my below two query is bit complex. Sohow to use pagination there ? I used ROW_NUMBER() OVER(ORDER BY IsNull(A.OEReference, B.OEReference) ASC) as Line in one but not sure am i right or wrong.
IF IsNull(@GroupID,'') = ''
SELECT IsNull(PartGroupName, 'UnMapped') AS PartGroupName,
CASE IsNull(PartGroupName, '')
WHEN '' THEN ''
ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'')
END AS PartGroupID,
[Code] .....
View 6 Replies
ADVERTISEMENT
Oct 19, 2015
I have a lookup table with 4 levels of codes like follows:
create table #RiskElementCategory(
[RiskElementCategoryCode] [nchar](5),
[RiskElementCategoryCodeDsc [nvarchar](50),
[RiskElementCategoryCode_2] [nchar](5),
[RiskElementCategoryLevel2Dsc] [nvarchar](50),
[Code] ...
Along with some other rows with the same format. I need to join to this table using a RiskElementCode that I get from the Source system. The trick is that it can be at any level, but I don't know which level it is at. So what I have to do is somehow get the correct row from the lookup table based on the code from the source to get the correct level.
So for Example, If i receive the RiskElementCode of 'SSR', that is in column RiskElementCategoryCode_3 so I need the row that has 'NA' for anything after RiskElementCategoryCode_3 where RiskElementCategoryCode_3 = 'SSR'. If i get 'DFR' I need to get the row where RiskElementCategoryCode_4 = 'DFR' since there are no levels deeper than 4 i don't need to check anything else. If I get 'PRR', then I need the row where RiskElementCategoryCode = 'PRR' and code_2, code_3 and code_4 = 'NA'.
So besides getting the correct row based on the code, i need to get the correct row based on the level where the next levels are 'NA'. I should only get 1 row each time.
View 2 Replies
View Related
Apr 20, 2015
I run into a problem when asking to show a query of employee vacation days.
table 1:
column1 is dates
e.g.
2015-01-01
2015-01-02
2015-01-03
.
.
.
2015-12-31
table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012
My query output need to be:
2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
View 4 Replies
View Related
Jun 2, 2015
I have a table (can't change the schema of it since it is part of an off the shelf app ) that has columns for individuals which I need to extract several pieces of information, essentially Phone, Email Address, etc. See U1 - U6
What is a better way to return this information rather than multiple joins?
Select
[AccountNumber]
,a.[AccountId]
,[Name]
,[new_LocationID]
,[ws_name]
,[new_BillingManageruserName]
,[new_AreaServiceManName]
[Code] ....
View 3 Replies
View Related
Jun 4, 2015
I am looking for an alternate logic for below-mentioned code where I am inserting into a table and having left join with the same table
insert TABLE1([ID],[Key],[Return])
select distinct a.[ID],cat1,cat2 from
(select ID,[Key] Cat1 ,[Return] cat2 from @temp as temp) a left join TABLE1 oon a.ID= o.ID
and a.Cat1 = o.[Key]
and a.cat2 = o.[return]
where [key] is null order by ID
View 2 Replies
View Related
Jun 23, 2015
I have a large table with 100 Million records that has around 1 million duplicate records that need to be deleted.
I am running a script that creates a staging table called,DuplicateTable that collects all the duplicates and then I want to write a an effecient delete statement.
Is it possible to write something like:
delete from OrigTable O join DuplicateTable D
on O.Key = D.key
Or do I have to run a loop on the DuplicateTable and run a delete statement record by record ?
View 4 Replies
View Related
Jan 9, 2006
Hi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks
View 1 Replies
View Related
Sep 24, 2015
I need sql script where i will pass startindex, endindex and sortcol and order then query return result accordingly. it would be better if query looks small and dynamic.
Here one sample of dynamic sql but there case is used which is not require.
DECLARE @sql NVARCHAR(MAX)
SET @sql = ';WITH cte as(SELECT *,
ROW_NUMBER() OVER
(ORDER BY ' + CASE (@SortColumn + ':' + @SortDirection)
[Code] ...
My requirement is that i will create a sp where i will pass start index and end index and sort column,sort order and filter means where clause and sp will generate and execute dynamic sql and return data. i need a sample of that kind of sp.
View 9 Replies
View Related
Oct 26, 2015
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
View 2 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
View 11 Replies
View Related
Aug 4, 2015
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name
View 10 Replies
View Related
Jun 10, 2015
Matrix table has ID column and data below.
ID Flag TestDate Value Comment
111 2 12/15/2014 7.5 null
222 2 Null 10 received
Matrix_Current table could have 1 or multiple rows as below.
ID Flag TestDate Value Comment
111 2 01/26/2015 7.9
111 2 02/23/2015 7.9
111 2 04/07/2015 6.8
222 1 null 8 test comment 1
222 3 null 9 test comment 2
When I run below update
UPDATE AM
SET M.Flag = MC.Flag, M.TestDate = MC.TestDate,
M.Value = MC.Value, M.comment = MC.Comment
FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID
Matrix table has value below:
ID Flag TestDate Value Comment
111 2 01/26/2015 7.9
222 1 Null 8 test comment 1
I want to update Matrix table from all row from Matrix_Current, final table would like below:
ID Flag TestDate Value Comment
111 2 04/07/2015 6.8
222 3 Null 9 test comment 2
View 3 Replies
View Related
Jul 16, 2015
I downloaded a day's worth of data into a single table first and named it as Full_Day_Data_Table.(10,000 records)
Later for some other purpose, downloaded the same day's data into five small tables.
Now when I compare the total row count of Full_Day_Data_Table vs Sum of row counts of five small tables, the row count does not match.
So, I would like to know the records that exists in Full_Day_Data_Table and not in small tables.
View 5 Replies
View Related
Jul 23, 2015
I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.
My query looks like this:
Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')
It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.
My question: how could I update the same column from many records in one execution?
View 10 Replies
View Related
Apr 29, 2015
I have these two CTE queries, the first one is the existing one which runs fine and returns what we need, the second is a new CTE query which result I need to join in to the existing CTE, now that would be two CTE's can that be done at all?The second query is working on the same table as the first one so there are overlaps in names, and they are the same, only columns I need to "join" is the "seconds" and "AlarmSessions".
;with AlarmTree as (
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
[code]....
View 4 Replies
View Related
Sep 28, 2015
What I am working with unfortunately is a very poorly designed and non-normalized database. Please don't criticize the design. I didn't design it, but I have to write queries against it.I have 2 tables. 1 is called EnterVolume. The other is ExitVolume. Similar columns exist in each.
CREATE TABLE [EventPortion].[EventEnterVolume](
[SequenceNumber] [int] NULL,
[TrialID] [nvarchar](255) NULL,
[TimeOfEvent] [int] NULL,
[UniversalTime] [nvarchar](255) NULL,
[SBOINumber] [int] NULL,
[SEntityPosUpdateIndex] [int] NULL,
[VolumeIndex] [int] NULL,
[code]....
The rules of the database state that for every EnterVolume row (for a given TrialID, SBOINumber, and OwnerBOI) there must be a corresponding ExitVolume row in the ExitVolume table.What I need to do is to capture the paired TimeOfEvent entries from each table for each paired row. Nothing says that an SBOI cannot enter and exit a OwnerBOI's volume several times during the same Trial.Every time a SBOI Enters an OwnerBOI's volume during a certain trial, a row is created in the EnterVolume table. And Likewise when Exiting a OwnerBOI's volume during a certain trial, a row is created in the ExitVolume table.
So here is a query that I attempted, but gave undesirable results:
SELECT EV.TimeOfEvent AS [Enter Time], XV.TimeOfEvent AS ExitTime
FROM IntegratedTest1.EventPortion.EventEnterVolume AS EV INNER JOIN
IntegratedTest1.EventPortion.EventExitVolume AS XV ON EV.TrialID = XV.TrialID AND EV.SBOINumber = XV.SBOINumber AND EV.OwnerBOI = XV.OwnerBOI
Here is some sample data:
EnterVolume
TrialID SBOI OwnerBOI TimeOfEvent
1 A D 5
1 A D 2000
[code]....
View 8 Replies
View Related
Oct 20, 2015
I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure
DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')
[code]...
how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.
View 4 Replies
View Related
Nov 6, 2015
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
View 8 Replies
View Related
Jun 1, 2015
I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.
CREATE TABLE Salesman_Product
(
SalesManID int,
ProductID int
)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4)
[code]....
View 6 Replies
View Related
Oct 12, 2015
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),
[code]...
View 19 Replies
View Related
Oct 5, 2015
I am joining on two tables, and returning the values that do not exist in #topoftheline -- well now I see that their are multiple calltimes so I want to return ONLY the most recent call time. What would I change in my syntax to only return that most recent datetime?
Create Table #topoftheline
(
callreceived datetime,
callerfirstname varchar(100),
callerlastname varchar(100),
callnotes varchar(4000)
)
[Code] ...
View 3 Replies
View Related
Jul 27, 2015
below is the sql query i am trying to write and i receive an error saying Incorrect syntax near the keyword 'Between'.
Update dbo.Claims_Primary_Adjuster_Test_Sujith
SET
Claims_Primary_Adjuster_Test_Sujith.Supervisor = Claims_Internal_Transfer.Supervisor,
Claims_Primary_Adjuster_Test_Sujith.Office = Claims_Internal_Transfer.Office,
Claims_Primary_Adjuster_Test_Sujith.Specialty = Claims_Internal_Transfer.Specialty
from dbo.Claims_Primary_Adjuster_Test_Sujith
Left Outer Join Stg.HS_DW_RV_ClaimsON Claims_Primary_Adjuster_Test_Sujith.Claim_Number = HS_DW_RV_Claims.ClaimNumber
[code]....
how to modify my code such that it works for my condition?
View 10 Replies
View Related
Apr 29, 2015
I have these two queries I would like to join, however the later is a grouped query how can I join it with the first query? Has to be joined on EventId. The second query is a total table scan.
SELECT AH.EventID,
AH.TechnicalAddress, AH.AlarmAlias, AH.AlarmPath as [OrgAlarmPath], AH.AlarmCounter as AlarmCount, AH.EventDateTime as EventTime,
AH.[Priority], AH.AlarmMessage, AH.EventText, AH.CallListName, AH.AlarmReadDate as EndTime,
AH.alh_EventEndedUserRemark as [EndRemark] --, SUM(seconds) here, and AlarmSessions here
FROM AlarmHistory AH
[Code] ...
2)
WHERE ia.EventTypeId = 0
group by ia.EventId
order by EventId desc
View 4 Replies
View Related
Nov 4, 2015
Existing table structure is below:
Table name: Student
Columns in Student are below:
Student_Id
Subject_Id
Quarter
Col1
Col2
Combination of Student_Id, Subject_Id and Quarter columns is the primary key. One student can take one subject in a quarter. Now the new requirement is a student can take multiple subjects in a quarter. So need to add another table like below:
NEW table name: Student_Subject and
column are below:
Student_id
Subject_Id
Quarter1
All the above three columns combination is primary key.
After the new table Student_Subject created,
remove Subject_Id column
from Student table.
When the user clicks on a button after selecting multiple subjects and provide col1 and col2 data then one row gets inserted into Student table and multiple rows gets inserted into Student_Subject table.
Is there any other table design that satisfies one student can take multiple subjects in a quarter?
View 15 Replies
View Related
Jun 30, 2015
I have a temp table with the following columns and data
drop table #temp
create table #temp (id int,DLR_ID int,KPI_ID int,Brnd_ID int)
insert into #temp values (1,2343,34,2)
insert into #temp values (2,2343,34,2)
insert into #temp values (3,2343,34,2)
[Code]....
I use the rank function on that table and get the following results
select rank() over (order by DLR_ID,KPI_ID,BRND_ID ) Rown,* from #temp
I am interested only in Rown and Id columns. For each Rown number, I need to get the min(ID) in the second column and the duplicate ID should be in 3rd column as shown below.If i have 3 duplicate IDs , I should have 3 rows with 2nd column being the min(id) and 3rd column having one of the duplicate ids in ascending order(as shown in Rown=6)
View 7 Replies
View Related
Jul 16, 2015
We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.
Below is the Table Structure.
Table Name IN_USE_FG
CUST_D 0
PROD_D 0
GEO_D 0
DATE_D 0
Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.
Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.
View 6 Replies
View Related
Jun 17, 2015
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 1 1
AED BNT 12 1
AED SCN 1 1
AED SNT 1 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 13 2
AED SCN 1 1
AED SNT 11 7
AFN BPC 8 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
View 5 Replies
View Related
Jun 9, 2015
I have 5 tables that are joined respectively,
Each one of the tables listed below has a “CreateDateTime” and “UpdateDateTime” fields, I need to get yesterday changes, I can get any record where either CreateDateTime or UpdateDateTime is greater than midnight yesterday butI need to watch dates on all of the tables so I need to do atleast 10 date checks.
If any table shows an updated or created record, I need to gather ALL of the information for that customer. So, if my name didn’t change (SCUS table), but my email does (SEML table), I have to pull out both the SCUS and SEML tables (and the others, of course). So It may not be simple WHERE clause, How can I achieve this:
SELECT
SCUS.CUSFULLNAME
,
SCUS.CUSMIDDLENM
,
SCUS.CUSLASTNM ,
[Code] ....
View 3 Replies
View Related
Jun 10, 2015
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
View 3 Replies
View Related
Mar 5, 2007
table - employee
empdcno name
1 jon
2 peter
3 john
4 n1
5 n2
6 n3
7 n4
8 n5
9 n6
10 n7
table - personalinfo
empdcno telno address
1 111 aaa
2 222 bbb
3 333 ccc
4 444 ddd
5 555 eee
6 666 fff
7 777 ggg
8 888 hhh
9 999 iii
10 000 jjj
table - hrappempeducs
empdcno schoolcode degree
1 A1 BSCS
3 A2 BSIT
5 A3 degree1
9 A3 degree2
10 A2 degree3
table - hrsetschools
schoolcode schname schaddress
A1 Harvard usa
A2 LaSalle philippines
A3 UP india
A4 s1 peru
A5 s2 japan
Result expected:
name telno address degree schname
jon 111 aaa BSCS Harvard
peter 222 bbb null null
john 333 ccc BSIT LaSalle
n1 444 ddd null null
n2 555 eee degree1 UP
n3 666 fff null null
n4 777 ggg null null
n5 888 hhh null null
n6 999 iii degree2 UP
n7 000 jjj degree3 LaSalle
I am not quite familiar with joins.
Thanks
-Ron-
View 4 Replies
View Related
Dec 5, 2005
I have three tables
1st table is Student
StudnetID (pk)
Other fields…
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…
StudnetID: 1
Name: John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager: 123-456-7890
Fax:
Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won’t have.
Thanks in advanced,
Nathan Rover
View 3 Replies
View Related
Dec 14, 2004
Hi,
This seems like a basic problem but I can't figure out how to resolve it.
I have a query :
SELECT PR.WBS2, SUM(LedgerAR.Amount * - 1) AS Expr5, LB.AmtBud AS budget
FROM PR LEFT OUTER JOIN
LedgerAR ON PR.WBS1 = LedgerAR.WBS1 AND PR.WBS2 = LedgerAR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND PR.WBS3 = LB.WBS3
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> 'zzz') AND (PR.WBS2 <> '98') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (PR.WBS1 = '001-298')
GROUP BY PR.WBS2, LB.AmtBud
ORDER BY PR.WBS2
The output of the above query:
WBS2Expr5budget
0141
0141953000
0143
121724540
1217500
1217622.5800
12171000
12172000
12174000
12174500
121772908000
121793513500
12173445018000
12176596032000
12173801044000
121838100
121913224.5
1220
1221
122262000
12224000
122312702
I want to sum up the middle column and last column grouping by wbs2. However, when I do SUM(lb.amtbud) the budget column is not summing correctly it is summing the column as if the data appeared like this:
0141
01410101410101410103000
01410101410101410147.53000
01410101410101410147.53000
0143
014305
1217
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
1217010121701012170101017.58000
121701012170101217010382.58000
12170101217010121701027.58000
121701012170101217010302.58000
12170101217010121701027.58000
121701012170101217010382.58000
121701012170101217010302.58000
1217010121701012170104958000
1217010121701012170102008000
1217010121701012170101017.58000
1217010121701012170101182.58000
1217010121701012170101952.58000
1217060
1217061
121708012170804000
So as a result I am getting 9000 where wbs2 = '0141'
I figure that in my top query I am not joining something correctly. Could someone point out what I am doing wrong?
Thank You.
:)
View 2 Replies
View Related
