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.
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.
I have one table say A and in which 4 columns are there. Out of 4 , one columns stores the queries like 'select * from table xyz' etc(Only select queries). I am writing a procedure in which I have to fetch this column and execute the query and wants to check whether query i.e. "select * from table xyz" contains any record or not. If yes , I am updating the table B with value as Pass , else Fail.
I used execute @queryfromvariable but it does not gives me count..
I have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:
ColumnName DistinctCount NormalCount Id 1000 1000 Name 1000 1000 Phone 600 600 PINCode 200 1000 City 400 1000 Gender 2 1000
I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.
i.e. EmployeeID: 001
week1: 40 week2: 24 week3: 24 week4: 39
This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?
I need help to build a query that shows me how many columns inside a range on columns are null. Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Christine the Pharmacist writes "Please pardon as I am not horribly advanced with SQL...
Using SQL Server 2000, Windows 2000, Service pack 4
I'm trying to figure out marketshares for "preferred" products. I have a table put together by hospital and month of medication use with the totals of each drug dispensed in columns as well. (example table below)
Hospital Month Drug A Drug B Drug C Drug D A 7 5 10 58 73 B 7 NULL 26 98 43 etc.
** Drug C and Drug D are the preferred drugs
I'm getting correct preferred % in all cases except when a value is NULL (result is NULL). I'm using an aggregate (sum), which should ignore NULLs, but it ignores NULLs within a column, and not ignoring across columns. I've tried setting concat_null_yields_null off and I'm still getting NULL (since I'm using the plus sign).
This is my admittedly confused select statement, suspect this is where my problem lies (problems with saying "sum", but using "+" as well?):
select a.Hospital,a.month,convert(float,sum(c.Drug_C+d.Drug_D))/convert(float,sum(a.Drug_A+b.Drug_B+ c.Drug_C+d.Drug_D)) as preferred_share
Hi,I have some tables where I import data in, lots of field have gotten aNULL value which the application can not handle.Now can I replace each NULL value with '' in a columns with:update <tableset [<column>] = '' where [<column>] IS NULLBut because there are lots of columns this is pretty much work, alsothere are multiple tables.Is there an easy way to replace all NULL values in all columns in atable?Thanks in AdvanceBob
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 am facing a problem on validating the data from a flat file while inserting the data into the destination table of sql server 2005 database. In my package, i have to validate the input data whether the values are coming as null or not, before inserting into the destination database. The flat file may not contain data for all NOT NULL columns. I have to find out that row(s) and reject the record. If the rows are coming as Null for the Not Null columns, the OLEDB Destination throws OLEDB exception for the constraint.
To resolve this, i have an script component in data flow, to check whether the input data is coming as null. I have added the output column of boolean type to the script component, it will be assigned to TRUE when there is null for the Not Null column in the script.
And in the follwing conditional split, i am checking the flag for TRUE to reject the record.
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.
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 have two columns A (which allows nulls) and B( which does not allow nulls). How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.
The result of A+B concatanation will be stored in a column, C.
I have this bit of code below and would like to bring back stat_code's which have no value as 0...
select stat_code, COUNT(stat_code)AS Stat_Count --INTO tableSroStatABD from fs_sro where sro_stat ='O' and whse = 'ABD' group by stat_code having stat_code in ('1 QUOTE WN', '2 PR & DEF', '2.1 PRTORD', '3 PRTS AVL', '4 SCHEDULD', '5 LABB JBC', '6 CSTB JBC', '7 WRKS CMP', '8 CSH APP', '9 TO B INV') order by stat_code asc
I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):
SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs FROM APs WHERE (completed IS NOT NULL) GROUP BY [File]
Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select Test_Name AS 'Test_Name', Table_Bird.Animal AS 'Birds', Table_Mammal.Animal AS 'Mammal', Table_Reptile.Animal AS 'Reptile, Table_Fish.Animal AS 'Fish' From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
the Table columns is like this NO ProductNo Area In Out1 0001 US NULL NULL2 0002 UK NULL Y3 0003 FR Y NULL 4 0004 FR Y NULL5 0005 UK Y NULL I have Query get the result belowArea Count In&OutUS 1UK 2FR 2 the Area is Group By by Area and the Count columns is counting how many recoreds Table for each Areathe problem is the column "In & OUt'I have to make sure if the "In" or "Out" is Null , if one of them is Null the plus 1 so the result would like I have Query get the result belowArea Count In&OutUS 1 1 UK 2 1 FR 2 0 which syntax I can use for the problem? I just think maybe I can use IsNULL?but I have no idea how to wirte a query ...can you give me a hint? thank you
I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.
For instance, a row from my table: Row1 : field1 field2 field3
If field3 is empty or null, my query should return 67%.
So far I have gotten the number of fields: select count(1) from information_schema.columns where table_name='myTable'
I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.
I joined different tables and got a result like this:
result | process | goal | date | ------- ---------- ------ ----------- ok | process4 | 1 | 12.10.2013 bad | process1 | 2 | 13.10.2013 ok | process1 | 4 | 12.12.2013 good | process4 | 1 | 03.01.2014 ok | process1 | 3 | 10.04.2013 bad | process3 | 6 | 09.01.2014 bad | process4 | 3 | 30.12.2013 best |NULL| NULL
Now I want to count the results by counting the processes and group them by the result.
But it should be count the latest result per process only, e.g. for goal "1" just "good" at 03.01.2014. I solved that with a subquery (date=SELECT MAX(...)..).
But now the result "best" disappears, because that column has no date.
Secondly I want to count results for a specific process, e.g. for process4. Every goal has max. one process, with different dates. But one process could have more than one goal.
I want to have this result for process4:
count | result ------ ------- 1 | good 1 | bad 0 | ok 0 | best
But I got only:
count | result ------ ------- 1 | good 1 | bad
I have tried a lot, but nothing works.
The whole result (best, good, ok, bad) are stored in an other table and I joined it.
I have a DB of professors and information related with them. I created the cube, it consist of: Measures: Measure group Professors: Amount of projects (COUNT proj_id) Amount of pulications (COUNT pub_id) Amount of e_books (COUNT book_id) -------------- Measure group Projects: Distinct amount of projects (DISTINCT COUNT proj_id) -------------- Measure group Publications: Distinct amount of publications (DISTINCT COUNT pub_id) -------------- Measure group E_books: Distinct amount of e_books (DISTINCT COUNT book_id) -------------- Calculated measures: Amnt_Projects iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects]) Amnt_Publications (similar to the above one) Amnt_E_books (similar to the above one) --------------------------- Dimensions: dimPROFESSORS - prof_id -surname -name -gender dimPROJECTS - proj_id -type name -name dimPUBLICATIONS - pub_id -type name -name dimE_BOOKS - book_id -name Date_Projects -date_id -years Date_Publications -date_id -years Date_E_books -date_id -years
For example, when I browse the cube: prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects 1032------------------- 30 --------------------------1----------------1 1070-------------------90 --------------------------2----------------2 1111-------------------0 ---------------------------1----------------0 1137-------------------0 ---------------------------1----------------0 1234-------------------1404--------------------------9----------------9 1721-------------------504--------------------------7----------------7 2661-------------------85 --------------------------5----------------5 ...--------------------...---------------------------...----------------... 6999------------------- 20---------------------------1-----------------1 9956-------------------50---------------------------5-----------------5 Unknown----------------(empty)---------------------(empty)-----------0 Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of: Measures: Measure group Professors: Amount of projects (COUNT proj_id) Amount of publications (COUNT pub_id) Amount of e_books (COUNT book_id) -------------- Measure group Projects: Distinct amount of projects (DISTINCT COUNT proj_id) -------------- Measure group Publications: Distinct amount of publications (DISTINCT COUNT pub_id) -------------- Measure group E_books: Distinct amount of e_books (DISTINCT COUNT book_id) Calculated measures: Amnt_Projects iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects]) Amnt_Publications (similar to the above one) Amnt_E_books (similar to the above one) --------------------------- Dimensions: dimPROFESSORS - prof_id -surname -name -gender dimPROJECTS - proj_id -type name -name dimPUBLICATIONS - pub_id -type name -name dimE_BOOKS - book_id -name Date_Projects -date_id -years Date_Publications -date_id -years Date_E_books -date_id -years
For example, when I browse the cube: prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects 1032------------------- 30 --------------------------1----------------1 1070-------------------90 --------------------------2----------------2 1111-------------------0 ---------------------------1----------------0 1137-------------------0 ---------------------------1----------------0 1234-------------------1404--------------------------9----------------9 1721-------------------504--------------------------7----------------7 2661-------------------85 --------------------------5----------------5 ...--------------------...---------------------------...----------------... 6999------------------- 20---------------------------1-----------------1 9956-------------------50---------------------------5-----------------5 Unknown----------------(empty)---------------------(empty)-----------0 Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
im trying to display a category name in one column and then the number of books in that category in another column.
SELECT CATEGORY, COUNT(DISTINCT CATEGORY) "Category Total" FROM BOOKS; Gives me an error saying something about category not being a single group function. What am I doing wrong?
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')
I have a table like this. Each ConNo can have many boxes. The number of the box is an identfier, not a quantity.ie Box 3 = 1 box.A box may have a letter as a suffix or not.
I want to group by ConNo and total the boxes so I know how many Boxes are in each ConNo eg ConNo 200 5 boxes