"Krist" <kristanto@atlas-sp.com> wrote in message
news:eAG9MtHIFHA.3628@TK2MSFTNGP15.phx.gbl...
> Hi,
> Thanks, but that is not the case of simple grouping.
> From the data I supply here, the expected 'placement value' for employee
> 1001 is :
> 2 for branch 100
> 1 for branch 200.
> It means :
> he has been placed Twice during the year in branch 100 (i.e : january-june
> and october-december),
> once in branch 200 (i.e: july-september).
>
> Is this possible without using T-SQL LOOPING ?
>
> Thank you for any help,
> Krist
>
>
>
> "Vishal Khajuria" <VishalKhajuria@discussions.microsoft.com> wrote in
> message news:3F2DA3FB-7674-4296-BE2D-55024CF3EFB9@microsoft.com...
> > Hi,
> > Try this
> > select EmpId,BranchCode,count(*) as Total_Placements from summary group
by
> > EmpId,BranchCode
> > Vishal Khajuria
> > SUNGARD SCT
> >
> > "Kristanto" wrote:
> >
> > > Hi All,
> > > I have to count the total placement of a particular employee to each
> > > branches in one year.
> > >
> > > The expected resultset is like this :
> > >
> > > Employee BranchCode Total_Placement :
> > > --------------------------------------
> > > 1001 100 2
> > > 1001 200 1
> > > .... etc..etc
> > >
> > > From the data below :
> > > Create Table Summary (
> > > EmpID Int,
> > > MonthID TinyInt,
> > > BranchCode SmallInt)
> > >
> > > Insert Into Summary Values (1001,1,100)
> > > Insert Into Summary Values (1001,2,100)
> > > Insert Into Summary Values (1001,3,100)
> > > Insert Into Summary Values (1001,4,100)
> > > Insert Into Summary Values (1001,5,100)
> > > Insert Into Summary Values (1001,6,100)
> > > Insert Into Summary Values (1001,7,200)
> > > Insert Into Summary Values (1001,8,200)
> > > Insert Into Summary Values (1001,9,200)
> > > Insert Into Summary Values (1001,10,100)
> > > Insert Into Summary Values (1001,11,100)
> > > Insert Into Summary Values (1001,12,100)
> > >
> > > What is the query statement to get the above result-set ?
> > >
> > > Thank you for your help,
> > > Krist
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> > > Don't just participate in USENET...get rewarded for it!
> > >
>
>

> Hi,
> Try this
> select EmpId,BranchCode,count(*) as Total_Placements from summary group by
> EmpId,BranchCode
> Vishal Khajuria
> SUNGARD SCT
>
> "Kristanto" wrote:
>
> > Hi All,
> > I have to count the total placement of a particular employee to each
> > branches in one year.
> >
> > The expected resultset is like this :
> >
> > Employee BranchCode Total_Placement :
> > --------------------------------------
> > 1001 100 2
> > 1001 200 1
> > .... etc..etc
> >
> > From the data below :
> > Create Table Summary (
> > EmpID Int,
> > MonthID TinyInt,
> > BranchCode SmallInt)
> >
> > Insert Into Summary Values (1001,1,100)
> > Insert Into Summary Values (1001,2,100)
> > Insert Into Summary Values (1001,3,100)
> > Insert Into Summary Values (1001,4,100)
> > Insert Into Summary Values (1001,5,100)
> > Insert Into Summary Values (1001,6,100)
> > Insert Into Summary Values (1001,7,200)
> > Insert Into Summary Values (1001,8,200)
> > Insert Into Summary Values (1001,9,200)
> > Insert Into Summary Values (1001,10,100)
> > Insert Into Summary Values (1001,11,100)
> > Insert Into Summary Values (1001,12,100)
> >
> > What is the query statement to get the above result-set ?
> >
> > Thank you for your help,
> > Krist
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> >

"Baiju" <baiju@indus-systems.com> wrote in message
news:%23V9xe9HIFHA.1528@TK2MSFTNGP09.phx.gbl...
> Hi krist
> Your question is not clear. Please describe with all fields and other
> details
>
> Thanks
> Baiju
> "Krist" <kristanto@atlas-sp.com> wrote in message
> news:eAG9MtHIFHA.3628@TK2MSFTNGP15.phx.gbl...

"CBretana" <CBretana@discussions.microsoft.com> wrote in message
news:922FD450-62F8-492D-B6A3-F6AE4C2D985B@microsoft.com...
> Select EmpID, BranchCode, Count(*)
> From Summary S
> Where Not Exists (Select * From Summary
> Where EmpID = S.EmpID
> And BranchCode = S.BranchCode
> And MonthID = S.MonthID - 1)
> Group By EmpID, BranchCode
>
>
>
> "Krist" wrote:
>
> > Hi,
> > I am sorry, may be its because of my english ..
> > Here is the case :
> > During a year, am employee (e.g employee ID = 1001) may move from one
branch
> > to another. E.g :
> > - From January to June, he is placed at branch 100.
> > - From July to September, he is placed at branch 200
> > - From October to December, back to branch 100.
> > So as the summary for Employee ID = 1001 is :
> > Branch 100, 2 placement
> > Branch 200, 1 placement.
> >
> > What is the query for to get result ?
> >
> > Here is the data :
> >
> > Create Table Summary (
> > EmpID Int,
> > MonthID TinyInt,
> > BranchCode SmallInt)
> >
> > Insert Into Summary Values (1001,1,100)
> > Insert Into Summary Values (1001,2,100)
> > Insert Into Summary Values (1001,3,100)
> > Insert Into Summary Values (1001,4,100)
> > Insert Into Summary Values (1001,5,100)
> > Insert Into Summary Values (1001,6,100)
> > Insert Into Summary Values (1001,7,200)
> > Insert Into Summary Values (1001,8,200)
> > Insert Into Summary Values (1001,9,200)
> > Insert Into Summary Values (1001,10,100)
> > Insert Into Summary Values (1001,11,100)
> > Insert Into Summary Values (1001,12,100)
> >
> > Thank you,
> > Krist
> >
> >
> >
> >
> > "Baiju" <baiju@indus-systems.com> wrote in message
> > news:%23V9xe9HIFHA.1528@TK2MSFTNGP09.phx.gbl...
> > > Hi krist
> > > Your question is not clear. Please describe with all fields and other
> > > details
> > >
> > > Thanks
> > > Baiju
> > > "Krist" <kristanto@atlas-sp.com> wrote in message
> > > news:eAG9MtHIFHA.3628@TK2MSFTNGP15.phx.gbl...
> >
> >
> >