.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 figure out where records are being dropped from a pivot query

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server

I have a stored procedure that creates a pivot query. The problem I'm having is that the inner query is returning 1641 records, then when the pivot is executed, only 1450 records make it to the result set.

The query is as follows:

SELECT CallSegments.AppName, CH.CallDate, dbo.fn_IsContacted(CH.AppName, CH.Result) AS Contacted, CallSegments.QueueID, dbo.fn_GetResultCategory( CallSegments.AppName, CH.Result ) AS CallResult 
FROM CallSegments, CallHistory AS CH WHERE CH.CallInstance = CallSegments.CallInstance 
AND RTRIM( CallSegments.AgentID ) = ' 
AND RTRIM( CallSegments.QueueID ) <> ' 
AND CH.AppName = 'some_client' 
AND CH.CallDate >= '11/3/2009 00:00:00.000' AND CH.CallDate <= '11/3/2009 23:59:59.997' 


The interesting thing, for me, is that I can copy the internal select clause and run it separately. When it executes, I receive 1641 records in the result. Then when it goes throug the pivot, records are dropped. I don't quite understand because all of the inner records have a category listed in the pivot list. I've looked it over several times but I just can't see where I'm losing records.

View Complete Post

More Related Resource Links

Pivot w/o aggregation...query not working

Hi all--Given this query, I need to make this query into a pivot table: select Personnel_id, First Name, Last Name, E-mail, Division, Region, Country, Manager, Question, Answer, Date Completed from #pivot where Question Answer 01. Received File No 02. Read File Yes 03. Signed File Yes ... Needs to look like this: Personnel_id First Name ... 01. Received File 02. Read File 03. Signed File ... Test01 Test No Yes Yes I have the following pivot query: SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM (SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM #pivot) topvt PIVOT (MAX([Personnel_id]) FOR Answer in ([1],[2], [3], [4], [5], [6], [7], [8] , [9], [10])) as pvt ORDER BY pvt.[Personnel_id] I get this error: Msg 207, Level 16, State 1, Line 83 Invalid column name 'Personnel_id'. Any ideas? Thanks!

Inserting multiple records under the single query

Hi All,       I have to insert a multiple records under the single table.So i have to execute the command in the frontend for every records. Inorder to avoid i write the query like this   insert into table1(field1,field2) values('1','Test1'),('2','Test2'); and also i tried insert into table1(field1,field2) SELECT "1","Test1" Union all SELECT "2","Test2" But I am getting syntax error. How to insert a multiple records under the single query???...

Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. Converting an Access pivot query to SQL 2005


    Hi, I am new to table pivot and would like to know how to go about converting the following query in Access to SQL Server 2005:


    SELECT tbl_Proj_Budget.PROJ_ID,
    FROM tbl_Proj_Budget 
    GROUP BY tbl_Proj_Budget.PROJ_ID 

    1) what does the 1st line mean?


    2) How would I conver this to TSQL ?




load users records in infopath fields without using "run the query" button



is it posible to instead of clicking the "run the query" button to load the users records in the fields that the fields load automatically.

can this only be done with a role and a "set a value's field" action? 

problems in pivot query




             I am using following pivot query  but not getting result as single row getting as 4 rows.

One more problem i am unable to pass dates as parameters to storeprocedure something like this

select [@date] .

        SELECT 'Forecasted' AS HeadCount,
[8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010]
FROM TblEmpCount) AS SourceTable
FOR StartDate IN ([8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010])
) AS PivotTable;

I am getting result as 4 rows but i want result in single row like this

HeadCount    8/1/2010     8/8/2010    8/15/2010    8/222/2010

Forecasted     191                182                 176                169





query wich returns records for each day between start date from table 1 and mutation dates in table



I have to tables with dates, table Employments with all employements of all employees, with start date and end date, and a table Mutations with mutations (e.g in salary) with start dates.

Now I try to write a query which returns a record for each day an employee is in employment, with the correct salary. SO at first, it should return all days between the employment start date and the first mutation date, then the number of days between the first mutation date and the second mutation date etcetc, and at last the number of days between the last mutation date and the employment end date. The number of mutations varies for each employment, and employees cna have multiple employments (history, so not at the same time) in the employments table.

How to do this?

Use power pivot to query the data in MS CRM


Hi All,

I want to query the data in MS CRM (entities) using power pivot from IT Share Point. Can any one guide me in this please.


Select Query to get records within last 24 hours in Nhibernate


Hi All

How to Retrive records within last 24 hrs using Nhibernate.
I want to retrieve the 10 records within last 24 hours.

How can i achieve this using Nhibernate.

Please Help.

How to find records based on CAPITAL and small letters in SQL Query?



I stumbled upon this scenario at my work and it is really something I was not expecting. I know that in general SQL itself is not a CASE SENSITIVE language ... so it will not make any difference if i write select *, or SELECT * or SeLEct * ...

But recently I came across a situation where I am required to find results from a table where more than one record exists with just a difference of Captial and Small letter word. Let me explain my problem in detail,

I work for a government client, in one of their table they have list of all state statues and out of them some have exactly same string except in that string some letters are capitalized, e.g.  46b-12 (A) and 46b-12 (a) ... now my question is how can i write a query which can find only record that I search, e.g if I search select desc from statues where code='46b-12 (a)' ... it should find only one record not two.

Query issue - Pivot?


I a query that returns the following:

Day    TypeID     Product A     Product B
1        1            10               20 
1        2            30               40
2        1            50               60 
2        2            70               80

But, i need to return the following:
Day    Product A Type1     Product A Type2     Product B Type1
     Product B Type2
1       10                        30                        20                        40

Duplicate Records Query


Hi All,

I have the following table:

Table A:

OrderNumber     EventDescription

1                       New Order

1                       On Hold

2                       New Order

There are other fields I need to bring back as well. Now I need to create a query that if a jobnumber has more than record in table to not return those records. How would I accomplish this?

Thanks In Advance,









CAML Query for display Top n records in sharepoint 2010


Hi friends,

I have added the Content Query web part in my publishing page for displaying data from list.

Now i want to use additional filter option from content query web part.

pls tell me wht query i should write for returning Top n Records from list.

also pls tell me whole caml query format dat i need to put in custom value or query option in additional filter option in Content Query Web Part

Users use Excel-Pivot table to query the cube, how to capture those queries?


Most of our users use Excel'10 for querying the cube,

I've set up the querylog-properties in SSAS engine but it only captures the query from SSAS engine and not from the other platform such as Excel. So, when I use Usage based aggregation wizard, it shows the users/ueries from SSAS engine only. Is there any way to capture the user/query information that comes through Excel pivot table and somehow create aggregation through that information?


Any suggestion/help is very much appreciated,


sql query to compare the last two records

Hi Frinds,

Please can any one tell me the logic of how to compare the last two records

I have the table like this


Id    Number

1      23

2      34

3     23

4     43

5      12


I want to compare the last two records in the above table i.e the numbers have to comapre 

i.e if 43>12  print "grater"  else


this is the logic for finding the last two records

SELECT id,number FROM

(SELECT TOP 2 * FROM table_1



but where I have to write the logic for compare the last two records in above query,


Please if any body have ideas let me know





caml query - group by column and get count of records



I want to group by on column name and get the count of records returned.



Need to query three records at a time.


I need to write a select that pulls all of the rows from a data table but the problem is that I need to get the rows three at a time.

I am querying SharePoint data and since SharePoint only allots so many fields per record per data type, my list has three rows for every list item. So in order to sum fields 1-31 of loat data I actually need to grab row instance 0, 1, and 2.

For each list item field float 1-12 holds 1-12 in row instance = 0 and float 1-12 holds 13-24 in row instance = 1 and float 1-7 holds float data for 25-31 in row instance = 2. The field name of the row instance is . I need to sum fields float1-float12 for the three rows and get one total.

Normally I would use something that summed the 12 float fields and assign it to one variable field to be returned in the select. But I have to loop through the three rows to get that for each and then sum the subtotals of the three rows adn repeat until eof.

Can anyone give me a pseudo code for the select? I am just not that fancy with SQL I guess.

You can assume that there are always exactly 3 rows for each item whether or not there is data in any of the given fields. If the field has no positive data it is recorded as NULL in the field.

Thank  you in advance to any with ideas.


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