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
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.
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 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]
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
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
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?
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 )
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?
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.
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 .
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
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
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
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
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.
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.
I am trying to code a proceedure that will run every weekend. This process will run for a number of hours beginning at 0900 Saturday and ending at 2100 Sunday. However, on the 3rd weekend of the month, I need it run for a shorter time and to also skip some tables. What I have now to find the day of the month I need is
Begin SET @3rdSaturday = @1stDayMonth /*Loops until the date of the first Saturday(DayofWeek #7) of the current month is found */ WHILE DATEPART(dw,@3rdSaturday) <> 7
/*Adds 1 day to the first day of the month until it reaches the date of the first Friday of the month */ SET @3rdSaturday = DATEADD(d,1,@3rdSaturday)
/*Adds 14 days to the first Friday of the month. The end result is the 3rd Fridays date for the current month*/ SET @3rdSaturday = DATEADD(d,14,@3rdSaturday) End
I am trying to find a better way to find the 3rd Saturday (or similar) of the month. I have also investigated DATENAME function but ran into the same problem. Also if I were using SQLDMOFreq_Monthly I could do it but I am trying to write all of this in T-SQL.
Today's month is 9 as in September. How can I retrieve records entered last month?This is what I got so far... WHERE (Classes.CLWhenDt >= DATEADD([MONTH], - 1, GETDATE()))
Hi, I have two varchar column in SQL.there im storing Month name Lets say, col1 col2 JAN APR MAY DEC Is there any possibility to use the sql query select * from tablename where DEC between (col1,col2) I just wonder since it is varchar field and not date field can i use this syntax to compare ? or any other better way to achieve this? Thanks for any response. Regards, RR
