I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:
tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end
But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:
I am wanting to show Total Quantity of Presentations2 for each EmpVol and TypeofPresentation, even if there are no Presentations done in TypeofPresentation or if an Employee did not do any Presentations.
I am close with the query below - but not getting back exactly what I want - Ideas.
SELECT ISNULL(Presentations2.Quantity, 0) AS Quantity, TypeofPresentation.Typeofpresforrpt, EmpVol_2.LastName, EmpVol_2.FSSTVOLUNTEER FROM Presentations2 INNER JOIN EmpVol ON Presentations2.ID = EmpVol.ID RIGHT OUTER JOIN TypeofPresentation ON Presentations2.TypePresINT = TypeofPresentation.TypePresINT CROSS JOIN EmpVol AS EmpVol_1 CROSS JOIN EmpVol AS EmpVol_2 GROUP BY TypeofPresentation.Typeofpresforrpt, EmpVol_2.LastName, EmpVol_2.FSSTVOLUNTEER, ISNULL(Presentations2.Quantity, 0) HAVING (EmpVol_2.FSSTVOLUNTEER = N'FSST')
I have the following 2 tables:(BATCHES)BatchID [int] KEYID [int]OrderID [int]Action1DateTime [datetime]Action2DateTime [datetime]Action3DateTime [datetime]Action4DateTime [datetime]Action5DateTime [datetime]Action6DateTime [datetime]Action7DateTime [datetime]Action8DateTime [datetime](ORDERS)OrderID [int] KEYProductionLineID [int]RecipeID [int]OrderAmount [int]Batches.Action1DateTime to Batches.Action8DateTime can have several entrieseach day.I need a query to count all Batches.Action1DateTime to allBatches.Action8DateTime for each day in a specified period.I also need to specifically use where clauses for Orders.OrderID and/orOrders.RecipeID.I need the data to draw a graph for each ActionXDateTime as a function ofdate.Any help appreciated./Henrik
I have to retrieve a cross join between languages and language-skills for more person in a matrix (grid) report.
E.g. i like to ritrieve the TOP 2 people in each "cell" of the grid (order by name). There are 3 people in cell "ITALIAN / very good". So i have to retrieve only the top 2.
In table tEmployee i have the people names (john, anna, michael) In table tLanguage i have all language (english, italian, german) And in table tLanSkill i have the language rating (bad, good, very good)
How can I achieve the same effect as a cross join (since the merge operator doesn't have a cross join)?
Situation is this... a flat file has some header and footer information that I need to keep and attach to each row. So for simplicity sake of an example lets just say header has only 1 thing we care about - a row that says DATE=01/01/06.
I take the file and run a split to split into "Date" "Data" and "other" (other has all the throwaway rows in header and footer I don't care about). Then I use a derived column object to get all the columns out of the "Data". Finally I want to add that Date metadata back to every row in the data...
I thought this would be an easy thing to do.. but I can't seem to figure out how to duplicate that Date info into every row.. Hopefully I am overlooking something simple.
I have the following tables:members--------------member_idmember_tpc_id ( = tpc.tpc_id)tpc------tpc_idcourse------------course_idtpc_assignment---------------------------tpc_assignment_idcourse_idenrollment-------------------member_idcourse_idenrollment_status Now I want to select all members where member_tpc_id>0 and get the enrollment_status of each member in each course where course_id IN (Select course_id From tpc_assignment)Now what i did was get all the members and then all the courses and did a cross join between them. There are about 1900 members and 80 courses and when I do a cross join I get 1900*80 rows (152000) and the status of each member for all the 80 courses. If not enrolled it returns Not Enrolled (i have a UDF which takes a member_id and a course_id and returns the status). The BIG problem is that its taking about 6-8 mins to run the query and as a result its timing out on the aspx page. Can someone please tell me how I can do what i am trying to do without using the cross join because I suspect its the culprit here. The query I came up with is Select *, dbo.returnStatus(temp1.user_id, temp2.course_id) As Status, (Select tpc_title From tpc Where tpc_id = temp1.member_tpc_id) As Tpc_Title From (Select member_id As user_id, member_name, member_tpc_id From members Where member_tpc_id> 0 And organization_id = '1' )temp1 cross join (Select course_id As course_id, course_title As course_title From course Where course_id IN (Select course_id From tpc_assignment Where tpc_requirement_id IN (Select tpc_requirement_id From tpc_requirement) And course_id<>0 And organization_id = '1') )temp2 Order By member_name, Tpc_TitlePlease help. Thank you.
Hello, I have a query that returned much more results than it should. I believe this is because it was a cross join instead of inner join (because I forgot to map a temporary relationship in the designer of SQL Server Express; I think this is what the problem may have been, but I can't remember), but could it be because the join criteria is because it is an nvarchar(MAX)? I did the join on two nvarchar(MAX) fields. The table has 31102 rows in both tables, and it would pull back the first entry in the first table, and all 31102 rows in the second table, and so it would pull back 31102 rows for each entry in the first table. Is that only because of the cross join, or maybe because of nvarchar(max)?
Hey All... Got a View question. Have 2 tables: #1 Currencies |CCY_Name|CCY_Code|
#2 Rates |CCY1|CCY2|CCY3|...etc|Active| -> where the Columns CCY# = the Records in #1
How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).
I populate the first table with a litst of stores that offer all desired items.
I populate the second table with a list of vendors, the item is, and cost avaiable at each of the stores in the first table.
What I would like is to output all possible the store and vendor combos ordered by combined price.
So, for instance, I have 3 products, A B and C. Store X has A and B by vendor G, and A B and C by vendor H. I want the output to have all iterations of (Store, Product, Vendor, Price) grouped in order of total price. So...
X A G X B G X C H
X A G X B H X C H
X A H X B G X C H
X A H X B H X C H
ordered by each group's combined price.
For some reason, I can't get this straight in my head. Must need more coffee.
I need to pickup a tax rate, that is stored on a 1 record file. I would like to avoid using the CROSS JOIN. Is there a way to SELECT the record and set a Variable = to the tax rate so I can pickup the rate in another SELECT statement on each record?
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity ----------------------- Red 10 100 Red 12 200 Red 14 300 Blue 10 400 Blue 12 500 Blue 14 600 Green 10 700 Green 12 800 Green 14 900 Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16 ------------------------------------- Red 100 200 300 0 Blue 400 500 600 0 Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that -- two queries against a columnstore index that each execute in batch mode -- will continue to execute in batch mode when inner joined. -- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows IF OBJECT_ID('dbo.IntCount') IS NOT NULL DROP FUNCTION dbo.IntCount;
select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/11/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/12/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/13/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/14/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/15/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/16/2006' group by task_date
My out put is Date Program Design Upload Testing Meeting Others 2006-09-11 00:00:00.000 42 0 0 8 2 1 2006-09-12 00:00:00.000 77 0 0 4 0 0 2006-09-13 00:00:00.000 56 0 0 8 0 1 2006-09-14 00:00:00.000 63 0 0 6 0 1 2006-09-15 00:00:00.000 63 0 0 6 0 1
Now i want in below format 2006-09-11 2006-09-11 etc
From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF
XSection (Width, Height, Flange, Leg, LegCount)
I tried the following to get a pivot table but it does not give a single row but 5.
SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width, CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height, CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange, CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg, CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp ON tsp.fkProperty = sp.Property WHERE tsp.fkTemplate = 1
So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?
Hi all,I have a table in this formatcolname1 colname2 colname3col1data1 col2data1 col3data1col1data2 col2data2 col3data2col1data3 col2data3 col3data3col1data4 col2data4 col3data4I want to display it in this formatcolname1 col1data1 col1data2 col1data3 col1data4colname2 col2data1 col2data2 col2data3 col2data4colname3 col3data1 col3data2 col3data3 col3data4Basically rotate it through 90 degrees clockwise and flip it over :)I'm pretty sure this is done by using a crosstab query and or aderived table or temp table. The problem is I use a crosstab query toget the original data into the first format. I've been strugglingtrying to get the ouptput into the second format for over a day nowand just can't seem to get it to work. Can anyone give me any pointerson the general solution to this?I hope this makes sense. Thanks for the help.
Hi All, The problem is about cross reference. 1. I have a third party cross reference store procedure SimpleXTab CREATE PROCEDURE [dbo].[SimpleXTab2] @XField varChar(50), @XTable varChar(100),@XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(50), @XRow varchar(300),@ResultTable varchar(100) ASDeclare @SqlStr nvarchar(4000)Declare @tempsql nvarchar(4000)Declare @SqlStrCur nvarchar(4000)Declare @col nvarchar(100) set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + '] ' + @XWhereString + ' Group By [' + @XField + ']' /* select @sqlstrcur */exec sp_executesql @sqlstrcur
declare xcursor Cursor for Select * from ##temptbl_Cursor open xcursor Fetch next from xcursor into @Col While @@Fetch_Status = 0Begin set @Sqlstr = @Sqlstr + ", " set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col + "' then [" + @XFunctionField + "] Else 0 End) As [" + @Col + "]" ,'') set @Sqlstr = @tempsql Fetch next from xcursor into @Col End /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */ set @tempsql = 'Select ' + @XRow + ', ' + @Sqlstr + 'into ' +@ResultTable+' From ' + @XTable + @XWhereString + ' Group by ' + @XRowprint @tempsql set @Sqlstr = @tempsql Close xcursor Deallocate xcursor set @tempsql = N'Drop Table ##temptbl_Cursor' exec sp_executesql @tempsqlprint @tempsql /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */print @sqlstr exec sp_executesql @Sqlstr if @@rowcount = 0 select 'No Records found'GO 2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function. how can i modify SimpleXtab to avoid using math function but still can generate cross reference. exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'
I am currently building a website to deal with different product information and sales with php. I am using SQL to sort the database and pull out information.
The final thing i need to do is work out the total revenue of each product however the problem i am having is that the 'Price' column and 'SalesVolume' column are in two different tables and they need to be multiplied together.
The two tables and column headings are as follows:
Product ID Name Price
MonthlySales ID ProductCode Month Year SalesVolume
(ID and ProductCode are linked together in a relationship)
I cannot see anything wrong with the syntax in my query however i believe there is.
Here is the query I am using:
Code: "SELECT SUM(Products.Price * SUM(MonthlySales.SalesVolume)) as revenue FROM Products INNER JOIN MonthlySales ON(Products.ProductCode = MonthlySales.id) GROUP BY Products.ProductCode";
Is it possible to create cross table query via SQL Express 2005 since it's possible to do with MS Access?
Sample query in MS Access:
TRANSFORM Count([tip]) AS [The Value] SELECT [sifra], [naziv] FROM naselja GROUP BY [sifra], [naziv] PIVOT [opstina] Is there any soultion how to make same or at least identical SQL query expression which will behive like MS Access ones?
I was writing a query using both left outer join and inner join. And the query was ....
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
Hello, I have an SQL problem that is hard to describe so here's an example scenario:
a) company has a personnel database, identifying each person in the company and their assigned role(s).
b) tblPersonnel has a row per person, key is 'PersonnelID' c) tblRoles is a list of all the roles in the company (administrator, clerk, shipper, truck-driver, etc.), key is 'RoleID'
d) tblPersonnelRoles is a cross-reference of all the roles assigned to each person - in simple format of:
PersonnelID | RoleID
Given all the above, I need to have a select statement that produces one row per person, and list all the fields from tblPersonnel AS WELL AS identifies each role assigned to the person.
I'm no SQL guru .. is this even possible?
I know there are alternative solutions (e.g., to write code that for each Personnel does a lookup in Roles) but for one particular situation I need a single SQL statement, or even Stored Procedure.
Thanks in advance, Rick Piovesan Detaya Corp Aurora, Ontario, Canada
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status] ,[final uplift status], [final rank], [final uplift percentage] ,practiceID=row_number() over (partition by [practice unit name] order by Metricname) ,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
