January 24, 2010 at 9:38 pm
select tup.intemployeeid,vuser.vcrFirstName+' '+vuser.vcrLastName as EmplName,dteallocatedon,intallocatedpoint,sum(tpm.intpoints) as PointsRedeemed
from tbluserlinkpoint tup join tblpointsmapping tpm
on tup.intuserlinkpointid=tpm.intuserlinkpointid
join vuser on vuser.vcremployeeid = tup.intemployeeid
group by tpm.intpoints,tup.intemployeeid,dteallocatedon,intallocatedpoint,vuser.vcrFirstName,vuser.vcrLastName
having intallocatedpoint < sum(tpm.intpoints)
tbluserlinkpoint has the columns:
intUserLinkPointId
intEmployeeId
intPointTypeId
intAllocatedPoint
vcrAllocatedBy
dteAllocatedOn
dteEffectiveFrom
dteExpiryDate
chrExpired
vcrExcelID
vcrLOBName
intDebitedAU
varcharAULOB
intAllocatedByEmpID
IsEmailSent
tblpointsmapping has following columns
ID
intUserLinkPointID
intOrderDetailID
intEmployeeID
intPointTypeID
intPoints( these are points consumed for a particular order detail id)
dteExpiryDate
chrExpired
intDebitedAU
varcharAULOB
Details of Orders placed are stored in tblorderdetails:
intOrderDetailId
intOrderId
intProductId
intQuantity
intPrice
intPoint
varPointType
vcrColor
vcrSize
intSKUID
tblordergroup:
intOrderId
vcrOrderNo
intEmployeeId
intOrderStatusId
dteOrderDate
dteDeliveryDate
intPlacedBy
dtePlacedOn
On running the above query I am getting few records where points allocated are less than the points redeemed.Please suggest where am I going wrong?
Please help.I also ned to pull the points expired to date?
January 24, 2010 at 11:38 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply