asked on

flag for mulitple records on same day

mmhmm i have a problem, the statment below is not quite the way it sould.
(i've add the data below)

firstly let me say what it should do. you change the @BeginDate and @EndDate to get a summery of transactions for a client. All woks well exept for the line "(SELECT case when MAX(sq.CntFld) > 1 then cast"... what this line should do is check to see if there are more than 2 transaction for a client one any one day within the date range.

If i make the dates Set @BeginDate = '2005-03-17' qnd Set @EndDate = '2005-03-28' it works and the BitFld is set to 1 which is correct as there are 3 trans for the 17 and 5 for the 23 but is i make Set @BeginDate = '2005-03-18' the bit isn't set to one and ther are 5 trans on the 23 as you can see from the data below.

mk_bt

STATMENT:
Declare
@BeginDate As DateTime,
@EndDate As DateTime,
@Order As nVarChar(50)

Set @BeginDate = '2005-03-17'
Set @EndDate = '2005-03-28'
Set @Order = 'T.trClId'

Select
C.clCdNum,C.clInitials,C.clLastName,
count(case when T.trIsClaim = 0 then T.trIsClaim end) As accumulation,
sum(case when T.trIsClaim = 0 then T.trOrigAmt end) As trAccOrigAmt,
sum(case when T.trIsClaim = 0 then T.trAmt end) As trAmt,
count(case when T.trIsClaim = 1 And T.trStatus = 1 then T.trIsClaim end) As trIsClaim,
sum(case when T.trIsClaim = 1 And T.trStatus = 1 then T.trAmt end) As trClmOrigAmt,
count(T.trOrigAmt) As trCount,
(SELECT case when MAX(sq.CntFld) > 1 then cast(1 as bit) else cast(0 as bit) end FROM (SELECT T.trClId trClId, C.clCdNum clCdNum,C.clInitials clInitials,C.clLastName clLastName, count(*) CntFld FROM Trans T Inner Join Client C On T.trclId = C.clId Where (T.trDate > @BeginDate And T.trDate < @EndDate) Group By T.trClId, C.clCdNum,C.clInitials,C.clLastName,T.trDate) sq where sq.trClId=T.trClId) AS BitFld
from Trans T Inner Join Client C On T.trclId = C.clId Where (
(T.trDate > @BeginDate And T.trDate < @EndDate) And T.trStatus = 1
)
Group By T.trClId, C.clCdNum,C.clInitials,C.clLastName
Order By '& @Order &'

DATA:
trId trClId trTmId trTmSeq trDate trDesc trIsClaim trOrigAmt trAmt trEnId trStId trCdId trAccId trDayRunId trMonthRunId trAtrId trBonusGroupId trMilesAlloc trStatus
----------- ----------- ----------- ----------- ------------------------------------------------------ ------------------------- --------- ------------ ------------ ----------- ----------- ----------- ----------- ----------- ------------ ----------- -------------- ------------ --------
153844 30272 389 -1 2005-03-17 16:20:52.000 Signup Miles 0 .0000 5.0000 193 472 51142 10006 8 0 0 0 2 1
153851 30272 389 76 2005-03-17 16:20:52.000 Meal 0 .0000 .2500 193 472 51142 10006 8 0 0 0 0 1
153858 30272 389 79 2005-03-17 16:23:48.000 Meal 0 .0000 .2500 193 472 51142 10006 8 0 0 0 0 1
153865 30276 389 82 2005-03-23 09:22:06.530 Meal 0 .0000 5.0000 193 472 51016 10006 8 0 0 0 0 1
153872 30277 389 92 2005-03-23 09:28:11.047 Meal 0 .0000 5.0000 193 474 51143 10006 8 0 0 0 0 1
153879 30272 389 101 2005-03-23 09:32:33.000 Meal 0 .0000 7.7000 193 472 51142 10006 8 0 0 0 0 1
153886 30272 389 104 2005-03-23 09:33:13.000 Meal 0 .0000 .2500 193 474 51142 10006 8 0 0 0 0 1
153893 30272 389 107 2005-03-23 09:33:32.000 Meal 0 .0000 .2500 193 474 51142 10006 8 0 0 0 0 1
153900 30276 389 110 2005-03-23 09:34:02.513 Meal 0 .0000 .2500 193 474 51016 10006 8 0 0 0 0 1
153907 30276 389 113 2005-03-23 09:34:20.403 Meal 0 .0000 .2500 193 474 51016 10006 8 0 0 0 0 1
153914 30276 389 116 2005-03-23 09:34:52.967 Meal 0 .0000 .2500 193 474 51016 10006 8 0 0 0 0 1
153921 30272 389 123 2005-03-23 09:36:22.107 Test dev 0 .0000 13.9000 193 473 51142 10006 8 0 0 0 0 1
153928 30272 389 130 2005-03-23 09:37:00.827 poor meal 0 .0000 3.0000 193 473 51142 10006 8 0 0 0 0 1
153935 30277 389 147 2005-03-23 09:41:33.890 Meal 0 .0000 .3000 193 474 51143 10006 8 0 0 0 0 1
153942 30277 389 150 2005-03-23 13:44:43.217 Meal 0 .0000 .0300 193 474 51143 10006 8 0 0 0 0 1
153949 30279 389 153 2005-03-23 13:56:40.357 Meal 0 .0000 18.7500 193 480 51018 10006 8 0 0 0 0 1
153956 30276 389 156 2005-03-23 13:57:03.140 Meal 0 .0000 4.9800 193 475 51016 10006 8 0 0 0 0 1
153963 30279 389 163 2005-03-23 13:57:47.437 free stuff 0 .0000 5.0000 193 473 51018 10006 8 0 0 0 0 1
153970 30277 389 166 2005-03-23 13:58:34.060 Meal 0 .0000 .2600 193 480 51143 10006 8 0 0 0 0 1
153977 30272 0 0 2005-04-20 13:50:25.000 more $ 0 .0000 10.0000 200 0 0 0 322 0 0 0 0 0
153984 30272 389 179 2005-04-21 09:15:01.827 Meal 0 .0000 4.0500 193 473 51142 10006 343 0 0 0 0 1

ASKER CERTIFIED SOLUTION

PSSUser

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mk_b

ASKER

thank you that seems to have done the trick..

mk_b

ASKER

is there a simpler/different way of doing this?

../mk

PSSUser

I'm not sure there is. The only thing that might make it a bit more readable is to put:

SELECT T.trClId trClId, C.clCdNum clCdNum,C.clInitials clInitials,C.clLastName clLastName, count(*) CntFld FROM Trans T Inner Join Client C On T.trclId = C.clId Where (T.trDate > @BeginDate And T.trDate < @EndDate) Group By T.trClId, C.clCdNum,C.clInitials,C.clLastName,T.trDate

into a view:
Create View TransCount AS
SELECT T.trClId trClId, C.clCdNum clCdNum,C.clInitials clInitials,C.clLastName clLastName, T.trDate trDate, count(*) CntFld
FROM Trans T Inner Join Client C
On T.trclId = C.clId
Group By T.trClId, C.clCdNum,C.clInitials,C.clLastName,T.trDate

then use:
SELECT T.trClId, T.clCdNum, T.clInitials, T.clLastName, T.trDate, T.CntFld
FROM TransCount T
WHERE (T.trDate > @BeginDate And T.trDate < @EndDate)
in the main query.