.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

Multiple Dates Query

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

I need help in creating a query to get info for year selected and previous year of the year selected. Needs to be by quarter(QTR) for both years. QTR1 will always be the month 03, QTR2 month 06, QTR3 month 09, & QTR4 month 12. No computations are required or averaging.

Fields/columns are YEARMONTH stored as mm/dd/yyyy with dd always being 01, ACTUAL_MONTH(float)for all QTRs based on current YEARMONTH selected and one year prior,TARGET_MONTH(float) for all QTRs based on YEARMONTH selected, and PROJECTION_MONTH(float) for all QTRs based YEARMONTH selected.

YEARMONTH (PK, Datetime)

Should look like this if any YearMonth in 2010 was selected
                   Q1 Q2 Q3 Q4
09-Actual      xxx xxx xxx xxx
10-Actual      xxx xxx xxx xxx
10-Target      xxx xxx xxx xxx
10-Projection xxx xxx xxx xxx

View Complete Post

More Related Resource Links

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits 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.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

QUERY filter dates by DAY

i have the following data : first column: dates and 2nd column: values i want to filter the dates per day and calculate the AVERAGE of their values per day i can filter them manually by setting day(datetimefield) = 12 and calculate the AVERGAE but i dont know how to do it for all in one query? each record resulting the average of that day   2010-07-12 00:00:00.000 900 2010-07-12 10:00:00.000 1000 2010-07-12 10:10:00.000 1200 2010-07-12 12:00:00.000 1100 2010-07-12 15:00:00.000 1300 2010-07-12 15:10:00.000 1500 2010-07-13 00:00:00.000 6500 2010-07-13 10:00:00.000 5500 2010-07-14 00:00:00.000 5500 2010-07-14 10:00:00.000 4400 2010-07-15 10:00:00.000 4500 2010-07-16 00:00:00.000 1000 2010-07-17 00:00:00.000 7000 2010-07-18 00:00:00.000 8500 2010-07-19 00:00:00.000 1500 2010-07-20 00:00:00.000 1700 2010-07-21 00:00:00.000 1900 2010-07-22 00:00:00.000 2000  

Running a single SQL update query on multiple foxpro databases

Hi, Env : SSIS 2008 My requirement is to run a update SQL statement on multiple foxpro databases. The fox database paths are available in a SQL server table.  I've created a for each loop control and assigned the database path to a variable named dbpath. Within the loop, I added an "execute sql task" control. Now what properties are to be set to get the requirement done. Appreciate any help. Regards. BH  Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

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???...

CAML query to access List data across multiple site collection.

Hi   Is there any way to get the Task List data across multiple site collection using CAML query. Currently, I have the code which will retrieve the data from a single site collection. Below is the code.                           /***************************************************I am using Moss2007.Thanks!Ravi             SPSiteDataQuery q = new SPSiteDataQuery();             q.ViewFields = "<FieldRef Name=\"Title\" />" "<FieldRef Name=\"Status\" Nullable=\"TRUE\"/>";             q.Lists = "<Lists ServerTemplate=\"107\"/>";             q.Webs = "<Webs Scope=\"SiteCollection\"/>";             q.Query = "<Where>" +                                "<Eq>" +                                     "<FieldRef Name=\"Status\" />" + "<Value Type=\"Choice\">Not  Started</Value>" +                                     "</Eq>" +                         "</Where>";             DataTable dt=null;             using (SPSite s = new SPSite("http://banglabguest1:31000/"))             {                 SPWeb w = s.OpenWeb();                 dt = w.GetSiteData(q);                 w = null;             }                              *************************

Taming Multiple Validators on Dates

I have a text box for input of a date.  It has three validators: Required Field - to make sure it's completed Compare - setup to check the data type Compare - setup to check against a limit The second two, as expected, do not fire if the field is empty. If the user keys a stupid date, such as 01/19/0010 the second validator fires as expected.  However, the third validator also fires because the "date" is earlier than the specified limit. Is there any way to prevent the third validator firing, if the second validator has fired (ie once one validator has fired on a particular control, no more validators on that control will fire)? If not, then I guess I'm either going to need to write my own third validator which ignores invalid dates, just like it will ignore an empty field (assuming that the second validator will always be there to take care of invalid formats); or again my own validator but effectively combining the checks of validators two and three (ie checking for format first and then against the limits). I can't be the first person to try and do this.  Anyone?

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

Multiple value checking in Caml Query.

    i would like to check the multiple values in caml. for example In operator in sql server . all helps are appreciate .

SQL Query for Date Check User Dates



I am using two columns in one table such as StartDate,Enddate.

In asp.net Form two text boxes there. One is StartDate other one EndDate..

When user Click Submit button the Two dates is compare above DataFiled's...


-------- Data Base Values ----------------

StartDate            EndDate


10/09/2010         14/09/2010

16/09/2010         28/09/2010



user send Start Date ' 27/09/2010' and EndDate '30/09/2010'


Now the Query Check Dates Between Userdates avialable in Database....


What I ll do?????


(Note : Opertion only allow the Dates Not Exists in Database Dates)



The first axis of the query should not have multiple hierarchies








[Calendar Date].[Calendar Hierarchy].[0-90]






Is there a way to query entity based on multiple filter criteria? WCF Data Services, Linq to Entiti


Instead of:

DW_CMSOPEN dwc = new DW_CMSOPEN(new Uri("http://acctdev02/WCFDataService/EmployeeService.svc"));

dwc.Credentials = System.Net.CredentialCache.DefaultCredentials;

var employees = from emp in dwc.Employees 
             where emp.DEPT == "123"
             select emp;

I'd like the linq query to resemble:

var employees = from emp in dwc.Employees
              where emp.DEPT // in {"123", "456", etc}
              select emp;

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?

SQL Query Multiple rows Top problem.

This is a simplified example of my problem.
I have a Product table, and a ProductSupplier table.  A Product can have multiple Suppliers; these suppliers are ordered according to a sort order field and each has an associated price for the Product.

I want to run a query to return all my products, along with their first supplier and the cost against this supplier.

Currently the only way I can do this is by performing select statements in the manin select clause to retrieve the supplier information.






Select Query For Dates comparision


Data is like this in one table.Those are dates but values are VARCHAR datatype



How to write a select query by passing  two parameters Dates between @StartDate and @EndDate . But From Front end values are passing like below for example

01-Jan-2010 and 31-Dec-2010

How to write a select which consider dates in above format and gives correct results.




Retrieving multiple values from a select query



This code works just fine for me to select * from x,y,z. However what I want to do is retrieve the values return and assign them to variables. How do I go about this? Thanks in advance.


 Using MyConnection As New SqlConnection("Data Source=sql2008.aspnethosting.co.uk;Initial Catalog=xxx_xxxx;Persist Security Info=True;User ID=xxx_xxx;Password=xxxxx")
            Dim MyCommand As New SqlCommand("SELECT txfrequency,mode,details FROM frequencies WHERE county = @location", MyConnection)
            MyCommand.Parameters.Add("@location", SqlDbType.NVarChar).Value = e.PostBackValue
            Dim r As SqlDataReader = MyCommand.ExecuteReader()
            GridView1.DataSource = r
            'SELECT COUNT(*) FROM TableName WHERE SomeWhereClause 
        End Using


Lookup field which allows multiple choice - problem when query with CAML


I created one simple list and added one column - lookup - I've chosen some source list and field, then marked "Allow multiple choice". I created new item in that list, by adding more than one valie to that coulm.

The problem is, when I use CAML query, that column has such values:


and it's suppose to be like this:


do You have any idea, why it doesn't query first ID?

Highlight multiple dates in calender extender



       I want to highlight the dates corresponding to an event (eg Jan 5-Jan 10) when the user clicks on the calendar extender.. Please advise how this can be done.


ALso is it possible to restrict the the dates that can be selected from the calendar extender?

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