SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'
[Code] .....
Here is my query against the table above:
SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL
[Code] .....
My Desired results are as follows:
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement
what is the logic in multiple "FROM" statements inside a single query?
View 1 Replies View RelatedHow can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
SELECT bms_id,email_address,COUNT(*)
INTO #temp
FROM emp_db
WHERE email_address IS NOT NULL
GROUP BY bms_id,email_address
ORDER BY bms_id DESC,COUNT(*) DESC
SELECT bms_id COUNT(*)
FROM #TEMP
GROUP BY bms_id
ORDER BY COUNT(*) DESC
How can i put these two statements into a single sql statement.
Thanks.
I have the following 3 SQL statements that need to be combined, ifpossible. The output of one feeds the input of the next. I need to viewall of the defined output fields (the output needs to be used in aCrystal Report).The SQL Follows:/* Input is ISBN (vendor_part_number) */QUERY_1 - returns 1 recordselect p.product_id, v.name, m.description, p.author, p.title,p.revision_number, p.copyright_edition, p.vendor_part_number,p.conforming_flag,m.code, mp.unit_price_product, mm.quota_pricefrom T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,T_MULTILIST_PRODUCT mp, T_MULTILIST m,T_MULTILIST_MEMBERSHIP mm where/* p.vendor_part_number == input */p.vendor_part_number = '0153364475' and p.medium_type ='TEXTBOOK' andp.product_id = pv.product_id and pv.type = 'CONTRACT' andpv.vendor_id = v.id andp.product_id = mp.product_id andm.code = mp.multilist_code and m.proclamation_year =mp.proclamation_yearand m.proclamation_seq_id = mp.proclamation_seq_id andm.code = mm.multilist_code and m.proclamation_year =mm.proclamation_yearand m.proclamation_seq_id = mm.proclamation_seq_id/* The above should return a single record */QUERY_2 - returns 2 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id =90321/* 90321 = result from above: pr.parent_product_id = p.product_id*/QUERY_3 - returns 18 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id in(90322, 90323)/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =p.product_id */Only 21 records are returned from these combined queries. I need accessto all of them even though there are 3 different resultsets, 2 of whichcontain the same fields. Is there a way to simplify this into a storedprocedure or a view that can take 1 input parameter? It needs to beused in a Crystal Report, which is limited in its handling of thesetypes of complex queries.
View 1 Replies View RelatedHi guys! Is there a way to combine these update statements?
Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @syain_No", cnn)
Dim update_phase2 As New SqlCommand(" UPDATE TE_shounin_zangyou SET " & " phase=2, phase_states2=06,syounin2_sysd=CONVERT(VARCHAR(10),GETDATE(),101) WHERE [syain_No] = @syain_No", cnn)
The same table is updated so I think it would be better to have just one update statement. But the problem is that, the first update statement retrieves values from another table, whereas the update values of the second statement is fixed. Is there a way to combine these two statements. I tried to do so but it does not update. Here's my code...
Dim update_phase As New SqlCommand("UPDATE TE_shounin_zangyou SET TE_shounin_zangyou.syain_No=TE_zangyou.syain_No, TE_shounin_zangyou.date_kyou=TE_zangyou.date_kyou, TE_shounin_zangyou.time_kyou=TE_zangyou.time_kyou FROM TE_zangyou WHERE TE_zangyou.syain_No = TE_shounin_zangyou.syain_No", cnn)
Please help me. Thanks.
Audrey
hello gang, Is it possible to combine sql update statements? something like:
UPDATE table_nameSET column_name = new_valueWHERE column_name = some_valueANDSET column_name = new_valueWHERE column_name = some_other_value
I am not sure if this is possible, but I was wondering if I can combine 2 SELECT statements so as to aquire a percentage..
I could be overthinking this....I am fairly new to SQL writing.
Here is an example of the 2 SELECT statements that I am using:
Code:
SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS' AND [Overall Rating] = 'Good'
Code:
SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS'
Within my output I am than taking the the data from the 1st query and dividing it by the 2nd query to get a percentage.
I was hoping that I could accomplish the same action within one SQL statement.
Thank you for your consideration!
I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question. Do you have any ideas or examples of how to solve this scenario?
View 9 Replies View RelatedI have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).
I have two statements that look like this:
SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
Each statement correctly aggregates the sums, but I need a record that shows me:
CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid
I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).
Any help is appreciated.
SELECT *
FROM dbo.empBenefits q
WHERE (StartDate IN
(SELECT TOP 2 STARTDATE
FROM EMPBENEFITS
WHERE EMPBENEFITS.employeeno = q.employeeno AND Benefitcode = 'HON'
ORDER BY startdate ASC))
I have this select statement working however I need to combine 2 records in a single row in a single table. The unique key is Employee No.
This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.
View 13 Replies View RelatedWith the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.
;WITH stored_procedures AS (
SELECT o.id,
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT id,proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name
Hello:
I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on.
productID SectionID statusID
10 1 0
10 2 1
10 3 2
10 4 1
10 5 3
10 6 1
11 1 0
11 2 1
11 3 2
11 7 3
11 8 3
Need to return two rows with the respective values for each section.
productID section1 section2 section3 section4 section5 section6 section7 section8
10 0 1 2 1 3 1
11 0 1 2 3 3
Any information or if you can point me in the right direction would be appreciated.
Thanks
Hi,I'm working on a system migration and I need to combine data from multiplerows (with the same ID) into one comma separated string. This is how thedata is at the moment:Company_ID Material0x00C00000000053B86 Lead0x00C00000000053B86 Sulphur0x00C00000000053B86 ConcreteI need it in the following format:Company_ID Material0x00C00000000053B86 Lead, Sulphur, ConcreteThere is no definite number of materials per Company.I have read the part ofhttp://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'TheIterative Method' but my knowledge of SQL is very limited and I don't knowhow to use this code to get what I need.Can anyone help me?
View 7 Replies View RelatedHi everyone,I really appreciate if anyone could help me with this tricky problemthat I'm having. I'm looking for a sample script to combine data inmultiple rows into one row. I'm using sqlserver. This is how data isstored in the table.ID Color111 Blue111 Yellow111 Pink111 GreenThis is the result that I would like to have.ID Color111 Blue, Yellow, Pink, GreenThere is no definite number of colors per ID. I have to use ID togroup these colors into one row. Therefore, ID becomes a unique keyin the table.Appreciate your help and time. Thank you in advance
View 1 Replies View RelatedHow to combine multiple rows to single rows for the below sql query.
SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME,
dbo.AccessLog.INOUT
FROM dbo.AccessLog LEFT OUTER JOIN
dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN
dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID
WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015')
ORDER BY dbo.AccessLog.EMPLOYEEID
The reult for the above query is:
RCDID | EmployeeID | LOGDATE | LOGTIME | INOUT
1 10763 01/04/2015 08:00 0
1 10763 01/04/2015 19:46 1
I need the result like the below
RCDID | EmployeeID | LOGDATE | IN | OUT
1 10763 01/04/2015 08:00 19:46
Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888
And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?
What is the single SQL statement to truncate the blank space on either side of data.
Ex.
Table1 has Name as column.
I have records filled with blank space on both side for Name field.
With one query I want to correct (truncate the leading and trailing space) the data.
How?
SQL Server 2005 SP2.
Thank you,
Smith
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
Hi,I am wondering if anyone has any examples of how to run multiple sql statements from a file using .net? I want to automatically install any stored procedures or scripts from a single file on the server when my web application runs for the first time. Thanks for your help in advance!
View 1 Replies View Related
Hi,
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too.
Basically those are SELECT statements.
I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report.
If possible, can anyone show me the format?
Thanks
I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot.
I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences.
First, the new flat file is missing two columns (not critical data).
Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns).
I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server.
Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!
I have write two query but its only work one at a time need your expertise what i am doing wrong.
[Category].Category AS project_type,
[SalesManager].SalesManager As Manager,
FROM [Category] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [Category].ID = Projects.Category
FROM [SalesManager] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [SalesManager].ID = Projects.SalesManagerID
I am trying to write a SQL statement query, that will calculate the SUM value in one table for a March period, and update that value to another table, here is what I have so far, but the compile error says Operation must use an updatable query.
UPDATE League_Totals SET League_Totals.March_Total = (SELECT SUM(Result_Value)
FROM Result_Details
WHERE LEAP_Date = "March_2014");
Is it possible to combine a detailed query with its related count and sum without using any #temp tables at all?
ex. select customerID, customerName,
(select count(orderID) from tblOrder where orderDate > '01/01/2003'
and orderStatus = 'active') as countActive,
(select count(orderID) from tblOrder where orderDate > '01/01/2003'
and orderStatus = 'inactive') as countInActive
from rfCustomers
something like that? I was heard SQL2k has some new feature like this, or a UDF may be required? Currently, I have to use #temp table to get it.
thanks
David
I have these 2 queries that I need to combine into one. What is the best way of doing it? This website is made up of 293 tables so it gets confusing.
(Query 1)
SELECT category_products.category, category_products.product, category_products.position, data.base_price, data.custom_description, models.manufacturer_id, models.custom_search_text, models.name, models.image_location
FROM category_products, data, models
WHERE category_products.category = '36'
AND category_products.product = data.model_id
AND data.model_id = models.id
AND data.active = 'y'
$manufacturer_id=$data["manufacturer_id"];
(Query 2)
SELECT inventory_types.manufacturer_id, inventory_types.default_vendor_id, vendors.id, vendors.name
FROM inventory_types, vendors
WHERE inventory_types.manufacturer_id = '$manufacturer_id'
AND inventory_types.default_vendor_id = vendors.id
This might be a question with an extremely easy answer.. I don't know but here I go.
I want a report with lets say
|A | B | C |
----------------
I can easily figure out the sql statements to find the columns A, B and C individually but how do I combine them?
so lets say I have
select cola as A from table1 where ....
select colb as B from table2...
They are not from the same table so I cannot combine them either (I cannot do select cola, colb from table1 etc.. )
How would I do this? Am I missing something?
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
The following query gets all the data I need except for one new field that I need which combines multiple fields and some text. Here is the query:
SELECT [Make Mods-Additions HERE].StockNumber AS ProductID, [Make Mods-Additions HERE].[Long Description], [Make Mods-Additions HERE].[Short Description], [Make Mods-Additions HERE].NEWwholeEachCost AS [Wholesale Each], [Make Mods-Additions HERE].Units, [Make Mods-Additions HERE].[Sale/CameoPrice] AS [Case Price], [Make Mods-Additions HERE].MinQty, [Make Mods-Additions HERE].Multiples, [Make Mods-Additions HERE].UPC, [Make Mods-Additions HERE].MSRP, [Make Mods-Additions HERE].[Availability Date], [Make Mods-Additions HERE].[Item Description (Detailed)] AS [Full Item Description]
FROM [Make Mods-Additions HERE]
WHERE ((([Make Mods-Additions HERE].Active)="YES"));
I need one more field named 'Rep Order Description' that concatenates the following:
[Short Description], "-$", [Wholesale Each], " ea, MSRP $', [MSRP]
It is important that the [Wholesale Each] and [MSRP] values are in 0.00 format (they are currency)
Example of output:
Short Description-$0.00 ea, MSRP $0.00
I have 2 Columns FirstName and LastName but i need to show it in UI as User Name ,that means i need to combine both First Name and Last name and display both as 1 field namely UserName ,How to query tht ? What shld i use?
View 2 Replies View Related