.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

Complex SQL query. Please solve.

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server
i have a table named -> tbl_name

id | name | id_name 
1    a      1---->See id column
2    b      1
3    c      1
4    d      2
5    ef     3
6    de    6

now i want result like this[Acording to there id's related to id_name]

id  name  id_name 
--  ----  -------
1    a      a  ->id is 1 and id_name is 1 so result is a
2    b      a  ->id is 1 and id_name is 1 so result is a    >View Actual data
3    c      a  ->id is 1 and id_name is 1 so result is a    >in upper table   
4    d      b  ->id is 2 and id_name is 2 so result is b
5    ef     c  ->   ""
6    de    de

View Complete Post

More Related Resource Links

MDX query performance is slow for complex logic implementation in Calculated Members.

We have implemented a calculated members which involves multiple calculated members  called in a nested e.q. Cal1 calls Cal2 and Cal2 calls  Cal3  and Cal3 calls Cal4 .  And when browing Cal1 through Excel it takes around 30 mins for getting result from a Partition having row count around 5 million. Follwing are the information on the scenario I am having: We are having partitioning in the cube. one partition contains around 4-6 million records Processers on Query Server: 4 quad core RAM on the Query Server: 64 GB Calculated members having simple calculation logics are giving results in no time. There is a Calculated members which calls 6 intermediate calculated in nested way and with small Data Set like 5-6 k rows it is running fine.   Any help will be much appreciated.   Regards, Sandeep

how to solve this query


Dear all

I have three tables first is main hdr which contains single records of employee sales , second table contains differnent customer for that month  and third is sales details that is it contains items sold to the customer



















help me in this to solve this, need result based on this two query


This is my query im little struct need help

SELECT  a.ItemName
	  , a.AvailableQty   
	  -- - ISNULL(b.RequiredQty,0)  AS Balance Total

  FROM ( SELECT l.ItemName,
               l.Weight * 30 /100 as AvailableQty
           FROM dbo.FR l inner join dbo.Fg m
on l.ID=m.ID 
m.A='C' and m.S='B') AS a
  ;with cte as 
(select Date,A,S,Types,Refno,Sum(P.Consumption) over (partition by RefForAll) - ISNULL( t.Qty,0) as Qty,ROW_NUMBER() 
over (PARTITION by RefForAll order by ID desc) as rn from dbo.MReg P 
cross apply (select SUM(Qty) as Qty from dbo.FDes where RefForAll = P.RefForAll)t 
where RefForAll in (select RefForAll from dbo.MReg)) 

(select ll.ItemName,
               ll.Weight * Z.Qty /100 as RequiredQty from cte as Z               
               inner join  dbo.Fg m on Z.A=m.A and Z.S=m.
               inner join dbo.FR ll on ll.ID=m.ID 
               where Z.rn = 1 and Z.Qty!=0 
               and m.A='C' and m.S='B') 

The first query above left outer join its work fine... second query after left outer join i cant able to do..

What i need is i need to set this


T-SQL 2008 date range complex query?


Hi, I have SQL 2008 table like

SiteID      TotalBeds     AdmissionDate      DischargeDate

01             10             2010-08-01            NULL
01             10             2010-08-02            2010-08-05
01             10             2010-08-02            NULL
01             10             2010-08-02            NULL
01             10             2010-08-02            NULL<

Solve this Query


ID    Name

1      Y

1     Y

Table A

id is not auto generated i want to delete one row from table a after that display row i need 

single query to perform such operation.

Please help to sought out such problem.

Query to solve this


i used the query like below

select distinct  Fattribute_type from tblFrestaurent where Flocation ='1'

then i get result like this


my question is i want record like this 22,23,3,17,7 (no reption of same no(like 17 is not come twice)).

to solve this i used following query

declare  @fattribid varchar (100)
Select @fattribid=stuff((
select distinct ','+ Fattribute_type from tblFrestaurent where Flocation ='1' for xml path('') ),1,1,'')
print @fattribid

but result is like this

22,23,3,17,3,7,3,7,17 (means there are data are repeated ).........

above is my first problem


okes my next problem is


when i execute below query

create proc test

@locid varchar(10)


declare  @fattribid varchar (100)
Select @fattribid=stuff((
select distinct ','+ Fattribute_type from tblFrestaurent where Flocation =@locid for xml path('') ),1,1,'')

Select * from tblFattributeMAster where Fattribute_id in (@fattribid)


then this give the error

Conversion failed when converting the varchar value '22,23,3,17,3,7,3,7,17' to data type int.

and when we change the data type o

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

MVC too complex?


I believe lot of people will disagree with me on this one. But the MVC model sucks. The reason:

1.       Over complicated;

2.       Cannot handle complex UI;

3.       Hard to debugging as code scattered all over the place.

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

How to send an email with information in query string


First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.

How do I persist custom complex types in design-time


Let's say that I've got one custom webcontrol called MyControl.

public partial class MyControl: WebControl {
public IComplex Complex { get; set; }

The control is a very simple control but it holds an instance of a complex type that is accessable through a property. The property has the propertytype IComplex

public interface IComplex {
string Name { get; set; }
List<IComplex> Children {get;}

Let's say that I am able to pick one of several different types of object that derives from IComplex in design-time and assign it to the controls property "Complex" property.

Naturally I would like to persist this information to the HTML-View in a user-friendly way.

Because we don't know what type the user has picked there is no way to instansiate the type if we don't persist the name of that type. The Type could also contain more properties than the Interface the controls property is refering.

<cc1:MyControl id="MyControl1" runat="server">
<Complex> <!-

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

Invalid length for a Base-64 char array for my coding in ASP.net C#. How to solve it ?


Hi Friends ,

I have done encrypted password and decode but i got error  like  this  " Invalid length for a Base-64 char array" .

Pls help . Thanks


psswd = txtPassword.Text;

pwed = base64Decode(psswd);

public string base64Decode(string sData)
            System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
            System.Text.Decoder utf8Decode = encoder.GetDecoder();
            byte[] todecode_byte = Convert.FromBase64String(sData);
            int charCount = utf8Decode.GetCharCount(todecode_byte, 0, todecode_byte.Length);
            char[] decoded_char = new char[charCount];
            utf8Decode.GetChars(todecode_byte, 0, todecode_byte.Length, decoded_char, 0);
            string result = new String(decoded_char); return result;

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