Re: Sync data Bi-Directional Automatically- Sql Server 2008 - Database Discussion Boards

Re: How to filter duplicate data about technical support and compute rank

bbaaking17-Apr-12 22:54

17-Apr-12 22:54

hi,Jörgen Andersson
thank you for your help. the statement you posted seemed like a sqlsvr statement, I ran it on mysql and got a error:You have an error in your SQL syntax near "with.....".
I should run it on mysql.
in any case, I have got the correct answer of aim 1( I have write it in the top/first post), and plz help me with aim 2-4( It's still in trouble).

Re: complicated SQL: How to filter duplicate data about technical support and compute rank

TenmanS1418-Apr-12 11:40

TenmanS14

18-Apr-12 11:40

SELECT * FROM (SELECT COUNT(TCID) as C, TCID FROM service WHERE Qos = 1
and MONTH(EndTime) = 1
GROUP BY TCID) AS T
ORDER BY C DESC

This'll rank em for a month, I'll leave you to learn how to increment that month value to loop through for every one...

SELECT * FROM (SELECT COUNT(TCID) as count, TCID, Month FROM (SELECT month(endtime) as Month, tcid, serviceid FROM service
WHERE qos = 1
group by month(endTime),tcid, serviceid) AS T
GROUP BY month, tcid) AS S
order by month, count DESC

alternatly, this'll give you all of the months in the statement, I'll leave you to actually learn some SQL to refine it to get exactly what you want...

Re: complicated SQL: How to filter duplicate data about technical support and compute rank

bbaaking18-Apr-12 21:56

bbaaking

18-Apr-12 21:56

Thanks a lot!
I add filter statement( aim 1, without it the result will not be correct),like:

SQL

SELECT * FROM (SELECT COUNT(TCID) as count, TCID, Month FROM (
    SELECT month(endtime) as Month, tcid, serviceid 
	FROM (SELECT MIN(Qos) qos, serviceid, id, TCID, EndTime from service
		GROUP BY serviceid
    	)T1
	WHERE qos = 1
	group by month(endTime),tcid, serviceid
    )As T
GROUP BY month, tcid) AS S
order by month, count DESC

and It got correct data:

SQL

count	TCID	Month
4	Andrew	1
3	Jacob	1
2	Tyler	1
2	Dylan	1
1	Daniel	1
4	Dylan	2
4	Jacob	2
4	Andrew	2
2	Tyler	2
1	Daniel	2

I think It is half of aim 2, thank you. please go on ,I think Top-2-in-January(rank) is Andrew/Jacob, Top-2-in-February(rank) is Andrew/Dylan/Jacob . So the result of aim2 should be 2 reocrders: Andrew/Jacob

modified 19-Apr-12 4:11am.

I have solved it by myself

bbaaking22-Apr-12 20:22

bbaaking

22-Apr-12 20:22

I can not find a SQL solution to solve this problem, but finally, I found a esproc solution.

It is not a traditional SQL, but It fit for me, because ESPROC can return a resultSet to my Jasper reporting tool.

Anyway, I still need a SQL solution, can some body help me?

ESPROC's solution is just like this:
http://stackoverflow.com/questions/10184205/complicated-sql-how-to-filtrate-duplicate-data-about-technical-support-and-comp/10275434#10275434[^]
or check here:
http://i.stack.imgur.com/p5rp0.jpg[^]

Here are some information about this solution:
http://www.esproc.com/tour/what-is-esproc.html[^]
Or here is a SQL Server Solution(but not fit for mysql):http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e11c754d-559e-4eb7-9682-e998ea3387d9[^]

Hope this helps people that meet the same trouble

Sync data Bi-Directional Automatically- Sql Server 2008

dsrao16-Apr-12 11:49

dsrao

16-Apr-12 11:49

I have table(Tbl_A) in Server1 whenever DML(Insert/Update/Delete) operation happens in Tbl_A
it has to be automatically insert/Update/Delete into Table B,C(Tbl_B,Tbl_C) in different sever2.
'Whenever Table B,C Update/Delete/Insert record automatically record in Table A(Tbl_A).

I tried trigger but it does not help work for me.

Please advice me how to deal this scenario.

Thanks
Rao

Thanks & Regards
Rao

Re: Sync data Bi-Directional Automatically- Sql Server 2008

kalaisw17-Apr-12 2:06

kalaisw

17-Apr-12 2:06

Hi,
Try sync Framework
Reference :
http://msdn.microsoft.com/en-us/library/bb902853.aspx[^]

Re: Sync data Bi-Directional Automatically- Sql Server 2008

dsrao17-Apr-12 6:48

dsrao

17-Apr-12 6:48

Thanks..

It won't do automatically only sync occasionally / manually.

I am looking for whenver DML operation happens.

Thanks & Regards
Rao

Re: Sync data Bi-Directional Automatically- Sql Server 2008

TenmanS1419-Apr-12 4:44

TenmanS14

19-Apr-12 4:44

Well, you could use the OUTPUT Clause instead of a trigger to capture the data to input to the other server, and send to the application that inserted the row to actually put it into the other server, never tried it but this might be worth a go if you wanna do it in pure T-SQL

http://stackoverflow.com/questions/545364/tsql-command-to-connect-to-another-server-sql-server-2005[^]

Re: Sync data Bi-Directional Automatically- Sql Server 2008

JosephvObrien19-Apr-12 22:14

JosephvObrien

19-Apr-12 22:14

I have no better idea about relevant of database but your problem will be solve at w3school site because of there is well explain about database...I also daily visit that site for getting new information about html...

T-SQL Developer needed for hire.

SQL Ed15-Apr-12 5:13

SQL Ed

15-Apr-12 5:13

Have been working with and studying Microsoft SQL Server, SSRS and SSIS. I am reading about six books at the same time plus using them at work. I am close to interviewing for a Mid level job. I wish to hire a good T-SQL developer to put together study sheets which will include snipets of real world code in the major areas of TSQL such as stored procedures, funtions, loops, CASE and much more. I need to go in strong on the coding side but don't get much experience coding at work. Reading the books helps but its not like the real world.
ANY Suggestions. Big Grin | :-D

Re: T-SQL Developer needed for hire.

Mycroft Holmes15-Apr-12 13:54

Mycroft Holmes

15-Apr-12 13:54

It is an interesting approach, not sure what the response will be like! The way I always learn is to build, if there is an area where I feel deficient and work does not supply an opportunity to expand into that area I will build a project on my own time to explore the area.

While you are trying to short cut that process I don't think it can be done, someone can't feed you experience.

Never underestimate the power of human stupidity
RAH

Re: T-SQL Developer needed for hire.

SQL Ed16-Apr-12 0:32

SQL Ed

16-Apr-12 0:32

As I ststed I am already reading 5 books and studying as hard as I can. I have no free time. I am only trying to get study sheets from a professional not take short cuts. I guess you suggest I stay in this underpaid paid possition that I can't even pay my rent with for another five years while I set up projects on my own to explore.

Re: T-SQL Developer needed for hire.

SQL Ed16-Apr-12 0:32

SQL Ed

16-Apr-12 0:32

Stated

Re: T-SQL Developer needed for hire.

Mycroft Holmes16-Apr-12 3:15

Mycroft Holmes

16-Apr-12 3:15

I was not denigrating the fact that you are trying, just the fact that experience is just that, experience and cannot be transferred, you can however learn from the experienced which is what you are trying to do.

Your problem is finding an experienced person with the spare time to teach and transfer that knowledge.

Never underestimate the power of human stupidity
RAH

Re: T-SQL Developer needed for hire.

PIEBALDconsult15-Apr-12 18:47

PIEBALDconsult

15-Apr-12 18:47

Get about ten years experience. Then you will know how to deal with the types of situations you will likely encounter.

select in star schema

mrx10014-Apr-12 22:34

mrx100

14-Apr-12 22:34

hi ,i want to select name from all around tables
& i have near 25 tables such as : select name from pharmacy , hospital , theater ,etc...
all of them is linked with place table by id

this picture show sample of tables :
http://www.mediafire.com/?20rsb4q25kyj14w[^]

Re: select in star schema

phil.o14-Apr-12 22:46

phil.o

14-Apr-12 22:46

If there can be several names for a specific place :

SQL

SELECT
   'Pharmarcy' AS Entity
  ,Pharmacy.Name AS Name
FROM
  Pharmacy
  INNER JOIN Place ON Place.Id = Pharmacy.PlaceId

UNION

SELECT
   'Hospital' AS Entity
  ,Hospital.Name AS Name
FROM
  Hospital
  INNER JOIN Place ON Place.Id = Hospital.PlaceId

and so on...

If there can be only one name for a specific place:

SQL

SELECT
   Pharmacy.Name AS PharmacyName
  ,Hospital.Name AS HospitalName
FROM
  Place
  INNER JOIN Pharmacy ON Place.Id = Pharmacy.PlaceId
  INNER JOIN Hospital ON Place.Id = Hospital.PlaceId

No memory stick has been harmed during establishment of this signature.

Re: select in star schema

mrx10014-Apr-12 23:04

mrx100

14-Apr-12 23:04

i have used union

select mall.name from mall,places where mall.place_id=places.id UNION select hospital.name from hospital,places where hospital.place_id=places.id UNION select clinic.name from clinic,places where clinic.place_id=places.id UNION select shop.name from shop,places where shop.place_id=places.id

but it's very long i should select name from 25 table
can you use nested query i think it will solve the problem

Re: select in star schema

Eddy Vluggen15-Apr-12 0:45

Eddy Vluggen

15-Apr-12 0:45

Having a long query doesn't count as a problem. Why is it classified as such? Does it not work?

Bastard Programmer from Hell Suspicious | :suss:

Re: select in star schema

Luc Pattyn15-Apr-12 3:29

Luc Pattyn

15-Apr-12 3:29

when there is much commonality in the fields of a number of tables, it tells me the data should have been structured differently, using just one or two tables and one more field. You now have to pay for the bad decisions made earlier, by adding unproductive code everywhere.

Hmmm | :|

Luc Pattyn [My Articles] Nil Volentibus Arduum

Re: select in star schema

Mycroft Holmes14-Apr-12 22:56

Mycroft Holmes

14-Apr-12 22:56

Now I get nervous when a newbie wants me to download a file to my computer so I can help him! So I won,t be looking at your image!

You need to be clear as to what you want - in your message not some dodgy image. Ery you looking for the entity name from each of your tables or do you have a name field on each of your tables or (god forbid) you actually store a persons name in each table!

If they are linked by ID fields you should be able to build FKs and create a view of all the entity names in one table (possibly).

Never underestimate the power of human stupidity
RAH

Re: select in star schema

mrx10014-Apr-12 23:05

mrx100

14-Apr-12 23:05

don't download image just click on preview on medifie

Re: select in star schema

Mycroft Holmes15-Apr-12 13:50

Mycroft Holmes

15-Apr-12 13:50

As Luc pointed out, that is a lousy design, if you have address all over the place then you have made an error in your data design. You are going to have to live with the monster query or refactor your database (probably not reasonable solution)

Never underestimate the power of human stupidity
RAH

Re: select in star schema

Bernhard Hiller15-Apr-12 22:00

Bernhard Hiller

15-Apr-12 22:00

Change your place table: add the column name. And then move your data to the correct place, and get rid of the name column in those other tables.

select in star schema

mrx10014-Apr-12 22:34

mrx100

14-Apr-12 22:34

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.