Ok, getting a little better at joins, but now need to take it two more steps.
Main table tblEquipments
Fields: ID, EquipmentType, Location, Status
Second Table tblEquipments_Group
Fields: equipmentID, categoryID, subCategoryID
(all are ints)
I joined these two together and got the info back I needed:
SELECT a.ID, a.Description, a.Status, a.Location,
from tblEquipments a
inner join tblEquipments_Group U on u.categoryID = a.EquipmentType
and u.subCategoryID = @type
Now, the a.status returns a number. What the status actually is is in tblStatus.
Fields: statusID (int) , Status (varchar).
So, the statusID matches a.Status.
I need to get the text from tblStatus in the field Status and replace the a.Status (which is a number still) with the text.
The other one, location, is the same situation.
I've been trying to do another join and am at an end on this.
I am trying to merge the selected contents of 2 tables by using a date field in both tables.
For example, in the AdventureWorks database of VS2005, I would like 3 columns: loginID, modifieddate and addressline1. Where the row has come from the Employee table, the address field should be blank, so its not really a join that I want (at least I dont think so).
This query only returns 2 colums: select loginid, modifieddate from HumanResources.Employee union all select addressLine1, modifiedDate from Person.Address
Is this possible, or do I need an additional common column that can be inner joined on the 2 tables? Can anyone help?
Hi, I have three tables named as BroadCastetails,PaymetMaster,MemberMaster. I have two query as mentioned below-- SELECT MemberMaster.FirstName, MemberMaster.LastName, BroadCastDetails.BroadCastName FROM BroadCastDetails INNER JOIN MemberMaster ON 5 = MemberMaster.MemberID AND BroadcastCreationDateTime between '01/01/2007' AND '12/12/2007' SELECT BroadCastDetails.ScheduledStartDateTime, BroadCastDetails.TotalCalls FROM BroadCastDetails UNION SELECT PaymentTransaction.TransactionDate, PaymentTransaction.CallsToCredit FROM PaymentTransaction
I want the result of the above two query in a GridView.How can I do that ? Its urgent...
How can I write a query that will return the following result to show the order details for customerid 1000 for a date range between 01/01/2003 and 01/08/2003
OrderDate Quantity FreeShipping ========= ======== ============ 01/01/2003 5 Y 01/03/2003 0 Y 01/04/2003 9 N 01/08/2003 14 N
Note that even if an order was not placed and the date was a freeshipping date, it is still displayed in the resultset
I am trying to normalize data using the unpivot transform. I have to unpivot using more than one key so I have a multicast feeding into two unpivot transforms then into a sort transform. This is where my problem starts - I have tried using a Merge Join (inner Join) transform but dont get the expected result.
My original data looks like this:
Pk_ID
Choice1
Choice2
Feedback1
Feedback2
10
a
b
x
y
After the mulitcast - unpivot - Merge Join, the expected result is: (pk_newID is an identity)
Pk_newID
fk_ID
Choice
Feedback
563
10
a
x
564
10
b
y
However with a Merge-Join (inner join on pk_ID) I get
Simple Explanation: I have a table with numbers 1-5, I want to left join a table with numbers 1-10, and show a sum... So since numbers 6-10 will not match anything, I will have a sum of zero for those rows. Simple, I thought!
SELECT SUM(ISNULL(RI.quantity,0)) AS Quantity, usr.conversionRate, usr.CorpType FROM ( SELECT 0 AS conversionRate, 1 AS CorpType UNION ALL SELECT .05, 1 UNION ALL SELECT .5, 1 UNION ALL SELECT 1, 1 UNION ALL SELECT 1.5, 1 UNION ALL SELECT 1.75, 1 ID UNION ALL SELECT 2, 1 UNION ALL SELECT 0, 2 UNION ALL SELECT .05, 2 UNION ALL SELECT .5, 2 UNION ALL SELECT 1, 2 UNION ALL SELECT 1.5, 2 UNION ALL SELECT 1.75, 2 ID UNION ALL SELECT 2, 2 ) usr LEFT OUTER JOIN Items I ON I.ConversionRate = usr.conversionRate LEFT OUTER JOIN Requests_Items RI ON I.ID = RI.itemID AND ISNULL(RI.impactMonth,RI.dueDate) >= '02/01/2008' and ISNULL(RI.impactMonth,RI.dueDate) < '03/01/2008' LEFT OUTER JOIN Corporations_Requests CR ON CR.requestID = RI.requestID LEFT OUTER JOIN Corporations C ON C.CorpID = CR.corporationID AND C.CorpType = usr.CorpType GROUP BY usr.CorpType, usr.conversionRate ORDER BY usr.CorpType, usr.conversionRate
I have no clue why this doesn't work. It appears that the UNION ALL(s) are being crossed joined. Any light shed on this would be greatly appreciated!
Hallo !I have 2 tables with similar construction.DataOLD and DataNEW.In my grid I need a JOINED table.On the one hand I need all the data from the old table and the datafrom then new table with the same id (left outer join works)But additionally I need all rows from the new table where no equivalentrows are in the old table.example:TableOLDdiameter amountOLD20 10050 20030 300TableNEWdiameter amountNEW20 50060 60070 120Wished Result:diameter amountOLD amoutNEW20 100 50050 200 NULL30 300 NULL60 NULL 60070 NULL 120Who can help me?Many Thanks.aaapaul
I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5and table B has field 1,2,3,4,5. I want to do a union on these. (I havedone so successfully if I stop here) I also want to join table C whichhas field 1,6,7,8,9. I would like to join on field 1 and bring in theother fields. I can join table C to A or B. I can union table A and Bbut I do not know how to both union A and B then join C. Can someoneplease help me? Thanks in advance.
Can i do a Full outer join on a union? and if so how?
I have this stored procedure. but then i was notified that they want to show all the sales reps, even if they dont have visits, and all the activities, even if neither has values. Right now this is what it shows:
Code Block ALTER PROCEDURE [dbo].[PROC_DAILY_ACTIVITY] (@Region_Key int=null) AS BEGIN
SELECT NULL as Progress_time, NULL As completed_time, NULL as Dial_in_date, NULL AS Customer_Code, Non_Customer_Activities.question_code, Non_Customer_Activities.description, Sales_Group.Region, Sales_Group.Name AS Territory_Name, Non_Customer_Activities.Que_Desc AS Store_Name, Non_Customer_Activities.Logged_Time AS TheDate, CONVERT(FLOAT,Non_Customer_Activities.Logged_Time) Float_Date, Non_Customer_Activities.response AS Response, NULL AS is_Visit_Fg FROM Qry_Sales_Group AS Sales_Group INNER JOIN (SELECT QH.question_code, Question_Header.description, CONVERT(datetime, DATEADD(day, QH.cycle_day - 1, P.start_date), 6) AS Logged_Time, SUBSTRING(QH.entity_code, 1, 5) AS SR_Code, QH.response, Territory_In_Sales_Responsible.Territory_Code AS SR_Territory_Code, 'Not Customer Related' AS Que_Desc FROM question_history AS QH INNER JOIN period AS P ON P.period_code = QH.period_code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code = SUBSTRING(QH.entity_code, 1, 5) COLLATE Latin1_General_CI_AS INNER JOIN questions AS Question_Header ON Question_Header.question_code = QH.question_code WHERE (QH.entity_code LIKE '%.USER%') AND (Question_Header.question_code IN ('AME01', 'ASE01', 'ACO01', 'ALU01', 'AOS01', 'APH01', 'ATR01', 'ATE01', 'ACR06', 'ACR05', 'ACR02', 'ACR03', 'ACR08', 'ACR07')) AND (CONVERT(datetime, DATEADD(day, QH.cycle_day - 1, P.start_date), 6) = case when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3 else datediff(dd,0,GetDate()) - 1 end )) AS Non_Customer_Activities ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code WHERE Region_Key=@Region_Key UNION ALL SELECT in_progress_time, completed_time , dial_in_date, Customer_Activities.Customer_Code, NULL AS Expr1, NULL AS Expr2, Sales_Group.Region, Sales_Group.Name AS Territory_Name, Customer_Activities.Customer_Name AS Store_Name, Customer_Activities.Logged_Time AS TheDate, CONVERT(FLOAT,Customer_Activities.Logged_Time) Float_Date, NULL AS Response, Customer_Activities.Is_Visit_Fg FROM Qry_Sales_Group AS Sales_Group INNER JOIN (SELECT DISTINCT in_progress_time,completed_time,dial_in_date, V.visit_date AS Logged_Time, Customer_Code, customer_name AS Customer_Name, Territory_In_Sales_Responsible.Territory_Code AS Cust_Territory_Code, 1 AS Is_Visit_Fg FROM Qry_visits AS V INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code = V.sales_person_code COLLATE Latin1_General_CI_AS INNER JOIN FSSRC.dbo.Communication_statistics as Communication ON Communication.sales_person_code=Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS WHERE (CONVERT(datetime, CONVERT(Varchar, V.visit_date, 110)) = case when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3 else datediff(dd,0,GetDate()) - 1 end )
) AS Customer_Activities ON Sales_Group.Code = Customer_Activities.Cust_Territory_Code WHERE Region_Key=@Region_Key end
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr, FROM plan2008 GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr FROM fsp_auftrag GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
I have a query which wants to union join the data. no matter how many times I tried, I got an error. How to change my union query?
select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT (Select distinct b.lev5 from bf_data a inner join bf_orgn_cnsl_tbl b on a.bf_orgn_cd = b.bf_orgn_cd
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100 101 Vol charity 200 101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error: Server: Msg 207, Level 16, State 3, Line 2 Invalid column name 'PRODUCT_CODE'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'.
SELECTp.ID, p.PRODUCT_CODE as Chapt, p.product_code as Dues, p.product_code as Vol from ( SELECT ID, product_code as Chapt, Null as dues, Null as Vol from subscriptions where prod_type = 'chapt' AND BALANCE > 0
union all
SELECT ID, Null as chapt, product_code as Dues, Null as vol from subscriptions where prod_type = 'dues' AND BALANCE > 0
union all
SELECT ID, Null as chapt, Null as dues, product_code as Vol from subscriptions where prod_type = 'vol' AND BALANCE > 0
Struggling to come up with an overall sum for each individual user when using the below code
SELECT Practice.ibvStaffCategorisation.StaffId ,Practice.ibvStaffTotalsCL2Y.Period ,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes ,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
[Code] ....
As I am using union all it shows two values for each user, the problem is I am getting the data via lookup like below
I don't think I can use a Sum within a lookup so at the moment it is only bringing through the first value for each user but I would like it to bring through both.
Is there a way to do this within the dataset or via the query in the table?
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table] UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
SELECT * FROM A INNER JOIN B ON A.AId = (SELECT B.BId FROM B WHERE (B.AId = 4))
I have two tables Table A has several columns and B has just two columns B.AId and B.BId.
I will pass B.AId as a parameter and that will return more than one B.BId values. Then I need to create a join where A.AId matches B.BId.
I tried the above SQL but it says subquery can't return more than one value. In my case it must return more than on value. How can I construct the SQL query. Please Help. Thanks
I’m attempting to use a Left Join to pull information from a table that contains my objectives and place in with information from a Summary table. I use one where clause per Left Join, that defines which rows from the objective table I want that left join to represent (for instance “(mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL )� ). I have 7 left joins and where statements.
The problem I’m having is that the where clause are removing all rows where the Left Join does not result in a match, despite the fact that I have the OR IS NULL in the where clause.
Can anyone help!
The full SQL is as follows:
SELECT DATE_DAY , SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , FORCE_STATUS , NUM_CREATED , NUM_OPEN , NUM_AWAITING_DISPATCH , NUM_OPEN_IN_DUE_DATE , NUM_DISPATCH_TO , DURATION_DISPATCH_TO , SLA_DURATION_DISPATCH_TO , SLA_DURATION_DISPATCH_TO_G_L , MET_SLA_DISPATCH_TO , mdt.SLA AS SLA_MET_DISPATCH_TO , mdt.SLA_G_L AS SLA_MET_DISPATCH_TO_G_L , C_NUM_DISPATCH_TO , C_DURATION_DISPATCH_TO , C_SLA_DURATION_DISPATCH_TO , C_SLA_DURATION_DISPATCH_TO_G_L , C_MET_SLA_DISPATCH_TO , mdt.SLA AS C_SLA_MET_DISPATCH_TO , mdt.SLA_G_L AS C_SLA_MET_DISPATCH_TO_G_L , NUM_DISPATCH_FROM , DURATION_CREATE_TO_DISPATCH_FROM , NUM_DISPATCH_TO_TO_FROM , DURATION_DISPATCH_FROM , SLA_DURATION_DISPATCH_FROM , SLA_DURATION_DISPATCH_FROM_G_L , MET_SLA_DISPATCH_FROM , mdf.SLA AS SLA_MET_DISPATCH_FROM , mdf.SLA_G_L AS SLA_MET_DISPATCH_FROM_G_L , C_NUM_DISPATCH_FROM , C_DURATION_CREATE_TO_DISPATCH_FROM , C_NUM_DISPATCH_TO_TO_FROM , C_DURATION_DISPATCH_FROM , C_SLA_DURATION_DISPATCH_FROM , C_SLA_DURATION_DISPATCH_FROM_G_L , C_MET_SLA_DISPATCH_FROM , mdf.SLA AS C_SLA_MET_DISPATCH_FROM , mdf.SLA_G_L AS C_SLA_MET_DISPATCH_FROM_G_L , NUM_CLOSED , DURATION_WIP_TO_RESOLVE , SLA_WIP_TO_RESOLVE , SLA_WIP_TO_RESOLVE_G_L , MET_SLA_WIP_TO_RESOLVE , wip.SLA AS SLA_MET_WIP_TO_RESOLVE , wip.SLA_G_L AS SLA_MET_WIP_TO_RESOLVE_G_L , DURATION_MTTR , SLA_MTTR , SLA_MTTR_G_L , MET_SLA_MTTR , mttr.SLA AS SLA_MET_MTTR , mttr.SLA_G_L AS SLA_MET_MTTR_G_L , NUM_RESOLVE_FD , rfd.SLA AS SLA_RESOLVE_FD , rfd.SLA_G_L AS SLA_RESOLVE_FD_G_L , NUM_DISPATCH_NTF , ntf.SLA AS SLA_DISPATCH_NTF , ntf.SLA_G_L AS SLA_DISPATCH_NTF , AR_NUM_MET_DUE_DATE , ar.SLA AS AR_SLA_MET_DUE_DATE , ar.SLA_G_L AS AR_SLA_MET_DUE_DATE FROM ( SELECT DATE_DAY , CASE WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN (CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END) WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN (CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END) ELSE SEVERITY END AS SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END AS FORCE_STATUS , sum( NUM_CREATED ) AS NUM_CREATED , sum( NUM_OPEN ) AS NUM_OPEN , sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH , sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE , sum( NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO , sum( DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO , max( SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO , max( SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L , sum( MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO , sum( C_NUM_DISPATCH_TO ) AS C_NUM_DISPATCH_TO , sum( C_DURATION_DISPATCH_TO ) AS C_DURATION_DISPATCH_TO , max( C_SLA_DURATION_DISPATCH_TO ) AS C_SLA_DURATION_DISPATCH_TO , max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS C_SLA_DURATION_DISPATCH_TO_G_L , sum( C_MET_SLA_DISPATCH_TO ) AS C_MET_SLA_DISPATCH_TO , sum( NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM , sum( DURATION_CREATE_TO_DISPATCH_FROM ) AS DURATION_CREATE_TO_DISPATCH_FROM , sum( NUM_DISPATCH_TO_TO_FROM ) AS NUM_DISPATCH_TO_TO_FROM , sum( DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM , max( SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM , max( SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L , sum( MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM , sum( C_NUM_DISPATCH_FROM ) AS C_NUM_DISPATCH_FROM , sum( C_DURATION_CREATE_TO_DISPATCH_FROM ) AS C_DURATION_CREATE_TO_DISPATCH_FROM , sum( C_NUM_DISPATCH_TO_TO_FROM ) AS C_NUM_DISPATCH_TO_TO_FROM , sum( C_DURATION_DISPATCH_FROM ) AS C_DURATION_DISPATCH_FROM , max( C_SLA_DURATION_DISPATCH_FROM ) AS C_SLA_DURATION_DISPATCH_FROM , max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS C_SLA_DURATION_DISPATCH_FROM_G_L , sum( C_MET_SLA_DISPATCH_FROM ) AS C_MET_SLA_DISPATCH_FROM , sum( NUM_CLOSED ) AS NUM_CLOSED , sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE , max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE , max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L , sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE , sum( DURATION_MTTR ) AS DURATION_MTTR , max( SLA_MTTR ) AS SLA_MTTR , max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L , sum( MET_SLA_MTTR ) AS MET_SLA_MTTR , sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD , sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF , sum( AR_NUM_MET_DUE_DATE ) AS AR_NUM_MET_DUE_DATE FROM CTS.dbo.NDC_Scorecard_Summary_Table t WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-30,1) as datetime) AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime) AND upper(EQ_REGION) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' ) AND upper(EQ_MARKET_CLUSTER) IN ( 'NEW ENGLAND','GEORGIA' ) GROUP BY DATE_DAY , CASE WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN (CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END) WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN (CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END) ELSE SEVERITY END-- AS SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END-- AS FORCE_STATUS ) t LEFT JOIN CTS.dbo.NDC_SLAs mdt ON t.SEVERITY=mdt.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs mdf ON t.SEVERITY=mdf.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs wip ON t.SEVERITY=wip.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs mttr ON t.SEVERITY=mttr.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs rfd ON t.SEVERITY=rfd.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs ntf ON t.SEVERITY=ntf.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs ar ON t.SEVERITY=ar.SLA_SEVERITY WHERE (mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL ) AND (mdf.SLA_NAME = 'Duration NDC to Field % Met SLA' OR mdf.SLA_NAME IS NULL ) AND (wip.SLA_NAME = 'Duration Dispatch Out % Met SLA' OR wip.SLA_NAME IS NULL ) AND (mttr.SLA_NAME = 'Total MTTR % Met SLA' OR mttr.SLA_NAME IS NULL ) AND (rfd.SLA_NAME = '% Resolved 1st Dispatch' OR rfd.SLA_NAME IS NULL ) AND (ntf.SLA_NAME = '% Dispatched NTF' OR ntf.SLA_NAME IS NULL ) AND (ar.SLA_NAME = '% Met Due Date' OR ar.SLA_NAME IS NULL )
I have two tables populated during the use of an application to loguser events and application states. They are named "EventTable" and"StateTable" and the structures follow:EventTable:ID EventTimeStep EventID-- ------------- ---------1 5 E12 22 E23 56 E3StateTableID StateTimeStep StateID-- ------------- -------1 1 S12 39 S2I want to perform a query that reports the StateID of the applicationat the time that each event was logged to the EventTable. The desiredoutput is:ID TimeStep EventID StateID-- -------- ------- -------1 5 E1 S12 22 E2 S13 56 E3 S2I have tried to create a query with an INNER JOIN where the value forthe StateID output field comes from the last row in the StateTableWHERE StateTable.TimeStep <= EventTable.TimeStep and where I use aGROUP_BY EventTable.ID to merge the following rows from the join:3 56 E3 S13 56 E3 S2However, the closest I can get is a query that gives me the wrongstate when applying the GROUP BY clause3 56 E3 S1I also think that the queries I have written is slow and inefficient.Is there a better way to perform this query or is my database designfatally flawed?Thanks,Adam Nemitoff
I am trying to use the merge join example in the following link. To import new records only.
http://www.sqlis.com/311.aspx
The problem is that for some unkown reason the join is not woeking correctly. One of the records is incorrectly showing a NULL on the output. This would indicate that it would be a new record, but it is not it already exists in the new table.
I created a dummy table in SQL and executed the same join and I always get the right answer. What the heck could be wrong?
For example. Table A has 20 records Table B has 3 records. Table B has the new records I want to import into Table A. The package runs corectly the first time, only importing the 3 new records. Then the next time the package runs it shows 1 of the 3 records as being new still, and tries to import the record causing a PK error. Adding a watch to the MERGE output shows that the one record has a NULL on the join.
THIS LOOKS LIKE A LOT BUT I JUST WANTED TO GIVE ALL INFORMATION NEEDED FOR YOU TO EASILY ASSIST ME. I am not good with SQL so please bare with me. I will give a dumbed down table for us to work with:timestamp - DATA1 - DATA2 - MAXVALUENEEDED - DATA32006/8/12 12:00 123 125 1289 121 2006/8/12 12:05 124 128 1291 1232006/8/15 12:00 130 128 1190 1242006/8/15 1:00 131 127 1196 123 The columns labeled DATA do not really matter. The only important ones are timestamp and MAXVALUENEEDED but I do need the query to return all columns. What the query has to do: The query needs to find the MAX of MAXVALUENEEDED for a given DAY. Example Output- 2006/8/12 124 128 1291 1232006/8/15 131 127 1196 123As you can see the MAX values were grabbed for each given day and the unique records were returned on a per day basis. My current SQL Query is: SELECT CONVERT(varchar, timestamp, 101) AS Time, CONVERT(int,LV50AEPM_AMPS_A_VAL0) AS AMPS_A, CONVERT(int,LV50AEPM_AMPS_B_VAL0) AS AMPS_B, CONVERT(int,LV50AEPM_AMPS_C_VAL0) AS AMPS_C, CONVERT(int,LV50AEPM_KVA_TOT_VAL0) AS KVA_TOTAL, CONVERT(int,LV50AEPM_KW_TOT_VAL0), CONVERT(int,LV50AEPM_VLL_CA_VAL0), CONVERT(int,LV50AEPM_VLL_BC_VAL0), CONVERT(int,LV50AEPM_VLL_AB_VAL0) FROM Table_1 T1 WHERE CONVERT(int,T1.LV50AEPM_KVA_TOT_VAL0) = (SELECT MAX(CONVERT(int,T2.LV50AEPM_KVA_TOT_VAL0)) FROM Table_1 T2 WHERE CONVERT(varchar,T1.timestamp,101) = T2.timestamp) ORDER BY Time ASC The problem is it only returns the max value for one day instead of all days. Can anyone help me correct this? Thanks in advance for your help.
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***
I have two employee tables called EmpA and EmpB.Each table has the same attributes of Employee ID and Email address.I do an inner join on email address like this:
select * from EmpA inner join EmpB on EmpA.email = EmpB.email where EmpB like '%@mydomain.com'
I now want to modify the above where I want to output rows such that
I am getting different results with LEFT outer join operator and *= operator. With *= I am getting the expected results. Can anyone look at SQL and tell what I am doing wrong?
SQL with Left Outer join operator:
select CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.line#, WeeklyMetricsFormat.MetricsType, WeeklyMetricsFormat.Metrics, WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol ) else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end else convert (varchar, CurrentWeekFinMetrics.displayCol ) end from WeeklyMetricsFormat LEFT JOIN CurrentWeekFinMetrics on (WeeklyMetricsFormat.Line# = CurrentWeekFinMetrics.Line#) where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen' order by CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.Line#
SQL with *= operator select CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.line#, WeeklyMetricsFormat.MetricsType, WeeklyMetricsFormat.Metrics, WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol ) else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end else convert (varchar, CurrentWeekFinMetrics.displayCol ) end from WeeklyMetricsFormat , CurrentWeekFinMetrics where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen' AND (WeeklyMetricsFormat.Line# *= CurrentWeekFinMetrics.Line#)
For Left outer join operator, I am getting 54 rows, *= I am getting 69 rows.
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
