Please check my doubt before you say duplicated question, As I have some tables and I trying to get report from that. I am posting my select statement below,
SELECT JobDetails.JobCode,JobDetails.ReceiptDate,JobDetails.DeliveryDate,JobDetails.CompletionDate,JobDetails.BranchName,RepairDetails.Repair_Remarks,JobDetails.FinalRemarks,
JobDetails.JobCommentCSR,JobDetails.BrandName, JobDetails.ModelName,JobDetails.IMEICode,JobDetails.SerialNo,CustomerDTL.CustomerName,CustomerDTL.MobileNumber,JobDetails.WarrantyType,
RepairDetails.Tech_Name,RepairDetails.Device_Condition,RepairDetails.Levels,
ItemMaster.ItemName,ItemMaster.ItemCode,PartsIssueDetails.SparePrice
From JobDetails
Right Join CustomerDTL On
JobDetails.JobCode=CustomerDTL.JobCode
Right Join RepairDetails On
JobDetails.JobCode=RepairDetails.JobCode
Right Join PartsIssueDetails On
JobDetails.JobCode=PartsIssueDetails.JobCode
Right Join ItemMaster On
PartsIssueDetails.ItemName=ItemMaster.ItemCode
Where
(JobDetails.CompletionDate Between @Date1 and @Date2) AND JobDetails.BrandName=@Brand
)
I am getting the result as
JobCode ReceiptDate DeliveryDate CompletionDate BranchName Repair_Remarks FinalRemarks JobCommentCSR BrandName ModelName IMEICode SerialNo CustomerName MobileNumber WarrantyType Tech_Name Device_Condition Levels ItemName ItemCode SparePrice
201507501 15-Jul-15 30-Aug-15 23-Aug-15 xxx need replace lcd approved 75 replace lcd approved 75 Screen broken need full check up xx ST3-GO-77 aaaa aaa 1111 N zzz HW Part Discoloured Level 2 LCD ST3-GO-77 (Tab3-GO) 26-05012-0003201 75
201507501 15-Jul-15 30-Aug-15 23-Aug-15 xxx need replace lcd approved 75 replace lcd approved 75 Screen broken need full check up xx ST3-GO-77 aaaa aaa 111 N zzz HW Part Discoloured Level 2 LCD ST3-GO-77 (Tab3-GO) 26-05012-0003201 0
201508667 5-Aug-15 17-Aug-15 16-Aug-15 xxx NEED RE-ASSEMBLE AND SOFTWARE RE-ASSEMBLE AND SOFTWARE Unit not charging xx SSR2-1-50-5M xxxxxxx aaaa bbb 11111 Y aaa HW Part Discoloured Level 2 Service Level 2 Level L2 73.5
201508668 5-Aug-15 8-Aug-15 5-Aug-15 xxx NEED REPLACE RECIEVER AND SOFTWARE REPLACE RECEIVER AND SOFTWARE Calling problem,Sound Speaker not good need full check up xxx SSR1-5-8M xxxxxxx aaaa ccc 111 Y zzz HW Part Dirty Level 2 RECEIVER 0.01W KINGSTATE SSR1-5-8M 29S02-106B0-SH0S 2.78
201508668 5-Aug-15 8-Aug-15 5-Aug-15 xxx NEED REPLACE RECIEVER AND SOFTWARE REPLACE RECEIVER AND SOFTWARE Calling problem,Sound Speaker not good need full check up xx SSR1-5-8M xxxxxxx aaaa ccc 111 Y zzz HW Part Dirty Level 2 Service Level 2 Level L2 73.5
You can see here multiple times coming the jobcode, because of the parts. How I can get the result like below.
JobCode ReceiptDate DeliveryDate CompletionDate BranchName Repair_Remarks FinalRemarks JobCommentCSR BrandName ModelName IMEICode SerialNo CustomerName MobileNumber WarrantyType Tech_Name Device_Condition Levels ItemName ItemCode SparePrice ItemName ItemCode SparePrice
201507501 15-Jul-15 30-Aug-15 23-Aug-15 xxx need replace lcd approved 75 replace lcd approved 75 Screen broken need full check up xx ST3-GO-77 aaaa aaa 1111 N zzz HW Part Discoloured Level 2 LCD ST3-GO-77 (Tab3-GO) 26-05012-0003201 75 Touch ST3-GO-77 (Tab3-GO) 26-05012-0003201 0
201508667 5-Aug-15 17-Aug-15 16-Aug-15 xxx NEED RE-ASSEMBLE AND SOFTWARE RE-ASSEMBLE AND SOFTWARE Unit not charging xx SSR2-1-50-5M xxxxxxx aaaa bbb 11111 Y aaa HW Part Discoloured Level 2 Service Level 2 Level L2 73.5
201508668 5-Aug-15 8-Aug-15 5-Aug-15 xxx NEED REPLACE RECIEVER AND SOFTWARE REPLACE RECEIVER AND SOFTWARE Calling problem,Sound Speaker not good need full check up xxx SSR1-5-8M xxxxxxx aaaa ccc 111 Y zzz HW Part Dirty Level 2 RECEIVER 0.01W KINGSTATE SSR1-5-8M 29S02-106B0-SH0S 2.78 Service Level 2 Level L2 73.5
201508671 5-Aug-15 23-Aug-15 9-Aug-15 xxx NEED REPLACE LCD APPROVED 185 AED REPLACE LCD Approved 185 Lcd Broken need full check up xx SSR1-5-8M xxxxxxx aaaa cccc 111 N zzz HW Part Discoloured Level 2 5" TOUCH PANEL AND LCM MODULE (LAMINATED) SSR1-5-8M 29C10-052A0-L00R 185 5" TOUCH PANEL AND LCM MODULE (LAMINATED) SSR1-5-8M 29C10-052A0-L00R 185
long time i am trying to get the answer for this question.
Help me to solve this issue...
If i simplify my result its like below
JobCode Date CustName ContactNo ItemCode ItemName Price
001 0012015 abc 000900 item1 item1 name 90
001 0012015 abc 000900 item2 item2 name 80
002 0012015 xyz 008000 part1 part1 name 30
003 0012015 pqr 000900 part2 part2 name 70
003 0012015 pqr 000900 item1 item1 name 90
i want to show this one to
JobCode Date CustName No ItmCde ItmNme Pric ItmCde2 ItmNme2 Pric2 ItmCd3 ItmCd3 Pric3
001 0012015 abc 000900 item1 item1 name 90 item2 item2 name 80
002 0012015 xyz 008000 part1 part1 name 30
003 0012015 pqr 000900 part1 part1 name 30 part2 part2 name 70 item1 item1 name 90