|
Hi,
I wanna ask if I can put just one table for both prices an cities.
In the beginning I created just table Product, but I have two different prices by cities(each city has a particular price).
After that I put two tables one for cities and one prices. but I think I don't need table cities, I'm asking if I can create one table prices with field city.
my data base NOW contains : Table Products: ProductID,ProductCode,ProductName,CityID,SubCategoryID...
Table Cities: CityID,PriceID,City Name.
Table Prices : PriceID,Price
Table OrderDetails: OrderDetailsID, OrderID, FK ProductID, UnitPrice
Table order OrderID, CostomerID,...
The problem with prices : there are many prices for same product when they ordered (each city has a different price)
|
|
|
|
|
Well, there are multiple ways to solve a problem. It depends on whether you want to achieve speed or whether you want to have a normalized database. Here's what I would have done:
Have a table called Products: ProductID, ProductCode, ProductName,...
Have a table called Cities: CityID, City Name
Have a table called Prices: PriceID, Price
Have a table for mapping products with prices and cities, ProductPrice: ProductID, CityID, PriceID
Pro: This way your data remains in a normalized form which is considered to be a good design approach.
Con: You have to perform multiple joins while fetching data which will hamper performance a bit. But, I don't think you have millions of cities. So, I won't be bothered much with that.
Hope it helped. 
|
|
|
|
|
Maintain normalized database whereas you can avoid redundancy and if you are not maintaining more cities for one city then you can go for same table.
|
|
|
|
|
Based on what you say, it sounds like there is one price per city.
If so, I will differ a little from above answer.
I would have designed as:
Have a table called MstProducts: ProductID, ProductCode, ProductName, CityID, ...
Have a table called MstCities: CityID, City Name, Price
This covers 'each city has different price' and is also normalized.
|
|
|
|
|
Thank you guys.
I appreciated.
|
|
|
|
|
ALTER procedure [dbo].[sp_pubhol]
as
begin
declare @i int
declare @NewDate date
set @i = 0
while(@i<3)
begin
IF DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 7 OR DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 1
print 'weekend'
else
print 'weekday'
select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
SELECT @NewDate = DATEADD(DAY,@i,CONVERT(date,GETDATE()))
RETURN @NewDate
select @i = @i+1
end
end
my task is if today is neither weekend nor public holiday ,i have to that date, increment loop...i have retrieve all the 3 working days...can anyone help me pls
if i want declare @NewDate as array[[3] ,how to declare that..pls help me
|
|
|
|
|
Use a table variable not an array.
Declare @Tbl Table(Date datetime)
Insert @tTbl
Select getdate()
So instead of buying a book and studying you are going to bring every problem to the interweb forums for answer. I predict your questions shortly be ignored and no answers will be forth coming!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
alter procedure sp_pubhol as begin select Holiday_Name from Holiday where Holiday_On = CONVERT(date,GETDATE()) end when i execute sp_pubhol , i am facing the following issue... Invalid object name 'Holiday' pls suggest me what should i do
|
|
|
|
|
The error is that it can't find a table called "Holiday". You can specify the database name followed by the table.
I think the syntax is YourDB.Holiday or YourDB..Holiday.
Give it a shot.
|
|
|
|
|
You really need to get a book on databases and SQL, learn the basics before attempting to build a solution. Forum messages and cut and paste is not the way to learn!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i created a table Holiday....even then it is showing error...my doubt is Holiday_On =
CONVERT(date,GETDATE()) can i do like that....
|
|
|
|
|
|
can anyone help me in writing stored procedure to Activate reminder for event to send email notification 3 days before event along with event description pls .i am using sqlserver2008R2.the list of events are in DB.
|
|
|
|
|
Use the datetime data type to AddDays to to the getdate() function to determine the date to check against your stored event date.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What have you tried so far? Where are you stuck and need help?
Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.
Try them and tell if you face issues.
For now, have a look at this to start of:
Send Email from Database[^]
Automatically send email every night 12.00 am[^]
|
|
|
|
|
Hi,in this you can create an job using SSMS.No need of writing stored procedure for this.
|
|
|
|
|
Hi,
I need a help please
I am using MySQL. I have a patients database. In patients database I have a table called visit with created_date field as timestamp datatype.
I want to do a query which will give the break down of total number of patients in every hour like this:
hour -------- Patients
00 -------- 7
01 -------- 8
02 -------- 11
03 -------- 4
...
...
...
20 -------- 5
21 -------- 10
22 -------- 3
23 -------- 0
Technology News @ www.JassimRahma.com
|
|
|
|
|
Create a query that extracts the hours (use datepart(h,datefield) component of the created date as an integer. A simple group by will then give you the result you need.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The amount of 0 items will make a problem with Mycroft Holmes' answer - that line won't show up.
You need the numbers from 0-23 (somehow generated, or make a table "Hours" with those values, and then JOIN the query suggested by Mycroft Holmes to it, and replace NULL values with 0.
|
|
|
|
|
Not sure if this will directly work in MySQL, but here is what I would do in SQL Server
SELECT
DATEPART(hh, Created_Date) [Hours]
, COUNT(*) [Patients]
FROM Visit
GROUP BY
DATEPART(hh, Created_Date)
EDIT
There is a format function in MySQL that will return the hour
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format[^]
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Hi,
I have visits table for my patients database. I have created_date field which is timestamp with CURRENT_TIMESTAMP and closed_date with datetime datatypes. I have no problem with the created_date but when I close the file using this command:
UPDATE visits SET total_amount = param_total_amount, balance_amount = param_balance_amount, visit_is_open = FALSE, closed_date = NOW(), closed_user = param_created_user WHERE visit_id = param_visit_id;
The closed_date will have the same value of the created_date. All my closed_date now equal created_date
Technology News @ www.JassimRahma.com
|
|
|
|
|
try GETDATE()
Cheees,
Edo
|
|
|
|
|
sorry Edo, forgot to mention it's MySQL spo there is no GETDATE()
Technology News @ www.JassimRahma.com
|
|
|
|
|
Then I agree it's a strange phenomenon
All other fields get updates correctly or the whole row does not get updated?
Cheees,
Edo
|
|
|
|
|
Not sure how created_date is equal to closed_date.
Make sure, that when you create a patient, at that time populate the created_date field (not closed_date - keep it blank)
At the time of closing the patient, just update the closed_date for that patient, something like:
UPDATE visits SET closed_date = GETDATE() WHERE visit_id = param_visit_id;
Both are separate queries and both target separate fields.
Refer: http://msdn.microsoft.com/en-us/library/ms188383.aspx[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
