Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Pull Data For Each Day Of A Given Month


Hi,

Writing a report that displays total hours, etc for each day in a given month and I'm having trouble comking up w/ an elegant solution. Tables are set up like this:

table1 (id, start_date,end_date,submitter)

table2( id,table1_id,start_time,end_time, etc)

Each record in table2 is a child to a parent record in table1 and there may be multiple children for each parent. The "start_time" and "end_time" fields are datatype "datetime" and are stored with actual times (not 12:00:00 AM"). I need the report to display the values for each day in a gridview, etc so I can act upon them from the "RowDataBound" sub (more math, etc). The problem that I am running into is that for records that have multiple children for a given day, I need to sum those results and display the summed data as a single row within the GV - can't seem to work that out - I keep getting every child record displaying as its own row in the GV.

Any thoughts? I'm sure it's probably fairly simple but I am still a beginner...thanks as always


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Parameter Selection Of Month, Showing Selected Month And Sum Up To That Month In Another Row
Hello what I'd like to display the following in a matrix report:

Parameter selected: 3 (March), 2008 (Year)

Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...

So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.

View Replies !   View Related
RDA Pull Problem: Command=PULL Hr=80040E4D Login Failed For User 'test'
Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:






Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}





Everythink works fine, when I use'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I foundfollowing line:




Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456



The user 'test' is member of db_owner, db_datareader andpublic roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.



So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?



Please help.



Thank you.

Fipil.





View Replies !   View Related
Pull Data From Two Tables
I am new to SQL, as old as it is. I am not new to programming Inormally just use Access.I have two tables for a little project manager I made. After updates Isent an email to the user. I need to populate the user based on the"Assigned To" field I use, but I only log the username and not theemail address. Is there a way to associate the "Assigned To" to theUser's account in the "User" table so that I can pull thier emailaddress through code?ThanksChuck

View Replies !   View Related
Pull Data From Sql Express...
I have the report sever running on windows2003 with sql standard server 2005. However, I' m trying to create a report that pull data from a different server with sql express. Could I do this? If so...any links, examples, suggestion I can take a look at.

thanks

View Replies !   View Related
RDA Unable To Pull Data
Hello -

I am trying to pull data from SQL Server 2000 database onto my Pocket PC and the simple query works onNorthwind sampledatabase but does not on another custom build database.I did test the statement in the SQL Query Analyzer and it works.

The error I get is: "Failure setting up a non parameterized query, possible incorrect SQL query."

I know the code is right - thanks to Rory B's screenscasts. Any guidance will be greatly appreciated!

Thanks!

Moldau

Here is the code:

private void RdaPull()

{

try



{

//Create the database

if (File.Exists("\My Documents\Test.sdf"))

File.Delete(\My Documents\Test.sdf);



SqlCeEngine engine = new SqlCeEngine();

engine.LocalConnectionString = localConnection;

engine.CreateDatabase();

engine.Dispose();

//Initialize RDA Object





SqlCeRemoteDataAccess rda = null;

rda = new SqlCeRemoteDataAccess(rdaUrl, localConnection);

rda.Pull ("Customers", "select * from Customers", rdaOleDbConnectionString);

rda.Dispose();

MessageBox.Show("Done");

}

catch (SqlCeException ex)

{

MessageBox.Show(ex.Message);

}



View Replies !   View Related
Pull Back Data Using A Dataset
Hello, I am trying to pull run this sql statement but it's bombing out at the comm.ExecuteNonQuery();. ..
 
Could someone help me figure this out..
 Ex.System.Int32 bum = System.Convert.ToInt32(Request.QueryString["dum"]);
SqlConnection conn = new SqlConnection("Data Source=**********************");SqlCommand comm = new SqlCommand();
comm.Connection = conn;SqlDataAdapter myadapter = new SqlDataAdapter(comm);DataSet myset = new DataSet();
conn.Open();
 comm.CommandText = "Select * from Order_Forms where (Order_Num = " + Session["dum1"] + " ) ";
 
comm.ExecuteNonQuery();myadapter.Fill(myset, "Order_Forms");
myset.AcceptChanges();
 
Can yo usee the problem???
 
 
 

View Replies !   View Related
User Defined SQL Data Pull With ASP
Good Afternoon,
I have recently been tasked to come up with a means by which to create SQL data pulls based on user entries.  What I mean by this is, I will have a pre defined SQL data pull set up and the user can then select the criteria upon which to pull said data.
So, say I want to display a table where one of the colums has is the "Year".  I dont however want all years of data pulled, only 2005 - 2006.  So, in form field 1, I could select start year as 2005 and in form field 2, I could select end year as 2006.  This would then give me all data from this table based on that year range, or whatever year range I select and submit.
Is there a way to do this in ASP?  I have been reading up on UDF with SQL, but I cant find anything about linking it through the use of web forms or through ASP.  Or if there is another way to accomplish this?  Any assistance is very much appreciated!

View Replies !   View Related
Pull Data From Different Database Server
Hi,
  In my application i am storing the data in a database(server A). And i have a scenario where in a middle server(server B) is there in which i need to create a table and pull the data from one of the tables from my original database server(server A) into second database server(server B). Where can i get the code to write a job script which can connect from server B to server A and pull the data from the table and delete that data from the table in server A. Can any give me the code or link where i can find the code. Please help.
Thanks

View Replies !   View Related
Pull Olap Data Using SQL Commands?
I have an OLAP server and would like to use my Chart FX software without having to purchase the OLAP extensions on the server due to budget restraints (ouch).

I've heard that it is possible (although limited) to attach toan OLAP cube using SQL select statements (not MDX).

Basically, I would like to pull the OLAP data in the relational sense.

Is this possible? If so, are any good articles on this subject?

I'm new to OLAP and would like to transition slowly.

Thanks in advance :)

View Replies !   View Related
How To Pull Isolated Data From Two Tables?
Hi, I am trying to pull e-mail addresses of certain users from a list of all the users in our company. I have two tables. One contains a single column of only the users I need. The other table contains every user and their e-mail address. Could someone tell me how to pull this data from these two tables. I am new to SQL Query and have been trying to figure out these JOIN statements, but nothing I am doing seems to work.

Thank you,
Lisa

View Replies !   View Related
Data Pull From Dissimilar Tables
i am completely new to sql but have a need to create a sort of check registry.
the data comes from two separate files like

checks
datenumnameacctamount
09/17/071747companyAtmz187.50
09/18/071748companyBtmz199.24
09/18/079326company1tmz29.65
09/18/071749companyCtmz1103.54
09/20/079327company2tmz255.01
09/20/071750companyDtmz187.12


deposits
datetypeacctamount
09/17/07deposittmz1100.00
09/17/07deposittmz2200.00
09/19/07deposittmz1300.00
09/19/07deposittmz2400.00

i would like something like this as a result

(1st group - tmz1)
09/17/07 1747 companyA tmz1 87.50
09/17/07 deposit tmz1 100.00
09/18/07 1748 companyB tmz1 99.24
09/18/07 1749 companyC tmz1 103.54
09/19/07 deposit tmz1 300.00
09/20/07 1750 companyD tmz1 87.12


(2nd group - tmz2)
09/17/07 deposit tmz2 200.00
09/18/07 9326 company1 tmz2 9.65
09/19/07 deposit tmz2 400.00
09/20/07 9327 company2 tmz2 55.01



but when i form my statement with an inner join, i miss records, with the outer,
i get a ton of records (more than both tables combined).
i have been working with sql for about week now and can do simple queries but this is
beyond me at this point. any help what so ever would be greatly appreciated.

View Replies !   View Related
AS400 Data Pull Fails As Job
When I execute the package in Debug mode, it works.

I then import the package into SQL Server 2005 and it is showing under Stored Packages --> MSDB - AS400Package

I can right click and Run Packagefrom there and it works fine.

When I set the package up to run as a job, it fails. What I have noticed is that the Sign On that I use in the package shows that there was in "invalid login" after the job fails.

I hope I have this post in the right place and hope I have left enough information behind to help. If not let know what else I need to post. I have only been using Business Intelligence Development for a couple days and SQL Server 2005 for about a week or so. All of the SQL DTS packages that I have created seem to be working fine with their jobs as well as my stored procedures.

Thanks for any help you can give me.

View Replies !   View Related
Can I Pull The Data To An Existing Table?
I am building RDA process between SQL server and SQL server CE. Since when we use the method 'SqlCeRemoteDataAccess.pull()', the data will be pulled down from SQL server to CE server and a new table is created with some addtional columns. As I only intend to pull the data and never push it back to SQL server, if I pull the data down for the second time, there will be an error saying that table is already existing. I wonder whether I can pull the data to an existing table everytime I do the RDA?

View Replies !   View Related
Question On Data Retrieval Using RDA Pull
Hi All,


I am working on an application to retrieve data to the windows ce 3.0 handheld from the Sql server database. I am able to retrieve the data from the server using RDA pull method and able to see the data on the local handheld database. To display the retrieved data in the dialog to the user, it requires to query the local databse and get the information and then display the information, which is taking some time.

My question is, to reduce the time and improve the performance , instead of pulling the data to the local table, is there any way to pull the data and have it in memory and display the details to the user?

To develop the above application, I used some of the code to pull the data from the server from the sample application C:Program FilesMicrosoft SQL Server CE 2.0SampleseVCeVCReplRdaHPC. I am developing the application for windows ce 3.0 device using eVC 3.0

thanks
pyd.

View Replies !   View Related
Reg:Error In PULL (RDA) For Data Sync
Hi Darren & All,

              Thanks! As per your guidance, i have tried in the pull method.Now i am getting error "The LocalTablename parameter is already specified".

Coding as follows:

string rdaOleDbConnectString = @"Provider=SQLOLEDB; Data Source=PRASSANAPRASANA;Initial Catalog=master;User Id=sa;Password=master";
            SqlCeRemoteDataAccess rda = null;
            try
            {
                rda = new SqlCeRemoteDataAccess();
                rda.LocalConnectionString = "Data Source=E:\SampleTest.sdf";
                rda.InternetUrl = "http://192.168.1.118/TEst/sqlcesa30.dll";
                rda.InternetLogin = "IUSR_PRASSANA";
                rda.InternetPassword = "PRASSANA";
                rda.Pull("company", "Select * from company", rdaOleDbConnectString, RdaTrackOption.TrackingOn);
                MessageBox.Show("Pull Method");
            }
            catch (SqlCeException)
            {
      Message.show(ex.message);
            }
            finally
            {
                // Dispose of the RDA Object
                //
                rda.Dispose();
            }
        }
Please  guide me in detail, what mistake i have done in this method.

Thanks ,

Prasanna.

View Replies !   View Related
Remote Data Access (RDA.Pull)
Hi,

I am trying to use RDA.pull to pull data from SQL server 2000 to sql mobile, but it fails when some columns are included in the SQLSelectString, though the datatypes involved are only char, float, smallint and smalldatetime, and nothing is longer than 30. Since there is a very large number of columns, it is difficult toisolate the guilty oneby trial and error! Coud someone please help?

- Nag Rao

View Replies !   View Related
Pull Data From Cache 5.0 To SQL 2000 (fast?)
I'm currently working with a 10 million plus row database with the dataresiding on a Unix box with Cache 5.0. The problems is that it can take fivedays to pull one table from Cache to SQL 2000 using the ODBC connectionprovided by Cache in a SQL 2000 DTS package. I think the real problem isconverting the data from the post relational format (Cache) to a relationalformat (SQL 2000)???Does anyone have any ideas / suggestions on how to speed this transfer ofdata? I'm very new to Cache and any help would be greatly appreciated.Thanks,-p

View Replies !   View Related
General Syntax To Pull Xml Data From A Web Server


Could someone give me the general syntax to pull xml data from a web server that I have access to? I do know that I can not use an ip address, I have to use the hostname, etc.

I was using something like

FROM OPENROWSET(BULK '\SERVERNAMESHAREPATHDATAFILE.XML', SINGLE_BLOB) as x

instead of using SERVERNAME I am user http:\www.mycompanyname.commycompanynamestoredata.xml

I do not understand why this does not work. I hope I gave enough information for this.

Please help.

Thank you
LadyDee

View Replies !   View Related
Use SSIS To Pull Data From A BASIS Database.
Hi All,

Anyone have any experience in connecting to a Basis database?
I want to build an SSIS package to ETL data out of a Basis database and into a sql server 2005 db.
I have downloaded the ODBC driver, but am not usre how to plug it in to SSIS to do teh extraction.

Also I am totally new to Basis...it is not an RDBMS at all but the literature says it supports SQL queries, so I am not sure about where to start.

Thanks,

Stephen

View Replies !   View Related
Problem Pulling Data Using HTTPS -SQL CE RDA Pull


Hello All,
I have an application pulling data from SQL Server 2005 which is running on windows mobile 5 devices, and RDA is setup and working perfectly over http and this is in the production for long time, but we have decided to implement SSL for transportation recently and we have a certificate installed to the server and i am trying to pull data using https and getting "the request to send data to the computer running IIS has failed" message, only RDA stopped working over SSL, i can access scripts and other resources from the device using https (I made code to trust any certificate for any web requests) the link to the dll is accessible from outside,i have installed the root certicate on the server, the virtual directory has been changed to communicate via https on web synchronization wizard, then problem occurs, otherwise it will work ok, i am totally stuck at this point, is this a problem with port setup port 443 is opened, the issue is clearly a SSL issue. any help would be appreciated..what are the things to be done to implement SSL for RDA, i don't see anything more in msdn.
thank you so much!
sush

View Replies !   View Related
Using Stored Procedures To Insert And Pull Data From Database
I have my database: "RequestTrack"
My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate  (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID   (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button:
txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text
**After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!

View Replies !   View Related
Trying To Pull Data From Server But Getting A Blank White Screen
Hey guys, i finally connected to the db fine however for some reason i cannot pull the data.  I was hoping somebody here might be able to help me.  The code is below
 
    Function MyQueryMethod() As System.Data.DataSet        Dim connectionString As String = "server='xxxx'; user id='xxxx'; password='xxxxxx'; database='wel"& _            "sh_indiv'"        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
        Dim queryString As String = "SELECT [test12].[name], [test12].[grade] FROM [test12]"        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand        dbCommand.CommandText = queryString        dbCommand.Connection = dbConnection
        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter        dataAdapter.SelectCommand = dbCommand        Dim dataSet As System.Data.DataSet = New System.Data.DataSet        dataAdapter.Fill(dataSet)
        Return dataSet    End Function
 im kinda new to programming with vb for asp.net pages so please bare with me.

View Replies !   View Related
RDA Pull Method Not Creating Tables/inserting Data
I can't see what is going on, this is the situation:

I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:

I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.

So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
------------------------------------------------------------------------------------------------------------------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source="\Program Files\client\db\MobileDB.sdf"";

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);

IList _tableNames = new ArrayList();
IList _queries = new ArrayList();

############
Code that prepares tables and queries
############

for (int counter = 0; counter < _tableNames.Count; counter++)
{
rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError");
}


the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.

View Replies !   View Related
How To Pull Data In The Inline Code Block Inside A Template?
The code block is embedded into a grid view item template.    <ItemTemplate> <% if ((bool)Eval("isNew")) { %> New Post <% } %></ItemTemplate> but as per rule Eval and Bind can be only use bind the data. How would i pull the information from sqldatasource in the template like this? 

View Replies !   View Related
How To Pull Data Into A Datagrid From The Same Table, 2 Fields, And Display Them As 1 Field In The SqlDataSource?
Hi, Im trying to pull data from 2 fields in the same table into a SqlDataSource that feeds into a GridView, and display them as 1 field in GridView? I have a database table that has entries of users and their friends. so
this tblFriendUser has a column called UserName and another column
called FriendUserName.
I am trying to get a list of friends for that particular user. Note
that if User1 initiated the friend request, he will be listed as
UserName and his friend as FriendUserName, but if his friend initiated
the friend request, it will be vice versa: him being the FriendUserName
and his friend the UserName. So I want the following 2 queries run and merged into
one query in order to return 2 columns only: UserFriendID & UserName, is that
possible? Is my design bad? Any suggestions/advice would help! Thanks a lot!


SELECT UserFriendID, UserName
FROM tblUserFriends
WHERE (UserName = @UserName);

SELECT UserFriendID, FriendUserName AS UserName
FROM tblUserFriends
WHERE (FriendUserName= @UserName);

View Replies !   View Related
ODBC Error When Running Stored Proc To Pull Sql2005 Data Into Access 97
I have a stored procedure the runs a query and loads data into a access 97 table. We recently upgraded the sql server database to SQL2005. If I check my odbc connection when creating it is test successful. however when I run the query in access to kick off the stored proc it gives me a ODBC Connection failure message. Neither the stored procedure nor the access database have changed. I am able to run the stored proc successfully from sql query analyzer. is there are compatability issue with Access 97 (they refuse to upgrade) and SQL2005??

View Replies !   View Related
Select Last 6 Month Data In Sql
select * from tbl where nmonth between datepart(mm,DATEADD(month, -2, getdate())) and datepart(mm,getdate())
this query is returning my data  between 1(jan) and 3 (mar)
if i change my query to get all datas between nov to mar
select * from tbl where nmonth between datepart(mm,DATEADD(month, -4, getdate())) and datepart(mm,getdate())
this query is not returning data since the month between 11 to 3
Guys,Help me out to correct this syntax.
 
 
 
 
 

View Replies !   View Related
Group Data By Month
Hi,
how can i group data using specified date like for example per month.
here is my sample data.

fielddate | fieldnumber
january 1, 2007 | 1
january 5, 2007 | 2
February 2, 2007| 3
March 4, 2007 | 3
March 5, 2007 | 4

i want to sum all fieldnumber by month
result will be:

resultdate | resultnumber
january 2007 | 3
February 2007 | 3
March 2007 | 7

thnx for the help in advance.

View Replies !   View Related
12 Month Rolling Data
Hi All,
I wanna pull a 12 month rolling data and donno how to do that, can anybody help ?
Problem:
lets say if i run the query today i should get data between oct-3-2006 and oct-3-2007, my main colums is monthyear which is date datatype.
Appreciate your help.
Thanks
Chinna

View Replies !   View Related
How To Select Data For A Given Month Or All Records
I posted this question last night and thought I had an answer.  I have a date field.  I want to be able to filter records by the month of the date.  To do this, I pass the integer of the month.  But I also want to be able to return all the records if no month integer is passed.  So functionally,select * from table where (MONTH([AD ENDS]) = @month)will return all records where [Ad Ends] is in January if @month = 1 and all records if @month is empty.  The solution I got last night was to use select * from table where (MONTH([AD ENDS]) = ISNULL(@month, MONTH([AD ENDS]))) If @month is null, all records are returned.  I was focused on another aspect of the page when this was posted.  It worked in the designer, so I thought I was set.  This morning I realized I don't know how to pass a null variable in a querystring.  Since a querystring is a string, it probably can't be done.  Another suggestions was to change this programmatically.   But is there a way to do this in the dataset?  I'm using SQL Server 2005, a strongly typed dataset and the designer.Diane  The answer i got last night was to  

View Replies !   View Related
Yesterday, Last Month, Last Year Data
Hi,
Can anyone tell me how to get data for yesterday, last month, and last year?

As a example, I wanna know how many loans disbursed for yesterday(not for last 24 hours).

It may be smthing like this. But when I run this one , it gave me a syntax error.


WHERE DATEDIFF(dd,Account.CommencedOnDate,GetDate()) = 1


can anyone help me out pls?

View Replies !   View Related
Query The Data For Month,year Range
Hi,


I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.

(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month >= 6 AND R.Year = 2006)
Group By R.Year , R.Month )
Union
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month <=3 AND R.Year = 2008)
Group By R.Year , R.Month )

View Replies !   View Related
Show The Data Of A Report For Previous Month
hi,

Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.

we can show it if its only for previous day. In this case it is like this;

cdtable.SubmittedDate = GETDATE () - 1

But I dont find function like GETMONTH() or smthing.

Thanks

View Replies !   View Related
Need Report Also Showing Months Even When No Data For That Month
Hi All,

would like to make a report with sales figures per month even is there are no sales figures that month.
Report should still show the month.

Any help to get me started inthe wright direction much appreciated

edwin



View Replies !   View Related
Show The Data Of A Report For Previous Month
Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.

we can show it if its only for previous day. In this case it is like this;

cdtable.SubmittedDate = GETDATE () - 1

But I dont find function like GETMONTH() or smthing.

Thanks

View Replies !   View Related
A Design Related Problem Regarding Filtering Data Pr Month
Hi all!

I have a problem.. I have a report, with subreports, which does the job it was supposed to do. Unfortunately, subreports does not export to Excel very well, so I have to come up with a solution without the use of subreports.

The scenario (keeping it simple)

I have a list of departments. Each department can have multiple costs attached to it. The cost happens on a specific date. Fairly straightforward and simplified

I want to view each departments costs, sorted by month, like this:


Jan - Feb - March - April....
DepSUMCOST

DepFOR EACH MONTH
Dep
Dep

...
...

I did this in the old report by passing the department ID and the date interval as parameters to a subreport and have the subreport return the sum of all the fields, which worked fine.

Now that the subreports can no longer be used, I'm struggling on how to sortmy output on that date interval. I can group my result pr. department, but how to filter the result so that I get the right numbers in the right month... no luck yet.

As far as I know; I can't use different datasets in one table? I could have made a different dataset for each month...

Anyone know of a way to design this? I'm sure the problem isn't unique.
Does one have to aggregate the sums inside the SQL query instead?

Thanks for any replies!

Regards,
Mats

View Replies !   View Related
Sql Query To Retrieve Comments Based On Selected Month And Data
hi,
i am generating report for my project.
my table goes like
ID   VASID     VASSID     JanMail   JanVisit    JanPhone    JanComment    FebMail   FebVisit    FebPhone   FebComment................. DecPhone  
1      25            4              True         False         False            Mail me             False       True           False            Visit me                  
 
My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively.. 
once user check on Mail and select a month suppose January in my report,the report should generate only January Comments.
here user can select any number of months.if user selects january,february and march, report should generate respective months Comments.
Please help me in writing SQL Query to get the comments.......
 
if any one know the solution for the above problem,Please help me
 
Thanks & Regards,
 

View Replies !   View Related
Diffcult Is Issue, How To Add A Row Of Data For A &&"customer&&" That Has No Data For A Month
Ok i dont know how to ask this question but here it goes.
I am pulling monthly data for suppliers showing ontime, early and late deliveries
let say month of Jan. they had
supplier month stat qty

A Jan early5
A Janlate1

A Janontime20



Afeblate50
Afebontime5


I need to retrieve the data but as you can see since feb did not have any early I am missing the data

how can I create a line
A feb early 0

but remember that i have multiple customers and multiple months.

All I canthink of is saveing the base data then run a cursor to inser the lines as needed


View Replies !   View Related
Updating Datetime Data Types In A Table To Show Just Day, Month, Year
I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:


declare @MyDate as datetime

set @MyDate = '04/02/2008 18:00:00'

select substring(convert(varchar,@MyDate,101),1,10)

I get '04/02/2008', but when I do this:

update TheTable
set TheDate = substring(convert(varchar,TheDate,101),1,10)

I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?

View Replies !   View Related
Get Last Day Of Month For Agiven Month And Year
Does anyone know how I can get last day of month
if I pass a function a given month and and given year.
@Month = 2
@Year = 2004
The result I would need is 29 because there are 29 in
the month of February in the 2004.
Any help on this is greatly appreciated.
Kellie

View Replies !   View Related
Get Last Day Of Month For Agiven Month And Year
Does anyone know how I can get last day of month
if I pass a function a given month and and given year.
@Month = 2
@Year = 2004
The result I would need is 29 because there are 29 in
the month of February in the 2004.
Any help on this is greatly appreciated.
Kellie

View Replies !   View Related
Best Practice To Pull Data From Sql Server 2000 To Sql Server 2005 With Dynamic Queries
Hi There,

I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?

Thanks a lot!!

View Replies !   View Related
How To Convert Calendar Month Year To Fiscal Month Year


I have a calendar date field in my table. I want to add one more field (Fiscal Month/Year)in my table on basis of Calendar Date filed.
Our fiscal year starts from October i.e. Calendar Month/ Year : 10/2007 =Fiscal Month/Year: 01/2008

The end result which i am looking for :








CalendarMonth Year
Fiscal Month Year

10/2007
01/2008

11/2007
02/2008

12/2007
03/2008

01/2008
04/2008

02/2008
05/2008

03/2008
06/2008

04/2008
07/2008

05/2008
08/2008

06/2008
09/2008

07/2008
10/2008

08/2008
11/2008

09/2008
12/2008

10/2008
01/2009

11/2008
02/2009

12/2008
03/2009


View Replies !   View Related
Month-to-month Function
Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed

September:
@CurrentDate = 9/10/2007
@EndDate = last working day 9/30/2007

Total# of working days = 15

October:
@CurrentDate = 10/1/2007
@EndDate = last working day 10/31/2007

Total# of working days = 23

November:
@CurrentDate = 11/1/2007
@EndDate = last working day 11/30/2007

Total# of working days = 22
etc.
Any ideas of how i can approch this?

Thanks in advance.



View Replies !   View Related
Grouping My Month/year Desending By Month/year
i have some classes that I want to group by month/year (note:i dont need the day of the month)
how do i wirte my sql so it only gives me the dictinct groups month/year  of the classes I have so that it comes out like so..
11/2006
12/2006
1/2007
3/2007
i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.
dbo.classgiven.classdate date of the class.thank you all 
SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/"  + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode   FROM  dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND  (dbo.classT.coned IS NOT NULL)", conNorthwind )

View Replies !   View Related
Search Day Before Last Day Of The Previous Month + Day Last Day Of The Previous Month&&"
how can i do this
search between 2 rows
day before Last day of the Previous Month + day Last day of the Previous Month"





Code BlockSELECT empid, basedate, unit_date, shift, na
FROM dbo.empbase
WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)

AND

(basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)




TNX

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved