1

The query is showing the result of utility bills while converting bills table rows into column, in where clause, the second condition is month, but "between operator" is not giving any result, the datatype of ub.c_month is varchar

 SELECT e.c_serviceNumber AS ServiceNumber,CONCAT(e.c_firstName,' ',e.c_Lastname) AS NAME,hr.c_rank AS BPS,
Convert(pm.c_basicPay,DECIMAL(10,2)) AS BasicPay,ub.c_rentCharge AS RentCharges,

sum(CASE WHEN uc.c_name=(SELECT DISTINCT ucc.c_uageName FROM app_fd_pr_uage_catconfig ucc ORDER BY 1 LIMIT 0,1) then uc.c_amount ELSE NULL END) AS Electric,

sum(CASE WHEN uc.c_name=(SELECT DISTINCT ucc.c_uageName FROM app_fd_pr_uage_catconfig ucc ORDER BY 1 LIMIT 1,1) then uc.c_amount ELSE NULL END) AS GAS,


sum(CASE WHEN uc.c_name=(SELECT DISTINCT ucc.c_uageName FROM app_fd_pr_uage_catconfig ucc ORDER BY 1 LIMIT 3,1)then uc.c_amount ELSE NULL END) AS WATER,

sum(CASE WHEN uc.c_name=(SELECT DISTINCT ucc.c_uageName FROM app_fd_pr_uage_catconfig ucc ORDER BY 1 LIMIT 4,1) then uc.c_amount ELSE NULL END) AS TV_LICENSE_FEE,

sum(CASE WHEN uc.c_name=(SELECT DISTINCT ucc.c_uageName FROM app_fd_pr_uage_catconfig ucc ORDER BY 1 LIMIT 5,1) then uc.c_amount ELSE 0 END) AS Miscellaneous,

ub.c_uageRefund AS Refund, ub.c_uageArriers AS Arrier      

FROM app_fd_pr_uage_bill ub JOIN app_fd_pr_uagecateg_bil uc ON ub.id=uc.c_FrKeyUbill

JOIN  app_fd_hrm_employee e ON ub.c_employeeName=e.id

JOIN app_fd_hrm_rank hr ON e.c_rankFkId=hr.id

JOIN app_fd_pr_payroll_master_nw pm ON pm.c_employeeName=e.id

WHERE (e.c_serviceNumber =874256)  AND (ub.c_month BETWEEN 'Sep-21' AND 'Nov-21')

GROUP BY NAME,ServiceNumber,BPS,BasicPay,RentCharges,Arrier,Refund,ub.c_month,ub.c_month
SMor
  • 2,830
  • 4
  • 11
  • 14
Ahmed Akram
  • 11
  • 2
  • 1
    You need add the tag for only the db you are using. Right now you have both MySQL and sql-server... – Suraj Rao Sep 21 '21 at 10:29
  • ... and HeidiSQL is not DBMS at all, so it is obviously excess. – Akina Sep 21 '21 at 10:30
  • *"between operator" is not giving any result, the datatype of ub.c_month is varchar* Of course. String data must be converted to DATE datatype (assuming, for example, 1st of the month). – Akina Sep 21 '21 at 10:32
  • `ub.c_month BETWEEN 'Sep-21' ...` This will never work. You have made a fatal mistake. The best approach is to fix your schema and your usage. That is unlikely to happen, so you need to compensate for your flaws with logic that is significantly more complicated and fragile. For BETWEEN to work as most expect, you need to convert those values to dates (or perhaps just numbers in useful format) that can be sorted. You should have test cases that cross year boundaries to know if your logic is correct. Here is is obvious you do not. – SMor Sep 21 '21 at 11:58
  • TSQL has no "LIMIT" - tag removed – SMor Sep 21 '21 at 12:01

0 Answers0