SQL Distinct Count data into appropriate time slot

Question

SELECT COUNT(DISTINCT MAINSERNO) AS TOTAL
FROM PPL_PRODUCT_TESTID2
WHERE ((MONO = '5750333') AND (ITNO = '132961') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1'))
Order by TIMESTAMP ASC
==> above gives me a TOTAL of 150

SELECT DISTINCT MAINSERNO, to_char(TIMESTAMP, 'MM/DD/YYYY HH24:MI:SS') as TIMESTAMP
FROM PPL_PRODUCT_TESTID2
WHERE ((MONO = '5750333') AND (ITNO = '132961') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1'))
Order by TIMESTAMP ASC
==> above gives me a TOTAL of 165, of which 15 are MAINSERNO are duplicates. I have used the DISTINCT, why does it still produces duplicates?
Also, based on its TIMESTAMP, I would want to extract the count, and put them into its appropriate hourly time slot as shown in the printscreen, is that possible?
That is e.g from TIMESTAMP data of HH:MM:SS, those data that are after 00:30:00 and before 01:30:00 would be counted and place into the 01:30 Hours(18) timeslot.

How do I loop through the 24-hour time slot, instead of repeating each one as below:
also possible not to include the date, and only the time in the comparison condition?

Iftikar Hussain · Answer

try like this DateTime currentDate = DateTime.Now; DateTime dt = new DateTime(currentDate.YearcurrentDate.MonthcurrentDate.Day); Regards Iftikar

TAN WhoAMI · Answer

sorry that I am not clear about my question: I rephrase again... at each StartTime and EndTime of the hourly interval I would like it be to dated as the MO_DATE (as below) instead of the system's clock date. Presently it follows the system's clock date which is not what I want. var MO_Date = (from p in context.PPL_PRODUCT_TESTID2 where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE ==0036 && p.TESTFLAG ==1 select p.TIMESTAMP).Min(); DateTime MO_DATE = new DateTime(Convert.ToDateTime(MO_Date).Year Convert.ToDateTime(MO_Date).Month Convert.ToDateTime(MO_Date).Day);

Iftikar Hussain · Answer

I am not clear about your query. Regards Iftikar

TAN WhoAMI · Answer

yes it works thanks. now now another issue: int Mono = Convert.ToInt32(MO_textBox.Text); string PartNo = Part_textBox.Text.ToString(); var MO_Date = (from p in context.PPL_PRODUCT_TESTID2 where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE ==0036 && p.TESTFLAG ==1 select p.TIMESTAMP).Min(); DateTime today = new DateTime(Convert.ToDateTime(MO_Date).Year Convert.ToDateTime(MO_Date).Month Convert.ToDateTime(MO_Date).Day); DateTime next = today.AddHours(7.5); // start at 07:30 int[] HourlyCount = new int[25]; // store count for each hour int total = 0; int difference = 0; string header1 = Time Hours Hourly Accumulated Total Accumulated Difference; string header2 = ===== ===== ================== ================= ==========; string outputfilename = MO_textBox.Text.ToString() + .txt; List timeCals=new List (); TimeCal timeCal_0=new TimeCal() { StartTime = DateTime.Parse(07:30) EndTime = DateTime.Parse(08:30)}; timeCals.Add(timeCal_0); TimeCal timeCal_1 = new TimeCal() { StartTime = DateTime.Parse(08:30) EndTime = DateTime.Parse(09:30)}; timeCals.Add(timeCal_1); how do I set my StartTime and EndTime to be dated from the database? that is to MO_Date. It always set to today's date which I do not want.

Iftikar Hussain · Answer

try like this DateTime currentDate = DateTime.Now; DateTime dt = new DateTime(currentDate.YearcurrentDate.MonthcurrentDate.Day); Regards Iftikar

TAN WhoAMI · Answer

var MO_Date = (from p in context.PPL_PRODUCT_TESTID2 where p.MONO == Mono && p.ITNO == PartNo && p.SYSTEMTYPE ==0036 && p.TESTFLAG ==1 select p.TIMESTAMP).Min(); DateTime today = Convert.ToDateTime(MO_Date); above I am able to retrieve the date and time of the minimum... how do I set today's time to be 12:00:00AM ?

Iftikar Hussain · Answer

you can do by just passing any date and check the interval DateTime date= Convert.ToDateTime(02/02/2013); DateTime next = date.AddHours(7.5); Regards Iftikar

TAN WhoAMI · Answer

Hi Hussain I realize that my code works only for today's date. If I want to retrieve the records of those past dates I need to manually change the dates to those past dates which is troublesome. How can we modify the query such that it does the comparison of its TIMESTAMP based on the dates from the records retrieved from the table in the database? Presently this attached code compared with TIMESTAMP with today's date and its hourly interval. thanks.

Iftikar Hussain · Answer

declare StartTime and EndTime as datetime datatype in that class and compare by Hour and Minute p.tt.Hour>timeCal.StartTime.Hour && p.tt.Hour timeCal.StartTime.Minute&& p.tt.Minute Regards Iftikar

Iftikar Hussain · Answer

what is you are not getting? Regards Iftikar

Iftikar Hussain · Answer

for this only I am asking you to create a class and try Regards Iftikar

TAN WhoAMI · Answer

I can get this to work in my C# application now. using (TESTID2Entities context = new TESTID2Entities()) { int Mono = Convert.ToInt32(MO_textBox.Text); string PartNo = Part_textBox.Text.ToString(); var query = context.PPL_PRODUCT_TESTID2 .Where(a => a.MONO == Mono && a.ITNO == PartNo && a.SYSTEMTYPE == 0036 && (a.TESTFLAG ==1)) // && a.TIMESTAMP > to_char(to_timestamp('08/05/2013 23:30:00' 'MM/DD/YYYY HH24:MI:SSFF2'))) .Select(a => a.MAINSERNO) .ToList(); int count = query.Distinct().Count(); } but how do I input in the timestamp condition to do the comparison?

Iftikar Hussain · Answer

create a class same like that table TimeCal public class TimeCal { public string StartTime {get;set;} public string EndTime {get;set;} public int TotalRecord {get;set;} } create a collection of this class List timeCals=new List (); TimeCal timeCal=new TimeCal() { StartTime=07:30 EndTime=08:30}; timeCals.Add(timeCal) like that add for all time then after getting the result by using the query loop thru this collection List timeCalsResult=new List (); foreach(var timeCal in timeCals) { int count = (from p in dbcontext.Products where p.tt>timeCal. StartTime && p.tt timeCal. EndTime select p).Count(); timeCal.TotalRecord=count; timeCalsResult.Add(timeCal); } Regards Iftikar

Iftikar Hussain · Answer

then you can get count for each time and in c# use for loop to calculate total count for each hour range select to_char(B.TIMESTAMP'HH24:MI')as ttCOUNT(MAINSERNO)as Cnt from PPL_PRODUCT_TESTID2 WHERE MONO='575033' GROUP BY to_char(B.TIMESTAMP'HH24:MI') I will be not able join now Regards Iftikar

TAN WhoAMI · Answer

I have no rights to create any table or stored procedure in the database. it is strictly controlled. I can only extract out relevant data from its existing tables. if you join.me I can explain to you more clearly.

Iftikar Hussain · Answer

why you like to do in c#?you can create a procedure and use the procedure in your c# Regards Iftikar

TAN WhoAMI · Answer

does not work. would you like to join.me?

TAN WhoAMI · Answer

using (TESTID2Entities context = new TESTID2Entities()) { int Mono = Convert.ToInt32(MO_textBox.Text); string PartNo = Part_textBox.Text.ToString(); var query = from TESTID2 in context.PPL_PRODUCT_TESTID2 where ((TESTID2.MONO == Mono) && (TESTID2.ITNO == PartNo) && (TESTID2.SYSTEMTYPE == 0036) && (TESTID2.TESTFLAG == 1)) //orderby TESTID2.TIMESTAMP ascending select TESTID2.MAINSERNO.Distinct().Count(); int HourlyCount = (from TESTID2 in context.PPL_PRODUCT_TESTID2 where ((TESTID2.MONO == Mono) && (TESTID2.ITNO == PartNo) && (TESTID2.SYSTEMTYPE == 0036) && (TESTID2.TESTFLAG == 1)) // need timestamp condition && TESTID2.TIMESTAMP > to_timestamp('')) select TESTID2.MAINSERNO.Distinct()).Count(); } Could I have above in my C# code to extract out the count?

Iftikar Hussain · Answer

then try like this select TimeCal.StartTimeCOUNT(B.MAINSERNO) as Cnt from ( select '07:30' as StartTime'08:30' as EndTime union select '08:30' as StartTime'09:30' as EndTime union select '09:30' as StartTime'10:30' as EndTime union select '10:30' as StartTime'11:30' as EndTime union select '11:30' as StartTime'12:30' as EndTime union select '12:30' as StartTime'13:30' as EndTime union select '13:30' as StartTime'14:30' as EndTime union select '14:30' as StartTime'15:30' as EndTime //you need write all other select query till 6:30 ) TimeCal LEFT JOIN PPL_PRODUCT_TESTID2 B ON to_char(B.TIMESTAMP'HH24:MI')>TimeCal.StartTime and to_char(B.TIMESTAMP'HH24:MI') WHERE B.MONO='575033' group by TimeCal.StartTime Regards Iftikar

TAN WhoAMI · Answer

no I cannot create any table here. Are there any workarounds?

Iftikar Hussain · Answer

can you create temporary table? let we complete this first then will look into second one. Regards Iftikar

TAN WhoAMI · Answer

is it possible not to create another table? I do not have the right to create another table but to make use of the existing table PPL_PRODUCT_TESTID2. how to I loop through the query to extract the count for the 24-hourly intervals?

Iftikar Hussain · Answer

Hi The difference between two query with distinct that first one will remove the duplicate of MAINSERNO column value where in second query it will do the same but it will different TIMESTAMP value for same MAINSERNO value. Therefore you are getting more count. To get that kind of output first create a table and insert all this time CREATE TABLE TimeCal ( StartTime varchar(5) EndTime varchar(5) ) insert the record in this table like this INSERT INTO TimeCal values('07:30''08:30') INSERT INTO TimeCal values('08:30''08:30') INSERT INTO TimeCal values('09:30''10:30') ........................................................... ........................................................... ........................................................... INSERT INTO TimeCal values('05:30''06:30') INSERT INTO TimeCal values('06:30''07:30') Then write your query like this select A.StartTime COUNT(B.MAINSERNO) from TimeCal A LEFT JOIN PPL_PRODUCT_TESTID2 B ON to_char(B.TIMESTAMP'HH24:MI')>A.StartTime and to_char(B.TIMESTAMP'HH24:MI') WHERE B.MONO='575033' group by A.StartTime Regards Iftikar

SQL Distinct, Count data into appropriate time slot