.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

function or query to return continuous span of months $ years as rows in query

Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Sql Server


I am using SS2008.

There are many situations in which I need to return a table where the rows are EVERY calendar month between two dates, not just the months where there is data present.

Is there a function that would take a beginning and end date mmddyyyy which would do this?

For example if I wanted 1/2004 through 8/2010?




and so on to





Thanks in advance,




View Complete Post

More Related Resource Links

Return linq query in function

I try to return a var query from a function, but i'll get the error: Error:Error 41 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<System.Xml.XmlElement>'. An explicit conversion exists (are you missing a cast?)  Code: protected static IEnumerable<XmlElement> GetModels(byte MerkID) { string rawXML = AutoDisk.GetModels(12345, true, MerkID, "2010", ""); XmlDocument xml = new XmlDocument(); xml.LoadXml(rawXML); var query = from feed in xml.DocumentElement.ChildNodes.Cast<XmlNode>() select new { nModelID = feed.SelectSingleNode("nModelID").InnerText, Modelnaam = feed.SelectSingleNode("Modelnaam").InnerText }; return query; } Whats wrong and how can i make this working ?  

Query to return TOP n with 'Other' grouping for all remaining rows

Hi all, I've been given a specific type of report to produce, and am having difficulty in getting my SQL right to return the data as needed. I need to return TOP n Categories based on amount of sales (which I can do) but then a final category with a sum of all other sales. The purpose would be to capture all of the small sales values into one 'Other' category so that the chart I'll produce does not have lots and lots of small-value columns. I found the following post which seems to come close: SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales] FROM CompanySales GROUP BY CompanyID, CompanyName ORDER BY SUM(GrossSales) desc UNION SELECT -1, 'Other' [CompanyName], SUM(GrossSales) [GrossSales] FROM CompanySales WHERE CompanyID NOT IN ( SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales] FROM CompanySales GROUP BY CompanyID, CompanyName ORDER BY SUM(GrossSales) desc ) But this gives a syntax error on UNION, which I'm guessing is due to the ORDER BY clauses. However I think the ORDER BY is needed to ensure I return the results in the correct order. Does anyone have any ideas of how to do this? Thanks for any assistance Matt

MDX simple query doesnt show the years in the rows but shows (all) with value




this mdx script should return asset value for the individual years but instead it return one line with a value.

When i use the browser in ssas and select the year in rows and measure, it gives the result back as expected ie. years in the rows and values.

Can you help me out please im stuck here:




      [Measures].[Appraisal Forecasted Value] ON COLUMNS,

     [Asset Value Date].[Year] ON ROWS

FROM [CMSX_DWH_OLAP_AssetExposure]

// Result:


// Appraisal Forecasted Value

//All 1,931,290,000  I miss the Years on rows?



// This is from the browser which is correct because the year is added

Year Appraisal Forecasted Value









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.

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.

Return default value from LINK query

I am using the  query listed below which includes multiple joins, in the result of the query I need to do some value replacement values Example: db.ASSETS.BUILDING_ID=300 that ID does not exist in db.BUILDINGS, in that case I need to return BUILDING_ID=0, db.BUILDINGS.NAME="Unassigned" Please help  var productQuery = from assets in db.ASSETs join relocatableUnits in db.UNITs on assets.UID equals Units.RUID into assets_units from Units 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.T_TEST_SUITE_ID equals testTrgt.T_SUITE_ID into testTrgt_bins from testTrgt in testTrgt_bins.DefaultIfEmpty()

Pipe SQL Query function into Variable with no column headings

Hello, I don't know if I'm in the right forum but could not find one for Powershell. I need to return 1 piece of data from a sql database to a variable. I don't want the column headings. Here's what I have so far:         Function GetMenuGroup ([int]$StoreID) { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=Servername;Database=DatabaseName;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "exec storedProcedure $StoreID" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet)|out-null $SqlConnection.Close() $DataSet.Tables[0] } $MG=Getdata 2 |out-string Here's the results I get now - How do I return just "DatathatIreallyWant" in a string variable? I don't want the column or underline. ColumnName ------------- DataThatIreallyWant   Thank you, MG2

MDX query to only return first row.

I have a given MDX query AND would like to only return a single row instead of the full result set. also I need to do this in a way so I can simple add a line to it without re-writting the entire statement. any suggestions how to do this? background: I running stored MDx queries against the cube using OPENQUERY. the result set I parse first to understand what is part of my dimension (not time) and what column defines the time so I can later on work with the results. any hekp is appreciated.

Query DB and update Check Box List on JS/jQuery Function

Hey guys.  I'm not sure if this is the right forum to post this in but I've come to a roadblock in my app and I'm not sure which way I should go.  Currently, I have a slider control and two text boxes used to set the Start and End times of a section of a video.  Let's say the video is of some Music Awards.  In my database, I've tagged the start and end time of each celebrities on-screen appearance.  When the user adjusts their start and end times on the page, I want to query the database table and pull back all celebrities within that range.  The problem is I don't know how to do this in JS.Can I use the Check Box List and bind the values to it?  Do I need to render a table of Check Boxes in JavaScript?  Do I call the Server-Side code via Ajax and then partially render the page?Any help, comments or suggestions would be great.

Returning rows not found in inner query

select * FROM [C_Accessioning].[HL7].[GMessages] where MessageID not in (Select MessageID from HL7.ORM_MSH) There are two rows of data in GMessages: MessageID MessageDateTime              Filename 28             2010-09-07 16:29:02.543    ORM1.hl7 29             2010-09-07 16:29:07.547    ORM2.hl7 There is no data in ORM_MSH. Please see sql statement copied above. The inner query returns null, I am expecting that the query return both rows from GMessages, but it does not return any rows. Please let me know how to fix.   Thanks,Deep

how to query return all data in Array Object ,Collection Object

how to query return all data in Array Object ,Hash Table ,And DataTable dataRowSelect * From Doc a

Query returns correct rows but I am not able to concatenate the rows into a variable :(


Hi all,

Why in the code below I am able to list all rows and display the field 'stepID' but I am not able to concatenate them all in the variable @stepIds?

declare @date as datetime
set @date = '2010-09-20 23:01:20:000'
declare @instance as uniqueidentifier
set @instance = '04658F34-CBF1-4CCD-A683-F23BDB669D35'

  Declare @CrLf char(2)
  Set @CrLf = Char(13) + Char(10)
	Declare @stepIds varchar(8000)
	Set @stepIds = ''
		--@stepIds = @stepIds + ' - Step: ' + CONVERT(varchar(4), stepID)

Pivotal Query to sum up months and quarters together


Hi all,

i need below output do i need to use cursors for this can i do this with case statement.

can some one solve this for me if they have already solved similar problem before.

CREATE TABLE [dbo].[tablesales2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cost] [numeric](18, 0) NULL,
[saledate] [datetime] NULL,

insert into [tablesales2](name,cost,saledate)values('pen',100,'01/01/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',100,'01/01/2001')
insert into [tablesales2](name,cost,saledate)values('pen',200,'02/02/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',200,'02/02/2001')
insert into [tablesales2](name,cost,saledate)values('pen',300,'04/04/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',300,'04/04/2001')
insert into [tablesales2](name,cost,saledate)values('pen',400,'12/12/2001')
insert into [tablesales2](name,cost,saledate)values('pencil',400,'12/12/2001')

i need to have the o

Order By clause in RowNumber function slowing down the performance of my query.



I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.

I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.

Could you please suggest what i can do to improve the performance.






One to many query: return most common value


I am working on a one to many query for an assets database.  Here is some sample data

Server Name        Application        Primary_User
Server1                 SQL                   DBA
Server1                 Citrix                 IT
Server1                 Oracle                DBA
Server2                 Sharepoint         Web
Server3                 SQL                   DBA
Server3                 Sharepoint         Web
Server3                 Norton               Security
Server3                 IDS                   Security

The desired output is one row per server with the server name, count of applications, and the Primary User that appears the most (not just the first, last, min, or max).

It would look like this

Server Name    Applications  Primary_User
Server1            3                  DBA
Server2            1                  Web
Server3            4                  Security

Is there a sub query that can accomplish this?  Thanks in advance!

Using SQL to return matches from 2 different bits of data in one query


Hi there,

i am writing a website that has a social networking element to it, and i want to display mutual friends of the person being viewed with the person that is viewing.

is it possible to search one database table and look for matching entries using 2 different usernames? if so how would i do it?

hope u can help,

many thanks

Query to return conditional counts both distinct and non distinct.


I have the following table structure:

Table EmailTracking



Table EmailTrackingDetail


The following query returns the default recordset:


SELECT     TOP (200) EmailTracking.EmailAddress, EmailTrackingDetail.TranType, EmailTrackingDetail.TranDate
FROM         EmailTracking INNER JOIN
                      EmailTrackingDetail ON EmailTracking.RecordID = EmailTrackingDetail.EmailTrackingRecordID







2010-09-21 16:59:29.980



2010-09-21 17:19:58.790



2010-09-27 12:00:30.940

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