Query The Data For Month,year Range
Apr 30, 2008
Hi,
I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month >= 6 AND R.Year = 2006)
Group By R.Year , R.Month )
Union
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month <=3 AND R.Year = 2008)
Group By R.Year , R.Month )
View 9 Replies
ADVERTISEMENT
Feb 25, 2004
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?
Any suggestions on the best way to do this?
View 5 Replies
View Related
Jul 23, 2005
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.
View 1 Replies
View Related
Jul 21, 2015
I am in seach of a query where in I can provide month, year and client name and fetch last available comments from the table.
Client,Month,Year and Comments are columns in that table.
For Ex: If i pass client as A, month as 7 and year as 2015, I should get comments for client A, month July and year 2015 if available.
If data not available, it must go to June month and so on until it finds comments.Also when month is Jan, if query is going back, year also should get changed.
View 10 Replies
View Related
Dec 11, 2006
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 )
View 9 Replies
View Related
Jan 18, 2007
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
View 5 Replies
View Related
Oct 26, 2006
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?
View 8 Replies
View Related
Jan 5, 2008
Hi,
Can anyone tell me how to get data for yesterday, last month, and last year?
As a example, I wanna know how many loans disbursed for yesterday(not for last 24 hours).
It may be smthing like this. But when I run this one , it gave me a syntax error.
WHERE DATEDIFF(dd,Account.CommencedOnDate,GetDate()) = 1
can anyone help me out pls?
View 11 Replies
View Related
Dec 18, 2001
I need SQL to determine what the date range for the previous month was, ie
Start date 11/01/01 for 11/30/01.
The results will populate a drop down box for user queries.
View 1 Replies
View Related
Mar 18, 2015
This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?
View 3 Replies
View Related
Sep 18, 2013
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.
View 2 Replies
View Related
Aug 30, 2004
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:
CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()
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?
Thank you,
Cesar
View 4 Replies
View Related
Oct 21, 2014
I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.
View 9 Replies
View Related
Sep 30, 2015
I have below report with following data
Date Count
April-2015 100
January-2002 30
November-2010 55
July-2000 10
What is the best approach to sort this data based on date in a tablix and also sort the date in Column bar chart?
View 4 Replies
View Related
Apr 2, 2008
I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:
declare @MyDate as datetime
set @MyDate = '04/02/2008 18:00:00'
select substring(convert(varchar,@MyDate,101),1,10)
I get '04/02/2008', but when I do this:
update TheTable
set TheDate = substring(convert(varchar,TheDate,101),1,10)
I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?
View 4 Replies
View Related
Feb 2, 2004
I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.
I'm using a DatePart function that converts my date:
CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END
This returns te american version, can anyone help me to get this to convert the UK way.
Thanks
View 3 Replies
View Related
Sep 10, 2014
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
View 5 Replies
View Related
Aug 2, 2002
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
View 1 Replies
View Related
Apr 30, 2007
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.
View 4 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Mar 31, 2008
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...
output: period, periodint, campaign, itemnmbr, totalquantity
@startmonthyear datetime
@endmonthyear datetime
This is hardcoded, but the output of this is an example of what i would want. The purpose of this is to be able to do a multi-line graph.
select 'Oct 07' as Period, 200710 as PeriodInt, campaign,itemnmbr, totalquantity from
mycampaignitemquantity('COPD','10/1/2007','10/31/2007')
union
select 'Nov 07' as Period, 200711 as PeriodInt, campaign,itemnmbr, totalquantity from
mycampaignitemquantity('COPD','11/1/2007','11/30/2007')
union
select 'Dec 07' as Period, 200712 as PeriodInt, campaign,itemnmbr, totalquantity from
mycampaignitemquantity('COPD','12/1/2007','12/31/2007')
union
select 'Jan 08' as Period, 200801 as PeriodInt, campaign,itemnmbr, totalquantity from
mycampaignitemquantity('COPD','1/1/2007','1/31/2007')
union
select 'Feb 08' as Period, 200802 as PeriodInt, campaign,itemnmbr, totalquantity from
mycampaignitemquantity('COPD','2/1/2007','2/28/2007')
union
select 'Mar 08' as Period, 200903 as PeriodInt, campaign,itemnmbr, totalquantity from
mycampaignitemquantity('COPD','3/1/2007','3/31/2007')
order by itemnmbr, periodint
thanks for the help.
View 15 Replies
View Related
Dec 19, 2007
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?
Funnyfrog
View 5 Replies
View Related
May 25, 2015
Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.
I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.
View 5 Replies
View Related
Oct 24, 2007
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
View 5 Replies
View Related
Jan 9, 2006
hi
I tried below but it doesnt work
select date_field from tbl group by DAY(date_field)
View 1 Replies
View Related
Jun 2, 2008
hai freinds ,
This is the query iam using to retrieve year and its month from a datetime column
quote:Select Distinct DateName(mm, col_datetime) as 'Month',
year(col_datetime)
as 'Year'from table_name
Order By DateName(mm, col_datetime)
result for the above query is
quote:
month year
june 2008
june 2007
july 2007
july 2008
aug 2007
aug 2008 and so on.....
but my need is
quote:
month year
june 2008
july 2008
aug 2008
june 2007
july 2007
aug 2007
and so on.....
so can someone fix it or give me a idea
thanks friends
View 3 Replies
View Related
Dec 17, 2007
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
View 7 Replies
View Related
Jan 19, 2008
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.
View 3 Replies
View Related
Mar 8, 2008
Hi,
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).
View 9 Replies
View Related
Jul 23, 2005
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.
View 8 Replies
View Related
Jan 15, 2007
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.
View 3 Replies
View Related
May 18, 2004
hi guys.
I have a datetime column in my SQL server database.. I need to select the value from the table by passing month and year only..
any suggestions..??
Thanks in advance..
:)
View 1 Replies
View Related
Feb 13, 2001
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.
View 1 Replies
View Related
