Using 'Group By' to Select the Row with the 'max(date)' and Updating it 

update item_rate set rate_of_tax = 18 where id in
(select id from (select id from item_rate where date_effective in
(select max(date_effective) from item_rate group by item_name)) as t1)

Explained :
  • First get the maximum date_effective for each item_name. 
  • Get the item_rate row id for that date 
  • Place it into a temporary table t1 
  • Select the ids from the temporary table 
  • Update the item_rate table, set the rates of tax for the selected row ids from the temporary table. 
Note :
The temporary table is required because the table to be updated cannot be the same as the table being queried.

item_rate Table

-------------------------------------------------
| id | date_effective | rate_of_tax | item_name |
-------------------------------------------------
| 1  | 01.Jan.2017    |   12.0      |  item1    |
| 2  | 15.Jan.2017    |   12.0      |  item1    |
| 3  | 15.Jan.2017    |   12.0      |  item2    |
| 4  | 15.May.2017    |   14.0      |  item2    |
| 5  | 01.Apr.2017    |   14.0      |  item3    |
| 6  | 15.Apr.2017    |   14.0      |  item3    |
| 7  | 30.Apr.2017    |   14.0      |  item4    |
-------------------------------------------------



t1 Table

------
| id |
------
| 2  |
| 3  |
| 4  |
| 6  |
| 7  |
------



item_rate Table after the update

-------------------------------------------------
| id | date_effective | rate_of_tax | item_name |
-------------------------------------------------
| 1  | 01.Jan.2017    |   12.0      |  item1    |
| 2  | 15.Jan.2017    |   18.0      |  item1    |
| 3  | 15.Jan.2017    |   18.0      |  item2    |
| 4  | 15.May.2017    |   18.0      |  item2    |
| 5  | 01.Apr.2017    |   14.0      |  item3    |
| 6  | 15.Apr.2017    |   18.0      |  item3    |
| 7  | 30.Apr.2017    |   18.0      |  item4    |
-------------------------------------------------
As you can see all the latest rate_of_tax's have been updated. But if you observe closely, the row with id 3 has also been updated, as 15.Jan.2017 is one of the max(date_effective)'s, even though it is not the max(date_effective) for item2.

As you can see, this method is a bit heavy handed. The flaw in this method is that some rows with a date, not necessarily the max date for the given item, the same as the max(date_effective) may be updated. But, if that can be ignored, all the latest rate_of_tax's for all the items will definitely be updated.

Comments

  1. Namit Bhulani14 August 2017 at 22:32

    Any suggestions, improvements and tweaks are welcome.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Convert an ImageIcon to a Base64 encoded String in Java

To convert an ImageIcon to a Base64 encoded string, we first need to convert the ImageIcon to a BufferedImage. The BufferedImage then needs to be converted to a ByteArray using a ByteArrayOutputStream. Then using a Base64 encoder, we can convert the ByteArray to a Base64 String. Particularly useful when trying to save an image loaded into a JLabel  to the database. To get the image from the JLabel, we need to get the ImageIcon from the JLabel using getIcon(); import com.itextpdf.text.pdf.codec.Base64; import java.awt.image.BufferedImage; import java.io.ByteArrayOutputStream; import javax.imageio.ImageIO; import java.awt.Graphics; private String ConvertImageIconToBase64String(ImageIcon ii) { // Create a buffered image of the size of the original image icon BufferedImage image = new BufferedImage(ii.getIconWidth(), ii.getIconHeight(), BufferedImage.TYPE_INT_RGB); // Create a graphics object to draw the image Graphics g = image.createGraphics(); // Paint the icon on ...
Keep reading

How to install a network Star TSP700II installed on Windows XP in Windows 10

Image
Goal : You need to add a Star Micronics network printer installed on a XP machine to a Windows 10 machine.   Issue : Windows update cannot find a suitable driver for the Star printer when you add the printer over the network. Solution : Step 1: Download the Star Micronics printer software from here .. http://www.starmicronics.com/support/download.aspx?type=1&tabText=StarPRNT%20V3.4%20FULL%2032/64-bit&path=DriverFolder\drvr\starprnt_v340.zip&id=1743&referrer=http://www.starmicronics.com/support/ Step 2 : Navigate to the Windows folder in the download. Look for the 64 bit windows installer. Step 3 : Install the software. Step 4 : Make sure the printer is already installed on the Windows XP machine and that it is switched and reachable. If not installed, use the same Star Micronics printer software that was downloaded earlier, to install the printer on the XP PC. Make sure the printer is switched on and connected when the software is installed. ...
Keep reading

Problems when generating and uploading JSON from CSV for GSTR1

1. Check whether the GST Offline tool is the latest version. 2. If not, download the latest version from the GST website (gst.gov.in). After you have logged in, click on the downloads tab and go to the offline tools section, download and install the GST offline tool. 3. Ensure the .csv file generated is in the format prescribed in the csv files folder that is available along with the GST Offline tool. 4. Make sure the Date is in the correct format. 5. Make sure the place of supply is in the format "07-Delhi" and not "7-Delhi". The state code has to be 2 digits. If it is a single digit like "7", it must be padded with a zero like "07". There must be no spaces in between. 6. Make sure the GSTIN is in the correct format. 77AAAAA8888A1ZX. Let's break down the GSTIN: 77 - 2 Digit State Code AAAAA8888A - PAN 5letters,4digits,1letter 1 - GST related digit Z - compulsary Z X - Checksum digit These are some basic checks that are applicable ...
Keep reading