I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.
Here's an example of my data:
No code has to be inserted here.
No code has to be inserted here.
No code has to be inserted here.
No code has to be inserted here.
Right now my SQL is this:
Code:
SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum]
FROM PR04PTF
INNER JOIN PR11ERF_History ON
PR11ERF_History.EmployeeID = PR04PTF.EmployeeID
AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd
There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.
The first and second table have one field in common - the Account number.(ACCT_NUM)
the exclusion is based in steps..
1) if it has all the fields in the second table for that account number, then compare all of them.. 2) if only 4 of them are present,compare and check for null for the rest 3) if only 3 of them are present,compare and check for null for the rest 4) if only 2 of them are present,compare and check for null for the rest.
One of our departments once to automate a query that they have to pull data from the previous day. We are going to set this up as a job. How can we do this without using the Saturday and Sunday dates? So what we want to do is Pull the data from Friday on Monday. Is this possible? This is what they have. I know this pull the data from the day before.
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID WHERE B.SomeParamColumn = @SomeParam
SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam
Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?
Hi I hope i make sense this time around, I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
Code:
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID, dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID, dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID, dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime, CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '') WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION, CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '') WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category, dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID, dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE, CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0) WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0), tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0) WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0), tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2), IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2), IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage, dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0) / 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0) / 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0) AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID, ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1, dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1, dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate FROM
Hi this is my first post i am hoping its the write place. I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID, dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID, dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID, dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime, CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '') WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION, CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '') WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category, dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID, dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE, CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0) WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0), tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0) WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0), tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2), IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2), IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage, dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0) / 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0) / 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0) AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID, ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1, dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1, dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate FROM
I have table with 25 colums. 3 of the colums(Chkflag,BMCHECK,UPDATED) have yes/no data type. What I am trying to do : If chkflag is No value (i.e 0) and BMCHECK or UPDATED has no value then bring one of the field from BMCHECK.
quote:SELECT DISTINCT BMCHECK FROM FEEDER WHERE Chkflag = 0 AND BMCHECK = 0 OR UPDATED = 0
I am using the above query in vb.net to look for any of above field is blank or not ticked if the query brings any data THEN ---run the other queries else (There is no data) ---do this---
I have a data flow that is updating an Access database using an OLD DB Command control. I am getting this error and have narrowed it down to a column the Access table called CreateDate. I don't think this is a reserved word, but even surrounding it in [] did not resolve the problem. The column from SQL Server is called order_date and is a datetime and the destination column createdate is a datetime in Access. When I remove this column fromt he insert command, it works fine but when included, it gives the data type mismatch on criteria error. Any ideas?
"UNION " & _ "SELECT p.ProductID, p.ProductTitle FROM Product p " & _ "WHERE (p.Price > '" & FormatCurrency(lowestPrice, 2) & "' AND p.Price < '" & FormatCurrency(highestPrice, 2) & "') " & _ "ORDER BY p.ProductTitle"
I don't know where the error goes wrong in here.. previously because of the union missing one spacing that resulted in syntax error, after i inserted a space to it.. it shows me Data type mismatch the criteria expression. Is it because in my sql coding i cant use FormatCurrency for ASP.net? please give me a hand.. thank you
Contact me at: ryuichi_ogata86@hotmail.com ICQ me at: 18750757
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. /advice generation/testdateprint.asp, line 371
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed. It now has to be deployed and therefore the data migration for this region has to take place. I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables. When doing this I will firstly, have to check that the CallerID is valid, if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXIDDailupDT DongleAccessNum CLI RegionalID RegionalDialup 838/8/2006 8:58:11 AM T2 UQ 28924 013249370000800003554 5438/8/2006 8:55:44 AM T0 UA 33902 012362350000800003554 12198/8/2006 8:59:03 AM T3 ZD 02031 015295809500800003554 10128/8/2006 9:02:54 AM T0 UA 41261 017301105000800003554 13318/8/2006 8:59:57 AM T0 UA 01938 012460462700800003554 19798/8/2006 9:02:52 AM T0 UA 09836 016375121000800003554 19038/8/2006 8:58:41 AM T0 UA 26009 014717535600800003554 15228/8/2006 8:58:54 AM T3 MB 94595 057391287100800004249 3198/8/2006 8:51:28 AM T2 ZD 32892 054337510000800004249 32708/8/2006 9:04:26 AM T2 MB 8733100800004249
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeIDRegionalID ExtName SubsNDCDLocCDUpdateStatusRegionDesc 79731 PRETORIA 0123620NORTH EASTERN REGION 79741 HARTEBEESHOEK 012 30120NORTH EASTERN REGION 79751 HARTEBEESHOEK 01230130NORTH EASTERN REGION 79761 PRETORIA 01730140NORTH EASTERN REGION 79771 PRETORIA 01230150NORTH EASTERN REGION I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID 0 1012 17 0 543 2
Please assist, it would be greatly appreciated. Regards SQLJunior
I am trying to import data from 4 columns in a spreadsheet, the Columns are (Last Name - First Name - ID - Code) and this spreadsheet has around 10k records. I want to add what is in this spreadsheet to the query I have below that uses the EXCEPT operator but I am not sure the best way to go about it.
Using the example I have filtered below for the name "Denise Test", at the end of the day I want everything that is in the spreadsheet to also be excluded from the results.
So before the spreadsheet lets say the 2nd query referencing table C has the following results for Denise Test
Last_Name First_Name ID Code Test Denise 1 5 Test Denise 2 4
After adding the spreadsheet I want it to show this:
Last_Name First_Name ID Code Test Denise 1 5 Test Denise 2 4 Test Denise 3 3
Here is the query as it stands now without the inclusion of the spreadsheet:
SELECT ta.last_name, ta.first_name, tb.ID, tb.code FROM TableA ta INNER JOIN TableB tb
I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!
Here's the basic idea:
SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept FROM Server.Database1, Server.Database2
I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).
I've tried:
WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))
(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)
What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.
The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.
I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.
This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.
The script basically maps ALL products to customer levels. The Delete statement at the start is needed to make sure that any deleted products or temporary mapped products are removed once a week. However I want to map some products to CustomerID's 9,10 and 14 only. But when I run this statement it maps all products to all customer levels. What I want is thisAll products mapped to customer levels 9,10 and 14Then every other product mapped to customer levels 0,5,7, and 8 but not those extra ones in 9,10 and 14.
DELETE FROM ProductCustomerLevel WHERE CustomerLevelID IN (0, 5, 7, 8, 9, 10, 14) INSERT ProductCustomerLevel ( ProductID, CustomerLevelID
I would like to exclude any parameter that is empty from the SELECT command? How do I do this? This is part of a stored procedure.
SELECT PersonID FROM Persons WHERE (FirstName = @firstname) AND (LastName = @lastname) AND (SSN = @ssn) AND (AddressID = @addressid) AND (DOB = @dob) AND (Middle = @middle)
Does anyone know how to write code to tell the amount of days between 2 dates excluding Saturday and Sunday. Datediff will tell me the total amount of days and I guess I need some logic to look at the startdate and manipulate the total after the fact.
and generating a report from an SQL table, and need to know how to exclude records that are "duplicates". Not duplicates in a sense that every field is identical, but duplicates in a sense where everything except the unique identifier is identical. Is there a quick and easy way to do this?
Suppose there is a table containing these recodes.country-------CON_CHNCON_JAPJAPCON_CHNWhen I use the following sql:select country, count(*) as num from table group by countrythe normal result will be:country num---------------CON_CHN2CON_JAP 1JAP1However, my desired result is as follows:country num-----------------CON_CHN2CON_JAP 2How can I re-write my SQL? Or any other methods to do that?
I have rows coming from the db including: Contract Number, Contract Name, owner and Actions associated with each contract.
SQL statement brings back: Contract Number Contract Name Sales Owner Action 1234 123453 $50 Neil x 1234 123453 $50 Bob y 534232 5464634211 $30 Harry z
The problem is that each contract can have multiple actions associated with it... There ideal output would be:
Contract Number Contract Name Sales Owner Action 1234 123453 $50 Neil x Bob y 534232 5464634211 $30 Harry z
Total: $80
Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..
I have to do various controls on a dataset - I created a multicast. After performing controls (one control per copy), I merge my (7) multicasted datasets using a Union All transformation. The problem I'm having are the duplicate rows created by merging the multicast copies.
How do I get rid of the duplicates? Is the Sort Transformation the solution by setting the option Remove rows with duplicate sort values to True? I have a unique key by which I'm able to discard the duplicates correctly. Are there any other ways (at a Union All level)? Is there sth like Union and Union All like in SQL?
I'm working on my 1st integration serv. project and it seems that more I work more questions I have. Shoudn't be the opposite? Thank you for the help.
i have a field with 2 characters(many of them ) and spaced inbetween them which are codes for something
ie XA OX YY BY CY DY XC XD OE In any or all combination
i want to make a count of rows which contains BOTH XA BUT DOES NOT CONTAIN any one or all of XC,XD & OE
SO I write the query select count(colname) from tablename where colname LIKE ('%XA%') AND colname NOT LIKE ('%XC%') OR colname NOT LIKE ('%XD%') OR colname NOT LIKE ('%OE%') ')
iS this correct why then it does not exclude the XC,XD,OE
I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?
Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds.
I used:
dataadd(ss, TTIME/1000, '1970-01-01')AS time
to create a column with a readable time in it.
Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg
Can anyone help me with this. Thanks very much. Andrew
Hi,Is there a way to exclude fields in a query other than just includingthe ones you want. If there are 20 fields and you want to see all but3, it would be a lot easier to exclude the 3.Thanks
I have two datetime fields that I would like to find out how much time has passed between them. First field is "start date" and the second is "end date" the dates in both fields are in this format (03/27/2008 4:00PM). The problem I am having is I need to exclude the time passed from Friday, 6:00PM to Monday, 7:00AM if the dates happen to go over a weekend.
