|
Hi,
I have a table of product movements between places,
Table Movements
{fromId, toId, date, productId, status, firmware, etc}
And a table of all the products
Table Products
{productId, name, status, firmware, etc}
My question is if its OK this design, in particular the bolded things.
In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table..
So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table.
Another solution could be to have a table
Table ProductProperties
{productPropertiesId, status, firmware}
and the other two would be like:
Table Movements
{fromId, toId, date, productId, productPropertiesId}
Table Products
{productId, name, currentPropertiesId}
Any comments?
|
|
|
|
|
It's a little confusing...
At what point does the Product table get updated to reflect its new status/firmware etc? Why does moving something from one place to another change these? Can a product exist that never moves?
|
|
|
|
|
The last movement associated to some product determines its status/firmware.
But yes, there might be products that havent been moved ever yet.
|
|
|
|
|
Quake2Player wrote: In each movement some properties of the product will change (status and firmware), but the one that is really active and prevailing is the one in the Products table..
So if I want to know the prevailing status I'll ask the Products table.. and if i want to know the status that some product had last tuesday I'll ask the Movements table.
This is definitely the best solution.
The other solution would only create one extra table you would need to query, making your queries needlessly complicated.
My advice is free, and you may get what you paid for.
|
|
|
|
|
I probably wouldn't have status and firmware in the Product table. When a new Product created I'd create the first Movement (created, bought, whatever) record for that Product.
|
|
|
|
|
Oh thats a nice solution..
Though the movements are used in other parts of the software.. so this "dummy" movements would have to have different properties.. like "from = to"
|
|
|
|
|
You might instead have a dummy location for the from value, e.g. "thin air".
|
|
|
|
|
Dear All
I have a Table Named CustomerDetails where i have millions of records the table structure is as follows
RecordId CustomerName CompanyName EmailAddress Phone
Is it possible to get the EmailAddress count of specific domains like how many emails with @gmail.com, @yahoo.com, @rocketmail.com, @att.net, @verizon.net
I have tried a lot of ways but i have failed in all tries , If possible drop me a line of code so it will be a great help for me
Kind regards
James
|
|
|
|
|
1. What type of database?
2. If using SQLServer, consider the following:
select count(*) from security_data
where charindex('Test',Resource_Desc) > 0
The table and column represent my data, but you should be able to modify it to your needs.
Tim
|
|
|
|
|
Hi tim
Thank your for your reply yes am using SQL2000
|
|
|
|
|
|
Hi Tim
select count(*) from CustomerDetails where charindex('@',EmailAddress) > 0
Here its showing total record count, i wish to get the count of emails with @gmail.com @yahoo.com, but i cannot hard code this cause the list is so big that it has to run a loop by itself
Kind regards
James
|
|
|
|
|
Using a different table on my end...
select distinct substring(email_distribution,charindex('@',email_distribution),len(email_distribution)), count(*)
from bow_reports
where charindex('@',email_distribution) > 0
group by substring(email_distribution,charindex('@',email_distribution),len(email_distribution))
Again, customize to your use.
email_distribution should be equivalent to your emailaddress
bow_reports should be equivalent to your customerdetails
Tim
|
|
|
|
|
Dear Tim
Thank you very much it worked 
Kind regards
James
|
|
|
|
|
I would write a user-defined function that returns the domain and then group by it.
|
|
|
|
|
Hi Everybody,
I have an issue in the SQL Server Reporting Services
I created InvoiceReport.rdl
In the Page header displays Title, Page Footer displays Page Number
In the Body section, I created a Table,
The structure is
1) Table Header
2) Group header Row1
3) Detail Section
Row1 [SubReport1]
Row2 [SubReport2]
The problem is, if the subreport print more than a page then Group Header is not repeating in every page other-wise it is printing
I also tried to check only ONE, either (1) Row Headers – “Repeat header rows on each page” OR (2) Column Headers – “Repeat header columns on each page”,
Iam waiting for yours valuable respose.
Thanks & Regards
Kumaran
|
|
|
|
|
hi ,
I am sending a mail using SQL server 2005 msdb.dbo.sp_send_dbmail , below written is my code:-
I need to change the color of the data . depending upon the value , i want to change it's color.
Kindly help me out.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Cummalative Airtel Report generated every half hour</H1>' +
N'<table width="100%" style="font-family:Arial; font-size:12px; color:#000066" border="1" cellspacing="0" cellpadding="0">' +
N'<tr><td width="20%"><b>Circle</b></td><td width="11%"><b>OfferedCalls</b></td>' +
N'<td width="11%"><b>AnsweredCalls</b></td><td width="11%"><b>AbandonedCalls</b></td>' +
N'<td width="11%"><b>PCA</b></td><td width="11%"><b>ATT</b></td>' +
N'<td width="11%"><b>FCR</b></td>' +
N'<td width="11%"><b>CQ</b></td>' +
N'</tr>' +
CAST ( (Select td =t.circle, '' ,td = t.offd, '' ,td =t.answ, '' ,td =t.abnd, '' ,
--td =Convert(char,Round(t.[%abnd],2)),
-- '' ,
td =Convert(char,Round(t.pca,2)), '' ,
td =Convert(char,round(tt.SumAtt/Answ,2)), '' ,--td =ttt.[login], '' ,
td =Convert(char,Round(ttttt.[CQScore],2)),'',
td=Convert(char,Round(fcr.[fcr],2))
from <table_name>
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MailProfile',
@recipients='abc@gmail.com',
@subject = 'TestReport',
@body = @tableHTML,
@body_format = 'HTML' ;
Regards,
Aman
|
|
|
|
|
Hi all,
I am facing a problem on whether using Transactional replication or Merge replication technique in SQL Server 2005. In my senario, i have 2 database which located in one sql server,named D1 and D2. Now, i would like to sync some of the table between these 2 database, and the data will frequently update/delete/insert by user into D1 or D2 and user should have the latest data for proceed operation. So, may i know which technique should be use in this senario?
Any suggestion or tips are welcome.
Note: And what if the 2 database are located in different server(different location)?
thanks in advance
cocoonwls
|
|
|
|
|
I had the same scenario and ended up using transactional replication @ 2 hour intervals. I was going @ 15 minute intervals with no issues but I wanted to have some latency in the event the source DB has some problems it gives me time to shut down the replication.
I didn’t use merge because if one DB gets hosed or corrupted they both get hosed.
I hope that helps.
Regards,
Hulicat
|
|
|
|
|
Hi Hulicat,
Thanks for your helping hand. Is Transactional replication bidirectional? i need to do sync for both side, that mean either D1 or D2 update, then both of them are update. And the interval time is my piority concern, i have to do it as fast as possible to allow user to use the latest data, may be in 30sec or even per transaction.
thankx in advance.
regards
cocoonwls
|
|
|
|
|
You need to use "merge" if either database can be updated,
Regards,
Hulicat
|
|
|
|
|
Hi Hulicat,
Thanks for your information.I got it  .But then is there any solution/suggestion to prevent the problem you were mentioned in your first post?
regards
cocoonwls
|
|
|
|
|
Not to my knowledge; it's one of the caveats of using "merge" replication.
Regards,
Hulicat
|
|
|
|
|
I have a storedprocedure in oracle,in that am using
begin
OPEN RCT1 FOR
SELECT REPLACE(SelectStatus.SelectCommand, '@Location',
SelectStatus.Location) INTO
SelectStatus.SelectCommand FROM DUAL;
end;
My output is :-
Select * from area_master where location = '001' order by area_name
what is want is instead of getting this query as output,i want the result of this query as output..
how can i execute this query inside the SP?...
Any help will be appreciated
thanks in advance...
|
|
|
|
|
I have done it using cursor....
now its working...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
