.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

Trouble working examples of Cumulative columns in SQL Query

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

I can run the following query without the built in query in the select statement and it works fine.  All i am trying to do is to create a cumulative total for each day for a specific date range.  Any ideas?


SELECT OrderHed.OrderDate,

     (SELECT SUM(OD.OrderQty * OD.ListPrice)

        FROM   MFGSYS.PUB.OrderDtl OD INNER JOIN MFGSYS.PUB.OrderHed OH ON (OD.Company=OH.Company) AND (OD.OrderNum=OH.OrderNum)

        WHERE OH.orderdate<=OrderHed.OrderDate)

 FROM   MFGSYS.PUB.OrderDtl OrderDtl INNER JOIN MFGSYS.PUB.OrderHed OrderHed ON (OrderDtl.Company=OrderHed.Company)

    AND (OrderDtl.OrderNum=OrderHed.OrderNum)

 WHERE  (OrderHed.OrderDate>={d '2010-10-01'}

    AND OrderHed.OrderDate<={d '2010-10-31'})

    AND OrderDtl.Company='HEAPG'

    AND OrderDtl.RMANum<1

 GROUP BY OrderHed.OrderDate

 ORDER BY OrderHed.OrderDate

View Complete Post

More Related Resource Links

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.

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!

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

How to reference 2 columns in a query

Hi All I have 4 tables First Table Structure Id           AccountNum 1                Meals 2                Overtime 3                Wage SSAmount Id     SiteNbr     Meals            Overtime           Wage 1       145              100                 500                   1000 GGAmount Id     SiteNbr     Meals            Overtime           Wage 1       145              100                

Caml query not working

Hi, I'm trying to query a SPListItem from a sharepoint list but it doesn't seem to work:   SPDocumentLibrary myDocuments = (SPDocumentLibrary)site.Lists["Documents"]; SPQuery query = new SPQuery() { Query = " <Where>" + " <Eq>" + " <FieldRef Name='DocumentPath' />" + " <Value Type='Text'>" + source + "</Value>" + " </Eq>" + " </Where>" }; var result = myDocuments.GetItems(query); I get no result, but there's an item with the specified "source" that does exist.

Why is the Content by Query Web Part CommonViewFields attribute not working as expected?

I am using the Content by Query Web Part to show data from a Custom List.  I have already added to the XSL "ItemStyle" sheet so that I can pick the correct Item Style.  That has worked fine - I have been able to show whatever fields I would like and apply any CSS Styles I would like to them.  The issue I am having is in formatting the field correctly.  I have two different fields in my current example that need to be formatted: [1] I want to show an Image, but right now it shows the URL  [2] I want to show Rich Text, but right now it shows all the HTML Code as Plain Text.  I have looked at many articles online, and they all tell me to change the following in the WEBPART file from: <property name="CommonViewFields" type="string" /> to: <property name="CommonViewFields" type="string">Thumbnail, Image;Teaser, RichHTML;</property> What I am doing wrong?  The Web Part still runs, but it does not format the fields any differently.  Thanks for the help!

Image stretch inside a grid stops working horizontally once columns are added to grid.

I have a simple setup of a window. Window contains a grid, inside the grid there is an image. I want the image to always stretch across the entire cell. I've set it's Stretch property to Fill and it works fine, as long there is only one column in the parent grid. As the window is resized, the image stretches correctly. As soon as I add another column, the image stops stretching horizontally. Vertially it works just like before, horizontally it now appears with a fixed width corresponding to the width of the picture used as the source. Does not matter which column it's positioned in, and I've tried encapsulating it into another grid but behaviour does not change. It behaves the same in both Visual studio 2010 designer and at runtime. In addition when I tried to encapsulate it in a second grid, this seond grid also now has altered behavior - instead of sizing itself to it's container (both height and width are not set) as it's parent grid does, it now sizes itself to its contents - the image. But only horizontally. What is going wrong here, is this a bug or am I missing something, and any advice on how I can get the image to fill one of the cells of a grid with more then one column?

Examples for custom query using nhibernate in asp.net 3.5

I am doing a project in which i am using nhiberante ORM, In that they provide default method, but i req. more custom query like getting data by using join, to get particular column, So, i req. help from expert developers, plz help me. Thanks in advanced.

while trading in stock market it was chnaging dynamically how it was working explain with examples

while trading in stock market it was chnaging dynamically how it was working explain with examples

Query Sharepoint list, rich text columns returning HTML tags

Hi! So I have an issue that maybe someone of you have encountered as well. I am querying a Sharepoint list and creating a report from the data collected from this list. This all works fine except that one of the columns in this list is of the type "Multiple lines of text" and also supports "rich text" with different fonts, sizes and so on. This has the effect that the text returned from this column is wrapped in HTML tags that specifies how the text should be formatted. Naturally I do not want this HTML code to appear in my report so for now I have used custom code to do a string.replace and replaced for example <div></div> and <br> with "". However, this column also has url references to documents and these links shows up like this "/servername/site/subsite/Gemensamma%20dokument/Mwh.docx">http://servername/site/subsite/Gemensamma%20dokument/Mwh.docx There is some sort of double reference to the document that I guess like the HTML tags are embedded in the answer from the rich text column and this is not so easy to do a string replace on because the url varies with the different document and document names. My question really is if anyone of you have done a similar report that I am doing, and have encountered this fenomen with this sharepoint column and how you did to solve this? I have tried to creat

Sql Order by not working with decimal/money type columns

Error Message:- Cannot convert a char value to money. The char value has incorrect syntax. DECLARE @Order NVARCHAR(50) = 'asc', @OrderBy NVARCHAR(50) = 'Shape'SELECTtop 1 Row_Number() OVER(ORDER BY  CASE WHEN @Order IS NULL OR @Order = 'asc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice    --MONEY    WHEN @OrderBy = 'Carat' THEN [Weight]   --DECIMAL   END  END ASC,  CASE WHEN @Order = 'desc' THEN    CASE WHEN @OrderBy IS null THEN Shape    WHEN @OrderBy = 'Shape' THEN Shape    WHEN @OrderBy = 'Cut' THEN Cut    WHEN @OrderBy = 'Color' THEN Color    WHEN @OrderBy = 'Clarity' THEN Clarity    WHEN @OrderBy = 'Price' THEN FinalPrice  --MONEY   WHEN @OrderBy = 'Carat' THEN [Weight]  --DECIMAL   END  

CAML query on external list is not working

Hi, I have an external list, display data from SQLServer 2008. Table on SQL have an UniqueIdentifire (GUID) type column as a Primary key. It also has one identifier column, Its value is increased by 1, as the item is created. In share point server side code, I want to fetch single record against the primary key GUID value. I write down the CAML query on GUID column and run without any error. But all the external list data is returned instead of Single list item. I also try to filter on Auto Incremented column, but same problem is occurred.

query with locks not working in sql server 2008

I have a select query with join on some view and locks as (readpas,updlock) after executiong this query it executes the same query with little modification( no locks etc). This sequence of query executes fine in SQL server 2000/2005.   When I am executing same seq in sql server 2008 through application, I am getting timeout for second query. Can someone please suggest what can be the reason? I cannot post the query.Cheers!!! Vatsa

Removing columns inherited from parent not working

I'm trying to install a couple of custom workflow task content types that are based on the standard workflow task type, but I want to remove some of the attributes defined on the std worfklow task type.  Per the MSDN online info you can use the <RemoveFieldRef/> tag in your XML content type definition file to remove such columns.  For some reason mine does not appear to work, however.  In addition to not removing the columns specified by the <RemoveFieldRef/> tags, it also does not create my custom "Comments" column -- It DOES create the custom "Respoinse" column. The following are my XML definition files for the feature (which installs fine and the content types are created, but the columns I want to remove still show up).  Any idea what I'm missing to remove these columns from the custom types?  I pulled the IDs for the columns I want to remove from the std type definition XML under the 12-hive FEATURES folder Type Definition File <?xml version="1.0" encoding="utf-8"?> <!-- See this blog for specifics: http://www.scribd.com/doc/2278897/Part-4-Custom-Workflow-Forms-TaskEdit-Form --> <Elements xmlns="http://schemas.microsoft.com/sharepoint/"> <ContentType ID="0x010801003D9E4ED01F024d28AF0056E2654CECB0" Name="MoreInfo Task" Group="

tfs mdx query(cumulative count)

I need cumulative count of bug,risk work item per day first, i made this query WITH MEMBER [Measures].[Date Value] AS     [Date].[Date].CURRENTMEMBER.MEMBER_VALUE   MEMBER [Measures].[Active Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Active], [Measures].[Cumulative Count])   MEMBER [Measures].[Resolved Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Resolved], [Measures].[Cumulative Count])   MEMBER [Measures].[Closed Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Closed], [Measures].[Cumulative Count])   SELECT {     [Measures].[Date Value],     [Measures].[Active Cumulative Count],     [Measures].[Resolved Cumulative Count],     [Measures].[Closed Cumulative Count] } ON COLUMNS, (     Filter(         [Date].[Date].[Date],         [Date].[Date].CURRENTMEMBER.MEMBER_VALUE >= CDate(@StartDateParam) AND         [Date].[Date].CURRENTMEMBER.MEMBER_VALUE <= CDate(@EndDateParam)     ) ) ON ROWS FROM [Team System] WHERE (     {[Work Item].[System_WorkItemType].&[Bug],[Work Item].[System_WorkIt

Content Query Web Part Styling and Columns with Underscores



I'm having a problem with using a column which has an underscore in a custom styled Content Query Web Part. To keep it simple the column name is Mobile_Content, with an underscore. This is causing the issue as columns without underscore work fine. My CommonView Fields property looks like this in the .webpart file:



property name="CommonViewFields"

Trouble installing SQL Server 2008 Cumulative 4


I have a server that is currently at 10.00.2723 (CU3).  I want to patch it to 10.00.2734 (CU4).  The service pack installer tells me that it can not find an instance of SQL Server on the machine.

SQL Server 2008 Enterprise x64
Windows Server 2008 R2 x64
This is a VM using VMware

Any body have suggestions, run into this before, etc..?



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