Hi, I would like to select data from my source system based on fiscal years. I don't want to hard code anything, e.g. select * from person where startdate > 01042005. I want to try and use a store proc or function. The main requirement is to always retrieve data from source tables for the last two fiscal years (based on todays date - getdate). Our fiscal year starts the 1st April. Therefore, If todays date is 14 October 2007, I would like to select all persons whose start date was greater than 01 April 2005. Is there an easy way to do this in sql server 2005 without hard coding dates?
i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?
the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY
problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.
again, thanks for reading ... and any help in advance.
SELECTcount(*) as 'Data Points', '2001' as 'Experiment Year' FROM tbl_experiment_data
WHEREstart_date BETWEEN '9/30/2001' AND '10/01/2002' and completion_date BETWEEN '9/30/2001' AND '10/01/2002' and status = 'CaseClosed'
UNION
SELECTcount(*) as 'Data Points', '2002' as 'Experiment Year' FROM tbl_experiment_data
WHEREstart_date BETWEEN '9/30/2002' AND '10/01/2003' and completion_date BETWEEN '9/30/2002' AND '10/01/2003' and status = 'CaseClosed'
UNION
...
expected output....
Data Points______ Experiment Year 32_____________ 2001 102____________ 2002 .... ....
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |... Orders -> id | idCustomer | date | ... Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total FROM Products INNER JOIN Orders ON Orders.id = Products.idOrder INNER JOIN Customers ON Customers.id = Orders.idCustomer WHERE year(date)=2014 GROUP BY customers.name ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015) -------- customer A | 1000$ | 2000$ | 3000$ customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
I am using the query below and it is working great to give me montly averages. I need to find the fiscal year to date averages for the same data. I think all I should need to change is the Select and Group by sections but I am having no sucess.
SELECT Format([CallDate],"yyyy mmm") AS [Month], Count(DailyCallStats.CallDate) AS CountOfCallDate, DailyCallStats.Agent, Avg(DailyCallStats.Inbound) AS AvgOfInbound, Avg(DailyCallStats.Outbound) AS AvgOfOutbound, Avg(DailyCallStats.TotalCalls) AS AvgOfTotalCalls, Avg(DailyCallStats.AvgWrap) AS AvgOfAvgWrap, Avg(DailyCallStats.ParkTime) AS AvgOfParkTime FROM DailyCallStats GROUP BY Format([CallDate],"yyyy mmm"), DailyCallStats.Agent, Format([CallDate],"yyyy mm") ORDER BY Format([CallDate],"yyyy mm");
I need to take multiple rows in a table that store fiscal data and denormalize into one row with all the fiscal information in it. any suggestions on how to approach this creatively - lots of data will make it important to make this efficent, will need to run each year so I would like to make it portable.
Hello, I see the following in connection strings all the time: providerName="System.Data.SqlClient"/> For example:<connectionStrings><add name="classifiedsConnection" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|classifiedsdb.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/></connectionStrings> What is providerName="System.Data.SqlClient" ? What is it used for?Thanks, Jim
care session quarter Q1-15 Q2-15 Q3-14 Q3-15 Q4-14
I am using this [care session quarter] column in the group by clause to achieve but no success.IF I use date column in the select clause and Group by clause then it comes correctly but groups by all dates which is not required.
Ideally I want show only quarter aggregates. The [Date Dimension] table has the column [care session quarter] which stores all the quarters of years along with dates for each day. i..e I have all columns in [Date Dimension] table as shown below
Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.
A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.
SELECT MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10], MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10], MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11], MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11], MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],
[Code] ....
I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).
"I meant to delete just one assignment, doing which was giving me difficulty.
I deleted a whole category by accident which had most of my daily grades in it for a course with quite a few of them. I was negligent in backing them up, so I cannot restore them. I have an XLS file from the midterm, but am missing about 6 grades after that.
Is there any way to recover the grades I had in the deleted category. It has been a couple of days since I entered any new grades. HELP!"
My current backup strategy is to do a full backup 5 pm, differentials every 2 hours, logs every 30 min, all so they arenot simultaneous. Backing these all to same backup device via three different jobs.
Then the server is backed up to tape shortly after the 5 pm full backup.
Obviously, I can't restore from the backup for this situation because it would hurt other data.
But - is there a way I can take last night's backup from tape and restore it to a different database than our production database? (Such a database doesn't at this time exist. Would I detach, copy over the files, and then reattach on prod and attach on the devl side, and then restore from the backup device to the devl copy?)
I've never had to perform this kind of rescue and am wondering if my backup strategy is flawed since it isn't immediately evident how I can easily do this.
I needed a similar thing from our Oracle DBA yesterday, and he went to tape, copied out a table and put it on the Oracle database under a different owner, and I easily fixed the few records that I had botched.
That's exactly what I need to do now with MS SQL 2000.
I was hoping someone would be able to help me, I trying to go through several databases, 111 to be exact, and get the data out of a particular table which is present in all the databases on the same server.
I know how to get the names of the databases from the INFORMATION_SCHEMA, but how can use the name for it to cycle through all the databases and get data out of the one particular table.
Hi, I need some help. I have this query where i need to pull up students that have more than or equal to 3 absences in a row. Now I am getting all those students that are absent but not in a row. But i was wondering if there is a way to tell if a student was absent three days in a row . There is a date field that is being used to identify the day of the class and a status field that identifies if the student was absent or present. Please help someone.
Up to the moment I've got enough knowledge for read data stored into .LDF files by dbcc log and so on. It's very useful and interesting. Now, I wonder how to retrieve the same information but on MDF files.
At first, I want to make sure that is not possible by mean of traditional methods (dbcc or something like that) I suppose so but I'd like to hear opinions regarding that.
Thanks in advance for any idea, though or further information.
this is a simple problem but it's just driving me mad as it's not reading from my dB. basically, I've have reviews stored in my dB and want to display them in a textbox by clicking on a button called btnReviews. I think the problem might be that there is too much text stored per row of the table (as it is a review), but I have the datatype set as text in sql. here's the simple un-errorred code I have behind the button. any ideas where I went wrong. i've a feeling it's something small but it's just taken too long to figure out.protected void btnReviews_Click(object sender, EventArgs e) { String strConn = ConfigurationManager.ConnectionStrings["conLocalDatabase"].ConnectionString; SqlConnection dbConnection = new SqlConnection(strConn); SqlCommand dbCommand = new SqlCommand("Select [ReviewC] From [Review])", dbConnection); dbCommand.Parameters.AddWithValue("Review", txtReviewView.Text); try { dbConnection.Open(); dbCommand.ExecuteNonQuery(); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { if (dbConnection != null) { dbConnection.Close(); } } }
Hi I ve a datagrid . And Two Database table in sqlServer2005. The name of the tables are 'Property' and 'userid'. My datagrid wants to retrive all records from Property table and one record from userid table. The Property table contains Propertycode, lastdate , departmentname. The userid table contains so many record along with 'id' record which my datagrid wants to retrieve. pl tel me how 2 write code for that?
I have four tables that need to be loaded into an ASP.NET application. They need to be loaded together into one result set and sorted. Is it possible to load four tables together and sort them using an SQL statement?
I am aware of and am using the SELECT ... ORDER BY feature of MSSQL in my present ASP.NET application to retrieve from single database tables. I'm using merged datasets and a sort method to solve the above problem at the moment.
I'm preparing a report that will display provider number, provider name, and a single field that will show all the counties that specific provider serves. I realize from researching the coalesce posts that this can be done. However, when I try to retrieve data in the same select statement as my coalesce, I get the error: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."The listing of the counties must be specific to the provider, so my original code was:
DECLARE @Counties varchar(1000) SET @Counties = '' SELECT a.PROV_ID, a.PROV_NAME, @Counties=coalesce(@Counties,'') + b.COUNTY + ',' from ECBH.dbo.tbl_PROVIDERS a inner join ECBH.dbo.tbl_Provider_Serv_Regions c on a.PROV_ID = c.PROV_ID inner join ECBH.dbo.tbl_Regions b on b.REGION_ID = c.REGION_ID where c.PROV_ID = a.PROV_ID and a.MASTER = 1
I thought about creating a table to hold the coalesced values (need to coalesce two other fields as well), but wouldn't an insert to a table fail for the same reason?The counties table does not relate to the provider table, but does relate to a provider_county table (which in turn relates to the provider table).
Thank you for reading this. I am faced with a problem where there is no storage of deleted or updated information in an OLTP DB. I am mostly concerned about retrieving the deleted data from a table when an update occurs so as to make the deleted data show as an error. Is there a way to retrieve these kind of data from a DB, if so how? Can these data be retrieved by dates? Also how much time do you have before the data can no longer be retrieved? Assuming that the data can be retrieved.
I know the good stuff about datawarehousing but unfortunately we have no DW. Any help will be greatly appreciated.
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
I am working on a project on PocketPC in which it is required to reteive data from database. I have created database on simulator as .sdf file. I want to retreive data from eVC++ code. How i can do so?
Hi to all,I made a database using SQL Server 2005 and now I want to interact with that database through VC++ 2005. Is that possible?? If so, how can I do it?
I am trying to retrieve only the first few characters (12 to be precise) from this string that is coming in from FoxPro to SQL Server 2005 and I am coding in C#. I have tried these methods (after reading it in a book, as I am new to this) but it still gives me an error saying that the field cannot exceed 12 characters.
I have been thinking of using SQL server 2005 as i would like the flexibility i get through UDT. Retrieving the UDT data in managed could is ok but i would like to retrieve it in non .NET languages too.
For example lets say i create a UDT "Point". I insert data in a table that has some columns of type point.
Now is there a way i can get the data of the type point in a point object in non .NET languages like perl, python...
I am using Visual Studio 2008 with .NET 3.5 Visual Basic. I'm having a problem retrieving data from my SQL CE database. I have a function that imports a file, parses it and writes the contents to a database. When I then query the database from my code, the data I have just written doesnt seem to be there. Even when i close and re-run the program. Only when I first view the table contents through Visual Studio then re-run the program does the data show up.
The things I know are. When I insert data there is no problem. I can view the data through visual studio server explorer. only when I view data through the server explorer can my code retrieve it.
Could this be something to do with caching? Any help would be appreciated. The (stripped down) code I use to insert data is below
kevin
Dim rowsAffected = 0
Dim connection As New SqlServerCe.SqlCeConnection(connectionstring) Dim transaction As SqlCeTransaction
' command objects for inserting a ContactList and retrieving its last identity Dim cmdInsertContactList As New SqlServerCe.SqlCeCommand("INSERT INTO ContactLists (Name, FolderID) VALUES (@Name,@FolderID)") cmdInsertContactList.Parameters.Add("@Name", SqlDbType.NVarChar) cmdInsertContactList.Parameters.Add("@FolderID", SqlDbType.NVarChar)
' insert a new list cmdInsertContactList.Parameters("@Name").Value = list.Name cmdInsertContactList.Parameters("@FolderID").Value = folder.ID rowsAffected += CInt(cmdInsertContactList.ExecuteScalar())
I attach my SQL Server Express data file to my host. I would like to copy all of my member information back to my local computer. How can I do this? My host won't allow my to physically copy the data file over. My host is discountasp.net. Thanks,Jeff
Hi, I am using visual web developer 2005 express edition and Microsoft SQL 2005 to develop a fast-food ordering website and I am having trouble in retrieving data from a database table into a form. Can some one please teach me the way to write the syntax for retrieving or selecting a inserted value from the database? I have only know the syntax to insert data value from a form which is something like this: 'Create a New Connection to our daabase Dim test As SqlDataSource = New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("Connectionstring1").ToString 'This is the SQL Insert Command test.InsertCommand = "Insert into Customer([Initial],[Correspondent_Name],[Correspondent_No],[Payment_Type]) VALUES (@Initial,@Correspondent_Name,@Correspondent_No,@Payment_Type)" 'Each of this insert a value into the appropriate command test.InsertParameters.Add("Initial", DropDownList4.SelectedValue) 'This is the selected "Initial" DropDownList value test.InsertParameters.Add("Correspondent_Name", TextBox10.Text) 'This is the Correspondent_Name value test.InsertParameters.Add("Correspondent_No", TextBox3.Text) 'This is correspondent_no value test.InsertParameters.Add("Payment_Type", "Pay upon delivery") 'This indicates that the payment is made with credit card test.Insert() But I have no idea the syntax to retrieve a data. Please help me with this matter as it is important to me. Thanks in advance!
Heres my requirement from a financial analysis im doing...I have just calculated an industry averages on financial ratios...Now i wanna upload this industry average to the system...so that I can compare it to the individual companies' averages after calculating a particular company's average; meaning i wanna be able to call the industry average of a particular ratio (eg Current Ratio) after calculating a company's corresponding ratio...Is there a code fragment i can use for this ?? Thanks in advance...Adam
Hi i have a junction table(UserGroups) which is linking my users table with my groups table, however when the information is coming back in the format below, instead i want the group names to appear in only one field, instead of repeating the same data, could someone please tell me what i need to change
UserName: Edwin CarolsUserAge: 28JobTitle: ManagerGroupName: AFC Below is my SQL statement; SELECT Users.UserName,Users.UserAge, Users.JobTitle, Groups.GroupName FROM Users INNER JOIN UserGroups ON Users.UserID = UserGroups.UserID INNER JOIN Groups ON UserGroups.GroupID = Groups.GroupID WHERE (Users.UserID = '5')
