Using SQL 2000. I need to select rows based on a date range. Both thebeginning date and ending date of the range will be entered in themm/dd/yy format. There are 2 columns in the table called MonthOfEvaland YearOfEval, both varchar(9) and both with data like "August" and"2005-2006". I think I need to use these 2 columns to create a date inthe mm/dd/yy format, then use that newly created date to see if it'swithin the date range. How would I go about doing this or is there abetter way?Thanks for any help or advice.
I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS SALES Sales Net Sales 444.65 Sales Sales LESS TRD 22.55 SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD) Finance LESS ED LESS ED 40 Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... .. production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime, @todate datetime, @category varchar(200) as begin create table #temp1
[code]....
like i m inserted so many accounts in temp tables .
IF (@category IS NULL or @catagory=' ' ) begin select responsibility, dept, category, round(Max(Actuals)/100000,1,2) as Actuals from #temp
[code]....
here i can display only current YTD only how to display previous year (13-14) YTD in level1
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 )
but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
I have this SQL table w/ fieldname "PayPeriod". Sample value of PayPeriod are SEP06-1, SEP06-2, OCT06-1, OCT06-2... Wherein the first 3 chars is the month, 06 is the year, 1 or 2 is the cutoff where 1 means the 2nd half of the previous month and 2 is the first half of the current month.
Now my questions are: 1.) How can I select these values sorted according to date. Since this is a character data type. Sorting is alphabetical. I need to sort it according to date where JAN05-1 is the first record and the last record is DEC06-2 (assuming that the oldest year is 05 and 06 is the latest). 2.) How can I convert the 3 char month into its number equivalent. Example, JAN is equal to 1, FEB is 2, MAR is 3.... 3.) How can I count the no. of days in a month? Is there a function that would result to the no. of days in a given month?
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
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.
I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no, isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr', isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May', isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun', isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
[Code] ....
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June .... 100 2012 100 $23 $30 $400 100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
And the resultant string is: dd/mm/yyyy, for example 30/08/2004. But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.
The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.
I've tried evaluating the parts of the episode date seperately like:
where (epimonth>=10 and epiyear=2003) or (epimonth<=2 and epiyear=2004)
and that works, but what happens when someone wants to see from 10/2002 to 2/2004?
i have to input month/year can u tell me what would the datatype of that column? - should it be datetime or just month/year? what would be the datatype?
i have to make function called getQuantityByMonth that when given the following inputs: input: campaign,begin month/year, end month/year
returns the item sales for the items in a campaign with the following column headers...
hi! i have this following query:Select DATEADD(YEAR, -2, @sDate),(dateadd(dd,-(day(dateadd(yy,1,@sDate))),dateadd(yy,1,@sDate)))..the results of these are:2005-01-01 ,2007-12-31 but i want 2007-12-31 to be 2005-12-31..how can i do that using dateadd function?
Hi,The problem here in hand is comparing the month & year ranges rather than the entire date. I am passing a start month and start year along with the end month and end year, now the query that i have written does not seem to be appropriateMy Query:MONTH(Date_Added) BETWEEN @StartMonth AND @EndMonthAND YEAR(Date_Added) BETWEEN @StartYear AND @EndYearFor some reason this works fine if i specify the startmonth as 1 and endmonth as 12, whereas if i specify 1 and 1 it doesnot work. Can anyone help me by refining my query so that i can search a particular date within mm/yyyy and mm/yyyy. Thanks in advance.Santosh
Hi i have a where clause which i think i need to change
i currently have the code below but dont think it right i think it needs to be (O_Date) between @month1 @year AND @month2 @year2, but not sure how this should be done?
WHERE MONTH(O_Date) Between @month1 AND @month2 and YEAR(OW.O_Date) Between @year AND @year2
My data is mainly updated each month. Most of my tables have a month and year column. I have many statements such as this when I need to join tables:
SELECT equity FROM vMonthlyFirmSummary WHERE MonthlyInputs.year = vMonthlyFirmSummary.year AND MonthlyInputs.month = vMonthlyFirmSummary.month
Is this bad database technique? Do you think instead I should try to create a string key field such as 2007-01 (Year - Month)? Please give any insight on how to make the DB simpler. Thanks.
I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.
SO I need to do:
IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)
But it has to be comparing the year,month and day (excluding the time part of the datetime type).
Hi,We are using Month-year tables to keep the history of long transactionof our application. For example:We capture the details of a certain action in table"TransDtls<CurrMonth><CurrYear>" (this month: TransDtls072005).This way tables keep growing. every month a new table gets created. Wehave done it because we estimated that every month year table willcarry around 2 - 3 Lac records and most of the time the operations willwork on current month year table.Avoiding this way and carrying on with single table instead of "Monthyear" table might lead us system performance issues.But now we are a bit confused on the way we are heading and also facingthe implementation issues like manipulating data from different"month-year" tables. Could anyone please help us to make our visionclear on this?Looking for your valuable comments.Thanks.
Tried this: SELECT CONVERT(varchar,fieldMonthYear,107) 'Month in Question' FROM .....That returns: Apr 01, 2006What I need is this: April 2006 or even Apr 2006. But no date for the day.Is there a way I can trim the center 4 characters of this now converted varchar? This is in a datalist, btw. Thanks!bs.
I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1. I keep getting this error Line 5: Incorrect syntax near '='. If I comment out the case statement just evaluate the (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1 SELECT CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2 FROM Users3 WHERE (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4 case MONTH(User_Date) when 1 then 5 (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6 else (YEAR(User_Date) = YEAR(GETDATE()))7 END8 GROUP BY CONVERT(char(10), User_Date, 101), User_Name9 ORDER BY CONVERT(char(10), User_Date, 101), User_Name
I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date ---------------------- 2004101-Jan-2004 2004201-Feb-2004 2004301-Mar-2004 and so on ....
Many thanks in advance. I'll appreciate your help P.S. Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them