I have a database of the table payments
and I want to create another table with
the olds payments (18 months past)
a have a field with the payment date
date_pay.
How I can do that?
Thanks
Luiz Lucasi
I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .
View 6 Replies View RelatedI would like to show a datagrid where the columns are the last 12 months from whatever today is. I am drawing from what could be a rather large tagle in the DB. The Table looks like this:LogID LogTally LogDate Item
1 2 3/28/03 apples
2 1 3/29/03 apples
3 2 4/01/03 oranges
4 3 4/01/03 apples
5 1 4/01/03 grapes
.......
293 1 3/17/04 oranges
294 1 3/17/04 apples And if someone wants to see a report on apples, they would see something like:3/03 4/03 5/03..................3/04
3 3 - 3I can select one month (this one) like this:SELECT SUM(LogTally) AS Expr1
FROM TABLE
WHERE (MONTH(GETDATE()) = MONTH(LogDate)) AND (YEAR(GETDATE()) = YEAR(LogDate)) AND (item = 'apples')
GROUP BY itembut, I just don't know how to do it for the past 12 months. Do I play with the dates and do 12 seperate SELECTS?
THANK YOU!
I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
[Code] .....
Here is my Table:
CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));
[code]....
If I have and invoice date column and I want to now what invoices are 15 days past due or 30 days past due, how do I do this in a where clause?
WHERE tblInvoices.InvoiceDate ???
Hi every one,
I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview.
How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
I have a start date saved and an end date saved but when I query I only want to return the amount of months that have elapsed between the two, any idea?
View 4 Replies View RelatedIs there an easy way to calculate a sum for the previous three months?
Data
Date,Area,PropertyID,Volume,DaysInMonth
197904,6,888888,9589,30
197905,6,888888,27403,31
197906,6,888888,17130,30
197907,6,888888,14321,31
197908,6,888888,15234,31
[Code] .....
Desired Output
Date,Area,PropertyID,Volume,DaysInMonth,3MonthSum, DaysInMonthSUm
197904,6,888888,9589,30,NULL,NULL
197905,6,888888,27403,31,NULL,NULL
197906,6,888888,17130,30,NULL,NULL
197907,6,888888,14321,31,54122,91
197908,6,888888,15234,31,58854,92
[Code] .....
How to calculate the cumulative of months.
Jan FebMar AprMay JunJul AugSep Oct Nov Dec
321 394571 577 617 692924 944956 1010 1308 1686
if i execute my query in this month(getdate) then it should sum from Jan to Aug Similarly i execute same query it should sum from Jan to Sept So on.
I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)
Hi 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 a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
Is there an option is SQL Server or SSMS to view the past few already executed queries.
I need to check what are all the queries fired on SQL server.
------------------------
I think, therefore I am - Rene Descartes
Hello all,
Is there a way for me to copy a row, do some modification in a certain row and insert it to the same table as a new row?
Forward with thousand thanks.
Best regards,
Tee Song Yann
Hello All
Our Collection team has a report that has an error that I'm tring to fix. The Person who created this report just hard coded the current month due as =MonthName(Month(Fields!CurrentDue.value) &" " &year((Fields!CurrentDue.Value).
For the Year 2007 it works fine. But now that the new year has started the report Current Month Due read Dec 2008, but it should be Dec 2007. I thought Of putting in some IIF statements but there has to be a easier way. Is there a function that check the past due and automatically increases the year by one. Say the bill in sent in Nov 2007 past due should be 30 days, Dec 2007. 60 Day should be Jan 2008 and so one.
Last Report Hours
=monthname(month(Fields!LastReportedHours.Value)) & " " & year(Fields!LastReportedHours.Value)
Days Past Due
=Fields!DaysPastDue.Value
Current Month Due
=monthname(month(Fields!CurrentDue.Value)) & " " & year(Fields!CurrentDue.Value)
Current Output
last reported Days Past due Current Month Due
September 2007 75 Days December 2008
Output should be!
last reported Days Past due Current Month Due
September 2007 75 Days December 2007
I want past two and next two quarters from the present quarterLike now its 4th quarter i should get 3rd and 2nd quarter and next two quarters that is 1st and 2nd also this is my table and when the year changes data is not fetched correctly
View 10 Replies View RelatedHow should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
View 5 Replies View RelatedHi,
I have a date column and I am trying to build a query that will give me the dates > 48 hours ago (in the past).
For example using todays date 2006.10.05 and time 23:00 I would be looking for the dates prior to 2006.10.03 and time 23:00.
Your help is greatly appreciated!
Thanks!
How to find the jobs executed in the past for a perticular period.
e.g. jobs ran between 2-3pm in the past.
I am using sysjobhistory but not really able to use column run_time
------------------------
I think, therefore I am - Rene Descartes
All,
I am trying to design a graph to shows out build status for past 7-8 weeks. I have to write the SQL query first.
After spending a good chunk of time, I could get some basic chart going. Problem is that I could give it a starting time till now (static) but I am not sure how to get the results for past 7-8 wekks.
1) What is a good approach for this since time is not static and it is changing.
any example or piece of sql syntax is greately appreciated.
Thaks in advance,
aa-
Hi,
I have some set of variables that I want to add in all my packages. Like other components, is there any way to copy & paste the variables from one package to another package?
Thx.
How should I find the dates for 3 days past and 5 days future. Such as TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
View 20 Replies View RelatedGoodday
Can anyone help please.
I have made a database and need to duplicate the tables to represent new catogaries. The definitions in the tables will stay the same , just the table names will change.
Is there a way to copy and past / duplicate tables without retyping all the definitions each time.
Thanks
Rob
Hello,I would like to show the result fo each day of the past 30 days. I might not have data to display for everyday but I still want the date to show up01/27/2007 resultA01/28/2007 resultB01/29/2007 .....02/26/2007 resultC02/27/2007 resultD
Thanks for your helpArnold
What would my statement look like if I have a column in a table (SoftwareInstall) called InstalledOn which stores a date in shortDateString format and I want to select all the records where that date is <= 30 days previous to today's date. Any ideas?
View 1 Replies View RelatedI need to show the count that occurred during the previous 30min. I've been told the following query doesn't work.
SELECT COUNT(*) FROM sampledata
WHERE (id = 254 AND sub_id = 731) AND (sampledate >= DATEADD(mi, -30, GETDATE()))
I am trying to get the date of Tuesday from now of the past x Years
The below Query works fine for weeks
DECLARE @x INT
SET x= 53
SELECT CONVERT(VARCHAR, DATEADD(WW,-@x,DATEADD(DD, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
The issue is with below query for any passed Number of Years
SET x = 7
CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
So need to get past years Tuesday Date and also verify if week also works for any passed week numbers ? The x parameter can be changed based on the situation
How do I write a script that will alert me if the tempdb database grows up past 100 MG and stays there?
Jim
Hi all,
I've been working hard to drag my family and the family business into the modern life, took me long enough to get then off a 'coal-powered' Siemens Nixdorf miniframe. Anyway, they went for a package system, on a PC ntwork. Now I've been gradually migrating them again, teasing them (as sometimes you do) with the benefits that could be supplied by my software, written on an SQL 2k back-end.
So anyway, we now have a system that ties into FoxPro as its original system (the bought package), plus SQL 2K..... I'm now upgrading to SQL 2005, and Express for starters....
Is it going to be possible to easily to run queries against both SQL2k and FoxPro, or should I redo my current fix from 2k to 2005 and work from there... at the end of the day, it will all hopefully become a single system, as my new system takes over more and more of the other packages functionality. And on the flip side, if i remake my current SQL2k hook-in, am i going to hit problems when i remake/convert it to SQL Express (and yes, i know Express has limitations, but i need to persuade them to move before they'll pay up the cost of the full version)
Yours,
Ann-Marie
I have a need to query my data and return only the last 24 hours (versus last day) worth of data.
My date and time fields are called:
calc_date = the date
Calc_time= the time
RealTime = Calc_date+Calc_time as realtime
I know if I use getdate()-1 that will get me the last day, but I need the last 24 hours. Any ideas on how would I do this?
Hello,
I've a table which contains the nest following data:
Date Sales Price
01-01-07 5,00
31-03-07 6,00
16-04-07 5.75
26-04-07 6.25
For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.
How can I get it?
Thx!
code that I can use to determine if a certain table has been accessed in the past?
View 6 Replies View Related