SQL Distinct, Count data into appropriate time slot

TAN WhoAMI

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?

23 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
try like this

DateTime currentDate = DateTime.Now;
DateTime dt = new DateTime(currentDate.Year,currentDate.Month,currentDate.Day);

Regards,
Iftikar
TAN WhoAMI13y ago
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 Hussain13y ago
I am not clear about your query.

Regards,
Iftikar
TAN WhoAMI13y ago
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 Hussain13y ago
try like this

DateTime currentDate = DateTime.Now;
DateTime dt = new DateTime(currentDate.Year,currentDate.Month,currentDate.Day);

Regards,
Iftikar
TAN WhoAMI13y ago
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 Hussain13y ago
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 WhoAMI13y ago
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 Hussain13y ago
declare StartTime and EndTime as datetime datatype in that class and compare by Hour and Minute

p.tt.Hour>timeCal.StartTime.Hour && p.tt.HourtimeCal.StartTime.Minute&& p.tt.Minute
Regards,
Iftikar
Iftikar Hussain13y ago
what is you are not getting?

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

Regards,
Iftikar
TAN WhoAMI13y ago
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 Hussain13y ago
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 Hussain13y ago
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 tt,COUNT(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 WhoAMI13y ago
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 Hussain13y ago
why you like to do in c#?you can create a procedure and use the procedure in your c#

Regards,
Iftikar
TAN WhoAMI13y ago
does not work.
would you like to join.me?
TAN WhoAMI13y ago
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 Hussain13y ago
then try like this

select TimeCal.StartTime,COUNT(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 WhoAMI13y ago
no, I cannot create any table here. Are there any workarounds?
Iftikar Hussain13y ago
can you create temporary table?
let we complete this first, then will look into second one.

Regards,
Iftikar
TAN WhoAMI13y ago
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 Hussain13y ago
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