this table getting updated frequently by daily bases that means one record per one day
(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)
I need to query this table to get missing dates in between particular months
for example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB month
I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:
create table #InputTable (ID_No int ,OccurMonth datetime) insert into #InputTable (ID_No,OccurMonth)Â select 10, '2007-11-01' Union all select 10, '2007-12-01' Union all select 10, '2008-01-01' Union all select 20, '2009-01-01' Union all select 20, '2009-02-01' Union all select 20, '2009-04-01' Union all select 30, '2010-05-01' Union all select 30, '2010-08-01' Union all select 30, '2010-09-01' Union all select 40, '2008-03-01'
I'm trying to write some code that will get me the last day of each month for the months that fall between 2 dates.
So if i have a table with a record with a begin date of 01-01-2012 and an end date of 09-14-2012, i would want a result set of the following 9 records:
So i basically get the last day of each month between the dates listed, including the months that the dates are in themselves (inclusive list).
I have a date dimension table with 1 row for every day from 1990 to 2025. There is also a field with the Last day of Month for a given date. My issue is that I'm not sure how to write the T-SQL to get what I need.
I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.
I am unable to find the statement that will execute this operation.
I have a problem in finding month and year sequence between to date. one date is minimum date for example march 2005 and one maximum date is today's date april 2008. I need output of month between these years like this
March 2005
April 2005
May 2005
......
January 2006
...
December 2007
January 2008
..... April 2008
Can any one help me to develop a stored procedure to produce this output...
I currently have a stored procedure that returns a list of dates based on a date range a user enters.
CREATE PROCEDURE sp_GetContactScheduleDates @MonthFrom int, @YearFrom int, @MonthTo int, @YearTo int, @DaysInMonth int AS Select distinct s.ScheduleMonth, s.ScheduleYear From OnCall_Schedules s Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime) >= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime) And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime) <= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime) Order by s.ScheduleYear, s.ScheduleMonth GO
However, this only brings back those dates that are in the table. I need to get ALL dates within the range.
For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?
Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.
Can some sort of function be used? What would the code look like?
I am trying to get a count by product, month, year even if there are is no record for that particular month.
Current outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ April 2014 34 ...
Desired outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ March 2014 0 XYZ April 2014 34 ...
The join statement is simple: Select Product, Month, Year, Count(*) As Count From dbo.Products Group By Product, Month, Year
I have also tried the following code and left joining it with my main query but the product is left out as is seen:
DECLARE @Start DATETIME, @End DATETIME; SELECT @StartDate = '20140101', @EndDate = '20141231'; WITH dt(dt) AS ( SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start), 0)) FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 FROM sys.all_objects ORDER BY [object_id] ) AS n )
2nd attempt: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 NULL March 2014 0 XYZ April 2014 34 ...
What I want is this (as is shown above). Is this possible?
Desired outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ March 2014 0 XYZ April 2014 34 ...
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 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.
i have to check first the missign dates between dates 1/1/1999 till current date how many dates are missing and for those dates i have set - update quantity=0 for all those missing dates. docdate is datecolumn in saleshsitory and quantity is in salestable. can anybody help me to solve this problem. thanks a lot!
i have a db that gets real time min by min datas everyday but sometimes somehow some of those dates did not written into that db and i wanna know which dates are missing? how can i do it?
I am working with a database containing time series data. In many, cases there is missing dates. For example, while there might be a value for 2001-01-01, 2001-02-01, 2001-03-01, there is none for 2001-04-01 and 2001-07-01. i.e. NR Date Value
------------- ------------- --------------- 2365 2001-01-01 67 2365 2001-02-01 111 2365 2001-03-01 2 2365 2001-05-01 23 2365 2001-06-01 85 2365 2001-09-01 26 2365 2001-11-01 543 2365 2001-12-01 32 54 2001-01-01 217 54 2001-03-01 4 54 2001-04-01 2 ... I want dates for each month in year for each NR I would like to replace the missing dates with data from the previous record.
I'm using Sql Server 2005, in which I've table like this
USE [Sample] GO /****** Object: Table [dbo].[Table1] Script Date: 06/07/2008 03:10:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Table1]( [TimesheetDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [EmpID] [int] NULL ) ON [PRIMARY]
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',3) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',3) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',9) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',9) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-05 00:00:00.000',9) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-17 00:00:00.000',9) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',9) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-01 00:00:00.000',10) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',10) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-06 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-08 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-10 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-11 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-12 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-14 00:00:00.000',11) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',13) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',14) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-24 00:00:00.000',14) INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',15)
My task is I want to find Missing Dates (Except Saturday, Sunday) for each Employee.
Note: Missing Dates should be Working Days only (Excluding Saturday & Sunday)
I need to count last 3 months records. The query I'm trying to use was based on last 8 days. How can I use this query in months and not days? Or should I use a new query?
Code Snippet SELECT COUNT(Column1) AS [Nº de Cartões], Column2 AS [Tipo de Cartão], Column4 AS Periodicidade FROM main_client_file WHERE (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 3), 0)) AND (Column9 <= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 1), 0)) GROUP BY Column2, Column4
I'm writing a query that will be calculating a number of business performance measures including the following...
Average Stock: This is calculated by taking the actual stock value of the product category per month and dividing it by 13 if we have over 12 month's history, or if a new product, then by the number of months we have had it in stock for.
Cost of Goods Sold Annualised: This is an average figure for the year of the sold landed costs for the product category over a 13 month period if we have over 12 months history. If it's a new product, then it's for the number of months we have had stock.
My question is, how do I tell the query to go back 13 months from the current month?
Hi, I need to write a select query to get last six months data from a table , on monthly wise.Can anyone help me in writing this query I am using sql server 2000.
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing( DepDate datetime, val int) insert into testing values ('2014-01-10 00:00:00.000', 1) insert into testing values ('2014-05-19 00:00:00.000', 10) insert into testing values ('2014-08-15 00:00:00.000', 20) insert into testing values ('2014-11-20 00:00:00.000', 30)
Hi,I have an SQl query that will list the results if it lies between two dates, for example;SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2This returns all results where Date1 and Date2 fall between the Current DateWhat i am looking for is a way to replace the { fn Now() } with a date of my choice.For example;SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2However this does not work.Has anyone any ideas why this may be ??Thanks in advanceAndrew Vint
How can I query date only in a where clause and not include the time part? When I use the where below if the time part of the date entry has past then the rows are not returned.
WHERE A.Selected=1 AND A.EndDate Is Null OR A.Enddate >= GETDATE()
Hi, im trying to write a sql query to check if dates are in the db, ias it is it is telling me the dates are there and their not. any help would be great. George
here's what i have...
SQL2 = "SELECT * FROM reservations WHERE" _ & "(arvdate >=#" & request("arvdate") & "# AND endate <= #" & request("endate") & "#) " _ & "OR " _ & "(arvdate >=#" & request("arvdate") & "# AND endate >= #" & request("endate") & "#) " _ & " AND idrent = " & idrent _ & " AND confirmation = 1" _ & " ORDER BY arvdate"
The Sales table includes: date, description, price, consigner The Consignment table includes: lastday, consigner
I need a query that will display all rows in Sales assuming Sales.date is less than or equal to Consigner.lastday. The .consigner fields need to be joined.
I have this and it isn't working:
SELECT DISTINCT [Sales Data].co1, [Sales Data].amount, [Sales Data].description, Consignment_Data.date AS Expr1 FROM Consignment_Data INNER JOIN [Sales Data] ON Consignment_Data.co1 = [Sales Data].co1 WHERE ((([Sales_Data].[date])=[Consignment_Data].[date]));
This is for MS Access 2003. Can anyone help me? I would appreciate some advice, as my code isn't working at all
SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear FROM dbo.Orders_Retail WHERE (account = @Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded') GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate) ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)
etc etc for each week in a year and then it goes onto the next year.
What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.
I've tried to do a WHERE DATEPART(wk, OrderDate) > @StartDate AND DATEPART(wk, OrderDate) < @EndDate AND YEar(OrderDate) > @StartYear AND YEAR(OrderDate) < @EndYear
But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.
I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.
