Selecting Field From Second Table If Same Field In First Table Is Null
Nov 25, 2007
Hi,
I have one table (PermProportionalCosting) that contains employeecode, costcentre and proportionpercentage. An employee may have two or more records in this table, indicating that their salary has been split across multiple costcentres.
Another table (Employee) contains a default costcentre for each employee.
Another table (TransActualHistoricalMaster) contains the hours paid to each employee for each pay period.
My query so far selects all employees and the total hours worked from the TransMaster table, and then selects the costcentre and proportionpercentage from the PermProportionalCosting table. Multiple lines are created for all employees in the PermProportionalCosting table, with each line indicating the proportionpercentage and associated costcentre.
This is my query so far:
SELECT TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode,PermanentProportionalCosting.PercentageSplit,
TransActualHistoricalMaster.OrdHours
FROM TransActualHistoricalMaster
INNER JOIN Employee ON TransActualHistoricalMaster.EmployeeCode = Employee.EmployeeCode
INNER JOIN CostCentre ON Employee.CostCentreCode = CostCentre.CostCentreCode
FULL OUTER JOIN PermanentProportionalCosting ON TransActualHistoricalMaster.EmployeeCode = PermanentProportionalCosting.EmployeeCode
GROUP BY TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode, PermanentProportionalCosting.PercentageSplit, TransActualHistoricalMaster.OrdHours
This next bit is what I am unsure of.
I would like the CostCentre field from the Employee table to go into the PermanentProportionalCosting.CostCentreCode column where the employee doesn't have a record in the PermanentProportionalCosting table. In the above query, there is a null value for each employee that doesn't have a record in the PermanentProportionalCosting table, so I would like my query to replace the null value with the costcentre from the Employee table.
Hi guys, I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE JurnalTransMoves.SUF SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752 10 60
5752 20
5752 30
5753 400 3000
5753 500
5753 600
5753 700
5753 800
5754 7 15
5754 8
Is there any chance to do that? Thanks in advance, Aldo.
Code Snippet SELECT Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME', Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY', Accounts.FILTER AS 'ACCOUNTS.FILTER', Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP', AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME', Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode', CreditTerms.DETAILS AS 'CreditTerms.DETAILS' CreditTerms.CURRENF AS 'CreditTerms.CURRENF' CreditTerms.MONTH AS 'CreditTerms.MONTH', CreditTerms.DAYS AS 'CreditTerms.DAYS', CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC', CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x' WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y' ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID', DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE', JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION', JurnalTrans.REF2 AS 'JurnalTrans.REF2', JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF', JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF', JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM JURNALTRANSMOVES AS JurnalTransMoves_1 INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY LEFT OUTER JOIN ITEMS AS Items INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE Accounts.SORTGROUP Between '3001' And '3020' AND Accounts.ACCOUNTKEY IN ('123456')
Total amount = 1000 salemancode1 = space salemancode2 = Staff-99 salemancode3 = space salemancode4 = staff-88
How I can write a one query statement to do this, we expect to count how many salemancode is not space and count the number of salesman to over the total amount.
total amount / (no_of_saleman) as commission the result is 1000/ 2 the commission is $500.
I have a datatable : Data_Table and a look up table: Lk_table. Myfield that I use in Inner Join is defined in both the data and look table.
So I build my query like this:
SELECT * FROM dbo. Data_Table INNER JOIN
dbo. Lk_table ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield
The pb, sometimes I have myfield still with its default null value in the datatable: Data_Table. So, I end up getting 0 record when I execute the query shown above.
How do I turn that around so that even if myfield in Data_Table is Null, I still get the records from Data_Table. (I don t want a set of records including all possible values from the look up table: Lk_Table)
I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie. Code Description Brand ABC1 BLANK DVD SONY ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
With this query: SELECT Table1.1ID, Table1.Field1, Table2.2ID, Table2.Field1, Table3.3ID, Table3.Field1, sum(Table1.Field1), sum(Table2.Field1), sum(Table3.Field1) FROM Table1 INNER JOIN Table2 ON Table1.1ID = Table2.2ID INNER JOIN Table3 ON Table2.2ID = Table3.3ID
I get this error: Column 'fieldname' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Is it because you can't do what I'm trying above or am I missing something?
Hi, I have a concern about adding a new field to a table with image field - which is huge. Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field? Previously this happened to me, and what was advised to put all the big fields at the end of the table. Thanks.
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I am having a table called as status ,in that table one field is there i.e. currentstatus. the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table. I can do programatically. but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
(Below are the three tables along with their fields)
1) tblEmailAddress Table a) EmailAddress b) Emailid (each EmailAddress has this corresponding Emailid)
2) tblEmailGroupLink Table a) Emailid b) Unsubscribed
3) Remove_List Table
a) EmailAddress
What I'd like to accomplish is...
A) To compare the EmailAddress field of all the records in the Remove_list Table to the EmailAddress field of all the records in the tblEmailAddress Table and if there is a match, get the Emailid field value.
B) Go to the tblEmailGroupLink Table and lookup the Emailid and change the value of the Unsubscribed field to 'True' for that record.
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price from tblCustomerPurchases as a join tblProduct as b on a.ID=b.ID
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate] /* Input Parameters */ @TableName Varchar(20), @Dist Varchar(20) AS Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000)
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Im using many source tables in a dataflow from which data has to be union in to a single table.While doing this i have to pull the table names in to the destination table.How to get the table names as column values?
CREATE PROCEDURE {databaseOwner}{objectQualifier} [PreciseData_IssueTracker_GetAllIssues]@moduleId intASSELECT PreciseData_IssueTracker_Issue.id AS 'issueId',PreciseData_IssueTracker_Status.name AS 'statusName',PreciseData_IssueTracker_Issue.subject AS 'subject',PreciseData_IssueTracker_Type.name AS 'typeName',Users.Username AS 'assignedUserName',Users.Username AS 'raiserUserName',PreciseData_IssueTracker_Issue.raiseDate AS 'raiseDate'FROMPreciseData_IssueTracker_Issue INNER JOIN PreciseData_IssueTracker_Status ON PreciseData_IssueTracker_Issue.statusId=PreciseData_IssueTracker_Status.idINNER JOIN PreciseData_IssueTracker_Type ON PreciseData_IssueTracker_Issue.typeId=PreciseData_IssueTracker_Type.idINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.assignedUserIdINNER JOIN Users ON Users.UserID=PreciseData_IssueTracker_Issue.raiserUserIdWHERE PreciseData_IssueTracker_Issue.moduleId=@moduleIdORDER BY PreciseData_IssueTracker_Issue.raiseDate DESCGOHow do i make it work?
I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005? This is my existing code:alter PROCEDURE dbo.SP_FeaturedClassifieds @PageIndex INT, @NumRows INT, @FeaturedClassifiedsCount INT OUTPUT
AS BEGIN select @FeaturedClassifiedsCount = (Select Count(*) From classifieds_Ads Where AdStatus=100 And Adlevel=50 ) Declare @startRowIndex INT; Set @startRowIndex = (@PageIndex * @NumRows) + 1;
With FeaturedClassifieds as (Select ROW_NUMBER() OVER (Order By FeaturedDisplayedCount * (1-(Weight-1)/100) ASC) as Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCountFrom classifieds_Ads WhereAdStatus=100 And AdLevel=50 )
SelectId, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount From FeaturedClassifieds Where Row between@startRowIndex And @startRowIndex+@NumRows-1
I need to do a SQL query based on the two biggest values of a field. PLS , what s the SQL syntax : select * from Table where PRICE ......Ex: if the values of the field PRICE are : 50, 40, 100, 30 and 150.The request should select the rows that have Price equal 100 or 120Thanks a lot for help
