I need to extract records for the last day of previous months (Up till January of the same year) from a table SALES, according to a date parameter ASOFDATE that the user enters.
For Example
If user keys in ASOFDATE as 10-May-2014, I would have
I have a start date saved and an end date saved but when I query I only want to return the amount of months that have elapsed between the two, any idea?
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?
I'm in need of creating a rolling sum to find the sum of 3 previous months gas production. Below is an example of the basic data I will be using to find the sum.
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths AS ( SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate FROM ( VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'
How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...
I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference ( id INT ,start_date INT ,end_date INT ) INSERT INTO datedifference VALUES (10,20091202,20100629) INSERT INTO datedifference VALUES (20,20071202,20090330) INSERT INTO datedifference VALUES (30,20051202,20101031)
I 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 .
I do appreciat your help, I want to run a store procedure which will show 6 months . I do not know how to write the procedure, here in the notion in my mind, I want to be able to pass an input parameter (month) to the procedure which will then run a query to show 6 months prior the input parameter month, how can I do that, thanks for your help
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
I have a ssrs report which has a parameter start date.i have made 12 columns acc to client req which i cannot change:
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
These columns are dynamic acc to start date for which i have used this exp for first cell:
=monthname(month(Parameters!Startdate.Value),true) and for next 11 cells accordingly.
These 12 columns are grouped quaterly which i have made statically again for clien req which i cannot change so they are grouped staticaly by me in report like:
q12015 Â Â Â |q2 Â 2015 Â Â |q32015 Â Â Â |q42015 jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec Â
exp for quarterly is :
="Q" & DatePart(DateInterval.Quarter, Parameters!Startdate.Value) & Â year(Parameters!Startdate.Value).
Now when i select my start date of months jan,apr,july and oct report work fine.... but when i select my start date months of feb,may,aug and nov then reportis something like: (lets say i choose feb)
q12015 Â Â Â Â |q22015 Â Â |q32015 Â Â Â |q42015 feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec|jan.
What is happening is in q1 feb,mar and apr are coming and what i want in q1 is jan,feb and mar.
And in q4 nov ,dec and jan is coming and what is want in  q4 is oct,nov and dec.
So generally what is am asking is how to display jan in this condition in my first cell.same apllies for rest of months..
Similarly when i select my start date months of mar,june,sep,dec i want previous two months to be displayed.
I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
This code displays dates, File name, and File size for four seperate dates 11/20/2007 , 11/30/2007, 12/30/2007 and 01/31/2007 . I'm trying to show the percentage growth from date to date (ie 11/20/2007 -11/30/2007 percentage growth)
is there a way i can get the previous date file size for each entry, so i can have a variable for the calculation. Or i can get the calculate it within this code (ie database_size_mb / ((database_size_md ) where database_size_datetime -1) *100 or whatever the formula is for percentage growth.
Code Snippet SELECT Database_Size_Datetime, Database_file_name, Database_Size_Mb FROM RC_STAT.dbo.Tbl_Database_Statistics AS Tbl_Database_Statistics_1 GROUP BY Database_Size_Datetime, Database_file_name, Database_Size_Mb
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[TrialBalance]( [Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
I need to get all the data based on the user selection of the months. So, I will provide a dropdown will all list of the months. When user pick the month, for example January, then I will show all the invoice on January. How to get the month of the year in the where clause in T-SQL? TIA
I have a question i am confused very much with i am doing on MTD Average , i really need some help with code.
the logic behind the code is
3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.
This code does not actually gives me the exact value, please advise on any correction to the code , or a new way to write the code.
Hi, I am trying to determine the amount of months between today and adate stored in the database. But I cannot seem to figure out get thedifference between the two dates. Thanks in advance.
Hi All, I provided the client with the dropdownlist which lists all the months (january - december). This input will be used to filter in the invoiceDT. How can I write the query where clause to compare the invoiceDT (mm/dd/yyyy) into this input which is only number 1 through 12? TIA
I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths. But it is minusing days not months. @TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.
I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.
Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.
SELECT K.TRANS_TYPE,K.COMPANY,K.DIVISION,K.SALES_FORCE,K.SALES_LINE,K.SALES_REGION,K.SALES_ZONE,K.FORECAST_ DATE,C.STATISTICS_PERIOD,K.PRODUCT_NUMBER, K.CUSTOMER_CODE,K.WAREHOUSE,0,0,0,K.FORECAST_QTY FROM KPI_SIGNOFF AS K inner join ROUNDED_CALENDAR as C on K.FORECAST_DATE between C.START_DATE and C.END_DATE
Hi this is the code that I am using to populate a table let us call new_table. However what I would like to do is get the max forecast date and get the statistics_period for that max forecast date.
Once I get that I would set that as my current statistics period.
Then I want to create a new column in the same new_table call stat_period-1, which would be the one period back.
Please note that the statistics period is like the months 1 – 12. Does not go higher that 12 and does not go lower than 1.
So for stat_period-1 when statistics period is equal to 1 it would have to be set to 12.
This is what I want but do not know how to place this into the code, please help me with this. THanks
