Hi, I'm using SQL 2005, and I want get the total of data for February as an example. My table table is like this
submitdt
-----------
01/02/2008
25/02/2008
29/02/2008
01/03/2008
02/03/2008
so total submit date for february is 3. Your idea is highly appreciated.thanks you so much
I am looking for a script whichs exports data (by DTS?) into a flat file and store the files (according their date stamp in the transactions) with a name like 05_2002.txt, 06_2002.txt etc. The data in the table Transactions will be deleted after some time to prevent fast growing of this particular table.
Any idea?
Thanks
Mike
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
View 2 Replies View RelatedI have a table based around requisitions, and each requisition has a number of positions. That number can change over time through updates to pertinent rows rather than through transaction-like records that record an entire history, and I'm only able to get a monthly snapshot of the table. What I decided to do is still use one table for OLAP (fact_requisitions) but add a column called period_key that refers to the month the data comes from. So if I have two months of data then the table has each requisition twice, possibly with differing position counts, and new requisitions from the second month are only present once. Then I tried to filter the MDX query like so:
SELECT {
([Dim TimeRequestClosed].[Year - MonthNumber].[Year_Text].&[2008].&[1],[Dim Requisitions].[Period].[Period Key].&[200801])
}
ON COLUMNS,
NON EMPTY
{
([Dim Location].[Region Name].MEMBERS, [Dim Location].[Period Key].&[200801])
}
ON ROWS
FROM
[Requisitions]
WHERE
[Measures].[Request Closed Date Count]
This query doesn't work even though the data is there, it just returns nulls. Am I going about this all wrong? If not, what might I be doing wrong, and how would I get the query to return more than one period (e.g. tell Dim Requisition to match up with Dim Location on the period key)?
I'm trying to develop a query that provides sales data by Customer.GroupCode in monthly columns as depicted below:
GrpCd JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOT
Film 5,000 15,000 20,000
Aero
Elct 3,000 950 3,950
Desg
Edu 150 150
Here€™s a simplified version of the DDL:
CREATE TABLE invchead (
invoicenum int NULL ,
invoicedate datetime NULL ,
invoiceamt decimal(16, 2) NULL ,
custnum int NULL )
CREATE TABLE customer (
custnum int NULL ,
groupcode varchar (4) NULL )
The query below gets me close but it gives me gives me one row for each customer. So if I have 5 customers with the same group code, I get 5 rows for that group code. I need to modify it or come up with a different approach that gives me only one row for each GroupCode.
SELECT distinct
c.Name,
c.GroupCode,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=1) JAN,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=2) FEB,
......
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate)) TOT
FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum)
WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008'
Grateful for any advice that will get me closer to accomplishing this.
Thanks
iam new to MS SQL 7 server...i have two tables in my database say Table1 and Table2 having a comman field--- Name String(30). I want that dual data entry should be made for any single entry. That is if a name is entered in Table1, then same entry should be automatically entered in table2
View 8 Replies View Relatedquestion... i heard a bit about Control Macro Generator but have not found much reading material on it. here at our company we have employees fill out a weekely timesheet and typically have clerks enter the information into the TM.PTA.00 (Project Timesheet with Rate/Amount Entry) and submit it that way. say we have this timesheet in a nice little excel sheet with columns that match the input boxes on the form, how can we get it to automatically input the data instead of having a person sit there and type it all in?
View 3 Replies View RelatedHello Everyone,
I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.
The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.
Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.
After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.
Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.
Thanks in advance,
Brian
Hi,
Do you have any idea if there is / where can I find web based data entry into SQL 7.0 database. What I'm looking for is something like Oracle Forms but for SQL 7.0.
Thanks in advance,
Boaz
hey folks,
i am looking for code to show the last entry of a database that has a unique field. for eg.. i have columns AREA, NAME, NUMBER. i need to show the last entry for each unique area.
so if there was 5 seperate areas, the table would show the last five entries for each area. anyone help?
Hello all,
I'm a newbie to SQL Server. I would like to practice/learn setting up web based data access. Using SQL Server 2005 and probably a sample database like AdventureWorks, I aim to create a web based interface that will allow for data access and data entry. I'm almost finished with a book about T-SQL, and I am starting to read the MS Implementation and Maintenance book, but where do I start to learn about creating the web interface? What technologies/languages are important? ADO.NET? VB.NET C#.NET?
Thanks in advance.
I have a query that pulls data by date so that I have every day of the month but I would like to group it by month so that I only have one entry per month. Can that be done?
View 2 Replies View RelatedHi all,
I have been using SQL Server 2K for a couple of months now and I have been entering data into my tables through Enterprise Manager. I do this by selecting the table, right-clicking on it, and selecting Return All Rows.
After months of use, this method of entering data has become tedious and cumbersome. Is there an easier way to enter data into SQL Server? I don't know how to program Visual Basic, but I know how to create dynamic websites.
Microsoft Access offers the ability to make Forms, so that data entry is easier. Can SQL Server offer anything similar? Maybe SQL Server 2005 offers a GUI for data entry?
Cheers for your help!
Hi Guys,
I'm using Visual Basic 2005 Express and SQL Server 2005 Express. I have textboxes on a VB form linked to 2 database tables.
I am wondering if it is possible to use just ONE BindingNavigator to manage data entry and updates to THE database tables. I initially thought I could manage the tables but have I encountered some problems:
i)When I entered a new record, and clicked on the SAVE BUTTON the new record the textboxes for the 1st table saves the record to the database, but the textboxes for 2nd table still retained data in them and are not saving theirs to the database.
ii) The same textboxes for the 2nd table are NOT allowing for updates too! Or, could it simply be that it is not possible to use this method for data entry and updates?
Thanks you.
I am researching ideas for the best way to have users enter data into SQL.
I am familiar with VB and Access Forms and HTML coding but what I am looking for is a way to have a standalone app or website where users can enter information but I want the form to have branches that change depending on user input (such as selecting a meeting type then having specific questions open up related to it).
Any ideas on the best way to do this?
If a different forum would be better for this request please advise and I will move appropriately.
Respectfully,
Lewis
Hi,
I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows:
Duplicate data might arrive at your database via an interface to another system
Data is loaded into table from other sources because during data loads, the integrity constraints are disabled
Merging data from disparate systems
Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me???
Thanks,
Hi,
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -
"Invalid Value for cell (row 1, column 2).
The changed value in this cell was not recognised as valid.
.Net Framework Datatype: Datetime
Error Message: Index was outside the bounds of the array.
Type a value appropriate for this data type or press ESC to cancel the change."
Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
How do I input data into the field?
My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.
Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1
Here are the contents of the same -
Post#1
Orginally posted by me -
Hi,
I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -
"Invalid Value for cell (row 1, column 2).
The changed value in this cell was not recognised as valid.
.Net Framework Datatype: Datetime
Error Message: Index was outside the bounds of the array.
Type a value appropriate for this data type or press ESC to cancel the change."
Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.
How do I input data into the field?
Post#2
Originally posted by Jens K. Suessmeyer -
Hi,
you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.
---
http://www.sqlserver2005.de
---
Post#3
Originally posted by me -
Hi,
I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.
Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -
1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35
The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.
I also tried entering a time value standalone, without the date. Still no go.
(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)
Now I'm in a real fix because of the inability to enter date/time values.
Could you please help me out?
Post#6
Originally posted by me -
I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?
Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.
Post#7
Originally posted by me this afternoon, but it never showed up on the page -
Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.
Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.
I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?
Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.
In my search results I need to order my data by a particular entry rather than a particular column and then add all the other entries onto the end of the result set. Specifically I'm trying to order my records by a particular location in a city so those entries in that location appear first in the result set and then on the end I want to include all the other locations in that city.
I'm assuming I'll need to do these as separate queries and then use a UNION statement but I'm wondering if there is a better or easier way. Is there, for example, another type of ORDER BY clause that lets me order by a particular entry rather than a column?
Thanks
Has anyone here been able to incorporate a way of data entry using reporting services. What I would like to see is a single page that not only provides the report but also allows for the entry of data. It would be much cleaner to have both together.
View 7 Replies View Relatedhi i'm thinking of putting 3 texboxes for the user to enter their previous work exp which look something like this:
experienceFirst job.textboxSecondjob.textboxThirdjob.textbox
i'm kinda confuse on with the database. The 3 tables required means i will hav 3 sqldatasource ....which i didin't quite get it right with the Secondjob table.I try using identity_scope() and @@identity to get the ID from my first table(personel)which is autogenerate number. It works fine with Firstjob table but not secondjob table.
Generated error message:
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 6
The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_Secondjob". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.
The statement has been terminated.
INSERT INTO dbo.Personnel
(Name)
VALUES ('Jon')
insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)
values('Teachier, 'Gam', '7thn', scope_identity())
insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel)
values('Musician', 'KKSB', '8thn', scope_identity())
any suggestions....
Hi Guys,
I have a request to create a data entry form with validation. The data is in hardcopy and people have to enter data on the screen and then this data should be output to a CSV file, so that I can load that csv file to a table. One of other important requirement is that Data entry person should be able to view the data entered and she should be able to modify them aswell. I.E we should have a form where it should also display the entered data with modifyable feautre.
I know some VB, I thought about doing this in VB. Can anyone please SUGGEST ME HOW TO IMPLEMENT THIS or Any sample code?? I really appreciate for your Help and Input. Thanks
We have an asp.net app with about 200 data entry forms. Customers mayenter data into any number of forms. Each form's data is persisted ina corresponding sql table. When data entry is complete, it needs to beprocessed. Here's where the questions start.How can we easily determine in which tables a customer has data and howbest to select that data?We're not opposed to putting all the data in a single table. Thistable would wind up having ~15 million records and constantly have CRUDoperations performed against it by up to 5000 users simultaneously.With sufficient hardware, is this too much to ask of the db?
View 10 Replies View RelatedI'm reading data from a flat file source. If some data gets truncated, i have the option of 'ignoring', 'redirecting' or 'fail component'.
What i'd like to do is, to allow the data to be truncated, but i'd also like to write a log entry so that i can know that a particular rows data has been truncated.
I've tried 'redirecting' the row to a script component (as transformation), but i don't know how to determine if the error is truncation or not, not only that, i can't redirect the row back to the 'original' flow, which is a derived field column.
Any ideas on how to do ?
Cheers.
Hello All,
I am using the Web Site Administration Tool with the ASP.NET login controls to create/manage user logins & roles for my site.
I have exported the ASPNETDB.MDF into my SQL Database ready to use with my app. The problem that I'm having is that whenever I add a user, using either the Web Site Administration Tool or the Classes available in the code-behind. The entry is doubled up in the database.
This is not a problem when logging on....however, when it comes to deleting a user it will only take one of the entries out and leave the duplicate in there. So if the administrator has deleted a user from the app and then tried to create another one with the same UserName (Which whomever is well within his/her rights to be able to do), they cannot because the entry which has "stay behind" in the database conflicts with the entry.
Any help on this matter would be greatly apprieciated. If any more info on this matter is required please ask (It was quite hard to try and explain this :-)
I am getting the exception - System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database. Does anyone has any idea, how to resolve this?
Thanks
hai iam new to ssrs, please help me.
i have student billbale information assume what ever data it. i need to to dispaly total amount for the student at
Bottom Of Report By Daily, Weekly, Monthly, SYTD . take any example, i want to know formula.
thanks to advanced
Jacks v
I have a table "PrintArea" with below table structure.
The Details column have " " separated data.
CREATE TABLE [dbo].[PrintArea](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccessDate] [datetime] NULL,
[IPAddress] [varchar](16) NULL,
[SerialNum] [varchar](256) NULL,
[Description] [varchar](256) NULL,
[Details] [varchar](2048) NULL,
)
This is how the data lookis like.
INSERT [dbo].[PrintArea] ([ID], [AccessDate], [IPAddress], [SerialNum], [Description], [Details]) VALUES (104280, CAST(0x0000A28A00A88310 AS DateTime), N'70.89.39.161', N'InSign-f529c824-85a6-4a64-8c59-e9f786d718f3-YM2AS31367,BFEBFBFF000306A9', N'Printing', N'Headboard_Insert_4
1
1
7938
5579
[Code] ...
I want to calculate totalAreaPrinted based on below formula from Details column.
I am writing some scripts to calculate mean (avg) stdevs count and percentile_cont for some data..I am partitioning over multiple columns e.g. partition by year,month, day order by value..I performing unnecessary overhead as I throw away all but the last (or max) value in each window. (I use cte then select max from cte).That is I only care about the avg of the window in its entirety not specific subwindows.
View 5 Replies View RelatedHi there,
if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.
Thanks very much,
Manny
I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.
View 3 Replies View RelatedI have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
View 1 Replies View RelatedHi all
Recently i was asked to design and implement an On-line WebSite for a Shop Store.I decided to define some Category for this Shop's Products such as MP3-Players,Mobiles,Televisions,Monitors and so on....and then Assign each product to a certain Category.Something like Amazon.Com , So i designed my Category and Product Table as follow:
Category Table----------------CatID int Primary-KeyCatDesc varchar(50)
Product Table-------------ProductIDProductDescCatID
After that i noticed that each category has its own Fields and Property and sometimes somecategories have common Fields for example , Price ,Color are two Common Property or Fieldsfor all Categories that i mentioned earlier But , Size is a special property for only[Monitor] or [Television] Categories and isn't useable for [Mice] or [KeyBoard] categories for example. So i Added a table for Keeping each category's Fields as follow:
Category Fields Table---------------------FieldID intCatID intFieldDesc varchar(50)FieldType tinyint --> 0=Numeric 1=String 2=Boolean...
After that i should save each Field's Value somewhere for each product so i added thefollowing Table(Product-Field-Value Table) for this purpose:
Product-Field-Value Table-------------------------ProductIDFieldIDValue
So far every thing is ok But i've faced with a new issue!!!.As you have realized so far , each productbelongs to certain category and each category has its own Fields List , in the other hand one category maybe has only 3 to 6 Fields but another Category might has 10 or more fields,So my problem here is when user wants to Add new product to Databse ,how should i get the Field's value for each product in an ASPX page ?in the other hand i'm dealing with a dynamic structure as you can see, so how should i Render the appropriate User Entries for getting Values of each product dynamically and then Saving these values in the Database? what Asp.Net Components should i use for this purpose? Could anyone help me?
Thanks in advance.Kind Regards.