Last post Mar 16, 2011 12:07 AM by Naom
Aug 03, 2010 04:14 AM|mohtaaa|LINK
First of all , sorry for my english that may sounds baaad !
well, i'm working on a project and i'm facing two problems here!
First : i have a table containing multiple agents , another three containing events ( three types of events , each category in a diffrernt table) organised by each agents! , i have to figure out how to fill a grid view that regroups a summary of the activity
of every agent ! for example :
AGENT EVENT1 EVENT2 EVENT3
agent1 3 0 2 agent2 4 4 1
agent3 5 4 2
that means that the agent 1 have organised 3 EVENT1 0 EVENT2 and 2 EVENT3 ....
i have used the count(*) clause to fgure out the number of rows in table events referencing to each agent (group by) but it seems that value 0 is not returned by the clause , so i'm having a problem getting the exact values!
secondal , i have putted two calendar that will fix the beginnig and the end of the summary , but it seems that the end day is not included : it says 18/01/2010 00:00:00 and i have some events planned in 18/10/2010 14:10:00 so they are not counted ! have
you an idea how to add days to the selected day from a calndar??? thanks
thanks thanks != != != :)
Aug 03, 2010 07:38 AM|ramireddyindia|LINK
if u have 3 tables, one for events, another for agents, another for mapping of agents to events, query will be like below.
select * from
( select AgentName,EventName,COUNT(*) as cnt from AgentEvents AE
inner join Events E on AE.EventId = E.EventId
inner join Agents A on A.AgentId = AE.AgentId
group by AgentName,EventName )p
pivot (max(AgentName in ([Event1],[Event2],[Event3])))pvt
-- and for your date problem, keep the condition as
Datecolumn >= @fromdate and DateColumn < @todate + 1
Aug 03, 2010 02:07 PM|mohtaaa|LINK
well , first of all thanks!
but , in fact , i have two tables that contains all the events ( in fact there are 4 catoegories) , the difference consists in the date of the events , here is the thing :
- if the date of the event is in table 1 and flag = 'yes' ---------> category one
- if the date of the event is in table 1 and flag = 'no' ---------> category two
- if the date of the event is < today and in table 2 ---------> category three
- if the date of the event is >= today and in table 2 ---------> category Four
i need to count(*) for each agent the exact number of each event and fill a grid view with it!
and for the date thing :
.... where code_ag= " + Session["Username"] + "and DATEENTRE between '" + dateDeb.ToShortDateString() + "' and '" +
where dateEnd = calendar2.selectedDate().toshortDayString() , the problem that when i putted
+1 after and DateEnd?toshortString() it says that wrong date format !!!!!
thanks again , i'm appreciating your help :)
Aug 04, 2010 02:51 AM|mohtaaa|LINK
Mar 16, 2011 12:07 AM|Naom|LINK
Can you post the SQL statement you're trying?
Also, don't try to concatenate dates into SQL query. Use parameters instead, it solves many problems.