.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

Help :Is this Query right? How to Get the Result: Which to use union OR join AND How?

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server
Can you please help me?


SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T1_TIME DESC

T1_COUNT ---------- T1_TIME
3 ---------------- 2010-09-25
4 ---------------- 2010-09-18
2 ---------------- 2010-09-11


SELECT COUNT(T1.ID) AS T2_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw,T1.TIME)), T1.TIME),101) AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T2_TIME DESC

T2_COUNT ---------------- T2_TIME
1 ---------------- 2010-09-25
2 ---------------- 2010-09-18

Sample Query:

View Complete Post

More Related Resource Links

Use DB query result to generate Jquery required lines of code



I 'm gonna query database for photo records. each record include "photo path" where imagesURL  stored in string format

 I Choses my favorite Juqery plugin to show images. Plugin needs some lines of code like below.

How could I  generate these lines

$(function() {
sleep: 2,
fade: 1
}, [
{ src: 'picture1.jpg' },
{ src: 'picture2.jpg' },
{ src: 'picture4.jpg' }

How to join two sharepoint list in c# by CAML query?

Hii All,
   I am using wss 3.0 and i have one site and site has 4 to 5 lists. lists are interconnected with lookup column and same column. I need to display some column fron one list and some from another.. i need to join that lists by where condition like project =project..

So how can i join??

Thank You

On New Item form, select item on choice dropdown based on query result.


I posted this on Experts Exchange awhile back with no answer, so here's hoping someone here has an idea :)

So I've mostly figured this problem out, but I need some help in getting over the hump... note that I'm using JavaScript to do this (SPServices) on SharePoint 07'.

I'm on the New Items page of a list. Based on a user's selection in a "Teacher" dropdown, I'm doing the following:

1.) Get the value of the Teacher field and several other fields (School and Visit Date)
2.) Run a CAML query based on those fields to find the last list item entered for that Teacher

So here's where I'm stuck...

I have 10 choice fields, all with choices 0, 4, 7, and 10. Depending on what they were set to in the last entered item (which I queried), I want to set the selected item on each of those dropdowns to equal that. So "Score 1" was set to "4" on the la

Dynamic Class with Linq query Result

Hi, var   query = db.Customers.Where( "City == @0 and Orders.Count >= @1", "London", 10). OrderBy( "CompanyName"). Select( "New(CompanyName as Name, Phone)");    I am using this query as a sample which returns a dynamicclass type, and i need this should to converted to a list collections or datatable etc., As i am holding it in my wcf service with linqtosql as dataaccesslayer. Trying to send the results to the UI which i have consumes this service. Kindly help me in this. Thanks query = db.Customers

Cross list query for two sharepoint lists and a join between the fields

Hello All, I have a requirement where i need to query the data from two  sharepoint lists  on a same site by doing a join between those lists and get the result into datatable.I am doing this using sharepoint object model.Is there any possibility to do this. Any pointers in the right direction are greatly appreciated. Thank you Smith   smith

Linq Query Union

HelloI need to use Union for below Linq queries. I could use Union it if it was simple "select new whith{" but Now that I Specified Class, I encounter Error using unionDim CStep() As New List(Of ProductModel) CStep= (From p In db.Produts _ Where p.CategoryID= 1 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).Union (From p In db.Produts _ Where p.CategoryID= 2 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).ToList()

Need to add hard coded values to LINQ query result

I am running a LINQ query which populates a list used by a DropDownList, I need to insert an "Unassigned" value to the list. The function below queries the values correctly from the db but does not insert the "Unaasinged" value to the list. Please help.   public List<KeyValuePair<int, string>> GetTestList() { KeyValuePair<int, string> item = new KeyValuePair<int, string>(0, "Unassigned"); using (DataClassesDataContext db = new DataClassesDataContext()) { var Query = from records in db.TEST_SUITEs orderby records.TEST_SUITE_ID select new KeyValuePair<int, string> ( records.TEST_SUITE_ID, string.Format(records.TEST_SUITE_ID + " " + records.SUITE_TYPE + " " + records.SUITE_NAME) ); //Query.ToList().Insert(0, item); Query.ToList().Insert(0,item); return Query.ToList(); } }  

Check if Linq query return result

HelloI have Linq query likeDim Prood= (From p InProdutcts _                                          where p.ProductID="InputInteger"                                                         ..... ) .SingleOrDefaultI could Use Count if I Use .ToList Instead of .SingleOrDefault , But now that I use 'SingleOrDefault', how Could I find if Prod contains any record or it's Empty.

Sql query inner join issue

so i have a datagridview in a vb from. my plan is to display a table named emplyees, having the following fields: [employees] ([lastname], [firstname], [cost_center], [division], [job_title], [hiring_date], [exit_date], [functional_manager], [member_of], [backup_by], [out_of_office], [desk_phone], [mobile_phone], [comments]. But I need to make an inner join with 3 other tables. job_titles, divisons and rss. How should i writethe select command for this, having the inner join forthe follwing fields: division- division.name,job_title-job_titles.name , and member_of- rss.group_name. I have no idea how to do it. pls help Thanks

SQL server management studio 2008 freezes when running a query to Grid result

Hello, I've got problems when I run a Query to a grid result using SQL server management studio 2008 under W7 pro. When I execute the query, the first part of the grid returns, but then it freezes. The query should present a double resultset and contains approx 6100 records, devided over 2 grids. Strange enough, the query runs fine when I export to TXT or to a file. It seems though that the problem is building the grid. When it occurs I cannot end the application bij shutting down de SSMS window, but have to kill the Ssms.exe. In task manager I see only 25% CPU usage and approx 20% memory usage. Nothing exiting there I guess. Suggestions anybody? Microsoft SQL Server Management Studio      10.0.2531.0 Microsoft Analysis Services Client Tools      10.0.3798.0 Microsoft Data Access Components (MDAC)      6.1.7600.16385 Microsoft MSXML      3.0 4.0 5.0 6.0 Microsoft Internet Explorer      8.0.7600.16385 Microsoft .NET Framework      2.0.50727.4952 Operating System      6.0.6001 regards, Pim

Analysis Services - Query returns a #error result

Hi, I have a cube - which i had backed up and just retored it. After the restoration, when i try to reply the cube e.g: select [Time].&[200503] on 0 , {[Process].[All process]} on 1 from [myConsolidations] - i recieve an a result showing #error only. What could be the cause of this?

Exporting my query result to .CSV or .Txt fomat?

Hi everyone, i would like to ask if there is way on how to export my query result into .csv or .txt file?   Thanks

How to setup Query Result Shaping for LinQ to SQL instead of Entity Framework?

Hi, I am new to this, and I am trying to implement a linq query similar to db.Genres.Include("Albums") command from mvc music store using my current DB setup. I realize that the tutorial mentions Checlking the "Include foreign key columns in the method" when creating the tables in ADO.NET, but if I am using LINQ to SQL classes, then how can I obtain the same effect. And how do I use db.Genres.Include("Albums") to simplify my life? Thanks

How to copy column headers along with the query result in SS00

HiIs it possible to copy the column headers along with the query result from SQL Server 2000 Query Analyser.Thanks in advance.fjz

Split query result to text


Dear Friends,

I Have problem with query result with split from sql server,

ex:   select * from RefCustType

in the table RefCustType:

code name location telp


in the query result in text file I want this ersult like:

"code","name","location","telp"               <- field name

"111","ucok","rusia","622192356"           <- record

"222","fery","depok","6235145"              <- record


thank's B4 for your help.

sorry my english is not very well.

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

Performance tuning > two tables with strings > best query to join them


I have two subqueries which build a string (varchar(200)), first subquery creates 200000 records and second 50000 records, how can I write a select statement to get records from subquery first with string existing in second subquery records.

Will sorting both the sub query results be helpful? Kindly advice for best performing select statement.

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