How to retrieve only Monday dates from each month in sql server.
If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.
I want to calculate bi weekly range data in the sql.
I need some with selecting the number of days, in a month, between a date range. For example, my data looks like:
FileNumb | startdate | enddate
1 04/25/2015 05/02/2015
2 05/01/2015 05/10/2015
The output I am looking for would be:
FileNumb | Year | Month | Days
1 2015 4 6
1 2015 5 2
2 2015 5 10
I need to retrieve the last record for each month between two given dates from a unique table that contains on record per day.
View 3 Replies View RelatedI have a CheckDate field and I only what to retrieve dates that fall in the month previous to when the query is run. I'm assuming it will involve DATEADD or DATEPART, but I'm not sure how to do it since the end of the month day will vary from month to month.
For example, today is 04/09/2008 so I want to retrieve checks with a date between 03/01/2008 and 03/31/2008.
i have this FUNCTION
and the FUNCTION work OK
now how to add to this FUNCTION another mondy date ?
"last month last monday"
Code Snippet
CREATE FUNCTION [dbo].[Monday_List]( @arg_date datetime )
returns table
as
return
(
select convert(varchar(10), (dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7) + offset, 103)
as Date,
[Index],
Number
from
( select 1 as Number, 'First_Monday' as [Index], 0 as offset union all
select 2, 'Second_Monday', 7 union all
select 3, 'Third_Monday', 14 union all
select 4, 'Fourth_Monday', 21 union all
select 5, 'Fifth_Monday', 28
) x
where month(dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7)
= month((dateadd(mm, datediff (mm, 0, @arg_date), 0)
+ (8 - datepart(dw, dateadd(mm, datediff (mm, 0,
@arg_date), 0))) % 7) + offset)
)
this is the Output i get
select * from Monday_List('1/3/8')
Date Index Number
---------- ------------- -----------
03/03/2008 First_Monday 1
10/03/2008 Second_Monday 2
17/03/2008 Third_Monday 3
24/03/2008 Fourth_Monday 4
31/03/2008 Fifth_Monday 5
*/
now i need to add to the FUNCTION "last month last monday"
like this
select * from Monday_List('1/3/8')
Date Index Number
---------- ------------- -----------
04/02/2008 last monday 0 ---------------------- i need to add
03/03/2008 First_Monday 1
10/03/2008 Second_Monday 2
17/03/2008 Third_Monday 3
24/03/2008 Fourth_Monday 4
31/03/2008 Fifth_Monday 5
*/
------------------------------
TNX
hi need help
how can i put all the monday'S day next month in one table like this
so i can use it all years
date index number
---------------------------------------------------------
04/02/2008 first_monday 1
11/02/2008 second_monday 2
18/02/2007 third_monday 3
25/02/2008 fourth_monday 4
TNX
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
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
How to write this query?
I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010
SQL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
[Code] ....
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
View 9 Replies View RelatedWe had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
How to convert a date to the format as Month name and year(2 digits).
For e.g.- Jan 14, Feb 14......Mar 15
How I want to see the below dates via 6 different Select Statements but I am unsure how to get this?
01-May-2015
01-Apr-2015
01-Mar-2015
01-Feb-2015
01-Jan-2015
01-Dec-2014
I have this Select statement
select replace(convert(char(11),getdate(),113),' ','-')
But it is returning the 15-May-2015 and it should be 01-May-2015 for this select statement
Is there anyway I can find or make a report if a databases has been accessed/used since last month?
View 4 Replies View Relatedi was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.
View 9 Replies View RelatedHow to show the dates in between a range. Its hard for me to explain so I have ddl with the original results and then ddl of how I would like the desired outcome.
On the side I have a visit ID I need to show each day logged for each ID. Sometime the Start and End are a single day and sometimes they are a range. I need a row for each date.
CREATE TABLE #Results (VisitID INT, DateFrom DATE, DateTo DATE)
INSERT INTO #Results VALUES (361, '2015-03-07', '2015-03-07'), (361, '2015-03-08', '2015-03-10')
,(48, '2015-03-18', '2015-03-18'),(48, '2015-03-19', '2015-03-23')
SELECT *
FROM #Results
DROP TABLE #Results
[Code] .....
I am trying to generate dates for a roster dynamically in a stored procedure. If my start date is '01/01/2015', end date is '12/31/2015', I should generate dates from '01/01/2015' adding 7 days.
The output comes like this:
01/01/2015
01/08/2015
01/15/2015
.
.
.
until end date
I do it through loop but just posting it here to know, if there is any other efficient way using CTEs or some sort of queries which can avoid loop in SP.
I’m trying to extract a list of records that don’t appear between 2 dates. I have 4 tables:
tblCustomers
tblMachines
tblServiceOrders
tblMachinesServiced
tblCustomers contains a CustomerID that is unique to each customer.
tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.
tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.
tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.
What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.
For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.
I’ve tried several different queries but here is an example:
SELECT tblMachines.MachineID,ServiceMachines.ServiceDate
FROM tblMachines
LEFT JOIN
(SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced
WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo
) ServicedMachines
ON tblMachines.MachineID=ServicedMachines.MachineID
WHERE YEAR(ServiceDate) != '2014'
I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.
How would I find the last read/write dates for all the tables within a database.
View 6 Replies View RelatedHow do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.
CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time
I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.
Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000
I have a SSRS report using 2008 R2. It prompts the user for the start and end dates. This all works. But now I want the start date parm to default to the first day of the current month and the end date parm to default to the last day of the current month.In the new query window in SQL Server Management Studio, I can run this chunk of code to get the first day of current month:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
And this code to get the last day of current month:
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),
DATEADD(MONTH, 1, GETDATE()))
But I don't know how to do this in SSRS 2008. How can I make my start / end parms to get these values.
I am trying to find an easy way to create multiple of just two date in a single sql statement.
E.G.
A statement using the parameters
@StartDate = '2015-01-01'
@EndDate = '2015-01-05'
Ends up with rows:
'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'
What would be the best way to do this ?
I need to prepare a roster rotating persons within each Department for the available dates
Please refer to the below sample tables and data
CREATE TABLE Roster
(
StartDate Date,
EndDate Date
)
CREATE TABLE Department
[Code] ....
Expected Output
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'A' Department,'P1' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'B' Department,'P3' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'C' Department,'P6' Person
[Code] .....
I have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient.
create table TEST
(
MRN varchar(10),
EncDTTM datetime,
Sequence int
)
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')
[code]....
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
View 0 Replies View RelatedI have three tables:
"PaymentsLog"
"DatePeriod"
"PaidOrders"
As per below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
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.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).
ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11
I'd like to show the assets with a start date and end date for the placement of the asset.
The start date to be the placement date and the end date to be the next placement date of the asset.
Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.
ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()
I'm guessing some sort of recursion is required here to produce this.
hii all
i am havie a database with given fileds like projectid,projectcode,projectname,startdat,enddate,etc....
my problem is to show all months and year between each projects startdate and enddate in a datagrd..and the table dont have month, year field..
i need to use sql quiery..
if anybody can help me in this matter.. it will be greatful..
thanks in advance..binoy
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:
01-31-2012
02-29-2012
03-31-2012
04-30-2012
05-31-2012
06-30-2012
07-31-2012
08-31-2012
09-30-2012
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.
need help
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month
"tb_all_holiday before"
date_start date_end
----------------------------------------------------------
15/03/2008 00:00:00 17/09/2008 00:00:00
20/04/2008 00:00:00 12/05/2008 00:00:00
i must to covert it like this
to this - see relative dates for the next month
"VIEW_all_holiday after -next month only "
date_start date_end
----------------------------------------------------------
01/05/2008 00:00:00 31/05/2008 00:00:00
01/05/2008 00:00:00 12/05/2008 00:00:00
TNX for help
I have a table that i want to query and group the records by themonth, what SQL stanment would i use?Somthing like this? apart from i know that the "Group BY (MM/YYYY)"wont work !:)Select Date,NameFROM TableGroup by (MM/YYYY)Does this make sense?Willa
View 1 Replies View Related