|
Hi, I need some guidance on the following scenario:
I need to create a historic inventory of number of rooms sold per hotel.
A transactional table stores data in the following columns
IdHotel, CheckInDate, CheckOutDate, NoRooms
I need to generate a table with 365 records/days per year per hotel as follows:
hotel1, 01/Jan/2014, 1 room
hotel1,02/Jan/2014,2 rooms
hotel1,03/Jan/2014,2 rooms
hotel1,04/Jan/2014,0 rooms
.
.
.
.
.
.
hotel1, 01/Nov/2014, 1 room
hotel1, 02/Nov/2014, 1 room
hotel1, 03/Nov/2014, 1 room
.
.
.
.
.
I am a bit lost as to how to approach this problem for a solution.
I do not need the actual code. Just a hint on how to solve this problem.
|
|
|
|
|
Select hotel,date,sum(no rooms )from table where datepart(yy,datefied)=2013 group by( hotel,date)
That should give you some ideas, get a book on tsql and work through it,you will find it invaluable .
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have some quite dirty solution
1. Create a dummy table of an int column stores 1...365
2. Select DATEADD 1/1/2014 with an int from table above. You'll get all "dates" in 2014.
3. Select COUNT on your transaction GROUP BY date from (2).
If you want to count how many rooms had been sold each day, you might need to count by matching CheckInDate.
If you want to count how many rooms were occupied each day, you might need to count by seeing if the "date" within CheckInDate & CheckOutDate.
|
|
|
|
|
so,the hotel name is same ?
|
|
|
|
|
chichocojo wrote: I need to generate a table with 365 records/days per year per hotel as follows:
You do realise than some years have 366 days, right? 
You can generate a list of all days in a given year by using a tally-table (or a common table expression which generates a tally table), along with the DateAdd function:
DECLARE <a href="/Members/Year">@Year</a>int = 2014;
DECLARE @StartDate date = Convert(date, Convert(char(4), <a href="/Members/Year">@Year</a> + '0101');
WITH cte1 (N) As
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
),
cte2 (N) As
(
SELECT TOP 366
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM
cte1 As A
CROSS JOIN cte1 As B
CROSS JOIN cte1 As C
),
cteDates (Value) As
(
SELECT
DateAdd(day, N, @StartDate)
FROM
cte2
WHERE
Year(DateAdd(day, N, @StartDate)) = <a href="/Members/Year">@Year</a>
)
SELECT
Value
FROM
cteDates
ORDER BY
Value
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am using database project in my solution. I Need to know how to synchronize source(in sql server management studio) and target(in your solution) databases, in case when someone update the schema from Sql server IDE. Let me know if you need more clarity on the question.
|
|
|
|
|
I have trouble with the whole idea that you the owner of the database do not control who and when the code is changed. You should be controlling who ans when someone or an application updates the code
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, please help me optimize this query
SELECT DISTINCT CC_STRINGENTEREXIT(ems.passdatetime,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
) as DetailEnterExit,
CC_DURATIONENTEREXIT(
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.passdatetime) as DurationInZone
FROM EMSINFO ems
where
trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS')) AND
CC_GETNEXTENTER(ems.EMSINFOID,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.PASSDATETIME) <> 0 and
CC_CheckForExit(ems.PASSDATETIME,
(
select ems2.PASSDATETIME from EMSINFO ems2
where ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.PASSDATETIME)
),
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
) <> 0
and ems.masterplatenumber = '150444833' and rownum <100 ;
you see that
1-
Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid
has been repeated multiple time, how do I declare it once and use it as many times as I want.
2-
CC_GETNEXTENTER()
has also been repeated, and in the functions I have a similar Issue, how can I get the value of the function once and use it as many times as I want through the query?
Thanx in advance
|
|
|
|
|
1. You use a join. Something like this: Untested!
SELECT DISTINCT
CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit,
CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
FROM EMSINFO ems JOIN EMSQUIRKINFO emsqi
ON emsqi.emsinfoid = ems.emsinfoid
WHERE trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND CC_GETNEXTENTER(ems.EMSINFOID, emsqi.PLATENUMBER, ems.PASSDATETIME) <> 0
AND CC_CheckForExit(ems.PASSDATETIME, (
SELECT ems2.PASSDATETIME from EMSINFO ems2
WHERE ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME)
),emsqi.PLATENUMBER) <> 0
AND ems.masterplatenumber = '150444833' and rownum <100 ;
2. Use a CTE, Something like this: Still untested!
WITH bla AS (
SELECT CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit,
CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME) GetNextEnter
FROM EMSINFO ems JOIN EMSQUIRKINFO emsqi
ON emsqi.emsinfoid = ems.emsinfoid
WHERE trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND ems.masterplatenumber = '150444833'
)
SELECT DISTINCT
DetailEnterExit,
DurationInZone
WHERE GetNextEnter <> 0
AND CC_CheckForExit(
ems.PASSDATETIME,
(
SELECT ems2.PASSDATETIME from EMSINFO ems2
WHERE ems2.EMSINFOID = GetNextEnter
)
,emsqi.PLATENUMBER) <> 0
AND rownum <100
;
Now, keep in mind that I don't have a clue what your functions do, so my suggestions is only to show you the methodology, It's up to you to see if it gives the right result.
But you want as much of the filtering in the CTE as possible to keep the amount of data down.
|
|
|
|
|
select cs.customer_name, st.staff_name
from Customer cs
inner join Staff st on cs.city_id=st.city_id
(just a sample query, dont ask for its purpose)
I'm quite sure that we need an index on st.city_id to speed things up.
But would an index on cs.city_id make any different?
Because it looks like we have to scan entire Customer table anyway.
|
|
|
|
|
Assuming you are using SQL Server, turn on the Profiler in SSMS and run the query, it will then recommend an index if required.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
khun_panya wrote: But would an index on cs.city_id make any different?
Most probably it will. The query optimizer decides whether to use an index or not depending on various criteria. If the number of rows in the table is very less, it might do a table scan rather than an index seek. But this is purely left to the optimizer to decide.
khun_panya wrote: Because it looks like we have to scan entire Customer table anyway.
What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.
|
|
|
|
|
Shameel wrote: khun_panya wrote: Because it looks like we have to scan entire Customer table anyway.
What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.
I have not checked for real execution plan. But above query do select with "no filter". So it would have to iterate all the rows in table. That's why I doubt that an index helps in this situation.
|
|
|
|
|
There is a good chance that an index would speed things up.
The advice given so far is sound, however.. indexes are a bit of an art and like all arts you have to experiment occasionally.
Two areas that affect indexes are the number of rows and the frequency of updates and inserts.
For this reason you need to understand some of the theory behind indexes and also be willing to experiment with adding indexes to see what happens and learn from that.
I don't have any links but I would suggest giving yourself a good hour to google and read up on indexes as it will stand you in good stead.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Here's[^] a really good link about indexes.
|
|
|
|
|
Hello all,
I ran the trace file and then analysed this data in the Anaylser in SQL Server 2008.
I have a table consisting of 2 million rows, and the analyser recommended 15 indexes. I am fairly new to this company and was not sure how to go about this.
Is there some other way to figure which index would be really required? I have a field which is the Primary Key, a datetime field, a unique identified field.
Thank you!
|
|
|
|
|
I usually try and pick the index that is going to hit the most granular field and create that first, rerun the analysis and repeat until satisfied with the performance. This often reduces the number of indexes actually required to gain significance performance gains.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As Mycroft suggests, create the most granular index first. Indexes tend to be more useful when the distribution of data in the indexed column(s) is high. And create one index at a time and rerun the query to see how much performance improvement you've gained, if any. This way, if you find that the query has actually slowed down (which may happen sometimes), you can always disable or drop the index and proceed with the next one.
|
|
|
|
|
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the if...else in the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.
|
|
|
|
|
I'd agree that it's better to put this in SQL than the application. In SQL, there's less chance of the row being deleted between the If Exists test and the Update statement.
However, I've seen suggestions that something like this might be slightly more efficient:
UPDATE YourTable SET ... WHERE ID = @ID
If @@ROWCOUNT = 0
INSERT YourTable (...) VALUES (...);
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: In SQL, there's less chance of the row being deleted between the If Exists test and the Update statement.
That's why we have transactions.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Just don't count on that to work on any database.
And for that purpose it's better to use MERGE[^]
|
|
|
|
|
IMHO anything that data handling (update, insert, delete, move, aggregate and on) is good to do in SQL - it's very good at it.
However IF isn't exactly in that category - so do update then insert where the where clause contains an addition of @@ROWCOUNT = 0 at the end...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Thanks for the feedback; I will use the ROWCOUNT test.
|
|
|
|
|
I don't like the rowcount method as is uses 2 writes to the database. Have not used the Merge function so I have no opinion.
I use convention instead, EVERY tale has an identity field as the primary key, so when I pass in the object (via variables) the ID field has either 0 or the record value. I then use IF to test the ID and either insert or update the record.
I put it into a tip [^].
While it is not guaranteed to be perfect it has been working for me for decades! 99% of database our work goes through stored procedure. String queries are heavily discouraged.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
