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 RepliesHi 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.
Hi agian. Another probs. I need help again....
DECLARE @quarter varchar(10), @year int
SET @quarter = 'First' --First, Second, Third, Fourth
SET @year = 2007
table1 - hrEmpPaySumm
empno period1 sss_ee sss_er
1 1/1/2007 500 100
1 3/1/2007 600 200
1 3/16/2007 700 300
2 4/1/2007 800 400
3 4/1/2007 900 500
expected result...
if @quarter = 'First' AND @year = 2007 - returns all data of jan,feb,mar of 2007
empno month1 sss_ee1 sss_er1 month2 sss_ee2 sss_er2 month3 sss_ee3 sss_er3
1 January 500 100 Feb 0 0 March 1100 500
2 January 0 0 Feb 0 0 March 0 0
3 January 0 0 Feb 0 0 March 0 0
if @quarter = 'Second' AND @year = 2007 - returns all data of apr,may,jun of 2007
empno month1 sss_ee1 sss_er1 month2 sss_ee2 sss_er2 month3 sss_ee3 sss_er3
1 April 0 0 May 0 0 June 0 0
2 April 800 400 May 0 0 June 0 0
3 April 900 500 May 0 0 June 0 0
and so on....
-Thanks
-Ron-
"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."
I have the following query, which is producing the results, which i need.
SELECT
*
FROM
(
SELECT
o.FDMSAccountNo,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
SUM(hst_sales_amt) AS 'Total'
[Code] ....
However I want to create an additional four columns, which gives me the quarterly sales amount
E.g.
FDMSAccount no 20120101 20120201 20120301
878743915885 NULL10000 35000
So the quarter sales amount for fdmsaccount no 878743915885 is 45000...
Hi,
i get a list of Quarters that look like following as an input to my proc.
'Q306/Q106/Q406/Q405'
I need to find out whihc one of the above is the least Quarter value.
In the above example it is 'Q405'
The Quarter values that come into the proc keep changing...It could be even 1 or 2 or 3 Quarters etc...
Is there any datatype called Quarter in SQL Server.It shows Quarter as a key word...
Can some one please help on this....
I would like to add a calculation to provide an annualized Gross Amount value. Most solutions I have seen are for annualizing down to the day. For instance, if today is the 123rd day of the year, the calculation would be Amount / 123 * 365. What I want to do is annualize the amount based only on completed periods. Today is 8/26.
The last completed quarter ended on 6/30. Therefore I want to basically take the sum of Q1 and Q2, divide by two, and multiply by four to get an annualized amount based on completed quarters. Eventually I want to do something similar with months.
So here's what I have so far. In my DSV my DimDate is actually based on a view. The view has a Date column with the actual date, all of the normal fields you would expect in a DimDate table, and two columns that look like this:
CASE
WHEN [Date] < DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
THEN 'Y'
ELSE 'N'
END AS
CompletedQuarter,
[Code] ....
Note that for dates between 1/1 and 3/31 the CompletedQuarterCount will be 0. I want any annualized amount to be 0 in that case because I only want to use completed quarters in my calculation.
I added both to my Pay Date dimension (which uses vwDimDate from the DSV) in the cube. I have tried the calculation below and in my Excel pivot table I'm getting blanks for the calculated field.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross Amount Annualized by Pay Date Quarter]
AS SUM(
IIF([Pay Date].[Pay Date Completed Quarter Count] > 0,
[Gross Amount] / [Pay Date].[Pay Date Completed Quarter Count] * 4,
0)
),
FORMAT_STRING = "Currency",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Claim' ;
So, is my calculation correct?
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
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 5 Replies View RelatedIf 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 ???
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
Hi,
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
I 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!
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 RelatedI 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 RelatedHi All
I have built this query :
Select position, SubsidiaryName
from position
Where SubsidiaryName in ('country1','country2')
and position not like('testuser_%')
and position not like ('Inactive_%')
and position not like('olduser_%');
However when I run it, the result more accounts in country 1 than expected. I think it could be inactive users who have not logged into this system for 6 months or more. How can I build a query that incorporates my original query + shows only users who logged into the past 6 months ?
I have a tricky question to Microsoft SQL Server 2000/2005. I have failed to find a solution to the problem yet and others have failed too. So I thought that I throw it around here, because the fact that a lot of knowledgeable DBAs hang around in this forum.
I am looking for a script/stored procedure that is able to show me upcoming job executions for a selected date/time range based on the current settings for the jobs configured on the Database server.
Example
Date/Time From: 2/16/2007 00:00 a.m. (Friday)
Date/Time To: 2/17/2007 12:00 p.m. (Saturday)
A. Job 1 / Schedule 1 = Week Days, 1:00 a.m.
B. Job 1 / Schedule 2 = Saturdays, 10:00 a.m.
C. Job 2 / Schedule 1 = hourly, between 10:00 a.m. and 1:00 p.m.
D. Job 3 / Schedule 1 = every 3rd Saturday of the month, 5:00 a.m.
E. Job 4 / Schedule 1 = every 1st Friday of the month, 8:00 a.m.
The routine I have in mind would not return Job E, because 2/16/2007 is the 3rd Friday of the month and not the first.
Job A: 2/16/2007 01:00 a.m.
Job B: 2/16/2007 10:00 a.m.
Job B: 2/16/2007 11:00 a.m.
Job B: 2/16/2007 12:00 p.m.
Job B: 2/16/2007 01:00 p.m.
Job D: 2/17/2007 05:00 a.m.
Job A: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 11:00 a.m.
Job B: 2/17/2007 12:00 p.m.
Output
It needs to return the Job ID, the Job Name, the Schedule ID, the Date and the Time.
Disabled Jobs and Schedules are by default excluded from the selection, but an option to include or exclude those would be a bonus.
Information such as the min, max and average execution time would be great too.
Notes
The schedules of Job D and Job B overlap as you can see in my example above.
This happens only once per month though. I have over 20 jobs with sometimes very frequent execution times, like every 5 minutes or every 20 minutes and jobs that run hourly, daily, weekdays only, weekends only, monthly once etc.
Purpose
I want to do two things.
I want to determine where jobs overlap, not just by start date/time, but also by average run time and maximum run time.
I also want to be able to generate a report that shows me what should have been done and what was actually done by the jobs (note on the site, 6 of the jobs create new jobs on the fly for other database servers and this is sometimes not happening properly, without getting any error message. The volume of jobs makes the manual search like a search for a needle in a haystack.)
Findings so far:
I did some digging myself and found following stored procedures that do some of the steps that I need and involved tables for the calculation.
Stored procedures:
- sp_get_schedule_description (in db: msdb) (undocumented stored procedure)
- sp_add_schedule (in db: msdb) http://msdn2.microsoft.com/en-us/library/ms187320.aspx
Tables:
- msdb.sysjobs
- msdb.sysjobschedules
- msdb.sysjobhistory
It is not a problem to determine the next execution of a job, but that is not what I need, anyway.
The problem is that it does not help you to determine all upcoming execution times, if the selected timeframe is long enough that SQL Server executes the job more than once.
There is no way around using the sysjobschedules table and calculate the execution dates and times based on the configured settings. See the Stored procedure: sp_get_schedule_description.
That one breaks down nicely the settings as documented for sp_add_schedule at
http://msdn2.microsoft.com/en-us/library/ms187320.aspx, but it does not allow the determination of the exact upcoming dates and times when the job is supposed to be executed.
Another Example
If there is only one job scheduled to run
1) every 5 minutes,
2) on every weekday
3) between 1:30pm and 2:00pm
You would get the following results
1) start date/time 7/7/2007 12:00pm, end date/time 7/8/2007 2:00pm
nothing, because the 7/7/2007 and 7/8/2007 are on the weekend
2) start date/time 7/5/2007 12:00pm, end date/time 7/5/2007 1:45pm
7/5/2007 1:30pm
7/5/2007 1:35pm
7/5/2007 1:40pm
7/5/2007 1:45pm
3) start date/time 7/5/2007 1:45pm, end date/time 7/6/2007 3:00pm
7/5/2007 1:45pm
7/5/2007 1:50pm
7/5/2007 1:55pm
7/5/2007 2:00pm
7/6/2007 1:30pm
7/6/2007 1:35pm
7/6/2007 1:40pm
7/6/2007 1:45pm
7/6/2007 1:50pm
7/6/2007 1:55pm
7/6/2007 2:00pm
Now SQL has a lot more configuration options for the scheduler.
And don't forget that you can have more than one schedule record for any single job, including no-schedule record (which would not interest me).
Autom. when SQL starts freq_type=64
Starts when CPU idle freq_type=128
One Time On Date mm/dd/yyyy at time: hh:mm:ss am/pmfreq_type=1
or
Recurring
Occurs
Daily freq_type=4
Every x day(s)freq_interval=x
Weekly freq_type=8
Every x week(s) onfreq_recurrence_factor=x
Mo [ ], Tu [ ], We [ ], Th [ ],
Fr [ ], Sa [ ], Su [ ],
1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday. Examples: Su and Mo enabled = 3 (1 (Su) + 2 (Mo)), Mo, We and Fr enabled = 42 (2 (Mo) + 8 (We) + 32 (Fr))
Monthly freq_type=16
Day X of every Y month(s)freq_interval=X
freq_recurrence_factor=Y
or
The 1st,2nd,3rd,4th,LAST WEEKDAYfreq_type=32
of every Y month(s)
freq_relative_interval=1,2,4 (3rd),8 (4th),16(last)
freq_interval= 1=Su,2=Mo,3=Tu,4=We,5=Th,6=Fr,7=Sa,8=Day,9=Weekday,10=Weekend day
freq_recurrence_factor=Y
Occurs Once at hh:mm:ss AM/PMfreq_subday_type=0x1
or
occurs Every X Hours/Minutes
Starting: hh:mm:ss A/PM freq_subday_type=0x4 (minutes) or 0x8 (hours)
Ending: hh:mm:ss A/PMfreq_subday_interval=X
active_start_time
active_end_time
Start Date mm/dd/yyy End Date mm/dd/yyyyactive_start_date
oractive_end_date
No End Dateactive_end_date=99991231
Does anybody has a script that does that or several individual scripts that would have to be combined to do what I want to do?
Thanks. I appreciate it.
Cheers!
Carsten Cumbrowski
http://www.sqlhunt.com/
I have an SSIS package where I have directed the error output to a Flat File Destination. The issue is that there are some bad entried in a set of log files, where the source file reads on more delimited column than there are actual columns. (As in there are 26 column headers, and one row will have 27 commas, or delimiters.) I am trying to redirect the row output to put the bad rows into a Flat File for debugging purposes. Although, the package is not able to continue past the error. As soon as it hits the bad row, it fails despite the error output.
Any ideas?
I have a table with addresses and activity dates. I need to be able to retrieve the past 3 activity dates and see if the first and last occurred within 15 days. If so, I need to flag them.
Using max date gets me the last date but not the previous two. I was trying to use top 3 in desc order and that didnt seem to work either.