.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Problem in Query Writing

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server
declare @site table
 SiteId int

insert into @Site
select 100 union all
select 200 union all
select 300 

declare @Ticket table
	@TicketID int,
	@SiteID int

insert into @Ticket 
select 10,100 union all
select 20,200 union all
select 30,100 union all
select 40,300 union all
select 50,200 union all
select 60,100

declare @FeedBack table
	@FeedbackID int,
	@TicketID int,
	@CreationDate DateTime,
	@OutageTime DateTime,
	@ClearedTime DateTime
insert into @FeedBack
select 1,10,'10-21-2010 10:30:16.047','10-21-2010 20:23:00.000',NULL union all

select 2,20,'10-21-2010 11:30:16.047','10-21-2010 12:23:00.000',NULL union all

select 3,20,'10-21-2010 14:10:18.045',NULL,NULL union all

select 4,10,'10-21-2010 20:30:18.045',NULL,NULL union all

select 5,20,'10-21-2010 15:18.047',NULL,'10-21-2010 18:15.034' union all

select 6,10,'10-21-2010 22:16.047',NULL,'10-21-2010 23:15.034' union all

select 7,10,'10-21-2010 23:20.034','10-21-2010 23:22.035',NULL union all

select 8,10,'10-22-2010 01:02.002',NULL,'10-22-2010 02:03.004' 
I have 3 tables named FeedBack,Ticket and Site

The Feedback table depends on Ticket and Ticket Table Depends on Site

for one Site There may be Multiple Tickets and for one ticket there may be multiple feedbacks.

Here in the feedback table once an outage is happend for a Ticket then

View Complete Post

More Related Resource Links

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

Problem writing xml

hi, I have wrote the code Dim doc As New XmlDocument() Dim xmlDeclaration As XmlDeclaration = doc.CreateXmlDeclaration("1.0", "utf-8", Nothing) doc.AppendChild(xmlDeclaration) Dim feed As XmlNode = doc.CreateElement("feed") Dim feedatt As XmlAttribute = doc.CreateAttribute("xmlns") feedatt.Value = "http://www.w3.org/2005/Atom" feed.Attributes.Append(feedatt) Dim feedatt1 As XmlAttribute = doc.CreateAttribute("xmlns:g") feedatt1.Value = "http://base.google.com/ns/1.0" feed.Attributes.Append(feedatt1) doc.AppendChild(feed) Dim entry As XmlNode = doc.CreateElement("entry") Dim title As XmlNode = doc.CreateElement("g:title") title.InnerText = "Hello World" entry.AppendChild(title) feed.AppendChild(entry) doc.Save(Server.MapPath("test.xml")) The output of this code is this <?xml version="1.0" encoding="utf-8"?> <feed xmlns="http://www.w3.org/2005/Atom" xmlns:g="http://base.google.com/ns/1.0"> <entry> <title>Hello World</title> </entry> </feed>  As you can see in my code I wrote g:title but here it is title this is the problem why doesn't it make

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

Problem with Content Query Webpart and Custom Userfields

Dear Community, I am using Sharepoint 2007's content query webpart to access content types from 2 lists each contained in a different sitecollection. To achieve this, I used this tutorial: http://www.heathersolomon.com/blog/articles/CustomItemStyle.aspx. The tutorial worked great. By using the property CommonViewFields and modifing the XML Stylesheets accordingly, i could access almost every field. Even User Fieldtypes like editor and author were accessible by adding (editor,User) or (author,User) to the CommonViewFields property. But when I tried to access the custom made userfield "Username" in the same way (adding Username,User to the property), the webpart threw an error and couldn't be displayed anymore. When I changed it to Username,Text , no error occured but it naturally didn't show up, since it is the wrong fieldtype. I hope some of you can help me, since all my researches on this topic only brought up some more people with the same problem, but no solution to it. Thanks in advance, SoundofSilence

sql query conversion problem

Hi i have a query which gives me the value 25.255555555 but i need output as 25.25..what should i use to achieve that

sql query conversion problem

Hi i have a query which gives me the value 25.255555555 but i need output as 25.25..what should i use to achieve that

Problems writing a dynamic L2E query

I'm trying to re-work a L2E query to be more dynamic, but I'm not having much luck. Basically, I have two parameters (and many more to come, just laying the foundation), and the parameters are both optional from a user-endpoint.Originally I wrote this static expression:int personnelId = 1234; int divisionId = 1234; var results = (from a in ctx.Attendees from d in a.Divisions join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId && d.DivisionID == divisionId select new Attendee { firstName = a.FirstName, lastName = a.LastName });Attendee is a POCO. After I wrote this, I realized that if personnelId or divisionId weren't passed in (or if just one were passed in) I'd want a different result set. I'm conceptualizing the idea like this (doesn't compile, but you get the drift):var results = (from a in ctx.Attendees select new Attendee { firstName = p.FirstName, lastName = p.LastName }); if (personnelId != null) { results = (from a in results join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId select a); } if (divisionId != null) { results = (from a in results from d in a.Divisions where d.DivisionID == divisionId select a); } results = results.ToList();Doesn't work well though, because my

Sharepoint 2010 FullTextSqlQuery query problem with where condition

Hello, i´m developing a webpart under Sharepoint 2010, i need to launch some different query´s to filter data but i´m having problems. First of all, i launch this query and i get all the users; SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') ORDER BY "Rank" DESC with that results i fill different dropdownlist with each of the columns, then I can filter for each column. For example: I filter by PreferredName, Lastname or WorkEmail, with this query SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') AND (("LastName" Like '%MOSS%')) ORDER BY "Rank" DESC But when i try to use the columns Department, OfficeNumber and Jobtitle every query returns 0 rows. SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') AND (("Department" Like '%Dirección%')) ORDER BY "Rank" DESC The main thing is if i get all the data i see that these columns (officeNumber, Jobtitle, Department) are not empty. Some of them have values some of them don´t. All the columns have the property "Reduce storage recquirement for text propert

Query execution plan problem

Hi, I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed like this: select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM. The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates). For some time my query executes using the efficient query execution plan. Below a copy from Management Studio from an ad-hoc query: SELECT (0%) <- Clustered Index Seek  (100%) But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):                             Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)                            |                           \/ Fast forward (0%) <- Population quer

syntax problem passing parameter into Indexing Service Query

Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

MDX newbie query problem

Hi,   I have following measures - Period - NumberOfPeriods - Amount   I want to add a calculated member to a cube where I get the sum of amount for all entries with "Number of Periods" = Period. Is there a way to do that?

problem with cross apply query

Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you. SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC;

Weird query plan problem where total is over 100%

Hi All, I am looking at the query plan for a stored procedure and I am seeing things like 1400% on the execution plan, the stored procedure I believe is called within a loop quite a lot of times, but I don’t quite understand why the percentages will be over 100 for one section of the execution plan. Any explanation for this ? Thanks in advance.

Getting Query Time Out Problem in Particular Dimension?

We are getting query time out problem in particular dimension in SSAS. can any one help on this?  

problem with sql query.....

hello everyone, i have a table call log table and column which are loign time AND logout time. for example, the login time contain 13:15:19 and the logout time contain 14:52:33. how can i write a query that will get the different b/w the two time and display the result of the different.....thanks.....

Problem with SELECT COUNT query and parameters

Hello!I have a problem with SELECT COUNT query in ASP.net. I want to create CMS with articles which have categories (which have the option to be deleted). The problem is that I want to get the number of articles within the specified category so if there aren't any articles with the specified category I can proceed with the category deletion.I have the following code:protected void Page_Load(object sender, EventArgs e) { } protected void GridViewKategorije_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "Uredi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell ClanakID = odabraniRed.Cells[2]; string ID = ClanakID.Text; Response.Redirect("/Portal/Administracija/Kategorija.aspx?idKategorija=" + ID); } else if (e.CommandName == "Obrisi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell KategorijaID = odabraniRed.Cells[2]; String connString = WebConfigurationManager.ConnectionStrings["CMS"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); using (SqlC

query join problem

I am using CTE and joining it to other tables in my query.  The issue is I am not creating my joins correctly.  I am using the CTE twice but the calues in it should only appear one time on amount based on the amount type.  attached is the code
declare @dt datetime
set @dt = getdate();

With itmlvl as (





select distinct top 10000 dnc.EM_ITEM_NUM
			,dnc.amt as DNC 
			,pur.Amt as PUR_AMT
			,psr.AMT as PSR_AMT
			,pur.RBT_TYP_ID purRBT_TYP_ID
			,pur.DSCR purDSCR
			,psr.RBT_TYP_ID psrRBT_TYP_ID
			,psr.DSCR psrDSCR
 from geprs_dnc..T_Item_cost_approved dnc
		join reference..T_iw_em_item ref 
		on ref.em_item_num = dnc.em_item_num

		INNER JOIN itmlvl 
		ON itmlvl.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		INNER JOIN itmlvl pur
		ON pur.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		and pur.RBT_TYP_ID = 101
		INNER JOIN itmlvl psr
		ON psr.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		and psr.RBT_TYP_ID = 102

where dnc.COST_ID =32

So for PUR and PSR there should only be one amount for the given date range ...i've
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend