Friday, March 15, 2013

[T-SQL] SQL Queries for geting common values and uncommon values

[T-SQL] SQL Queries for geting common values and uncommon values

SQL Queries for geting common values and uncommon values

Posted: 15 Mar 2013 12:23 AM PDT

Hi...... I have one table in my database say for example "TableA" and the data is shown below.. ID Value 1 A [b] (common for all ids)[/b] 1 B [b](common for all ids)[/b] 2 A [b](common for all ids)[/b] 2 C 2 B [b](common for all ids)[/b] 3 A [b](common for all ids)[/b] 3 B ([b]common for all ids)[/b] 3 C 3 D My problem is i need two sql queries which will give the following two resultant sets as shown below First sql query result will be like this Common Factors in ID (1,2,3) A B Second query result will be Common Factors in ID (1,2,3) C (here c is not in id 1 so will be listed here) D Can any one please give the two queries please , just i need common factors in one set and uncommon factors in another set ..I need two SQL queries but Thanks and Regards Sankar

Inner join vs Left join

Posted: 14 Mar 2013 07:59 PM PDT

Hi Everyone,I'm experiencing something I don't expect and I'm hoping someone can shed some light. We are using entity framework and LINQ to query our SQL Server 2008 R2 database and it is generating the following (fairly ugly) SQL:[code="sql"]SELECT [Extent1].[ValuationDateID] AS [ValuationDateID], [Extent3].[TransactionRef] AS [TransactionRef], [Extent3].[Counterparty] AS [Counterparty], [Extent2].[BookName] AS [BookName], [Extent2].[Category] AS [Category], [Extent2].[Type] AS [Type], [Extent2].[LifeLicence] AS [LifeLicence], [Extent2].[Manager] AS [Manager], [Extent2].[TaxFund] AS [TaxFund], [Extent2].[HiPortName] AS [HiPortName], [Extent2].[AliasPortfolioName] AS [AliasPortfolioName], [Extent4].[ParallelShift] AS [ParallelShift], [Extent5].[Tenor] AS [Tenor], [Extent1].[Value] AS [Value]FROM (SELECT [fctSwapDualShift].[ValuationDateID] AS [ValuationDateID], [fctSwapDualShift].[Currency] AS [Currency], [fctSwapDualShift].[BookID] AS [BookID], [fctSwapDualShift].[HiPortNameID] AS [HiPortNameID], [fctSwapDualShift].[ContractTypeID] AS [ContractTypeID], [fctSwapDualShift].[TypeID] AS [TypeID], [fctSwapDualShift].[ManagerID] AS [ManagerID], [fctSwapDualShift].[TransactionID] AS [TransactionID], [fctSwapDualShift].[ParallelShiftID] AS [ParallelShiftID], [fctSwapDualShift].[SwapTenorID] AS [SwapTenorID], [fctSwapDualShift].[Value] AS [Value], [fctSwapDualShift].[ClientID] AS [ClientID] FROM [Reporting].[fctSwapDualShift] AS [fctSwapDualShift]) AS [Extent1]INNER JOIN (SELECT [vwAllMappingWithDescriptions].[BookID] AS [BookID], [vwAllMappingWithDescriptions].[ValuationDateID] AS [ValuationDateID], [vwAllMappingWithDescriptions].[BookName] AS [BookName], [vwAllMappingWithDescriptions].[Category] AS [Category], [vwAllMappingWithDescriptions].[LifeLicence] AS [LifeLicence], [vwAllMappingWithDescriptions].[TaxFund] AS [TaxFund], [vwAllMappingWithDescriptions].[Type] AS [Type], [vwAllMappingWithDescriptions].[HiPortName] AS [HiPortName], [vwAllMappingWithDescriptions].[AliasPortfolioName] AS [AliasPortfolioName], [vwAllMappingWithDescriptions].[Manager] AS [Manager], [vwAllMappingWithDescriptions].[RasHiPortCode] AS [RasHiPortCode], [vwAllMappingWithDescriptions].[LiquidityRiskClassification] AS [LiquidityRiskClassification], [vwAllMappingWithDescriptions].[IncludeForRisk] AS [IncludeForRisk] FROM [configuration].[vwAllMappingWithDescriptions] AS [vwAllMappingWithDescriptions]) AS [Extent2] ON [Extent1].[BookID] = [Extent2].[BookID]INNER JOIN [Reporting].[dimTransaction] AS [Extent3] ON [Extent1].[TransactionID] = [Extent3].[ID]INNER JOIN [Reporting].[dimParallelShift] AS [Extent4] ON [Extent1].[ParallelShiftID] = [Extent4].[ID]INNER JOIN [Reporting].[dimSwapTenor] AS [Extent5] ON [Extent1].[SwapTenorID] = [Extent5].[ID]WHERE (20130314 = [Extent1].[ValuationDateID]) AND (20130314 = [Extent2].[ValuationDateID])[/code]Trying to run this query yields:[color="FF0000"]Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.[/color]However, if I change the INNER JOIN on the Reporting.dimParallelShift to a LEFT JOIN, then the query returns the correct results. Here's the part I don't understand. The ParallelShiftID column on the Reporting.fctSwapDualShift fact table is NOT nullable. Furthermore, the dimParallelShift dimension as a primary key on the ID column and so it is unique. To my mind, in this case, the LEFT join and INNER join are logically equivalent but that obviously isn't the case. What am I missing?

Generate Script

Posted: 14 Mar 2013 06:58 PM PDT

Hi AllIs there anyway I can use the below script to generate CREATE DATABASE commands? [code="sql"]select DB_NAME(smf.database_id)DatabaseName,type_desc,smf.name,physical_name,size ,sd.collation_namefrom sys.master_files smfinner join sys.databases sdon db_name(smf.database_id) = sd.namewhere db_name(smf.database_id) not in ('master','model','tempdb','msdb')order by name[/code]Thanks

DateDiff help to verify my code is correct or not?

Posted: 14 Mar 2013 07:44 AM PDT

Hi Friends,I'm using DateDiff function -my requirement is -1) "SmartLaborII Job Posting End Date"" is NOT < ""SmartLaborII Work Order End Date"and my code is ---------not(datediff(day,[WorkForce_JobPosting].[Job Posting End Date_JP] ,[WorkForce_WorkOrder].[Work Order End Date_WO]) > 0)--------2) "SmartLaborII Work Order End Date"" is NOT greater than ""SmartLaborII Previous Work Order End Date"my code is -/// datediff(day,[WorkForce_WorkOrder].[Work Order End Date_WO] ,[WorkForce_WorkOrder].[Previous Work Order End Date_WO] ) >= 0//Need help from experts to verify if my code for the 2 requirements is correct ?Kind RegardsDhananjay

Get missing records

Posted: 14 Mar 2013 10:36 AM PDT

Hi, I have a table #TransDetailCREATE TABLE #TransDetail( ID INT IDENTITY(1,1) PRIMARY KEY, Number INT, TransDate SMALLDATETIME, TransTime INT, Site VARCHAR(100), Company VARCHAR(15) )INSERT INTO #TransDetail (Number,TransDate,Transtime,site,company)SELECT 1,'12/1/2012',0757,'MyCompany','COMP'UNIONSELECT 2,'12/1/2012',0757,'MyCompany','COMP'UNIONSELECT 3,'12/1/2012',0757,'MyCompany','COMP'UNIONSELECT 5,'12/1/2012',0757,'MyCompany','COMP'UNIONSELECT 1,'12/2/2012',0957,'MyNewCompany','COMPUSA'UNIONSELECT 2,'12/2/2012',0957,'MyNewCompany','COMPUSA'UNIONSELECT 5,'12/2/2012',0957,'MyNewCompany','COMPUSA'I want to to know the missing numbers for a particular TransDate,Site and companyExample For 'MyCompany' , Number 4 is missing. So I want 4,Date and Time of prev number imported , That is '12/1/2012',0757Date and Time of next number imported , That is '12/1/2012',0757,Site (Mycompany)ANDCompany(COMP) returnedSimilarly for 'MyNewCompany' Number 3 and 4 are missingSo I want Nunbers 3 ,4 returned along withDate and Time of prev number imported , That is '12/2/2012',0957Date and Time of next number imported , That is '12/2/2012',0957,Site (MyNewcompany)ANDCompany(COMPUSA) returnedPlease help

delete query SQL 2008 in Virtual machine setup?

Posted: 14 Mar 2013 11:19 PM PDT

Hi,I am going to delete the records for last 2 years, and total number of rows 31935523..Should I go for as below delete statement..column name yyyymm - varchar datatype and nonclusted index already exists..column name date filed - datetime and non-unique nonclusted index already exists[code="sql"]select ep_no,date,yyyymm, schd_shft,dayin, dayout,rtype,stype,etype,stn_in, stn_out,updated_on, inserted_on from CONTRACT_MUSTER where yyyymm between '201001'and ''201006''delete from from CONTRACT_MUSTER where yyyymm between '201001'and ''201006'godelete from from CONTRACT_MUSTER where yyyymm between '201006'and ''201012'go[/code]thanksananda

Populate sql table field descriptions programatically

Posted: 14 Mar 2013 11:49 PM PDT

I am trying to update my sql table field descriptions programatically, but I am getting an error.------------------------------use NCOSGOEXEC sp_updateextendedproperty @name = N'MS_Description', @value = 'Your description',@level0type = N'Schema', @level0name = 'NCOS', @level1type = N'Table', @level1name = 'NCOS_Data', @level2type = N'Column', @level2name = 'NC_Prospect_Name';Error:Server: Msg 15600, Level 15, State 1, Procedure sp_updateextendedproperty, Line 42An invalid parameter or option was specified for procedure 'sp_updateextendedproperty'.

What needs to consider when adding a password to the table

Posted: 14 Mar 2013 07:41 AM PDT

Hi There,Could you please help me out, i have table with 9 columns and about a million rows,i need to add primary key, so what things do i need to consider here,what if my table has no unique column / columns(composite), wat can do in this situationadding a id field (just 1 to .... so on) and then making this pk, does makes any sense?could you please help me out hereThanks a ton in advancedhani

Group and Count from a table

Posted: 14 Mar 2013 03:39 AM PDT

I have a following table and I need to group by each topic and count them.[b]SNo [/b] [b] Category[/b]1 ------- XML/HTML2 ------- ASP.NET3 ------- C#4 ------- ASP.NET/C#5 ------- C#/XML6 ------- HTML/ASP.NET7 ------- SQL8 ------- SQL/HTML9 ------- SQL/XML10 ------- XML11 ------- C#12 ------- ASP.NET13 ------- SQL14 ------- XML15 ------- SQL[b]Expected Result:[/b][b]Category[/b] [b]Count[/b]C# -------- 4XML -------- 5HTML ------- 3ASP.NET --- 4SQL ------- 5

Hierarchy example - Ken Henderson's book - not working

Posted: 23 Feb 2013 10:16 AM PST

I'm working on the hierarchy lesson in Ken Henderson's The Guru's Guide to Transact-SQL. He wants to show two things. First, how to write a query that populates a temporary table (#org_chart) with all possible chartdepth levels detected in the staff table. Secondly, how to write a query that shows all the boss/subordinate relationships regardless of the levels by which they are removed from each other. But, I am having trouble with the first part, the query for building the #org_chart table. I've written it below, verbatum from book. It is syntactically correctly but when I run it adds no subsequent rows to the #org_chart table. Will someone tell me what is missing? Run in this order, the DDL, query 1, and query 2, should produce a table with 40+ rows:[code="plain"] --DDL for creating and populating staff tablecreate table staff(employee int primary key, employee_name varchar(10),supervisor int null references staff (employee))insert staff values (1, 'groucho', 1),(2, 'chico', 1),(3, 'harpo', 2),(4, 'zeppo', 2),(5, 'moe', 1),(6, 'larry', 5),(7, 'curly', 5),(8, 'shemp', 5),(9, 'joe', 8),(10, 'curly joe', 9);--query #1--establishes first 10 rows of table, with first level of chart depthselect chartdepth=1, employee=o2.employee, supervisor=o1.employeeinto #org_chartfrom staff o1 inner join staff o2 on (o1.employee=o1.supervisor)--query #2 does not work for me--it is supposed to populate #org_chart with remaining levels of chartdepth. --A while loop repeats the insert as many times as necessary to process all levels in staff tablewhile (@@rowcount > 0) begininsert #org_chart (chartdepth, employee, supervisor)select distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)and o1.supervisor<>o1.employeeend--if following the execution of query #1 you run the below query about 4-5 times, it accomplishes what query #2 is supposed to be able to --accomplish with a While loopinsert into #org_chartselect distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)[/code]Why is query #2 not working? Thanks.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog