I have a table called tbloffence where we can see list of offence which workers have done in worksite.so now i want to display the sumtotal of their offence in .aspx page based on workes userid,but the condition is like this:
a) offence since last login:
b)offence in last 1 month.
i want to write a sql query based on this condition...how can i track"offence since last login"in sql server 2005
1)tbloffence : where we will get the sum(offence) here i track maincontractorID and sum(offence) query will be like this :
"SELECT sum(o.OffenceID) as OffenceNumber,u.UserID
from tblOffence o
inner join tblSubContractor ts on
from here i will chk the tblUSER with these subcontractorID :
"inner join tblUser u on
and my doubt is that upto here i will get the total sum of offence and appropriate userid.,for eg
UserID : 520 TotalOffence:6000
but there is another table called tblAuditTrailSession where we will get logondate and userid which is tracking here..so i want to display offence based on userlogin(userid) ..since last login. i want a help in query format..Hope this is a generalized format question.
I’ve got a big problem. Could anyone give me a hand?
My first aim(not the final goal) is:fliter the duplicate data with query statment( select not delete), that is, the various entries of EndTime and Qos for a same ServiceID due to the repeated feedback. The rule is to only keep the records with the lowest Qos for a same ServiceID. If there are several records having the lowest Qos, then only keep whatever one of these records. The client accepts and prefers to this scheme. In this example, for the ID=6,7, and 8, just keep 6 or 7. And I have got answer of this aim here (thanks for TenmanS14 ):
SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
aim2: Qos=1 represents the Satisfied Service. The monthly total Satisfied Services achieved by each technical support engineer is referred to as “Personal Total Satisfied Services this Month”. Those technical support engineers whose “Personal Total Satisfied Service this Month” ranking among the top 2 in that month are referred to as the “Top 2 since this Month”. If they are outstanding enough to be entitled to the “Top 2 since this Month” every month, then they can be referred to as “Outstanding of Class 1”. All in all, this step is to compute the “Outstanding of Class 1”. In this example, “Top 2 since this Month” for January is Andrew and Jacob, and that for February are Andrew, Dlyan, and Jacob. Therefore, the honor title of “Outstanding of Class 1” is awarded to Andrew and Jacob.
please help me with aim2 (finally I should reach the aim 4, the final goal), result of aim 2 should be
explain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and the field ID is a unique primary key because there are some duplicates.
ServiceID ID TCID EndTime Qos
2000 2 Jacob 2011/1/1 2
2000 3 Jacob 2011/1/1 2
2001 4 Jacob 2011/1/1 2
2002 5 Jacob 2011/2/3 1
2003 6 Tyler 2011/1/4 1
ID: Unique primary key of record
ServiceID: ID of a certain service
TCID: ID of a technical support engineer
EndTime: Ending Time of aservice
Qos：Quality of service (1 Satisfied; 2 Average; 3Unsatisfactory/Dissatisfied).
Almost forget to say that, just a reminder, the client only accept the SQL instead of stored procedure in database to implement it. And I only need query statement because customer did not allow us to write database.
this is my first 2 aims, there are 2 more, I am intended to complete all these steps one after another. Could anyone help achieve the first goal? I know it is quite complicated and many thanks to you in advance.
aim3:Then, to compute the “Outstanding of Class 2” (the engineers of “Top 2 since this Month” are not included) equals to compute those ranking the third and the forth places. In this example, the “Outstanding of Class 2” is the Tyler.
aim4:The final goal is to combine the “Outstanding of Class 1” with “Outstanding of Class 2”. The result will be ultimately transferred to report for rendering. My dataSet is just like:
thank you for your reply, but I need a resultset object, It's hard to write it step by step like your solution.
I mean I need a single query statement
my result of goal 1 should be:
20002 Jacob 2011/1/1 220014 Jacob 2011/1/1 220025 Jacob 2011/2/3 120036 Tyler 2011/1/4 120049 Jacob 2011/2/4 1200510 Jacob 2011/2/5 1200611 Jacob 2011/2/4 2200712 Jacob 2011/1/8 1200813 Tyler 2011/2/6 1200914 Dyla 2011/2/8 1201015 Dyla 2011/2/9 1201119 Andrew 2011/2/2 1201218 Andrew 2011/2/19 1201317 Andrew 2011/1/1 1201416 Andrew 2011/1/1 1201520 Andrew 2011/2/1 1201621 Andrew 2011/1/19 1201722 Jacob 2011/1/1 1201823 Dyla 2011/2/3 1201924 Dyla 2011/1/9 1202025 Dyla 2011/1/1 1202126 Andrew 2011/1/3 1202228 Jacob 2011/1/9 1202329 Tyler 2011/1/19 1202430 Andrew 2011/2/1 1202531 Dyla 2011/2/3 1202632 Jacob 2011/2/4 1202733 Tyler 2011/2/9 1202834 Daniel 2011/1/6 1202935 Daniel 2011/2/1 1
WITH id AS (
SELECT serviceid,tcid,Min(EndTime) endtime,Min(Qos) qos
FROM service s,id
WHERE s.serviceid = id.serviceid
AND s.tcid = id.tcid
AND s.endtime = id.endtime
AND s.qos = id.qos
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).
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...
Thanks a lot!
I add filter statement( aim 1, without it the result will not be correct),like:
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
WHERE qos = 1groupby month(endTime),tcid, serviceid
GROUPBY month, tcid) AS S
orderby month, count DESC
and It got correct data:
count TCID Month
4 Andrew 13 Jacob 12 Tyler 12 Dylan 11 Daniel 14 Dylan 24 Jacob 24 Andrew 22 Tyler 21 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
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).
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
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...
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.
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
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.
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