March 27, 2013 at 12:09 pm
Hi i have a big problem here
i have a list of person in my database
each person have several entry date and exit date
i would like to know what is the average time those person stays
if the first person stay 2 days , the second 3 days ....
i would like to calculate 2+3+... and give the average
the problem is some person have entry date but without exit date
so i need to eliminate those person i will only consider person that have an exit following and entry
thanks to help please its very urgent and important
March 27, 2013 at 12:20 pm
asco5 (3/27/2013)
Hi i have a big problem herei have a list of person in my database
each person have several entry date and exit date
i would like to know what is the average time those person stays
if the first person stay 2 days , the second 3 days ....
i would like to calculate 2+3+... and give the average
the problem is some person have entry date but without exit date
so i need to eliminate those person i will only consider person that have an exit following and entry
thanks to help please its very urgent and important
This is certainly a feasible query to do. I see that you are pretty new around here. In order to offer much help we need to have ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. Please take a few minutes and read the article found at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2013 at 1:41 pm
Agree with Sean on providing the schema, sample data and expected output given the sample data.
I have taken the liberty though, to come up with some sample test data looking at your excel attachment.
Here's a possible solution to your problem:
IF OBJECT_ID('tempdb..#PersonLogEntries')IS NOT NULL
DROP TABLE #PersonLogEntries;
GO
--Create temporary schema to hold input sample data
CREATE TABLE #PersonLogEntries
(
nationalityVARCHAR(20),
personidINT,
[date]DATE ,
modeVARCHAR(5)
)
GO
--Some sample data.
INSERT INTO #PersonLogEntries (nationality,personid,[date],mode)
VALUES
('lib','123','20110101','entry'),
('fr','1254','20120504','entry'),
('spain','201','20130707','entry'),
('civ','658465','20130908','entry'),
('lib','123','20110503','exit'),
('lib','123','20110504','entry'),
('lib','123','20110508','exit'),
('civ','658465','20130928','exit')
GO
--The below CTE: CTE_StayLog will contain each visit of a person and how many days one stayed during the visit.
--The same person might have visited more than once.
;WITH CTE_StayLog AS
(
SELECT
PENTRY.nationality,
PENTRY.personid,
PENTRY.[DATE] AS EntryDate,
PEX.ExitDate,
DATEDIFF(DAY,PENTRY.[DATE],PEX.ExitDate) AS DaysOfStayPerVisit,
ROW_NUMBER()OVER(PARTITION BY PENTRY.personid ORDER BY PENTRY.[date] ASC) AS EntryNumber
FROM #PersonLogEntries AS PENTRY
OUTER APPLY
(
SELECTMIN([date]) AS ExitDate --Get the minimum exit date for each entry date. will be NULL if there is no exit.
FROM#PersonLogEntries AS PEXIT
WHEREPEXIT.mode = 'exit'
ANDPENTRY.personid = PEXIT.personid
ANDPEXIT.[date] > PENTRY.[date]
) as PEX
WHERE PENTRY.mode = 'entry'
)
--If average stay per person is needed
SELECT nationality, personid, AVG(DaysOfStayPerVisit)
FROMCTE_StayLog AS CTE
WHERECTE.ExitDate IS NOT NULL --People (rather entries) who do not have an exit date are ignored.
GROUP BY nationality, personid
If total average stay of all people is needed, rather than average stay per person, replace the last select above with the select below
--If total average stay of all people is needed, rather than average stay per person, replace the last select from the query above with the select below
SELECT AVG(DaysOfStayPerVisit)
FROMCTE_StayLog AS CTE
WHERECTE.ExitDate IS NOT NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply