Month-to-month Function
Sep 10, 2007
Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September:
@CurrentDate = 9/10/2007
@EndDate = last working day 9/30/2007
Total# of working days = 15
October:
@CurrentDate = 10/1/2007
@EndDate = last working day 10/31/2007
Total# of working days = 23
November:
@CurrentDate = 11/1/2007
@EndDate = last working day 11/30/2007
Total# of working days = 22
etc.
Any ideas of how i can approch this?
Thanks in advance.
View 3 Replies
ADVERTISEMENT
Apr 5, 2008
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.
View 3 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
Apr 22, 2015
following table global_usage
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
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
View 6 Replies
View Related
Nov 9, 2015
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
View 6 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 11, 2015
Most of the data is in one table.
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
View 2 Replies
View Related
Jul 25, 2013
I am using sql server 2005. I am trying to create a function that gives me the end of the month. I know how to get the current date.
What I ultimately want to do is display how many days are left in the month no matter what month I am currently in.
Upon research I found EOMONTH [URL] .....
But that doesn't work for server 2005.
View 3 Replies
View Related
Feb 27, 2008
Using RS 2000, is there a way to schedule a report for email delivery on the last day of each month. It looks like that would have to be a change made to the Report Manager. Is it possible to reconfigure the report manager to add a last day of the month or month end option?
thanks in advance
OS
View 3 Replies
View Related
Feb 16, 2006
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
View 1 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
May 15, 2007
Hi
I regularly use the T-SQL date functions to return the 1st of a particualr month.
e.g.
SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)
returns 2007-05-01 00:00:00.000
i.e the first of the current month at midnight.
However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.
DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)
returns 30/05/2007 00:00:00
Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?
View 3 Replies
View Related
Apr 20, 2015
I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :
SELECT
non empty
[Measures].[TRANSACTIONS Count] on 0,
non empty ([TRANSACTIONS].[Days].[Days],
[TRANSACTIONS].[Transaction Month].[Transaction Month]) on 1 from [cube]
I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :
WITH MEMBER
[Measures].[Cumm Account Count]
AS
(
AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count])
)
SELECT
non empty [Measures].[Cumm Account Count] on 0,
non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from [cube];
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function accepts members only...
View 4 Replies
View Related
Mar 25, 2014
I have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?
This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.
I tried to do LEAD but with not success..
/*
IF OBJECT_ID('tempdb..#t') is not null drop table #T;
WITH R(N) AS
(SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 )
SELECT N as Rn,
10001 ClientID,
DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],
[code]....
View 2 Replies
View Related
Apr 28, 2015
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
RETURNS date
AS
BEGIN;
DECLARE @Result date;
SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
RETURN @Result;
END;
View 11 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
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
Mar 20, 2004
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
View 1 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
May 6, 2008
In my database . Some to the dates has been stored wrongly .
for example
03/04/2008 to be stored in database . But in my database it was stored as 04/03/2008 . like this i have more that 100 records . How can i change this to correct format using query . Guide me urgent .
View 8 Replies
View Related
Dec 22, 2007
how can i do this
search between 2 rows
day before Last day of the Previous Month + day Last day of the Previous Month"
Code BlockSELECT empid, basedate, unit_date, shift, na
FROM dbo.empbase
WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)
AND
(basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)
TNX
View 5 Replies
View Related
May 21, 2007
The SQL statement below works to provide the number of help desk requests submitted per month for help_company_id = 1.
The problem is that the months are return as numbers (1 for January). I tried using the DATENAME function but all the months were return as January. This is how I tried to use it: SELECT DATENAME(month, MONTH(help_date_submitted)) AS Month, COUNT(help_request_id) AS Requests)
How do I convert the month numbers to month names?
SELECT MONTH(help_date_submitted) AS Month, COUNT(help_request_id) AS RequestsFROM help_requestsWHERE (help_company_id = 1) GROUP BY MONTH(help_date_submitted)ORDER BY Month
View 5 Replies
View Related
Jun 6, 2000
dear friends
i want to get last day of month
anybody make any stored procedure for checking lip year or how to get last day of month
Thanks
Nilesh Shah
View 2 Replies
View Related
Jan 21, 2005
How to get the 1st day of the month for a given date?
View 6 Replies
View Related
Sep 29, 2004
I have a sales table
date total
2004-1-3 123,000
2004-2-2 232,000
2004-4-1 142,000
...
I need a report has sum of total sale on every month even there is on sale record on that month(show as zero)
e.g.
date total
2004-1 123,000
2004-2 232,000
2004-3 0
2004-4 142,000
....
is this possible???
thanks
View 1 Replies
View Related
Sep 2, 2004
Hi to all..
is there a function in sql server 2k that returns the first day of a date (month)????
thx!
Ale.
View 6 Replies
View Related
Feb 8, 2007
What is the best way to get the month name from an int value
within a mssql statement ?
(there is no need to consider the language settings)
e.g.
1 -> January
11 -> Novembre
My first approach seems to work but is very ugly:
CASE WHEN my_month_int<10
THEN DATENAME(month,"20000"+cast(my_month_int as varchar(2))+"15 00:00:00")
ELSE DATENAME(month,"2000"+cast(my_month_int as varchar(2))+"15 00:00:00")
END AS my_month_name
View 2 Replies
View Related
Apr 16, 2008
I have the following data in a Table. Note that this table holds Sales Qty till Today and also Forecast Qty for the next n Months (where n could be between 1 - 15 months)
YYYYMM Stock_Code Whse_code Sales_Qty
----------- ---------------- --------- ------------
200710 009040 B 505
200711 009040 B 370
200712 009040 B 385
200801 009040 B 335
200802 009040 B 305
200803 009040 B 250
200804 009040 B 250
200805 009040 B 250
200806 009040 B 250
200807 009040 B 250
200808 009040 B 250
200809 009040 B 250
200810 009040 B 250
200710 009040 M 549
200711 009040 M 565
200712 009040 M 535
200801 009040 M 685
200802 009040 M 305
200803 009040 M 240
200804 009040 M 240
200805 009040 M 240
200806 009040 M 240
200807 009040 M 240
200808 009040 M 240
200809 009040 M 240
200810 009040 M 240
200811 009040 M 240
200812 009040 M 240
200901 009040 M 240
200902 009040 M 240
200903 009040 M 240
200904 009040 M 240
200905 009040 M 240
200906 009040 M 240
How can I create a Column (6MthQtyToDate) that is the Sum of the Qty of the Last6Mths. I need this Column for each Row of data.
View 2 Replies
View Related
Apr 23, 2008
Hi
Does anyone know a way of counting the number of employees with a date of birth in each month.
ie.
Jan 16
Feb 11
Mar 20
etc...
Also how would the months be displayed as their names as opposed to the month number?
Thanks
View 5 Replies
View Related
May 6, 2008
hi
i have one table their is two colomn one is name and second birhtdate
how to find name is born month name May
thank
View 4 Replies
View Related
May 19, 2008
I am having Joined Two Tables and i get sum of "calloutCharges" and get the result in the below procedure
Result
--------
Jan 100
Feb 200
March 300
May 200
Aug 100
But I need result is in the following Format
Jan 100
Feb 200
March 300
Apr 0
May 200
jun 0
jul 0
Aug 100
Sep 0
Oct 0
nov 0
Dec 0
This my Query
SELECT Sum(FT.CallOutCharge) AS 'CallOutCharge', Convert(CHAR(3), CC.CompletedDate,109) As 'Month', month(CC.CompletedDate) As 'intMonth', Year(CC.CompletedDate) As 'Year' FROM HSSPMS_Tbl_Callcentre_Compliants AS CC
INNER JOIN HSSPMS_Tbl_LandLordFulltimeEmployee AS FT ON FT.ContractorCode = CC.ContractorCode And CC.FaultCleared='1'
WHERE FT.CreatedBy=@OwnerId AND FT.IsDelete='0' And Year(CC.CompletedDate)=@Year Group BY CC.CompletedDate
any one Knows help me
Regards,
Prabu R
View 2 Replies
View Related
May 27, 2008
I want to get current month (May)and last month (April)? where in every month it will rolls out. Like comes June it will be June and April.
Thanks
View 13 Replies
View Related
Feb 5, 2007
Hello all,
I am very new to VBA, and my boss gave me a very simple assignment to do. Basically, right now the code is pulling data for forecasts for the following 10 days, and the code is the following:
WHERE (((dbo_ACTUAL_HDD_DAILY.DATE)>=Now()-1 And (dbo_ACTUAL_HDD_DAILY.DATE)<Now()+9)
All he wants modified is to pull data for the entire current Month (Ex. if it is in the middle of July, he would want the data from July 1-July 31, or if February from Feb 1-Feb 28) It would be nice to do this without having to change the VBA every month.
Thanks for the help, I know its really simple but I hope I will get the hang of this eventually.
View 2 Replies
View Related
